Pyramid Chart in SSRS

A Pyramid Chart in SSRS is very helpful to visualize the Data in staging (data at each stage). For example, we can use Pyramid Charts to visualize the Wastage percent ,or production cost, or number of resources we used in each stage of our product development. In this article we will show you, How to create a SSRS Pyramid Chart, change the Pyramid Chart Title, Legend Title, Legend Position, Pyramid Chart Font, Chart Model, Pallet, and Display Data Labels on Pyramid Chart in SQL Server Reporting Services 2014 with example.

For this example, we are going to write the Custom SQL Query against the SQL Server Data Source, and the query that we are going to use for this report is:

SQL Query

Create a Pyramid Chart in SSRS

In this example, we are going to create Pyramid Chart to display the Sales amount by the Product Color. Below screenshot will show you the, Data Source and Dataset we used for this Pyramid Chart Report.

In SSRS report design, we can add a Pyramid chart by dragging the Chart property from the SSRS Toolbox to Design space, or we can simply right click on the report designer to open the context menu. From the context menu, please select the Insert -> Chart option as we shown below.

Once you select the Chart option, a new window called Select Chart Type will be opened to select required chart from the available once. For this example, we are selecting Pyramid Chart as shown in below screenshot.

Once you click on Ok button, Pyramid chart will be displayed in design region with dummy data.

Click on the empty space around the Pyramid Chart will open the Chart Data window

• Values: Any Numeric (Metric) value such as Total Sales, sales amount, Number of Customers, Tax etc. All these values will be aggregated using aggregate function (Sum, Count etc). This is because we are grouping them with the category group items.
• Category Group: Please specify the Column name on which you want to partition you Pyramid Chart.

As we said before, In this example we will create Pyramid chart for Sales by Product color so, Drag and drop the Sales Amount column from data set to chart data values and Product Color 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 of Multi-color products, and NA. To resolve these situations, We have to use the Data Labels.

Add Data Labels to 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 as we shown below.

Click on the Preview button to see the Pyramid Chart in 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 as we shown in below screenshot, and change the title as per your requirement. Here we are changing as 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 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, Font size to 14pt, and color to Navy Blue as shown in below screenshot

Click on the Preview button to see the report preview.

Show Legend Title of a Pyramid Chart in SSRS

To display the Legend title, Please select the Legend region and right-click on it will open the context menu. From the context menu, please select Show Legend Title option as we shown in below screenshot.

Please rename the Legend title as per your requirement. Here we will name it as Product Colors

Format Legend Position, and Font of a Pyramid Chart in SSRS

To format the Legend region, Please Select the Legend region and right-click on it will open the context menu. From the menu, please select Legend Properties option as we shown below.

This will open a new window called the Legend Properties Window. Within the General Tab we have an option called Legend Position as shown in below screenshot. You can change the Legend Position by changing the dot positions. For now we are changing the position from default right corner to Left corner.

Next, Within the Font tab we can change the Font Size, Font Family, Font Style and Color of a Pyramid Chart Legend as shown in below screenshot

As you can see, we changed the Font to Cambria, and Font size to 12 pt

Click Ok button to close the Properties window, and hit the preview tab to see the 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 selecting the Palette property as shown in below image

Here we selected 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 preview tab to see the report preview

Change the Pyramid Chart in SSRS

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 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 Chart Type from 2D Pyramid to 3D Pyramid

Thank You for Visiting Our Blog