Tuesday, June 10, 2014

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


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)
-- 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
 Select * from tbl_Players  
is as under
Report_CDI/1.jpg
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

(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.
Report_CDI/2.jpg
Step 3: After clicking on OK button the below screen will appear
Report_CDI/3.jpg
Step 4: Let us click on the Shared Data Source - > Add New Data Source
Report_CDI/4.jpg
Step 5: Let us enter proper information in the Shared Data Source Properties and click on OK button.
Report_CDI/5.jpg
Step 6: After clicking on the OK button, the following screen will appear
Report_CDI/6.jpg
Step 7: Next click on Reports - > Add New Report
Report_CDI/7.jpg
and the report wizard will appear as under
Report_CDI/8.jpg
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.
Report_CDI/9.jpg
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
Report_CDI/10.jpg
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)
Report_CDI/11.jpg
Now let us write the below query and click on the Run Icon (!)
Select 
 PlayerID,
 PlayerName,
 BelongsTo,
 MatchPlayed,
 RunsMade,
 WicketsTaken,
 FeePerMatch
From tbl_Players
Report_CDI/12.jpg
Click OK button and let's come back to the Design the Query window again
Report_CDI/13.jpg
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.
Report_CDI/14.jpg
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.
Report_CDI/15.jpg
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
Report_CDI/16.jpg
Click on the Next button brings up the final screen where we will specify a suitable name to our report.
Report_CDI/17.jpg
Click Finish.
Step 8: Report created. If we look now into the Reports folder, Player Report.rdl has been generated.
Report_CDI/18.jpg
The Report design view is
Report_CDI/19.jpg
Now let us click the Preview button to get the full report
Report_CDI/20.jpg
Note: If we have specified a parameter, then at the time of preview we need to give some value to it as shown under
Report_CDI/21.jpg
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
Report_CDI/22.jpg
The Reporting Services Configuration Manager window opens up as shown under
Report_CDI/23.jpg
Click on the Connect button and we will be presented with the below screen
Report_CDI/24.jpg
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.
Report_CDI/25.jpg
Click on the URL and we will be asked to enter the Windows credentials
Report_CDI/26.jpg
Once entered, click on the OK button and we will get a screen as the below
Report_CDI/27.jpg
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
Report_CDI/28.jpg
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.
Report_CDI/29.jpg
Alternatively, we can do the same by choosing the Properties menu from the Projects tab
Report_CDI/30.jpg
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.
Report_CDI/31.jpg
After we click on the OK button , lets choose the project, right click and click on the Deploy from the available context menu.
Report_CDI/32.jpg
If everything goes fine, we will receive a success message
Report_CDI/33.jpg
At this point if we query our Catalog table by using the same query as described above, we will receive the below output
Report_CDI/34.jpg
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
Report_CDI/35.jpg
Now let us visit our report from the browser. Click the URL available in the Report Manager URL and we will get the below
Report_CDI/36.jpg
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).
Report_CDI/37.jpg
Click on the Player Report and we will get the Player Report as shown under
Report_CDI/38.jpg

(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.
Report_CDI/39.jpg
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
<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
Report_CDI/40.jpg
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.
Report_CDI/41.jpg
Upon clicking on the BasicSSRS_Report_Part1 directory link, we will get the Player Report folder
Report_CDI/42.jpg
Anyways, now run the application and on Display Report button click we will get the report as under
Report_CDI/43.jpg
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
Report_CDI/44.jpg
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

(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.
Report_CDI/45.jpg
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
Report_CDI/46.jpg
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
Report_CDI/47.jpg
Click on the Next button brings up the final screen where we will specify a suitable name to our report.
Report_CDI/48.jpg
Click Finish.
Step 2:Run the report or click on the preview button and the result is as under
Report_CDI/49.jpg

(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.
Report_CDI/50.jpg
Step 3: After clicking on OK button the below screen will appear
Report_CDI/51.jpg
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.
Report_CDI/52.jpg
Step 6: After clicking on the OK button, the following screen will appear
Report_CDI/53.jpg
Step 7: Click on the Reports folder - > Add - > New Item
Report_CDI/54.jpg
Step 8: Choose Report from the Report Project Categories and enter a suitable name. Click Add button.
Report_CDI/55.jpg
Step 9: After that, we will be presented with the below designer
Report_CDI/56.jpg
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.
Report_CDI/57.jpg
Click on the New button. From the Data Source Properties window, choose "Use data set reference" option. And lastly click OK.
Report_CDI/58.jpg
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
Report_CDI/59.jpg
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.
Report_CDI/60.jpg
Upon previewing we get our Matrix report
Report_CDI/61.jpg
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.
Report_CDI/62.jpg
Step 2: Next, let us click on Add->New Items menu
Report_CDI/63.jpg
Step 3: From the list of available templates, let us choose DataSet and name it as "PlayerDataset"
Report_CDI/64.jpg
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
Report_CDI/65.jpg
Step 5: Choose the SQL Server Connection, Server Name, Database Name and press the Test Connection button. Once successful, click on the OK button
Report_CDI/66.jpg
Step 6: Now expand the stored procedure tab and choose the stored procedure usp_SelectRecordsByPlayerNameand drag and drop the same onto the Player Dataset design surface.
Report_CDI/67.jpg
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"
Report_CDI/68.jpg
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.
Report_CDI/69.jpg
Once, the Dataset Properties has been set, the Table gets added in the Report Form
Report_CDI/70.jpg
Step 9: Next from the PlayerDataset, we will add those field names in the Report Form, in which we are really interested in.
Report_CDI/71.jpg
Step 10: Let us create a Windows Form say DisplayPlayerReport and add the following controls to it
Control TypeControl IDControl Display Text
LabellblPlayerIDEnter Player ID
TextboxtxtPlayerID
ButtonbtnGenerateReportGenerate Report
Report ViewerrvPlayer
Click on the |> sign on the top right corner of the ReportViewer control and choose the report file
Report_CDI/72.jpg
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.
Report_CDI/73.jpg
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.
Report_CDI/74.jpg
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
Report_CDI/75.jpg
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

Conclusion

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.