Solved

# Calculate % of Total per group

Posted on 2011-04-29
365 Views
Hi All,

I have a report that groups Agents by a call group, and shows the total for each call group in the Call Group header row, then agent totals in the detail row.  This works fine ... where I am running into trouble is showing the % of total calls each agent has taken for thr group.  See my sample below:

========================================
Call Group 8000           200 Calls
-Agent 2000                 25 Calls
-Agent 2001               150 Calls
-Agent 2002                 25 Calls

Call Group 9000           150 Calls
-Agent 3000                 75 Calls
-Agent 3001                 75 Calls

So basically, in the RDL, I want to calculate the % that each Agent took for the group.  Somehow I need to get that group total into the detail records, without editing the query passing in the data.  In other words, for Agent 3000, I need to somehow say 75 (Agent 3000 Calls) / 150 (Group 9000 Calls) = 50%.

I am having trouble getting the Group Total (150) value from the detail row.

Thanks in advance for any help!
-Matt
0
Question by:mdw233

LVL 22

Accepted Solution

I have a example attached. The file is an .xml because EE doesn't allow .rdl. Rename the file to a .rdl to use it.
The query creates its own data, no need for any tables, but you'll have to change the data source of course.

You can add another column with the expression:
=Fields!Calls.Value/sum(Fields!Calls.Value)
By default 'sum' will use the inner most group which is the call group. But you can specify the scope to sum also. This is what I did in the last column:
=Fields!Calls.Value/sum(Fields!Calls.Value,"DS")
"DS" is the name of the dataset, so now the sum is the overall sum. And for the group percentage I used:
=sum(Fields!Calls.Value)/sum(Fields!Calls.Value,"DS")
Which is the sum of the group divided by the overall sum.
When you have multiple groupings you can specify the name of the group as the scope and calculate the sum on every level you want.

Agents.xml
0

LVL 1

Author Comment

Perfect.  I put the name of the group instead of the dataset as my scope and it worked.  Thank you so very much!!
0

LVL 22

Expert Comment

Glad I could help (even more glad if you accept my solution :-)  ).
0

## Featured Post

### Suggested Solutions

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.