Post reply

Subtotal for cloumn group only showing first value

  • September 13, 2012 at 4:44 am

    #264066

    Hi,

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

    (SPROC_Results, attached - easier to read, can't add a table in here)

    I need it to be displayed like this:

    (Matrix, attached)

    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. 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?

    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!

  • September 13, 2012 at 6:29 am

    #1537157

    You have to choose scope of Total value.

    --Divya

  • September 13, 2012 at 6:31 am

    #1537159

    Thanks for your reply. How do I do that?

  • September 13, 2012 at 7:23 am

    #1537183

    Anyone who can help?? I just need a simple SUM of the values in each row. I thought that the built-in subtotal function would be fine.

    Here's what my matrix looks like in design mode:

    I clicked on the header of the middle column. As you can see, the header text is based on the expression I previously posted. I then chose 'Subtotal' and the last column ('Total') appeared. When this is run, it looks like this (I just chose January-February as the time period):

    The values are correct, but the total should be 43 on the first line. 0 is correct for the second, but I presume if there were values in there, that only the value for January would be showing.

  • September 14, 2012 at 1:19 am

    #1537647

    AnyHelpAppreciated (9/13/2012)

    Anyone who can help?? I just need a simple SUM of the values in each row. I thought that the built-in subtotal function would be fine.

    Here's what my matrix looks like in design mode:

    I clicked on the header of the middle column. As you can see, the header text is based on the expression I previously posted. I then chose 'Subtotal' and the last column ('Total') appeared. When this is run, it looks like this (I just chose January-February as the time period):

    The values are correct, but the total should be 43 on the first line. 0 is correct for the second, but I presume if there were values in there, that only the value for January would be showing.

    In the total you need to put an expression like "= SUM(Fields!noofLessons.Value)"

    And are you using matrix to display this?

    Please find the screenshots of a sample rpt.

    --Divya

  • September 14, 2012 at 2:05 am

    #1537670

    I can't access the greyed-out cell underneath 'Total' to put an expression in it, it's automatically generated when I click on 'Subtotal'. Yes, I'm using a matrix to display this.

    I see in your attachment that you've made a column group out of the months. I'm using 2005, so I can't seem to do that. The months column is an automatic group, because it's in a matrix. I need to use a matrix because I have dynamic columns - I just need to display the months within the time period the user chooses. I tried having a table with all 12 months, and using an expression to show/hide the relevant ones, but the end result looks like this:

    I have grouped by teacher number and month, but the value for February doesn't show up.

    Here is the expression I'm using to display the values from the dataset, this is in each column, in the details row:

    =IIf(Fields!lessonMonth.Value=1,Fields!numOfLessons.Value,0)

  • September 14, 2012 at 3:07 am

    #1537690

    AnyHelpAppreciated (9/14/2012)

    I can't access the greyed-out cell underneath 'Total' to put an expression in it, it's automatically generated when I click on 'Subtotal'. Yes, I'm using a matrix to display this.

    I see in your attachment that you've made a column group out of the months. I'm using 2005, so I can't seem to do that. The months column is an automatic group, because it's in a matrix. I need to use a matrix because I have dynamic columns - I just need to display the months within the time period the user chooses. I tried having a table with all 12 months, and using an expression to show/hide the relevant ones, but the end result looks like this:

    I have grouped by teacher number and month, but the value for February doesn't show up.

    Here is the expression I'm using to display the values from the dataset, this is in each column, in the details row:

    =IIf(Fields!lessonMonth.Value=1,Fields!numOfLessons.Value,0)

    Just try first without using any switch or if case. In the columns part in the matrix, just drop the column "Fields!lessonMonth.value". and then add a new column to the right, right click on that cell and choose expression and write the expression. I have attached the screenshot. Just try simple one first, dont give the Month names to the columns first. and then slowly move.

    --Divya

  • September 14, 2012 at 3:24 am

    #1537696

    Thanks for your reply.

    I think you are using VS2008, as my screen doesn't look like yours - I'm using VS2005. I don't have all those options in the drop down box for the cell, and I don't have the horizontal bracket above the 2nd column. (See below)

    I've already tried not including the month names, but that doesn't help. I also followed your suggestion of just entering the expression in the data cell:

    =Fields!numOfLessons.Value

    That didn't work either.

  • September 14, 2012 at 3:41 am

    #1537700

    AnyHelpAppreciated (9/14/2012)

    Thanks for your reply.

    I think you are using VS2008, as my screen doesn't look like yours - I'm using VS2005. I don't have all those options in the drop down box for the cell, and I don't have the horizontal bracket above the 2nd column. (See below)

    I've already tried not including the month names, but that doesn't help. I also followed your suggestion of just entering the expression in the data cell:

    =Fields!numOfLessons.Value

    That didn't work either.

    In the rows part, drag your Teacherno field, in the columns part drag your lessonmonth field, and in the data cell drag your numofllessons field, i can see these sections in your matrix.

    Then i can see an "Add Column" on the context menu you have shown, just select it, and add coumn. On that new column again right click as you have done and select "Expression", in that right "=sum(fields!lessonnumbers.value)"

    --Divya

  • September 14, 2012 at 3:59 am

    #1537709

    Thanks, I followed all your steps, as follows (screenshots included for each step):

    In the rows part, drag your Teacherno field,

    in the columns part drag your lessonmonth field,

    and in the data cell drag your numofllessons field, i can see these sections in your matrix.

    Then i can see an "Add Column" on the context menu you have shown, just select it, and add column.

    When I add an column as you described, it appears as above, not as a separate column to the right.

    On that new column again right click as you have done and select "Expression", in that right "=sum(fields!lessonnumbers.value)"

    As you can see in the 2nd screenshot, the new column is added inside the group, so it doesn't give the desired result when you preview the report. (Teacher number is shown as ADI in this screenshot, that's ok)

  • September 14, 2012 at 4:47 am

    #1537727

    I worked this out myself. For the months column, I used the following expression:

    =Sum(Fields!numOfLessons.Value)

    That worked.

    Thanks for your help.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply