Creating .rdl report in SSRS and deploying into ASP.NET

SSRS Series Part I: Various ways of Report creation, deployment and invocation

Table of Content

1. Introduction
2. Background
3. Data Source
4. Creating a basic Tabular SSRS Report using Report Wizard (Download BasicTabularReport.zip file)
5. Report Deployment on Report Server
6. Invoking the deployed report from Asp.net (Download Asp.net_SSRS_Reports.zip file)
7. Creating a Matrix report using Report Wizard (Download MatrixReportWithWizard.zip file)
8. Creating a Matrix report without Report Wizard (Download MatrixReprtWithoutWizard.zip file)
9. Creating a SSRS report using Microsoft ReportViewer Control in a Windows form (Download Winforms_Reports.zip file)
10. Creating a SSRS report using Microsoft ReportViewer Control in a WPF Application (Download Wpf_Reports.zip file)
11. Conclusion

Introduction

Since the last few months I have been working in SSRS and has gained some knowledge which I wanted to share here especially for those who are about to start learning SSRS from more of a step by step approach as well as for those who has generated some basic SSRS reports but did not got much exposure to it and wanted to learn more. I will start this journey from here with the very basic report generation and will move ahead from time to time. This will be an nth series of articles so in every part I will talk about some new feature. Kindly note that I am using Sql Server Denali CTP 1 for this article.

Background

SSRS has now become a defracto reporting tool and has become a necessity rather than luxury to become familiarise with it.I have found many peoples who have interest in the BI side, but didnot get any chance to work with because of many reason(may be they are not getting the exposure in their work field, lack of time to spend on the subject, frequent movement of projects etc.).Henceforth, I thought of writing this series of articles (SSRS/SSIS/SSAS) where basically I will talk about those features which I have touched upon as of now in my real time project. I will try to compile this series of articles more of step by step hands on approach so that people can refresh/learn by looking into it.Afterall, one picture is worth a thousand words.

Data Source

For this experiment, we will use the below script to generate and populate the Players table which is named as (tbl_Players)

Collapse | Copy Code

-- Drop the table if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Players' AND type = 'U')
    DROP TABLE tbl_Players
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_Players (
 PlayerID INT IDENTITY,
 PlayerName VARCHAR(15),
 BelongsTo VARCHAR(15),
 MatchPlayed INT,
 RunsMade INT,
 WicketsTaken INT,
 FeePerMatch NUMERIC(16,2)
)

--Insert the records
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Won','India',10,440,10, 1000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Cricket','India',10,50,17, 400000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('B. Dhanman','India',10,650,0,3600000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('C. Barsat','India',10,950,0,5000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Mirza','India',2,3,38, 3600000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('M. Karol','US',15,44,4, 2000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Hamsa','US',3,580,0, 400)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Loly','US',6,500,12,800000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Summer','US',87,50,8,1230000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.June','US',12,510,9, 4988000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A.Namaki','Australia',1,4,180, 999999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Samaki','Australia',2,6,147, 888888)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('MS. Kaki','Australia',40,66,0,1234)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Boon','Australia',170,888,10,890)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('DC. Shane','Australia',28,39,338, 4444499)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Noami','Singapore',165,484,45, 5678)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Biswas','Singapore',73,51,50, 22222)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Dolly','Singapore',65,59,1,99999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Winter','Singapore',7,50,8,12)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.August','Singapore',9,99,98, 890)

The partial output after running a simple Select query

Collapse | Copy Code

 Select * from tbl_Players 

is as under


We will also have the below stored procedure created in our database whose script is as under

Collapse | Copy Code

If Exists (Select * from sys.objects where name = 'usp_SelectRecordsByPlayerName' and type = 'P')
    Drop Procedure usp_SelectRecordsByPlayerName
