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
SQL Command we used in the above screenshot is:
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