Tutorial Gateway

Create SSRS Report using Views

by

In this article we will show you, How to Create SSRS Report using Views with example. This is one of the frequently asked question by SSRS followers. In order to demonstrate the same, we are going to use the SalesPerson View that comes with Adventure Works database.

Below screenshot will show you the data inside the View:

TIP: If you want to create a custom view on your own then I suggest you to refer SQL VIEWS article that we mentioned in SQL Server Tutorial.

Create SSRS Report using Views 1

For this example, we are going to write the Custom SQL query to select three column from the Sales Person View:

SQL Query

USE [AdventureWorks2014]
GO
SELECT [StateProvinceName] 
      ,[SalesYTD]
      ,[SalesLastYear]
  FROM [Sales].[vSalesPerson]

and the data for the above query is:

Create SSRS Report using Views 0

Create SSRS Report using Views

In this example, we are going to create a Column Chart to display the step by step approach to create SSRS report using Views. Below screenshot will show you the, Data Source and Dataset we used for this Column Chart Report.

Create SSRS Report using Views 2

If you find difficult to write the Custom SQL Query, then use the Query Designer to design the required query.

Create SSRS Report using Views 3

In SSRS report design, we can add a Column 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.

Create SSRS Report using Views 4

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 Column Chart as shown in below screenshot.

Create SSRS Report using Views 5

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

Create SSRS Report using Views 6

By Clicking on the empty space around the Column Chart will open the Chart Data window. In this example, we will create a Column chart for the Sales YTD, and Sales last Year so, Drag and drop them from data set to chart data values and State Province name in category group. I suggest you to refer Create Column Chart in SSRS article to understand the creation of Column chart

Create SSRS Report using Views 7

Click on the Preview button to see the report preview.

Create SSRS Report using Views 8

From the above screenshot you can see that, we created a Column chart using the Sales Person View. Let me Format the chart a bit for classy look. Remember, we already explained the Formatting options in Formatting Column Chart article so, Please refer the article to understand the steps.

Create SSRS Report using Views 9

Thank You for Visiting Our Blog