SSRS Aggergate Data

Travis456789
Travis456789 used Ask the Experts™
on
Need to add a column to the attached report to calculate the metric of % Correct:
11-23-2015-12-14-00-PM.jpg
But when I attempt to add the formula:

=SUM(Fields!COUNT_CST_ACCT_NUM.Value, "BUCKET")

/CUST_ACCT_METRIC_NUM is invalid.  The Value expression for the text box 'Textbox10' 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.

I believe from my screen shot you can see that [BUCKET] is a valid grouping.

Ultimately, I would only want the total if the value in the "BUCKET" is = 'CORRECT' only.  I need to calculate the % "CORRECT' over the over-all total.  

Help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
It looks like [Bucket] is only a label and  you should look at using the scope option to the aggregate operators like:

...=SUM(Fields!ID.Value, "NameOfRowGrouping") / SUM(Fields!ID.Value, "TopLevelGroupName")

and also to move it out of the expression and to either the placeholder properties or text-box property that contains the value.

Author

Commented:
How do you identify the name of the "NameOfRowGrouping" and the "TopLevelGroupName"?  

11-24-2015-9-59-37-AM.jpg
Is it in the Group Column names or can I see it in the properties somewhere?  I have tried just about every combination that I can find.
Business Intelligence Developer and Analyst
Top Expert 2015
Commented:
Hi Travis456789,

Please try the following solution.

You can simply add the column after the Column group. And put an expression to calculate the percentage of Correct. I have use the temp table to generate the dump data. It's having the Customer Order amount monthly.

Row group on Customer and Column group on Month.

Calculated the Even Month % and Odd Month %.
E.g. Even Month %  expression is
=ROUND(SUM(IIF(FIELDS!Month_Name.Value MOD 2=0,CINT(FIELDS!SubTotal.Value),0))/SUM(FIELDS!SubTotal.Value),2)*100

I have attached the Query and the RDL file.

Hope it will help you.
Percentantage-in-the-Column-Grouping.rdl
Script.txt

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial