Archive

Posts Tagged ‘SSRS – Pie chart’

SSRS – Pie Chart

September 11, 2011 1 comment

Pie chart is one of the new reporting style features in SSRS 2008. In technical point of view, I always feel that the secret of a great report development comes from the great combination of report properties options from the reporting tool.
My plan to document some of the very useful options while developing a pie chart report.
 
Step 1: Analysis of source data for reporting dataset. Here considering AdventureWorksDW2008R2 database as the source database for the report.
Source query for data extraction as

 
USE[AdventureWorksDW2008R2]
SELECT DG.DepartmentGroupName,AVG(FF.Amount) AS AvgAmount
  FROM [dbo].[FactFinance] FF
  JOIN dbo.DimDepartmentGroup DG
  ON FF.DepartmentGroupKey = DG.DepartmentGroupKey
  GROUP BY DG.DepartmentGroupName

Data source resultset

Data source resultset


 
Step 2: Select the chat type form and drag into the report body as it gives the standard chart by default.
Select chart into report

Select chart into report


 
Step 3: Change the chart type to pie chart as
On chart -> Right Click -> Select category -> Pie Chart
Chart data

Chart data


 
Step 4: Select the Chart data and categories from the dataset values as
On selecting the report body (pie chart), gives the properties to select chart data and categories options.
Here selected AvgAmount as Data and DepartmentGroupName as Category also known as legends of the report.
Chart categories - legends

Chart categories - legends


 
Step 5: Report view has changed with Data and Legend fields as
Chart properties

Chart properties


 
Step 6: Run the report to get the preview, we have got the categories list but missing the data values in the report
Chart view

Chart view


 
Step 7: Select the properties of data values [AvgAmount] and set the UseValueAsLable to True. The property will get the data values into pies as
Pie data properties

Pie data properties


 
Step 8: Run the report, preview of report with data in the pies as below but it is missing the usability of the report as it is missing the simple formatting of the data
Pie chart with data

Pie chart with data


 
Step 9: Here AvgAmount is the money, so it could be better to present the data in the money format for better usability. Select the Label values -> Right Click -> Properties
Set the Label properties as below
Label properties setup

Label properties setup


 
Step 10: The report view changed to money with decimal places as below and it has better usability than the above report with only the data
Pie with formatted data

Pie with formatted data


 
Step 11: Many times, it is very common requirement to have the reports with percentages rather than with data values.
Select data labels -> Right Click -> label properties -> General and select #PERCENT as below
Pie data in percentage

Pie data in percentage


 
Step 12: The pie chart view changed to percentage of data in all the pies. The usability of the report has increased as we could get many scenarios of business analysis from the report with percentages
Data in percentages

Data in percentages


 
Step 13: At the first instance, the pies with small percentage makes difficult to understand and also making the report less readable. It could be better to round-up all the pies with a threshold values as
CustomAttributes properties to set the CollectedStyle with threshold value as below.
We have two options on CollectedStyle
 
Option 1: CollectedStyle – SingleSlice

CollectedPie

CollectedPie


Pie custom attributes

Pie custom attributes


The report view changed to more usable and easy to analysis of data. The departments with less than the threshold value are a group to Other Departments
Pie groups with percentage

Pie groups with percentage


 
Option 2: CollectedStyle – CollectedPie
All the departments with less than threshold values will form as a pie and then the pie will linked to another pie with details of all the other departments as
CollectedPie chart

CollectedPie chart


 
Step 14: The data labels are inside of the pies, which make the report not so readable so it could be an option to change the presentation of the data outside the pies by setting the PieLabelStyle as below
Pie label style

Pie label style


 
On running of the report the data values are outside of the pies. This report is more readable and easy for data analysis.
 
Pie labels

Pie labels


 
Step 15: Deployment of the reports using Report builder 3.0 needs to have the target folder as it is one of limitation of report builder deployment.
Create the target folder at webserver, here it is my localhost
New folder

New folder


 
Step 16: Save the report to the target folder
Target path folder @webserver

Target path folder @webserver


 
Step 17: Run the report at report server and getting the report as
Report @webserver

Report @webserver


 
Step 18: 3D Report
One of the features of SSRS 2008 is to convert any chart report to 3D reports in a single click with many options for useability as
Select the report -> 3D Effects as below
Report 3D effects

Report 3D effects


 
Step 19: Enable 3D and select the rotation and inclination as per the requirement
Chart 3D effects

Chart 3D effects


 
Step 20: Run the report on the report server, we have a highly usable and simple to understand the 3D report.
Report in 3D @webserver

Report in 3D @webserver


 
Points to consider:
 
1. I have presented only the simple scenario with possible options to create a simple pie chart report
2. In general, graphical reports will go with tablix report as I haven’t presented tablix report
3. I haven’t implemented any pie explodes options, as I haven’t seen it as primary requirements in reports

Categories: SSRS Tags: