SSRS Dashboard Reports

In this article we will show you the step by step approach to create SSRS dashboard that holds different kind of reports with example. For this we are going to use the Shared data Source, and Shared Dataset.

Below screenshot will show you the Data Source, and Dataset that we used to create this SSRS dashboard.

and the Custom SQL Query we used against the SQL Server Data Source is:

SQL Query

-- SQL Query that we use in SSRS Area Chart
SELECT Cat.[EnglishProductCategoryName] AS ProductCategory, 
       SubCat.[EnglishProductSubcategoryName] AS ProductSubCategory, 
       Prod.EnglishProductName AS ProductName, 
       Prod.Color, 
       Fact.OrderQuantity, 
       Fact.TotalProductCost, 
       Fact.SalesAmount, 
       Fact.TaxAmt, 
       Fact.[Freight]
FROM FactInternetSales AS Fact
   INNER JOIN DimProduct AS Prod
      ON Fact.ProductKey = Prod.ProductKey
   INNER JOIN DimProductSubcategory AS SubCat
      ON Prod.ProductSubcategoryKey = SubCat.ProductSubcategoryKey 
   INNER JOIN DimProductCategory AS Cat 
      ON SubCat.ProductCategoryKey = Cat.ProductCategoryKey

SSRS Dashboard Reports

In this example we will show you the steps involved in creating SSRS Dashboard. To demonstrate the same we will add One Pie Chart, One Column Chart, and an Area Chart to the dashboard.

Add Pie Chart to SSRS Dashboard Example

We can add any 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. Please select the Insert -> Chart option from the context menu as we shown below.

Selecting the Chart option will open a new window called Select Chart Type. For this example, we had selected Pie Chart as shown in below screenshot. Next, Click on the empty space around the Pie Chart will open the Chart Data window.

For now, we want to create a Pie chart to display the Sales by Product Subcategory so, Drag and drop the Sales Amount column from dataset to chart data values and Product Subcategory in category group. I suggest you to refer Pie Chart in SSRS to understand the steps in detail.

Next, we did some formatting to the Pie Chart to look neat and clean. Please refer Formatting Pie Chart article to understand the steps involved in formatting labels, legends and pallets.

Click on the Preview button to see the report preview. As you can see from the below screenshot, there are too many Product subcategories to display in Pie Chart. So let me add a Filter to display the product Subcategories that are only belongs to Bikes.

Adding Filters at Dataset level

Please select the Dataset on which you want to apply filters from the Report data tab, and Right click on it will open the context menu. Please select the Dataset Properties.. option from it. Once we select that option, a new window called Dataset Properties will be opened. Please select the Filters tab as shown in below screenshot, and click on the Add button.

In this example, We are going to display the Charts whose [Product Category] is Bikes so, Please select the [Product Category] as an Expression and Operator as Equals to, and Value as Bikes. I suggest you to refer Filters at Dataset Level in SSRS article to understand the Filters.

Click OK to apply the Filters at Dataset level, and click on the preview tab to see the report preview. From the below screenshot you can see that Pie Chart is displaying the product subcategories that are totally belongs to Bikes Category.

Add Column Chart to SSRS Dashboard Example

Next, we will add a Column Chart beside the previously created Pie Chart. For this, right-click on the open space beside the Pie Chart to open the context menu. Please select the Insert -> Chart option from the context menu as we shown below.

Once you select the Chart option, a new window called Select Chart Type will be opened. Here, we will select the Column Chart as we shown in below screenshot.

Click on the empty space around the Column Chart will open the Chart Data window. Here, we want to create a Column Chart to display the difference between the Sales Amount, and the Total Product Cost by Product Color. So, Drag and drop the Sales Amount, Total Product Cost columns from dataset to chart data values and Product Color in category group. I suggest you to refer Column Chart in SSRS to understand the steps in detail.

Let me apply some formatting techniques on Column Chart. Please refer Formatting Column Chart in SSRS to understand the steps involved in formatting the column chart with example.

Let me show you the dashboard preview

Add Area Chart to SSRS Dashboard Example

Lastly, we will add an Area Chart below the previously created Pie Chart, and Column Chart. For this, right-click on the open space below the Pie Chart to open the context menu. Please select the Insert -> Chart option from the context menu as we shown below.

Once you select the Chart option, a new window called Select Chart Type will be opened. Here, we are selecting an Area Chart as shown in below screenshot.

Click on the empty space around the Area Chart will open the Chart Data window. Here, we want to create an area Chart to display the difference between the Total Product Cost, and Sales Amount by Product name. So, Drag and drop the Total Product Cost, Sales Amount columns from dataset to chart data values and Product Name in category group. I suggest you to refer Area Chart in SSRS to understand the steps in detail.

Next, we did some formatting to the Area Chart to look neat and clean. Please refer Area Chart in SSRS to understand the steps involved in formatting labels, legends and pallets.

Let me adjust the Charts so that they can be viewed like a Group.

NOTE: You can also use the Rectangle Report Item to place the Charts. This can give further formatting options.

Let me show you the Report preview

We successfully create a SSRS Dashboard that holds Pie Chart, Column Chart, and an Area Chart.

Thank You for Visiting Our Blog