How to pass parameters to Reporting Services from .Net applications

please see the clip

In the previous post you saw how to create a report in reporting services and you also learned how to create parameter for the report. You simple can show the same report and you do not need to pass parameter since everything is inside the report. But unfortunately you cannot change the UI of State dropdownlist or View Report button. So you may want to remove this parameter palette and show this information in another custom control then pass the information to reporting service.

First add a dropdownlist to the page that has the states and also a report viewer control and a button to trigger to show the report.

Add all states on the dropdownlist either by loading information from database or adding them manually. Then on button click please write this code

ReportViewer1.ShowCredentialPrompts = false; ReportViewer1.ServerReport.ReportServerCredentials = new ReportCredentials("emady", "MyPassword", "hhi"); ReportViewer1.ServerReport.ReportServerUrl = new Uri("http://falcon/Reportserver"); ReportViewer1.ServerReport.ReportPath = "/Report Project State/Report1"; List<ReportParameter> parameters = new List<ReportParameter>(); parameters.Add(new ReportParameter("state", DropDownListStates.SelectedItem.Text)); ReportViewer1.ServerReport.SetParameters(parameters); ReportViewer1.ProcessingMode = ProcessingMode.Remote; ReportViewer1.ShowParameterPrompts = false; ReportViewer1.ShowPromptAreaButton = false; ReportViewer1.ServerReport.Refresh();

The only change that you may see compare to what we had in previous clip is that:

We set that we do not want seeing any credentials error prompt. Moreover, we create a list of ReportParameter and add the state parameter to this list and we are passing this list to SetParameter method. Also we are hiding parameter part from report. Finally we are hiding any prompt from report server.

Important: ReportCredentials class is a custom class please get the code from previous posts about reporting services. Also please see the clip to understand how it works.

please see the clip

How to create a parameterized report with Reporting Services

please see the clip

I assuming you have already read the previous posts about Reporting Services and you familiar with creating report. Let's say you have the same project in previous post. And you need to show just post code information that is in just one specific State. I am going to explain how to create a state parameter.

Open the report then go to Data View and select"New DataSet..."

Then all you need write a query to show all the states like this:

Then Select the previous data source and change the query as below. Remember when your query has some parameters (in reporting service @variable is a parameter) it will be automatically added to Report Parameters

So this query will need a state to run. From Report menu select Report Parameters you need to select "From query" radio button from "Available values" and also set the default value. As below (for more information see the clip)

The result should be like this:

please see the clip

How to show a report of reporting services from .net applications

please see the clip

After deploying your reports to report server, you need to access these reports and show them in your custom .net applications. This post will help you to do this task. Please see the clip that shows you how to deploy your report and how to access them.

Let's say you have a web application that needs to show reports. Firstly, you need add the ReportViewer control from toolbox (which is in data tab) to your page. Then you need to write some code to load report inside the ReportViewer probably you write this code either in page load or in a button click. Take a look to this code:

ReportViewer1.ServerReport.ReportServerCredentials = new ReportCredentials("emady", "mypassword", "hhi"); ReportViewer1.ServerReport.ReportServerUrl = new Uri("http://falcon/Reportserver"); ReportViewer1.ServerReport.ReportPath = "/Report Project State/Report1"; ReportViewer1.ProcessingMode = ProcessingMode.Remote; ReportViewer1.ServerReport.Refresh();

The first line is trying to pass the credentials to server. Second line is the Reportserver address. Third line specifies the Report address which has Report folder address and the report itself. Forth line sets the processing mode to Remote because you are accessing the report from other application. Finally you need to call Refresh to show the report.

The very tricky part about above code is that I used a Custom class Called ReportCredentials to apply my credentials to the Report server. The reality is that you need a valid credential to access report server so you need to pass this credential by this class which is inherited from IReportServerCredentials. Take a look at the source of this class:

public class ReportCredentials : Microsoft.Reporting.WebForms.IReportServerCredentials { string _userName, _password, _domain; public ReportCredentials(string userName, string password, string domain) { _userName = userName; _password = password; _domain = domain; } public System.Security.Principal.WindowsIdentity ImpersonationUser { get { return null; } } public System.Net.ICredentials NetworkCredentials { get { return new System.Net.NetworkCredential(_userName, _password, _domain); } } public bool GetFormsCredentials(out System.Net.Cookie authCoki, out string userName, out string password, out string authority) { userName = _userName; password = _password; authority = _domain; authCoki = new System.Net.Cookie(".ASPXAUTH", ".ASPXAUTH", "/", "hhi"); return true; } }