Go
-- Create the  stored procedure
Create Procedure [dbo].[usp_SelectRecordsByPlayerName]
( @PlayerID int )
As
Begin
 Select 
  PlayerID
  ,PlayerName
  , BelongsTo
  , MatchPlayed
  ,RunsMade
  ,WicketsTaken
  ,FeePerMatch
 From
 tbl_Players
 Where PlayerId = @PlayerID
End

(A) Creating a basic Tabular SSRS Report using Report Wizard

Step 1: Open BIDS
Step 2: Choose Report Server Project from the available project type, give a suitable name to the project and click on OK button.

Step 3: After clicking on OK button the below screen will appear

Step 4: Let us click on the Shared Data Source - > Add New Data Source

Step 5: Let us enter proper information in the Shared Data Source Properties and click on OK button.

Step 6: After clicking on the OK button, the following screen will appear

Step 7: Next click on Reports - > Add New Report

and the report wizard will appear as under

Upon clicking on Next button, we get the Design the Query window where we will write an inline query to fetch the records from the Players table.

Click on Query Builder button and the Query Designer screen opens up .Note that 2 options are there in the table type

1. Text (where we write the normal /inline SQL query)
2. Stored Procedure

But in this example, we are interested only in the inline query; so we will choose Text.
Note: ~ we can click on the Import button and can specify the SQL file that contains the script. For specifying stored procedure , we need to specify the stored procedure name with a parameter name (since our's accept a parameter)

Now let us write the below query and click on the Run Icon (!)

Collapse | Copy Code

Select 
 PlayerID
 ,PlayerName,
 BelongsTo
 ,MatchPlayed,
 RunsMade
 ,WicketsTaken
 ,FeePerMatch
From tbl_Players

Click OK button and let's come back to the Design the Query window again

Click Next and from the Select the Report Type Screen that appears, let us choose Tabular as the report type and click on Next button.

And that brings up Design the Table Screen. From the Available Fields List box, let us choose all the fields and put those under the Displayed Fields of the Detailed section by clicking on Details button.

Clicking on Next button will display Choose the Table Style Screen from where we can choose the various table styles. We will choose Ocean here

Click on the Next button brings up the final screen where we will specify a suitable name to our report.

Click Finish.
Step 8: Report created. If we look now into the Reports folder, Player Report.rdl has been generated.

The Report design view is

Now let us click the Preview button to get the full report

Note: If we have specified a parameter, then at the time of preview we need to give some value to it as shown under

So far we have seen how to build the report. Now comes the part of deployment.

(B) Report Deployment on Report Server

Once the report has been made, next we need to deploy that on the report server. To do so, we need to know the report server name.
From the Configuration Tools, we need to choose the Reporting Services Configuration Manager

The Reporting Services Configuration Manager window opens up as shown under

Click on the Connect button and we will be presented with the below screen

I have deliberately highlighted the Reporting Server Database Name (which is ReportServer$NILADRIDENALI). We will see something about it within a short while. Let us click on the Report Manager URL tab in order to get the Report Server URL.

Click on the URL and we will be asked to enter the Windows credentials

Once entered, click on the OK button and we will get a screen as the below

At this point of time let us query the Catalog table of ReportServer$NILADRIDENALI database

Collapse | Copy Code

 Select * from dbo.Catalog 

and the result is as under

We have done this because when we design a report, Report Definition Language File (rdl) gets generated. It is basically a XML document file that contains all the information about how we design our report. As soon as we deploy the report, the rdl file gets inserted into the Report Server Database. We can query the catalog table to get the information about the same. We will look into that part shortly. But at present, our report is absent in this database.
Now let us come back to our SSRS project. Let us right click on the BasicSSRSReport_Part1 project's properties menu.

Alternatively, we can do the same by choosing the Properties menu from the Projects tab

In either of the case, we will be directed to the properties page where we will enter the Target Server URL obtained from the Report Manager URL of the Reporting Services Configuration Manager.

After we click on the OK button , lets choose the project, right click and click on the Deploy from the available context menu.

If everything goes fine, we will receive a success message

At this point if we query our Catalog table by using the same query as described above, we will receive the below output

So our report has been inserted into the Report Server Database. We can also figure out that, the rdl content has been inserted into the Content column (whose type is image) of the Catalog table. We can obtain the XML content from this hexadecimal representation and then use XQuery to get more information about the reports. To get the XML content, we can issue the below query

Collapse | Copy Code

Select
 [Report Name] = Name,
 [Rdl Content] = CAST(CAST(Content As Varbinary(Max)) As XML) 
From Catalog
Where Type = 2

The output being as under

Now let us visit our report from the browser. Click the URL available in the Report Manager URL and we will get the below

Now Click on the BasicSSRS_Report_Part1 and it will show the list of available report (in this case only one though i.e. Player Rport).

Click on the Player Report and we will get the Player Report as shown under

(C) Invoking the deployed report from Asp.net

Now that our report has been deployed in the report sever, we can invoke the same through the client application. In this section, we will use Asp.net as the client application. Also I am using VS2010 but it hardly matters .We can follow the same in earlier versions of VS. Here are the steps to be followed for doing so.
Step 1:Open Visual Studio. From File menu, choose New->Website. And from the available templates, choose ASP.Net Web site. And click OK button.

Step 2:From the toolbox item, let us drag and drop the below controls into the designer layout

1. One ScriptManager control (Available in Ajax Extensions)
2. One Button control (Available in Standard Section)
3. One ReportViewer control (Available in Reporting Section)

The html code is as under

Collapse | Copy Code

<body>
    <form id="form1" runat="server">
    <div>
        <asp:ScriptManager ID="ScriptManager1" runat="server" />
    </div>
    <table>
        <tr>
            <td>
                <asp:Button ID="btnShowReport" runat="server" Text="Display Report" Font-Bold="True" />
            </td>
        </tr>
        <tr>
            <td>
                <rsweb:ReportViewer ID="repViewer" runat="server" Width="1044px" Height="488px" />
            </td>
        </tr>
    </table>
    </form>
</body>

And the design looks as under

Step 3:Now let us double click on the button and add the below code in the code behind of the Button click event

Collapse | Copy Code

try
{
    repViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
    repViewer.ServerReport.ReportServerUrl = new Uri("http://niladri-pc/ReportServer_NILADRIDENALI");
    repViewer.ServerReport.ReportPath = "/BasicSSRSReport_Part1/Player Report";
    repViewer.ServerReport.Refresh();
}
catch (Exception ex)
{
    throw ex;
}

Our player report is located in the BasicSSRSReport_Part1/Player Report path in the server. We can get this information after we click the URls present in the Report Manager URL of the Reporting Services Configuration Manager.
First we will get a screen that will give the root directory which is BasicSSRSReport_Part1 here.

Upon clicking on the BasicSSRS_Report_Part1 directory link, we will get the Player Report folder

Anyways, now run the application and on Display Report button click we will get the report as under

So, in this section we learnt how to invoke the report which is deployed in the server through the client application.
N.B.~ The example that we have seen here is without any parameter i.e. our report does not accept any parameter. But in real time, it will accept parameter(s).Let us make a small change to our original report. Let us write the below query in the query designer

Collapse | Copy Code

Select 
 PlayerID
 ,PlayerName,
 BelongsTo
 ,MatchPlayed,
 RunsMade
 ,WicketsTaken
 ,FeePerMatch
From tbl_Players
Where PlayerID = @PlayerID

The same can be done by using stored procedure. We have already seen that in Creating a basic Tabular SSRS Report using Report Wizard section
The other thing will remenain unchanged. When we run the report it will ask for PlayerID parameter; upon supplying which the report will be generated. And it has been added in the Dataset properties. Let us seee the below figure which will give the true picture

As can be seen that, we have opened the dataset properties and from there we have visited the Parameters section. There we have specified the Parameter name (which is @PlayerID) and in the expression we have specified the value as =Parameters!PlayerID.Value .That's it.
And add the below code in the button click event

Collapse | Copy Code

try
{
 ReportParameter[] repParams = new ReportParameter[1];
 repParams[0] = new ReportParameter("PlayerID", "10");
 repViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
 repViewer.ServerReport.ReportServerUrl = new Uri("http://niladri-pc/ReportServer_NILADRIDENALI");
 repViewer.ServerReport.ReportPath = "/BasicSSRSReport_Part1/Player Report";
 repViewer.ServerReport.SetParameters(repParams);
 repViewer.ServerReport.Refresh();
}
catch (Exception ex)
{
 throw ex;
}

And ofcourse, we need to add the namespace Microsoft.Reporting.WebForms

(D) Creating a Matrix report using Report Wizard

In this section we will look into the steps for creation of the Matrix report which is basically use for generating pivot table style reports. The way of creating this type of report is very similar to that of a Tabular report which we have already explored with a subtle change. Henceforth instead of doing the similar steps that has been followed in Tabular report creation, we will see only from that point where basically we need to set the Matrix report type and will move ahead. We will start from the Select Report Type Screen. Please follow the steps provided in the Creating a basic Tabular SSRS Report section for setting up the previous steps.
Step 1:Let us choose Matrix as the report type and click on Next button.

And that brings up Design the Table Screen. Suppose we want to see, players belonging to which country is taking how much match fee. Basically, we want to see the BelongsTo field in the Report Column/Header, the PlayerName field in the rows and the FeePerMatch field in the Details section

Clicking on Next button will display Choose the Table Style Screen from where we can choose the various table styles. We will choose Forest here

Click on the Next button brings up the final screen where we will specify a suitable name to our report.

Click Finish.
Step 2:Run the report or click on the preview button and the result is as under

(E) Creating a Matrix report without Report Wizard

So far we have seen both the Tabular and Matrix Report generations by using Report Wizard. Now we will look into how to create the same without wizard. Basically we will generate the same Matrix report without wizard.
Let us see how we can do so.
Step 1: Open BIDS
Step 2: Choose Report Server Project from the available project type, give a suitable name to the project and click on OK button.

Step 3: After clicking on OK button the below screen will appear

Step 4: Let us click on the Shared Data Source - > Add New Data Source
Step 5: Let us enter proper information in the Shared Data Source Properties and click on OK button.

Step 6: After clicking on the OK button, the following screen will appear

Step 7: Click on the Reports folder - > Add - > New Item

Step 8: Choose Report from the Report Project Categories and enter a suitable name. Click Add button.

Step 9: After that, we will be presented with the below designer

From the Report Item toolbox, we will drag and drop the Matrix control. And in course of that, the Dataset properties window appears. Choose "Use a dataset embedded in my report" option.

Click on the New button. From the Data Source Properties window, choose "Use data set reference" option. And lastly click OK.

Now, in the DataSet properties window, let us add the below query in the query designer and run it by clicking on the Execute button(!) in order to ensure that the query works fine.

Collapse | Copy Code

SELECT 
      [PlayerName]
      ,[BelongsTo]      
      ,[FeePerMatch]
 FROM [SSRSExperiment].[dbo].[tbl_Players]

The DataSet properties window will now look as under

Step 10:Now from the available datasets we will fill into the Rows, Columns and the Data section of the Matrix and will add some customization like adding a row header, making the columns bold etc.

Upon previewing we get our Matrix report

Note: Like Matrix report, Tabular reports can be created in the same fashion in which case instead of Matrix control, we have to use the Table control.

(F) Creating a SSRS report using Microsoft ReportViewer Control in a Windows form

We have seen how to create a reports by using Report Designer as well as without using report designer.Now we will look into how to create a report using the Microsoft Report Viewer Control in a Windows Form. Well, the above approach can be applied to do reporting in windows application also, but this section will focus more into the usage of Report Viewer control.
For this demonstration, we will be using the stored procedure usp_SelectRecordsByPlayerName which has already been described in the Data Source section
Step 1: Open VS 2010 and create a windows application.

Step 2: Next, let us click on Add->New Items menu

Step 3: From the list of available templates, let us choose DataSet and name it as "PlayerDataset"

Step 4: Next let us click Server Explorer available in the View Menu. And add a new connection from the Data Connection as depicted under

Step 5: Choose the SQL Server Connection, Server Name, Database Name and press the Test Connection button. Once successful, click on the OK button

Step 6: Now expand the stored procedure tab and choose the stored procedure usp_SelectRecordsByPlayerName and drag and drop the same onto the Player Dataset design surface.

Step 7: Next, let us click on Add->New Items menu. From the list of available templates, let us choose Report and name it as "PlayerReport.rdlc"

Step 8: Drag and drop a TABLE item from the Reports Item Toolbox on the Report Form. In the course of doing so, we need to specify the Data Source in the Dataset Properties window which is PlayerDataSet here.

Once, the Dataset Properties has been set, the Table gets added in the Report Form

Step 9: Next from the PlayerDataset, we will add those field names in the Report Form, in which we are really interested in.

Step 10: Let us create a Windows Form say DisplayPlayerReport and add the following controls to it

Control Type	Control ID	Control Display Text
Label	lblPlayerID	Enter Player ID
Textbox	txtPlayerID
Button	btnGenerateReport	Generate Report
Report Viewer	rvPlayer

Click on the |> sign on the top right corner of the ReportViewer control and choose the report file

Step 11: In the Generate Report Button Click event, add the below

Collapse | Copy Code

private void btnGenerateReport_Click(object sender, EventArgs e)
{
 this.usp_SelectRecordsByPlayerNameTableAdapter.Fill
(this.PlayerDataSet.usp_SelectRecordsByPlayerName,Convert.ToInt32(txtPlayerID.Text));

            this.rvPlayer.RefreshReport();
 }

Step 12: Run the application. Enter a PlayerId and click on the Generate Report Button to get the below output.

N.B.~ Though in this example, we have seen of calling a local report, however, we can invoke the report deployed in the server in the same fashion as we have done for Asp.net application example.In the following topic, we will look into how to invoke the report through WPF

(G)Creating a SSRS report using Microsoft ReportViewer Control in a WPF Application

Step 1: Create a WPF project and add a reference to Microsoft.ReportViewer.WinForms assembly.

Step 2: Add the clr namespace in the xaml e.g. clr-namespace:Microsoft.Reporting.WinForms;assembly=Microsoft.ReportViewer.WinForms and then add a button control and a WindowsFormsHost control. Inside the WindowsFormsHost control, let us add the Microsoft ReportViewer Control
The xaml code looks as under

Collapse | Copy Code

<Window x:Class="WpfReport.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:rv="clr-namespace:Microsoft.Reporting.WinForms;assembly=Microsoft.ReportViewer.WinForms" 
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <Button Content="Display Report" Height="23" HorizontalAlignment="Left" Margin="17,17,0,0" Name="btnDisplayReport" VerticalAlignment="Top" Width="103" />
        <WindowsFormsHost Height="215" HorizontalAlignment="Left" Margin="25,67,0,0" Name="winFormHost" 
                          VerticalAlignment="Top" Width="466">
            <rv:ReportViewer x:Name="repViewer"/>            
        </WindowsFormsHost>
    </Grid>
</Window>

The design view is presented below

Step 3: In the button click event, let us write the below code

Collapse | Copy Code

try
{
 repViewer.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote;
 repViewer.ServerReport.ReportServerUrl = new Uri("http://niladri-pc/ReportServer_NILADRIDENALI");
 repViewer.ServerReport.ReportPath = "/BasicSSRSReport_Part1/Player Report";
 repViewer.ServerReport.Refresh();
}
catch (Exception ex)
{
 throw ex;
}

That's all. Run the report and we will get the desired output