how to total the values in a numeric column?

I'm building a report using the matrix control. I have financial data in
different line items (Salary, Rental Expense etc) that needs to be shown by
month (so the columns have months). I have it pretty much working but can't
figure out how to sum up the numbers in each column at the bottom so I can
have a total for every month. Could anyone help?
Thanks a lot.
BobRight-click on the row field that you wish to total, in your case it
would be the Salary field. Choose Sub-total from the menu.
To format the subtotals you need to right-click->properties on the tiny
green triangle that appears on the Total cell.|||ahhh. I was thinking this must be something really simple since it's such a
common function. You can't believe how much time I had spent trying to
figure this out. Thanks a lot.
Bob
"grahamiec" <grahamrichter@.gmail.com> wrote in message
news:1123769689.664448.10490@.g14g2000cwa.googlegroups.com...
> Right-click on the row field that you wish to total, in your case it
> would be the Salary field. Choose Sub-total from the menu.
> To format the subtotals you need to right-click->properties on the tiny
> green triangle that appears on the Total cell.
>

how to total a column in a matrix?

How do you total a column in a matrix? It seems like it should be simple, but I am not getting anywhere.
- Here is some of my matrix as seen in Layout view. The Total column shown was created when I added a subtotal to the group Fields!MonthName.Value.
=Fields!MonthName.Value Total
Travel =Sum(Fields!Travel.Value)
Supplies =Sum(Fields!Supplies.Value)
Services =Sum(Fields!Services.Value)
- Here it is in Preview:
Jan Feb Total
Travel 100 200 300
Supplies 200 300 500
Services 100 200 300
- Here is what I WANT, notice column totals:
Jan Feb Total
Travel 100 200 300
Supplies 200 300 500
Services 100 200 300
Month Totals 400 700 1100
Thanks!can you create this formula:
=Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
Sum(Fields!Services.Value)
If you have 1 value by column, then its an easy solution.
=Fields!MonthName.Value Total
Travel =Sum(Fields!Travel.Value)
Supplies =Sum(Fields!Supplies.Value)
Services =Sum(Fields!Services.Value)
Month total s =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
Sum(Fields!Services.Value)
"jacobryce" <jacobryce@.discussions.microsoft.com> a écrit dans le message de
news:4CD61168-A0DD-43CA-AE53-A6429D9CD63C@.microsoft.com...
> How do you total a column in a matrix? It seems like it should be simple,
but I am not getting anywhere.
> - Here is some of my matrix as seen in Layout view. The Total column
shown was created when I added a subtotal to the group
Fields!MonthName.Value.
> =Fields!MonthName.Value Total
> Travel =Sum(Fields!Travel.Value)
> Supplies =Sum(Fields!Supplies.Value)
> Services =Sum(Fields!Services.Value)
> - Here it is in Preview:
> Jan Feb Total
> Travel 100 200 300
> Supplies 200 300 500
> Services 100 200 300
> - Here is what I WANT, notice column totals:
> Jan Feb Total
> Travel 100 200 300
> Supplies 200 300 500
> Services 100 200 300
> Month Totals 400 700 1100
> Thanks!|||Yes that works, but I just thought there would be a more 'automatic' way, like something you would do in Excel. Thanks, though.
"Jéjé" wrote:
> can you create this formula:
> =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
> Sum(Fields!Services.Value)
> If you have 1 value by column, then its an easy solution.
> =Fields!MonthName.Value Total
> Travel =Sum(Fields!Travel.Value)
> Supplies =Sum(Fields!Supplies.Value)
> Services =Sum(Fields!Services.Value)
> Month total s =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
> Sum(Fields!Services.Value)
>
> "jacobryce" <jacobryce@.discussions.microsoft.com> a écrit dans le message de
> news:4CD61168-A0DD-43CA-AE53-A6429D9CD63C@.microsoft.com...
> > How do you total a column in a matrix? It seems like it should be simple,
> but I am not getting anywhere.
> > - Here is some of my matrix as seen in Layout view. The Total column
> shown was created when I added a subtotal to the group
> Fields!MonthName.Value.
> >
> > =Fields!MonthName.Value Total
> > Travel =Sum(Fields!Travel.Value)
> > Supplies =Sum(Fields!Supplies.Value)
> > Services =Sum(Fields!Services.Value)
> >
> > - Here it is in Preview:
> > Jan Feb Total
> > Travel 100 200 300
> > Supplies 200 300 500
> > Services 100 200 300
> >
> > - Here is what I WANT, notice column totals:
> > Jan Feb Total
> > Travel 100 200 300
> > Supplies 200 300 500
> > Services 100 200 300
> >
> > Month Totals 400 700 1100
> >
> > Thanks!
>
>|||So you have 3 static rows and no row groups? You could probably add a row
group with an expression =1 and then do a grand total.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"jacobryce" <jacobryce@.discussions.microsoft.com> wrote in message
news:773526EC-6041-4EFA-ABC3-F8193E88EB04@.microsoft.com...
> Yes that works, but I just thought there would be a more 'automatic' way,
> like something you would do in Excel. Thanks, though.
> "Jéjé" wrote:
>> can you create this formula:
>> =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
>> Sum(Fields!Services.Value)
>> If you have 1 value by column, then its an easy solution.
>> =Fields!MonthName.Value Total
>> Travel =Sum(Fields!Travel.Value)
>> Supplies =Sum(Fields!Supplies.Value)
>> Services =Sum(Fields!Services.Value)
>> Month total s =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
>> Sum(Fields!Services.Value)
>>
>> "jacobryce" <jacobryce@.discussions.microsoft.com> a écrit dans le message
>> de
>> news:4CD61168-A0DD-43CA-AE53-A6429D9CD63C@.microsoft.com...
>> > How do you total a column in a matrix? It seems like it should be
>> > simple,
>> but I am not getting anywhere.
>> > - Here is some of my matrix as seen in Layout view. The Total column
>> shown was created when I added a subtotal to the group
>> Fields!MonthName.Value.
>> >
>> > =Fields!MonthName.Value Total
>> > Travel =Sum(Fields!Travel.Value)
>> > Supplies =Sum(Fields!Supplies.Value)
>> > Services =Sum(Fields!Services.Value)
>> >
>> > - Here it is in Preview:
>> > Jan Feb Total
>> > Travel 100 200 300
>> > Supplies 200 300 500
>> > Services 100 200 300
>> >
>> > - Here is what I WANT, notice column totals:
>> > Jan Feb Total
>> > Travel 100 200 300
>> > Supplies 200 300 500
>> > Services 100 200 300
>> >
>> > Month Totals 400 700 1100
>> >
>> > Thanks!
>>|||interesting idea.
I'll test it.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> a écrit dans le
message de news:ekXD0nmXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> So you have 3 static rows and no row groups? You could probably add a row
> group with an expression =1 and then do a grand total.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "jacobryce" <jacobryce@.discussions.microsoft.com> wrote in message
> news:773526EC-6041-4EFA-ABC3-F8193E88EB04@.microsoft.com...
> > Yes that works, but I just thought there would be a more 'automatic'
way,
> > like something you would do in Excel. Thanks, though.
> >
> > "Jéjé" wrote:
> >
> >> can you create this formula:
> >> =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
> >> Sum(Fields!Services.Value)
> >>
> >> If you have 1 value by column, then its an easy solution.
> >>
> >> =Fields!MonthName.Value Total
> >> Travel =Sum(Fields!Travel.Value)
> >> Supplies =Sum(Fields!Supplies.Value)
> >> Services =Sum(Fields!Services.Value)
> >> Month total s =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value)
+
> >> Sum(Fields!Services.Value)
> >>
> >>
> >>
> >> "jacobryce" <jacobryce@.discussions.microsoft.com> a écrit dans le
message
> >> de
> >> news:4CD61168-A0DD-43CA-AE53-A6429D9CD63C@.microsoft.com...
> >> > How do you total a column in a matrix? It seems like it should be
> >> > simple,
> >> but I am not getting anywhere.
> >> > - Here is some of my matrix as seen in Layout view. The Total column
> >> shown was created when I added a subtotal to the group
> >> Fields!MonthName.Value.
> >> >
> >> > =Fields!MonthName.Value Total
> >> > Travel =Sum(Fields!Travel.Value)
> >> > Supplies =Sum(Fields!Supplies.Value)
> >> > Services =Sum(Fields!Services.Value)
> >> >
> >> > - Here it is in Preview:
> >> > Jan Feb Total
> >> > Travel 100 200 300
> >> > Supplies 200 300 500
> >> > Services 100 200 300
> >> >
> >> > - Here is what I WANT, notice column totals:
> >> > Jan Feb Total
> >> > Travel 100 200 300
> >> > Supplies 200 300 500
> >> > Services 100 200 300
> >> >
> >> > Month Totals 400 700 1100
> >> >
> >> > Thanks!
> >>
> >>
> >>
>sql

How to store matrix value in database

mo tu we th fr
_______________________
morning |___|____|_____|__X__|____|
afternoon |___|_X__|_X__|__X__|____|
evening |___|____|_X __|__X__|____|

Hey guys, i have to store the values of the above table into the database however i have know idea how to design the table. Could you guys give me some suggestion.Currently i come up with the below solution.

OpenTime
-----
OpenTimeID
DateID
TimeID

Date
----
DateID
DateName

Time
----
TimeID
TimeName

I prefer to use 1 table in this case, check this table:

CREATE TABLE testDate (id int identity(1,1),TDate datetime default getdate(),
WeekDay AS DATENAME(dw,TDate),
TimeRange AS CASE WHEN DATEPART(hh,TDate) BETWEEN 4 AND 12
THEN 'Morning'
WHEN DATEPART(hh,TDate) BETWEEN 13 AND 18
THEN 'Afternoon'
ELSE 'Evening'
END,
Value varchar(100))

INSERT INTO testDate(Value) SElECT 'This is a test record'

SELECT * FROM testDate

Hope this helps