Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Microsoft Reports in an ASP .NET website

How to use a Report Object (.rdlc) file in an ASP .NET Project with a DataSet

This article describes how to use a Report object in an aspx webpage using a DataSet with a TableAdapter.

SSRS Report Object (RDLC) Project Background

This project shows how to create a website to display an SSRS report. It is not the same as creating a simple BIDS SSRS report, but there are similarities. This report will use a SQL view in a DataSet in the Adventure works database. The Connections and formatting are shown here as well as overcoming any obstacles in getting the webpage to display properly.

SSRS Web Site Project in Visual Studio

Using the AdventureWorks database and a SQL Database View as the SQL Data source, note that the View is created in the Human Resources schema with joins between HumanResources, Person tables.

ALTER VIEW [HumanResources].[vEmployee]
AS
SELECT e.[EmployeeID] ,c.[Title] ,c.[FirstName] ,
c.[MiddleName] ,c.[LastName] ,c.[Suffix] ,e.[Title] AS [JobTitle] ,c.[Phone] ,
c.[EmailAddress] ,c.[EmailPromotion] ,a.[AddressLine1] ,a.[AddressLine2] ,a.[City] ,sp.[Name] AS [StateProvinceName] ,a.[PostalCode] ,cr.[Name] AS [CountryRegionName] ,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];

Open Visual Studio and create an ASP .NET Web Site project.

Visual Studio project

Right click on the project in Solution Explorer and click on Add New Item. Select DataSet from the list of templates.

Add DataSet

Drag and drop a TableAdapter object onto the Dataset Designer. The Wizard will open once this is done.

Add TableAdapter

Setting the SSRS Connections

Select the data connection, if available, or click New Connection to create a new SQL Database connection.

Data Connection

If adding a New Connection, select the Server name and Database names from the drop down items. Click Test Connection to verify. If all is OK, Click OK to finish.

New Connection Settings

The Save Connection String to Application Configuration file updates the web.config file and puts the connection string information in it. Click Next to proceed through the next steps.

Connection String Settings

Storing the SSRS DataSet Information

Select SQL Statements from the Command Type and click Next.

Command Type

In the Enter a SQL Statement section for the TableAdapter, click on the Query Builder button to select the item to use (Table or View).

SQL Statement section

Click on the Views tab and select vEmployee from the list and click on the Add button.

Add Table

Select the fields for the TableAdapter by checking the boxes next to them. The statement shown creates a field from the FirstName and LastName fields by concatenating them. Click OK when finished.

Query Builder

Review the SQL Statement. Click Next to continue.

SQL Statement

The Choose Methods to Generate are pre-populated with the default Fill and DataTable method names. Click Next to continue.

Methods

Click Finish to complete the set up of the TableAdapter in the DataSet.

TableAdapter Complete

Add a new item to the project and select Report and name the .rdlc file.

Report Document

SSRS Report Formatting and Formula Expressions

Drag and drop a Matrix object from the toolbox onto the blank report. The DataSet will have more than 2 columns. To add columns to the Matrix, right click in the Data cell and click Add Column.

Add Column

Drag and drop the EmployeeID field into the 1st column, 2nd row of the Matrix from the DataSet in the Website Data Sources tab.

Website Data Sources

Click in the next cell in the 2nd row and go to the Properties box. Under Value, the other Fields are now available to select. Select Fields!FullName.Value and continue doing the same for the next columns across row 2.

Matrix Values from DataSet

Add Titles to Row 1 in the Matrix and resize and format the report accordingly.

Report Formatting

Setting up the ASP.Net web page for the SSRS Report

In the Solution Explorer, double click the Default.aspx page to open it. Make sure it is in Design Mode. Drag and drop a MicrosoftReportViewer in the div section of the page.

Default.aspx

Click on the arrow next to the ReportViewer and click in the Choose Report box. Select the .rdlc file from the list.

ReportViewer

Run the project and verify that the report opens in the web page.

Web Page

Additional SSRS WebPage Notes

Here are some information regarding SQL Server reports on an ASP .NET website.

If you are running IIS 7, an error may appear the first time the report is run as shown below.

Report Error

To fix this error and prevent it from blocking your report, go to the IIS Manager and select the website from the Sites listed. Click on Advanced Settings.

IIS Manager

In the pop up, click the ellipsis (...) button to open a dialog box.

Advanced Settings

In the Select Application Pool box, click on Classic .Net AppPool and click OK. This will cause the error to disappear.

IIS Manager App Pool Settings

For additional information about Business Intelligence Development Studio SSRS Reports, please check out the link here to get started.