Subtotal in Matrix only shows val from 1st column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Subtotal in Matrix only shows val from 1st column

Author

Topic

AnyHelpAppreciated
Starting Member

3 Posts

Posted - 2012-09-14 : 05:06:39

Hi,

I have a matrix displaying the following information (this is what is returned from the SPROC):

http://i50.tinypic.com/otjmoj.png

I need it to be displayed like this:

http://i47.tinypic.com/246l5lc.png

The user chooses a time period (eg. January - February/March - August...any combination) and a teacher(s), then the number of lessons each teacher completed in each month in the chosen time period is returned. I'm using a matrix as the number of columns displayed is dynamic each time. I have a column group for the lesson month column in my matrix, and I right-clicked on the header cell and chose 'Subtotal'. I saw the total column appear at the end of the rows, great. Here's my matrix in design mode:

However, when I Preview the report, the value in the Total column is just the value for the first month, eg. 32 and 0 in the above example. Any ideas why this is happening?
Here's what the matrix looks at run time, when I preview the report:

The text in the header cell for each month column is based on an expression, as I want the name of the month to appear. Maybe the fact that the cell I'm clicking on to get the subtotal has an expression is having some effect?
Here's the expression:
=Switch(Fields!lessonMonth.Value=1, "January",
Fields!lessonMonth.Value=2, "February",
Fields!lessonMonth.Value=3, "March",
Fields!lessonMonth.Value=4, "April",
Fields!lessonMonth.Value=5, "May",
Fields!lessonMonth.Value=6, "June",
Fields!lessonMonth.Value=7, "July",
Fields!lessonMonth.Value=8, "August",
Fields!lessonMonth.Value=9, "September",
Fields!lessonMonth.Value=10, "October",
Fields!lessonMonth.Value=11, "November",
Fields!lessonMonth.Value=12, "December")

I've been searching & searching for an answer for this, and trying all sorts of things. Any help would be great, thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 10:33:43

you need to apply month as a column group in matrix

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AnyHelpAppreciated
Starting Member

3 Posts

Posted - 2012-09-14 : 10:47:46

I had month as a column group - when I created the matrix there was automatically 1 column group and 1 row group.

I worked this out myself anyway. For the month column group, the expression I used was this:

=Sum(Fields!numOfLessons.Value)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 10:52:14

quote:
Originally posted by AnyHelpAppreciated

I had month as a column group - when I created the matrix there was automatically 1 column group and 1 row group.

I worked this out myself anyway. For the month column group, the expression I used was this:
=Sum(Fields!numOfLessons.Value)

ok. and then when you add subtotal you should get the totals correctly
i didnt get why you add sum to column group

column group has to be on Fields!Month.value

and in data part you should give SUM expression

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AnyHelpAppreciated
Starting Member

3 Posts

Posted - 2012-09-14 : 11:02:06

Sorry, I explained that badly. The column was grouped on Fields!Month.value, then the data cell had the sum expression in it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 11:03:51

ok...cool
thats the way it should be

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources