[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 378

# Calculate % of Total per group

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
mdw233
• 2
1 Solution

Commented:
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

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

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

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.