A Pyramid Chart in SSRS is beneficial to visualize the Data in staging (data at each stage). For example, we can use SSRS Pyramid Charts to visualize the waste percent, production cost, or the number of resources we used in each step of our product development.
In this article, we will show you how to create an SSRS Pyramid Chart, change the Pyramid Chart Title, Legend Title, Legend Position, Pyramid Chart Font, Chart Model, and Pallet, and Display Data Labels on the Pyramid Chart in SSRS or SQL Server Reporting Services with an example.
For this SSRS Pyramid Chart example, we used a Custom SQL query
-- Query that we use in Pyramid Chart
SELECT Geo.EnglishCountryRegionName AS Country, Geo.StateProvinceName AS State,
Geo.City, Prod.EnglishProductName AS ProductName, Prod.Color,
Fact.OrderQuantity, Fact.TotalProductCost, Fact.SalesAmount, Fact.TaxAmt, Fact.[Freight]
FROM DimProduct AS Prod
INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey
INNER JOIN DimSalesTerritory AS Terry ON Terry.[SalesTerritoryKey] = Fact.[SalesTerritoryKey]
INNER JOIN DimGeography AS Geo ON Geo.[SalesTerritoryKey] = Terry.[SalesTerritoryKey]
Create a Pyramid Chart in SSRS
In this example, we are going to create a Pyramid Chart to display the Sales amount by the Product Color. The Data Source and Dataset we used for this Pyramid Chart Report is the adventure works DW database and the above query.
We can add a Pyramid chart by dragging the Chart property from the SSRS Toolbox to the Design space. Or right-click on the report designer and select the Insert -> Chart option.
Selecting the Chart option opens the Select Chart Type window. For this example, we are choosing the Pyramid Chart.
Once you click on the Ok button, the Pyramid chart will show on the design region with dummy data. Click on the empty space around the SSRS Pyramid Chart will open the Chart Data window
- Values: Any Numerical (Metric) value such as Total Sales, sales amount, Number of Customers, Tax, etc. All these values will be aggregated using an 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 your Pyramid Chart.
As we said before, In this example, we create a Pyramid chart for Sales by Product color. So, Drag and drop the Sales Amount from the dataset to chart data values, and Product Color in the category group.
Click on the Preview button to see the report preview.
If you observe the above screenshot, we are unable to identify the difference between Sales of Multi-color products, and NA. To resolve these situations, We have to use the Data Labels.
Add Data Labels to the Pyramid Chart in SSRS
Right-click on the Pyramid chart, and select the Show Data Labels option from the context menu to show the values
Next, we are formatting the Font, and changing the Number format from Default to Currency.
Click on the Preview button to see the Pyramid Chart in the SSRS report preview.
Now you can see the Sale difference between each color.
Change SSRS Pyramid Chart Title
To change the Pyramid Chart title, Please select the Chart title region, and change the title. Here we are changing to Pyramid Chart for Sales By Color because the report is displaying the same
Format SSRS Pyramid Chart Title Font
Please select the Chart title region, and right-click on it will open the context menu. From the menu, please select the Title Properties option.
Next, Within the Font tab, we can change the Font Size, Font Family, Font Style, and Color of a Pyramid Chart Title. Here we are changing the Font to Cambria, the Font size to 14pt, and the color to Navy Blue.
Click on the Preview button to see the report preview.
Show the Legend Title of a Pyramid Chart in SSRS
To display the Legend title, select the Legend region, and right-click on it will open the context menu. From the context menu, please select the Show Legend Title option. Please rename the Legend title as per your requirement. Here we will name it Product Colors.
Format Legend Position, and Font of a Pyramid Chart in SSRS
To format the Legend region, select the Legend Properties option from the above-mentioned context menu. It will open a new window called the Legend Properties Window.
Within the General Tab, we have an option called Legend Position. You can change the Legend Position by changing the dot positions. For now, we are changing the position from the Default right corner to the left corner.
Next, Within the Font tab, we can change the Font Size, Font Family, Font Style, and Color of a Pyramid Chart Legend. The option is the same as the fourth image before this, and in modern VS, use the toolbar to change the fonts. As you can see, we changed the Font to Cambria, and the Font size to 12 pt.
Click the Ok button to close the Properties window, and hit the preview tab to see the SSRS Pyramid Chart report preview
Change the Palette of a Pyramid Chart in SSRS
We can also change the default Pyramid Chart Colors or chart Palette in SSRS. First, select the Chart and go to properties. Change the palette color by choosing the Palette property
Here, we decided to change the palette color to Default. Remember, If you don’t like the palette colors, then you can select the Custom option, and choose the colors as you can wish.
Click on the Pyramid chart preview tab to see the report preview. The below image shows the colors.
Change the Pyramid Chart
SSRS allows us to change the chart type even after creating the Pyramid chart. To do so First, select the Pyramid chart and right-click on it will open the context menu. Please select the Change Chart Type… option from the context menu
Once you select the Change Chart Type… option, it will open a new window called Select Chart Type to select the change. Here we are selecting the 3D Pyramid chart
Click on the preview tab to see the report preview
From the above screenshot, you can observe that We successfully changed the SSRS Chart Type from the 2D Pyramid to the 3D Pyramid chart.