Answered by:
multiple conditional subtotal under each group
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

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 Snippetdeclare @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 PMAnswerer 
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