SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 Subtotal in Matrix only shows val from 1st column		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AnyHelpAppreciated
Starting Member

3 Posts
Posted - 09/14/2012 :  05:06:39  Show Profile  Reply with Quote
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

India
47023 Posts
Posted - 09/14/2012 :  10:33:43  Show Profile  Reply with Quote
you need to apply month as a column group in matrix

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

AnyHelpAppreciated
Starting Member

3 Posts
Posted - 09/14/2012 :  10:47:46  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 09/14/2012 :  10:52:14  Show Profile  Reply with Quote
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/
Edited by - visakh16 on 09/14/2012 10:53:50
Go to Top of Page

AnyHelpAppreciated
Starting Member

3 Posts
Posted - 09/14/2012 :  11:02:06  Show Profile  Reply with Quote
Sorry, I explained that badly. The column was grouped on Fields!Month.value, then the data cell had the sum expression in it
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 09/14/2012 :  11:03:51  Show Profile  Reply with Quote
ok...cool
thats the way it should be

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000