.NET Buzz Forum - Using SQL reporting services in an asp.net application with some notes on

In the Crystal days adding reports to an asp.net application could give you quite a hard time. Having dropped crystal in favor of sql reporting services (so far) just annoying little stuff keeps me busy. In this post I'll demonstrate how to add RS reports to an asp.net application and concentrate on fixing the annoyances.

To the application reports are available as an URL on the report server. You add reports to an asp.net application by hyper-linking to them. Displaying the reports requires authentication and authorization, anonymous access does not work. By default no-one has browsing rights. In the report manger, which is another URL to the report server, you set the browsing rights under the security tab.

Provided the user has the necessary rights the browser will show the report with a viewer toolbar. In the toolbar is the option to export or print the report. Make sure you have installed service pack 2 of reporting services or else the print button will be missing. For the print to work RS will (try to) install a little client side ActiveX control which provides a print preview and a clean print.

A report can have parameters, these are defined in the report's design. You can pass parameter values at runtime provided that the report has a prompt for parameters. If this prompt is lacking you get a nasty reporting services error. There are two way to pass parameters, either in the parameters pane of the report toolbar or in the URL. When you're linking to a report from an application passing the parameters in the URL is the most likely thing to do. This snippet builds an URL. It does include two parameter values.

To hide the parameter pane in the browser bar you are supposed to pass rs:Parameters=false in the Url. But to hide the pane also the prompt string, asking for a parameter value, should be empty. The prompt string is set in the report designer (in VS)

Now there is a little quirk. In the report definition itself there are two settings for the prompt. A logical one which determines whether to prompt and a string what to prompt. The report designer lumps these two; an empty string will set the prompt flag to false. Which will result in the exception mentioned. In the report manager you set these two properties independently. To be able to set the parameter from the URL and hide the parameter pane you have to check the prompt checkbox and empty the prompt string textbox.

You don't have to pass in every report parameter. As you see in the report manager there is also a Null column.

When the Null checkbox is checked a parameter can be omitted. Its value will be according to the expression in the default value textbox. The VS report designer has several options for that, non-queried, from-query or none. This is again an oversimplification of the underlying parameter properties. To get a default value of NULL for the parameter you have to check the has default and Null checkboxes as well as empty the Default Value textbox. Also this can only be done right in the report manager.

Now I have a great flexibility and can still keep the URL as simple as possible.

System.Text.StringBuilder sb = new System.Text.StringBuilder("UrenverdelingTotaal");
sb.Append("&CursusJaar=" + CurrentUser.CursusJaarOmschrijving);
sb.Append("&Sortering=S" + DropDownListSortering.SelectedIndex.ToString());
sb.Append("&idGebruiker=" + CurrentUser.ID.ToString());
sb.Append("&SelOnderwijs=" + (CheckBoxOnderwijs.Checked ? "1" : "-1"));
sb.Append("&SelOpleiding=" + (CheckBoxOpleiding.Checked ? "1" : "-1"));
sb.Append("&SelSchool=" + (CheckBoxSchool.Checked ? "1" : "-1"));
if (DropDownListSchool.SelectedIndex > 0)
   sb.Append("&Afdeling=" + DropDownListSchool.SelectedValue.Trim());
if (DropDownListOpleiding.SelectedIndex > 0)
   sb.Append("&Opleiding=" + DropDownListOpleiding.SelectedValue.Trim());
if (DropDownListAfkorting.SelectedIndex > 0)
    sb.Append("&Afkorting=" + DropDownListAfkorting.SelectedValue.Trim());
if (DropDownListNaam.SelectedIndex > 0)
    sb.Append("&Naam=" + DropDownListNaam.SelectedValue.Trim());
if (DropDownListKostenplaats.SelectedIndex > 0)
    sb.Append("&KostenPlaats=" + DropDownListKostenplaats.SelectedValue.Trim());
if (DropDownListTelcode.SelectedIndex > 0)
    sb.Append("&TelCode=" + DropDownListTelcode.SelectedValue.Trim());
if (DropdownlistJaargang.SelectedIndex > 0)
    sb.Append("&Jaargang=" + DropdownlistJaargang.SelectedValue.Trim());
if (DropDownListPeriode.SelectedIndex > 0)
   sb.Append("&Periode =" + DropDownListPeriode.SelectedValue.Trim());
if (DropDownListOnderdeel.SelectedIndex > 0)
   sb.Append("&Onderdeel=" + DropDownListOnderdeel.SelectedValue.Trim());
if (TextBoxOpmerking.Text != "")
   sb.Append(string.Format("&Opmerking={0}%", TextBoxOpmerking.Text.Trim()));

Response.Redirect(string.Format(reportServer, sb.ToString()), true);

I had found the answer to the prompt problem here in a blogpost whose comments had turned it into just the kind of wiki (loads of comments where the commenters start discussing solutions) some of my old Crystal Reports post have turned into. The solution found was also applicable to default parameter values. Getting reporting to work (right) is such grateful blog-fodder :)


	Web Artima.com