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
Step 2: Select the chat type form and drag into the report body as it gives the standard chart by default.
Step 3: Change the chart type to pie chart as
On chart -> Right Click -> Select category -> Pie Chart
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.
Step 5: Report view has changed with Data and Legend fields as
Step 6: Run the report to get the preview, we have got the categories list but missing the data values in the report
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
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
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
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
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
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
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
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
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
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
On running of the report the data values are outside of the pies. This report is more readable and easy for data analysis.
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
Step 16: Save the report to the target folder
Step 17: Run the report at report server and getting the report as
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
Step 19: Enable 3D and select the rotation and inclination as per the requirement
Step 20: Run the report on the report server, we have a highly usable and simple to understand the 3D report.
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