Create SSRS Report using Views 9

Create SSRS Report using Views

by

In this article, we will show you how to Create an SSRS Report using Views with an example. It is one of the frequently asked questions by SSRS followers. To demonstrate the same, we are going to use the SalesPerson View that comes with the 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 refer to the SQL VIEWS article that we mentioned in the 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:

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 an SSRS report using Views.

The below SSRS 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 it difficult to write the Custom SQL Query, then use the Query Designer to design the required query.

Create SSRS Report using Views 3

Right-click on the report designer and select the Insert -> Chart option

Create SSRS Report using Views 4

For this Create SSRS Report using Views example, we are selecting Column Chart.

Create SSRS Report using Views 5

Clicking the Ok button displays the Column chart in the design region with dummy data.

Create SSRS Report using Views 6

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 dataset to chart data values and State Province name in the category group.

I suggest you to refer to 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 SSRS Column report using the Sales Person View. Let me Format the chart a bit for the classy look.

Remember, we already explained the Formatting options in Formatting Column Chart.

Create SSRS Report using Views 9