Calculate % of Total per group

Posted on 2011-04-29
Last Modified: 2012-05-11
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!
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:
    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:
    "DS" is the name of the dataset, so now the sum is the overall sum. And for the group percentage I used:
    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.

    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!!
    LVL 22

    Expert Comment

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

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    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 (". It will help one to understand clearly the steps to track a lost android phone.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now