locked
multiple conditional subtotal under each group RRS feed

  • Question

  •  

    Lets say I have 11 rows of data in one group and 12 rows of data in another group.....My report will group the data by those groups....I'd like to have multiple subtotals under each group so that the first subtotal adds up the last three rows, the next subtotal adds up the last 6 rows, the next subtotal under that adds up the last 9 rows, and the last subtotal adds up the last 12 rows (if they exist)...

     

     

    • Group A
      • R1 - $10
      • R2 - $20
      • R3 - $30
      • R4 - $40
      • R5 - $50
      • R6 - $60
      • R7 - $70
      • R8 - $80
      • R9 - $90
      • R10 - $100
      • R11 - $110
      • Subtotal 1 Last 3 - $300
      • Subtotal 2 Last 6 - $510
      • Subtotal 3 Last 9 - $630
      • Subtotal 4 Last 12 (11 actually) - $660
    • Group B
      • R1 - $10
      • R2 - $20
      • R3 - $30
      • R4 - $40
      • R5 - $50
      • R6 - $60
      • R7 - $70
      • R8 - $80
      • R9 - $90
      • R10 - $10
      • R11 - $110
      • R12 - $120
      • Subtotal 1 Last 3 - $330
      • Subtotal 2 Last 6 - $570
      • Subtotal 3 Last 9 - $720
      • Subtotal 4 Last 12 - $780

     

     
     
    Is there a way to do this?...I've tried to so a Sum and Row Count but you cannot include two aggregates in the same query....

    Monday, October 20, 2008 2:07 PM

Answers

  • I came up with a solution like this:

     

    I added the 'row_number()' statement to my SQL code that my dataset calls such that my most recent row (R12) gets a value of 1, R11=2, R10=3, etc.  This would give me a line number for each row.  Then, in my first table on the report, I only included the detail of the first 12 rows (if they exist)  and only included Group A information using a filter and sorting the rows from 12(R1) to 1(R12) as returned by the Row_Number() function.  I then created a second table where I used the same dataset, but in this case I ordered the data from 1(R12) to 12(R1) but I hide the detail....in the table footer section I had four (4) rows.  The first row was the 'Last 3' row that added the values where the Row_Number < 3, the second footer row was the 'Last 6' row that added the values where the Row_Number < 6, the third row was for 'Last 9' and the fourth row was for 'Last 12'.

     

    Thursday, October 23, 2008 3:18 PM

All replies

  • Yes there is a way to do this. When you create matrix which is having fist column includes Groups. Second column will have subgroups. This will be populated from your table (it can be created in ur stored procedure or u ll already have it). Now if u select your subgroup cell and right click on it.

     

    it will say edit group.

     

    In Group on  Expression - just select your subgroup name (ie. fields!subgroup.value)

     

    In Parent group - write the expression and select group name (ie. fields!group.name).

     

    Then go to Visibility.

     

    Just select visibility can be toggled by another report item and give name of Group text box of matrix.

     

    When i say group name it means group A, group B etc. When i say sub group menas R10, R11 etc.

     

    U need to make sure that subgroup category will be linked properly in ur table so that you can differentiate in RS.

     

    I hope it helps.

     

     

     

    Monday, October 20, 2008 3:34 PM
  • Is there a way to do this in a table?  I have other columns that I left out of the example and thus the MATRIX will not work in this case.

     

    Monday, October 20, 2008 4:53 PM
  • Is there a way to do this?  I'm pulling out my hair and can't come up with a solution...why is it so difficult to add up specific previous rows that are based on row count?

     

    Monday, October 20, 2008 8:33 PM
  • Hi,

     

    You still trying to figure this one out using table? If so, here is an example, but it was done at the query level. Then, in the layout, just create a group base on grp (in my example here) and 4 lines of group1 footer. On each footer, use =First(Fields!Last3.Value), Last6 and so on. You don't need to sum it up at RS level. Good luck.

     

    Code Snippet

    declare @tmp table

    (    grp char(1),

    row int,

    amt money )

    declare @ctr int

    set @ctr = 1

    while @ctr < 12

    begin

    insert into @tmp (grp,row,amt)

    select 'A',@ctr,@ctr*10

    set @ctr = @ctr + 1

    end

     

    set @ctr = 1

    while @ctr < 13

    begin

    insert into @tmp (grp,row,amt)

    select 'B',@ctr,@ctr*10

    set @ctr = @ctr + 1

    end

     

    select *,

    Last3 = (select sum(amt) from (select top 3 * from @tmp where grp = a.grp order by row desc) b),

    Last6 = (select sum(amt) from (select top 6 * from @tmp where grp = a.grp order by row desc) b),

    Last9 = (select sum(amt) from (select top 9 * from @tmp where grp = a.grp order by row desc) b),

    Last12 = (select sum(amt) from (select top 12 * from @tmp where grp = a.grp order by row desc) b)

    from @tmp a

     

     

     

    Tuesday, October 21, 2008 6:16 PM
    Answerer
  • I came up with a solution like this:

     

    I added the 'row_number()' statement to my SQL code that my dataset calls such that my most recent row (R12) gets a value of 1, R11=2, R10=3, etc.  This would give me a line number for each row.  Then, in my first table on the report, I only included the detail of the first 12 rows (if they exist)  and only included Group A information using a filter and sorting the rows from 12(R1) to 1(R12) as returned by the Row_Number() function.  I then created a second table where I used the same dataset, but in this case I ordered the data from 1(R12) to 12(R1) but I hide the detail....in the table footer section I had four (4) rows.  The first row was the 'Last 3' row that added the values where the Row_Number < 3, the second footer row was the 'Last 6' row that added the values where the Row_Number < 6, the third row was for 'Last 9' and the fourth row was for 'Last 12'.

     

    Thursday, October 23, 2008 3:18 PM