please see the clip

Create a report with Reporting services

See the clip

What is Reporting Service?

Reporting service is a free application coming with sqlserver so if you have sql server in your organization then probably you have Reporting service as well. It is a repository for your reports you can create reports by some tools then publish your report to this repository then your applications including windows and web can send request to this repository and get the report in a browser. Since you can create complex report to show different charts and graphs, it is one of the handy tools to use specially in Intranet web applications. Also you can use reporting service for internet applications However, you will be charged for that.

What do you need to know about Reporting services?

1- How to create a report

2- How to publish a report

3- How to see the report in the server

4- How to access a report from other .net applications (Next post)

5- How to create a chart report (there is a sample in clip)

6- How to create a parameterized report (future posts)

7- How to pass parameters to a report from other applications (future posts)

How to create a report?

When you install sqlserver2005 you have a special template will be added to your visual studio which is "Sql server Business intelligence development studio". You can use this template to create a report then as I explained you have to publish your report then other applications can address that report. Open visual studio from file menu select New Project then you can select Business intelligence project. In this list you can find Report server project wizard or Report Server project. Using Wizard help you to speed up implementation of you report.

Let's select Report Server Project. As you see in solution explorer you have two folders one is shared data source and another one is Reports. You need a connection to Database in Reporting service we call that Data source. But if you have multiple reports over one data base you better create one Shared data source to share between them. Because if database changed then you need just change shared data source and everything should be all right. The reports folder is where you create your Reports. I recommend you to use wizard to create the basics of your report then change report to the way you like.

Create a share data source:

Right click on Shared Data sources folder and select Add new data source then you will see this window:

All you need to do provide an alias name for the shared data source and a correct connection string. The connection string implies to which data base shared data source can connect. You may click on Edit then you will see a familiar window to define a connection string. You can use windows or sql server authentication. Then click on ok. You are done. Now you have a shared data source that can be used in different reports in your project.

How to create a report?

All you need right click on Reports and select Add New Report then you will see the wizard window. As I said you better use wizard then customize your report the way you like later on. (I am going to take a report from a table that has suburbs, postcodes, states)

Click on next in next page select the shared data source as you see you can select a custom data source but I do not recommend this. Using shared data source is more maintainable.

In Next step you need to define your query in our case we are retrieving data from a table but you can retrieve data form view as well. If you take a look at the next picture you will see the query builder button when you go to that page in left top corner of window there is a button which is generic query designer then you can add the tables and views you want and define the query with query designer assistance.

In next step you need to define your report type in our case we want Tabular:

In next step you have the ability to define which fields you need to show in report also you can define group by to show details of one product (please see the clip to see a sample of this) right now we just choose the fields we want to have in our report.

Select a style for your Report and click on next you are done

You have three views: Data, Layout and Preview

In the Data view you can see and modify your query also you can add other queries you may use in the report (I will explain how to add another query for this report and why you may need to do that in future posts)

In layout you can change look and feel of your report or add other element please see your toolbox when you are in this view then you will see these elements (I will talk about some of these elements in next posts):

In the Preview tab you can see the result which is your report.

How to publish a report to reporting server

Next step is that you need to publish you report to Reporting Server to do this you need to right click on your project in solution explorer and select properties. Then you need define 4 properties at least:

TargetServerUrl: the reportserver address usually it is the same server as your sqlserver with report server virtual directory see the picture

TargetRootFolder: define in which folder the report should be deployed in your reporting server

TargetDatasourceFolder: define in which folder the data sources should be deployed in your reporting server

OverwriteDataSources: define whether you prefer to overwrite previous deployed data sources.

I highly recommend specify the same folder for TargetRootFolder and TargetDatasourceFolder because if you are getting Reporting services for an internet site from a shared reporting services in internet you will be offered just one folder so you need to have all of them in one folder.

Click on ok again right click on your project select deploy then after a while it will be deployed to the server.

How to check report in your server?

Go to your reporting service through a browser like this: http://yourserver/Reportingserver in my case I have to use http://falcon/Reporingserver then in that page find the folder in my case TestProjectReport then you will see all reports in that folder select your report in my case Report1

Then you will see the result.
The very power full point about Reporting service is that you can export data to different sources like pdf, Excel, Web services and etc.

Please see the clip to get some more information. Also you will learn how to create Chart reports.

See the clip