In this article, we will show you the step by step approach to create SSRS dashboard reports that holds a different kind of reports with an 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 for this SSRS Dashboard reports is:
-- SQL Query that we use in SSRS Dashboard
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.ProductCategoryKeyHow to Create Dashboard Reports in SSRS
In this example, we will show you, how to create Dashboard reports in SSRS. Or, 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 the category group. I suggest you 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 to 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 only belong to Bikes.
Add 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 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 totally belong to Bikes Category.
Add Column Chart to SSRS Dashboard Example
Next, we will add a Column Chart beside the previously created Pie Chart in SSRS Dashboard. 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 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 to understand the steps involved in formatting the column chart with an example.
Let me show you the SSRS dashboard preview
Add Area Chart to SSRS Dashboard Example
Lastly, we will add an Area Chart to the SSRS Dashboard. As you can see, we added it 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 refer Area Chart 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 in SSRS dashboard. 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 SSRS Dashboard Report preview
We successfully create an SSRS Dashboard that holds Pie Chart, Column Chart, and an Area Chart.
Thank You for Visiting Our Blog
