SSRS How to use SCOPE in a GROUP Expression for Percentage Calculation?


I'm using this expression in the GROUP Header area of my report:

=Sum(CDEC(Fields!Status_Complete.Value), "Status")/Count(Fields!Status_Complete.Value, "Status")

The percentage results seem to be calculating based on the ENTIRE REPORT and not each specific GROUP.

How can I add some kind of SCOPE parameter into this formula to advise the report to calculate the percentage based on each GROUP as opposed to the entire REPORT?

(Note: when I run the report for ALL groups, then the percentage calc is the exact same for all GROUPS and I believe it is based on the entire SUM of all Groups. When I run for a specific GROUP, the percentage calc is correct for that single GROUP)
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
I believe you need to add a specific "totals" section rather than using the group header, which is really just for text demarcating each group: "In the tablix data region row group area, right-click a cell in the row group area for which you want totals, point to Add Total, and then click Before or After" (and pick "before", obviously). The good news is that you should also be able to put the group header text into the details row, so you may be able to delete the header.
MIKESoftware Solutions ConsultantAuthor Commented:
Can't Add Totals this way..when I go and try the option to ADD TOTALS is dimmed and I"m unable to select it...
Russell FoxDatabase DeveloperCommented:
Instead of right-clicking the cell, try selecting the dropdown at the bottom of the screen, "(Details)", Add Total --> Before:
 Add totals from Row Groups.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

MIKESoftware Solutions ConsultantAuthor Commented:
I've tried that, other fields are able to total this way,..but not the field I'm working on above...
Russell FoxDatabase DeveloperCommented:
Gotcha - can you send a screenshot so I can see the layout? It's usually easier if you can see how the tablix controls are situated. Just be sure to crop out anything sensitive.
MIKESoftware Solutions ConsultantAuthor Commented:
I appreciate the help, in the meantime I've been researching and working with it... it appears that it may all be centered around me trying it use LOOKUP in an express for 2 separate data sets that actually come from the same CUBE. I've read that LOOKUP was really meant to link 2 datasets that are from totally different datasources.

I'm now working with the MDX Query to see if I can simply add the columns I need..and then use those columns (which will then share the same Dataset) to create a Subtotal as usual.. I think this path will help me with my original calculation issue.....

At any rate,.. I'll send more info when I can.

Russell FoxDatabase DeveloperCommented:
Gotcha, let me know if you get stumped. And if you figure it out yourself be sure to post the answer so others can learn.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.