SSRS Series Part I: Various ways of Report creation, deployment and invocation
Table of Content
- Introduction
- Background
- Data Source
- Creating a basic Tabular SSRS Report using Report Wizard (Download BasicTabularReport.zip file)
- Report Deployment on Report Server
- Invoking the deployed report from Asp.net (Download Asp.net_SSRS_Reports.zip file)
- Creating a Matrix report using Report Wizard (Download MatrixReportWithWizard.zip file)
- Creating a Matrix report without Report Wizard (Download MatrixReprtWithoutWizard.zip file)
- Creating a SSRS report using Microsoft ReportViewer Control in a Windows form (Download Winforms_Reports.zip file)
- Creating a SSRS report using Microsoft ReportViewer Control in a WPF Application (Download Wpf_Reports.zip file)
- Conclusion
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.
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.
For this experiment, we will use the below script to generate and populate the Players table which is named as (tbl_Players)
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 TABLE tbl_Players (
PlayerID INT IDENTITY,
PlayerName VARCHAR(15),
BelongsTo VARCHAR(15),
MatchPlayed INT,
RunsMade INT,
WicketsTaken INT,
FeePerMatch NUMERIC(16,2)
)
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
Select * from tbl_Players
is as under
We will also have the below stored procedure created in our database whose script is as under
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
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
- Text (where we write the normal /inline SQL query)
- 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 (!)
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.
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
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
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
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
- One ScriptManager control (Available in Ajax Extensions)
- One Button control (Available in Standard Section)
- One ReportViewer control (Available in Reporting Section)
The html code is as under
<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
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
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
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
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
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.
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.
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
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
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
<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
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
Hope this article has helped those who wants to start learning SSRS and equally those who wants to refresh their reporting topics in which they are not in touch for a long time.But this is not the end.It is just an introductory topic about the myraid ways we can create report, how we can deploy them, how to use parametrised report, using stored procedure in reports, how to invoke the report from various kinds of client application like Asp.net, WPF, Windows forms etc. Also we learnt the usage of Report Viewer control etc.In the next article we will look into other nice aspects of SSRS as expressions, data bars,indicators etc with more real time examples.Share your thoughts about the article and let me know if I have missed out any other way we can create reports.
Thanks for reading the article.
