Search code examples
reporting-servicesssrs-2008reportingservices-2005ssrs-tablixssrs-grouping

SSRS Chart. Displaying data in a different way than the dataset

Perhaps someone can point me an online resourse that can give me hints on how to accomplish the following.

Scenario. I have a dataset that returns two columns. NAME and AMOUNT. See chart below.

  1. Name----------------------- Amount
    Approved------------------ 0
    Canceled------------------ 20548010
    Completed----------------- 160320000
    Denied---------------------- 0
    Draft------------------------ 0
    Proposed------------------ 35209000
    Re-Approve--------------- 0
    Ready to Approve-------- 3208000
    Total------------------------ 219285010

  2. Currenty the pie chart shows all the Names/values. APPROVED, CANCELED, COMPLETED, DENIED, DRAFT, PROPOSED , RE-APPROVE, READY TO APPROVE as percentages of the total.

Here is what I would like to accomplish.

  1. I would like to only show APPROVED, CANCELED, COMPLETED and DENIED. I would like to group the others, DRAFT, PROPOSED, RE-APPROVE, and READY TO APPROVE in a separate group called "OTHER".

  2. I then would like to show in the pie chart the Names/values , APPROVED, CANCELED, COMPLETED, DENIED and OTHER as percentages of the total.

Solution

  • I know I'm late to the party, but here's another approach you could take.

    1. Right-click on your dataset and choose Add Calculated Field ...

    2. Add a field named DisplayName (we'll use this to perform your naming logic above), and set the expression to

      =IIf(Fields!Name.Value.Equals("Approved") OR 
   Fields!Name.Value.Equals("Canceled") OR 
   Fields!Name.Value.Equals("Completed") OR 
   Fields!Name.Value.Equals("Denied"), 
   Fields!Name.Value, "Other")

      enter image description here

    3. Setup your chart with the following base settings:

      enter image description here

    4. Right-click on your pie chart and choose Show Data Labels

    5. Right-click on one of the data labels and choose Series Label Properties

    6. Add the following expression under Label data

      =Fields!DisplayName.Value & " #PERCENT{P0}"

    7. You may find that there's too much text on the Pie Chart if it's small. You can set the PieLabelSyle CustomAttribute of the Pie Chart to Outside (you can find this by clicking on the Pie Chart and then examining the Properties window), which looks like this:

      enter image description here