CODEWITHSUNDEEP
reporting-servicesssrs-2008reportingservices-2005ssrs-tablixssrs-grouping
Michigan Man
Michigan Man

Reputation: 101

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.

Upvotes: 0

Views: 2779

Answers (2)

gannaway
gannaway

Reputation: 1882

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

Upvotes: 3

JC Ford
JC Ford

Reputation: 7066

You can do this with expressions in the pie chart.

You have a category group (or perhaps a series group) that groups by name. Edit that group and in the Group On write an expression that uses the Iif() function something like this:

=Iif(Fields!Name.Value = "APPROVED" Or Fields!Name.Value = "CANCELED" Or Fields!Name.Value = "COMPLETED" Or Fields!Name.Value = "DENIED", Fields!Name.Value, "OTHER")

Then edit the chart's [Value], go to the Point Labels tab. Check the SHow point labels box if it isn't already. Change the Data label expression to something like this:

=Sum(Fields!Amount.Value) / Sum(Fields!Amount.Value,"TheNameOfYourDatasetHere")

Then put 'p' in the Format Code box to show the value as a percentage.

NOTE: this is based on SSRS 2005. You have both 2005 and 2008 tags on the question, so I'm not certain which you're using. 2008 will be somewhat different but similar.

Upvotes: 0

Related Questions