none
Dynamic columns & subtotals in a matrix RRS feed

  • Question

  • I'm writing a report in VS 2005. I've got a matrix with dynamic columns, and I want a subtotal at the end of each row. I've right-clicked on the column group header and selected 'Subtotal', but for some reason it is only showing the amount from the first column in each row, not the total of all values across that row.

    The title of the column that is grouped is based on an expression. I want to display only the columns for each month from a chosen time period (the user can choose any time period in a year, eg. Jan - Feb, Jul - Sept, anything). The stored procedure behind the matrix's dataset is functioning correctly. Here's the expression that sets the column title:

    =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")

     

    lessonMonth is a value returned by the stored procedure, and it also has the number of lessons carried out by a teacher for that month. So, an example of the type of data that could be returned is as follows (I choose 2 teachers from a drop-down list, and Jan - Feb as the time period. Teacher 36101 had no lessons in that time period):

    Teacher #         Lesson month    Num of lessons

    350

    1

    32

    350

    2

    11

    360

    1

    0

    360

    2

    0

    

    Any help would be great. Thanks

     

    Thursday, September 13, 2012 8:20 AM

Answers

  • I worked this out myself. In the lessonMonths columns, I used the following expression:

    =Sum(Fields!numOfLessons.Value)
    That worked!

    • Marked as answer by crmNewbie1978 Friday, September 14, 2012 10:49 AM
    Friday, September 14, 2012 10:49 AM

All replies

  • Hi,

    If you want subtotal at the end of each row, you need to add ‘Subtotal' by clicking on row group.


    Aftab Ansari

    Thursday, September 13, 2012 8:33 AM
  • I've already tried that, but the total appears at the bottom of each row.

    I've tried adding a subtotal to the row group (as you described) and to the column group (by right-clicking on the cell with the expression for the lesson month column title). Only the column group displays the total in the correct place, ie at the end of each row.

    This is what I need my report to look like:

    Teacher #

    Lesson Month 1 <dynamic column>

    Lesson Month 2 <dynamic column>

    Lesson Month N <dynamic column>

    Total Lessons

    350

    32

    11

    N

    43 (+N)

    360

    0

    0

    N

    N

    Thursday, September 13, 2012 9:01 AM
  • 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.




    Thursday, September 13, 2012 1:49 PM
  • I'm really stuck on this! Anyone who can help please?
    Friday, September 14, 2012 8:28 AM
  • I worked this out myself. In the lessonMonths columns, I used the following expression:

    =Sum(Fields!numOfLessons.Value)
    That worked!

    • Marked as answer by crmNewbie1978 Friday, September 14, 2012 10:49 AM
    Friday, September 14, 2012 10:49 AM