Summing a Calculated value from group footers

[DietSquirt]

Hi all, I am new to SSRS. Please help me get past the "why doesn't this work like Crystal?" stage...
I have groups in my report, and calculated values in the group footer. (I have named all my groups and text boxes so I can refer to them.)
Each group footer calculates the % of each Customer to the total sales. This field is named PercentOfTotal.

=sum(fields!InvoiceExtendedPrice.Value,"Customer")/sum(Fields!InvoiceExtendedPrice.Value, "DataSource")

Then at the bottom of the report I want to add up all the PercentOfTotal values to make sure they add up to 100% (That's not my idea. You and I know that it will but we're transitioning this report from Excel...)

No matter what I try nothing works. Most of the time I run into the dreaded scope message. Here's what I tried first, then it went downhill from there.
=sum(Fields!PercentOfTotal.Value, "DataSource")
=sum(Fields!PercentOfTotal.Value, "Groupname")
Can anyone advise me on this? Thanks!

 

[ablackwe]

Are you using a table? If so, enter the same calculation into the table footer, i.e.

=sum(fields!InvoiceExtendedPrice.Value,"Customer")/sum(Fields!InvoiceExtendedPrice.Value, "DataSource")

Thanks

Adam Blackwell
Business Intelligence Analyst

http://www.uk.insight.com/

 

[SgtJarrow]

If you table is based on a a dataset in the report, and you are putting this into the table footer, you can leave the scope identifier off. The scope will be defined as the table as a whole since it is in the footer. So =SUM(Fields!InvoiceExtendedPrice.Value) should be sufficient.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[DietSquirt]

Thanks, both of you, your suggestions will work. I should have explained my question better.

I am creating this report for a high level user who uses Excel, from an excel model he provided. He thinks you have to ADD UP all the results in the group footers (rather than calculate the total) in order to prove that they add up to 100%. You would have to in Excel when you're constructing the spreadsheet to make sure you didn't make an error.

You and I know that SSRS doesn't need to be proved the way Excel creations do, but I wanted to keep it in the report to (1) increase his comfort level with the transition, and (2) learn how it's done since I'm new to SSRS.

So now my question is: is there a way to add up the values in the group footers and put the result in a page footer? If there is not, I'll tuck that in my brain and move on.
Thanks!