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 followers. To demonstrate the same, we will use the SalesPerson View that comes with the Adventure Works database.
To see the data inside the View, please open SSMS and type the below-mentioned query it will provide the result and the data will show the sales year to date and last year.
If you want to create a custom view on your own, I suggest you refer to the VIEWS article that we mentioned in the SQL Server Tutorial. For this example, we are going to write the Custom query to select three columns from the Sales Person View:
USE [AdventureWorks2014]
GO
SELECT [StateProvinceName], [SalesYTD], [SalesLastYear]
FROM [Sales].[vSalesPerson]
Create SSRS Report using Views
In this example, we will create a Column Chart to display the step by step approach to creating an SSRS report using Views.
The below SSRS screenshot will show you the Data Source and Dataset we used for this Column Chart Report. If you find it difficult to write the Custom Query, use the Query Designer to design the required query.
Right-click on the report designer and select the Insert -> Chart option
For this Create SSRS Report using Views example, we are selecting Column Chart.
Clicking the OK button displays the Column chart in the design region with dummy data. 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 the dataset to chart data values and the State Province name in the category group.
I suggest you refer to the Create Column article to understand the creation of it.
Click on the Preview button to see the report preview. Here, we created an 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 the Formatting Column Chart.
