Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

SSRS Dashboard Reports

by suresh

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 SSRS Dashboard example, we are going to use the Shared Data Source, and Shared Dataset.

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

SSRS Dashboard Reports 1

and the Custom SQL Query we used against the SQL Server Data Source for this SSRS Dashboard report 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.ProductCategoryKey

How 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 SSRS Dashboard creation, 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 right-click on the report designer to open the context menu.

Please select the Insert -> Chart option from the context menu, as we showed below.

SSRS Dashboard Reports 2

Selecting the Chart option will open a new window called Select Chart Type. For this example, we had selected Pie Chart. 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.

SSRS Dashboard Reports 3

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.

SSRS Dashboard Reports 4

Click on the Preview button to see the report preview of SSRS Dashboard. 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 view the product Subcategories that only belong to Bikes.

SSRS Dashboard Reports 5

Add Filters at Dataset level in SSRS Dashboard

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 choose that option, a new window called Dataset Properties will open. Please select the Filters tab, 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.

SSRS Dashboard Reports 6

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 belong to Bikes Category.

SSRS Dashboard Reports 7

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 choose the Insert -> Chart option from it.

SSRS Dashboard Reports 8

Once you select the Chart option, a new window called Select Chart Type opens. Here, select the Column Chart.

SSRS Dashboard Reports 9

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 the category group. I suggest you refer Column Chart in SSRS to understand the steps in detail.

SSRS Dashboard Reports 10

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

SSRS Dashboard Reports 11

Let me show you the SSRS dashboard preview

SSRS Dashboard Reports 12

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. Select the Insert -> Chart option.

SSRS Dashboard Reports 13

Here, we are selecting an Area Chart.

SSRS Dashboard Reports 14

Click on the 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 he Total Product Cost, Sales Amount columns from dataset to chart data values, and Product Name in the category group. I suggest you refer to Area Chart to understand the steps in det

SSRS Dashboard Reports 12

Next, we did some formatting to the Area Chart to look neat and clean.

SSRS Dashboard Reports 16

Let me adjust the Charts in the SSRS dashboard so that we can view it as a Group.

NOTE: You can also use the Rectangle Report Item to place the Charts in SSRS Dashboard. It can give further formatting options.

SSRS Dashboard Reports 17

Let me show you the SSRS Dashboard Report preview

SSRS Dashboard Reports 18

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

Placed Under: SSRS

  • Learn SSRS in 28 Days
  • Create New SSRS Project
  • Create a New SSRS Report
  • SSRS Query Designer
  • SSRS Shared Data Source
  • SSRS Shared Dataset
  • SSRS Embedded Dataset
  • SSRS Embedded Data Source
  • SSRS Lookup – combine datasets
  • SSRS Report Items
  • SSRS Table Report
  • SSRS Table using report wizard
  • Format SSRS Table Report
  • SSRS Matrix Report
  • SSRS Matrix with report wizard
  • Display Image in SSRS Report
  • Add Rectangle to SSRS Report
  • Add TextBox to SSRS Report
  • Create a SSRS List Report
  • Grouping in SSRS Matrix Reports
  • Grouping in SSRS Table Reports
  • Create SSRS Report using Views
  • SSRS report with store procedure
  • Add Total and Subtotal to Report
  • Add Headers & Footers to Report
  • SSRS Global References
  • Export Report to PDF, XML, Excel, Word
  • Format SSRS Table Report
  • Format Textbox Fonts & Bg Color
  • Format Numbers in SSRS Report
  • Format SSRS Report Date, Time
  • Add Row Numbers to Report
  • Headers Visible While Scrolling
  • Repeat Headers on Each Page
  • SSRS Sorting
  • SSRS Interactive Sort
  • Filters at Dataset Level in SSRS
  • Filters at Tablix Level in SSRS
  • Page Breaks in SSRS Report
  • Custom Code in SSRS Report
  • SSRS Calculated Fields
  • SSRS Dashboard Reports
  • SSRS Document Map
  • SSRS Drill Through Reports
  • SSRS Drill Down Reports
  • SSRS Drill Down Matrix Report
  • SSRS Go To URL Action
  • SSRS Subreports
  • SSRS Report Parameters
  • SSRS Cascading Parameters
  • SSRS Multiple Parameters
  • SSRS Multi Value Parameter
  • SSRS Drop Down List Parameters
  • SSRS Charts
  • SSRS Area Chart
  • SSRS Bubble Chart
  • SSRS Column Chart
  • Format SSRS Column Chart
  • SSRS Data Bars
  • Data Bars in SSRS Matrix Reports
  • Format SSRS Data Bars
  • SSRS Funnel Chart
  • SSRS Linear Gauges
  • SSRS Radial Gauges
  • Format SSRS Gauges
  • SSRS Indicators
  • Format SSRS Indicators
  • SSRS Line Chart
  • SSRS Pie Chart
  • Format SSRS Pie Chart
  • SSRS Pyramid Chart
  • SSRS Range Chart
  • SSRS Radar Chart
  • SSRS Scatter Plot
  • SSRS Sparkline
  • SSRS Stacked Bar Chart
  • SSRS Configuration Manager
  • Change Report Manager Title
  • SSRS Deploying Reports in BIDS
  • Deploy using Report Manager
  • SSRS Report Manager Shared DataSource
  • SSRS Cached Reports
  • SSRS Linked Reports
  • SSRS Report Parts
  • SSRS Report Snapshot
  • SSRS Report History Snapshot
  • SSRS Report Subscription
  • SSRS Security
  • Upload File to Report Manager
  • Install SSRS Report builder
  • Report Builder – Create a Report
  • Report Builder – Create a Dataset
  • Report Builder- Create Bar chart
  • Report Builder – Create Pie Chart
  • Report Builder – Create report without wizard

Copyright © 2021· All Rights Reserved.
About | Contact | Privacy Policy