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.
Right click on the project in Solution Explorer and click on Add New Item. Select DataSet from the list of templates.
Drag and drop a TableAdapter object onto the Dataset Designer. The Wizard will open once this is done.
Setting the SSRS Connections
Select the data connection, if available, or click New Connection to create a new SQL Database 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.
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.
Storing the SSRS DataSet Information
Select SQL Statements from the Command Type and click Next.
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).
Click on the Views tab and select vEmployee from the list and click on the Add button.
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.
Review the SQL Statement. Click Next to continue.
The Choose Methods to Generate are pre-populated with the default Fill and DataTable method names. Click Next to continue.
Click Finish to complete the set up of the TableAdapter in the DataSet.
Add a new item to the project and select Report and name the .rdlc file.
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.
Drag and drop the EmployeeID field into the 1st column, 2nd row of the Matrix from the DataSet in the Website Data Sources tab.
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.
Add Titles to Row 1 in the Matrix and resize and format the report accordingly.
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.
Click on the arrow next to the ReportViewer and click in the Choose Report box. Select the .rdlc file from the list.
Run the project and verify that the report opens in the 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.
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.
In the pop up, click the ellipsis (...) button to open a dialog box.
In the Select Application Pool box, click on Classic .Net AppPool and click OK. This will cause the error to disappear.
For additional information about Business Intelligence Development Studio SSRS Reports, please check out the link here to get started.