Adding subtotals to a matrix

Question

Post reply

Adding subtotals to a matrix

Canazei65

SSCommitted

Points: 1696
More actions
April 15, 2011 at 9:54 am

#249458

I have a matrix which is grouped on col 'PrimaryGroupDesc' and has an adjacent col 'Fin_Year' containing numbers I wish to have sub-totalled each time there is a change in col 'PrimaryGroupDesc' .
I have tried right-clicking col 'PrimaryGroupDesc' and using the 'add total-after' option and doing the same in the row group pane, but all I ever get is the Grand Total of all the numbers in col 'Fin_Year'. I can't for the life of me work out what I'm doing wrong.
I've attached a screen dump of the report's design view.
Any help would be much appreciated.
Many thanks in advance for any help provided.

Viewing 5 posts - 1 through 4 (of 4 total)

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

syedathariqbal Hall of Fame Points: 3175 More actions · Answer 1

Hi,

Let me know if this works...

As per your attachment......I am documenting the steps.

1) Delete all the contents of second row. Don't right click and Delete entire row, just delete the contents from 1st and last textbox of second row.

2) Right Click on [PrimaryGroupDesc] and say 'Insert Row', 'Below'. This will create a Row similar to what you have above in screenshot but within the same rowgroup. Good so far ?

3) Now as in your original screenshot, where you had [SUM(NUMBER)] written in second row at last. Right below

code in the new row created, in the same textbox.

=RunningValue(Fields!NUMBER.Value,SUM,"PrimaryGroupDesc")

This I think should work...Let me know if it doesn't.

Regards,

Athar

Canazei65 SSCommitted Points: 1696 More actions · Answer 2

Hi

Thanks for this - I'll try your solution when I return to work tomorrow (I've been on holiday for a couple of weeks). I'll let you know how it goes.

Cheers

Canazei65 SSCommitted Points: 1696 More actions · Answer 3

Hi

I tried the solution but I get the message when I run the report:

"

The Value expression for the text box ‘Textbox3’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.

Build complete -- 1 errors, 0 warnings"

I used the Insert Row-Inside Group Below option (I didn't get the option to Insert-Row only) and added the expression you suggested.

I've attached a screen dump again. Thanks for your help thus far.

Cheers

syedathariqbal Hall of Fame Points: 3175 More actions · Answer 4

Hi,

Try this....

Right Click on 'Primary Group Desc' textbox and select 'Add Total-After'. Screenshot 'Add Group' attached how to do that.

You will then need to add the expression in front of the textbox 'Total', same as the expression above it, which means.

In Column -

"Fin Year", add Expression as you had in your report just below textbox [Fin Year]

"Total", add expression "SUM(NUMBER)", or whatever it is in your report. I am just referring your screenshots.

I have added a screenshot 'Final', this is how I believe your screenshot should look like after you are done :w00t:.

Does this help ?

Regards,

Athar Iqbal