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

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

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
Asked:
mdw233
  • 2
1 Solution
 
Nico BontenbalCommented:
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
 
mdw233Author 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
 
Nico BontenbalCommented:
Glad I could help (even more glad if you accept my solution :-)  ).
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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