Pie Chart in SSRS is very useful to display the High level data and Sometimes, it can be visually good if we display the data in Pie Chart. For example, Sales by region, Country wide customers count etc. In this article we will show you, How to create and configure the Pie charts in SQL Server Reporting Services
Below screenshot will show you the, Data Source and Dataset we used for this Pie Chart Report.
SQL Command we used in the above screenshot is:
SQL QUERY
SELECT Geo.[EnglishCountryRegionName] AS [Country]
,Geo.[StateProvinceName] AS [State]
,Geo.[City]
,Cust.FirstName +' '+ Cust.LastName AS [Full Name]
,Cust.EnglishEducation AS Education
,Cust.EnglishOccupation AS Occupation
,SUM(Cust.YearlyIncome) AS YearlyIncome
,SUM(Fact.SalesAmount)AS SalesAmount
FROM DimCustomer AS Cust
INNER JOIN
FactInternetSales AS Fact ON
Cust.CustomerKey = Fact.CustomerKey
INNER JOIN
[DimGeography] AS Geo ON
Cust.GeographyKey = Geo.GeographyKey
GROUP BY Geo.[EnglishCountryRegionName]
,Geo.[StateProvinceName]
,Geo.[City]
,Cust.FirstName
,Cust.LastName
,Cust.EnglishEducation
,Cust.EnglishOccupation
ORDER BY [Country]
Creating a Pie Chart in SSRS
When you drag and Drop the Chart from Toolbox to Design space, a new window called Select Chart Type will be opened to select required chart from the available once. In this example, we are selecting Simple Pie Chart as shown in below screenshot.
Once you click on Ok button, Pie chart will be displayed in design region with dummy data.
Click on the empty space around the Pie Chart will open the Chart Data window
- Values: Any Numeric (Metric) value such as sales amount, Tax, Total Sales, Customer count etc. All these values will be aggregated using aggregate function (Sum, Count etc) because we are grouping them with the category group items.
- Category Group: Please specify the Column name on which you want to partition you Pie Chart.
In this example, we will create Pie chart for Sales by country so, Drag and drop the Sales Amount column from dataset to chart data values and Country Name in category group.
NOTE: Please be careful when dragging the columns to values region, if you mishandled or dropped in design area will end up in mess.
Click on the Preview button to see the report preview.
If you observe the above screenshot, It is providing perfect result but we are unable to identify the difference between Sales in France and Sales in Germany. To resolve these situations, We have to use the Data Labels.
Right click on the pie chart and select the Show Data Labels option from the context menu to show the values
Click on the Preview button to see the Pie Chart in SSRS report preview.
NOTE: Please refer Formatting Pie Chart article to understand the steps involved in formatting labels, legends and pallets.
Thank You for Visiting Our Blog
