Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Using SQL reporting services in an asp.net application with some notes on report parameters

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 the prompt property of its parameters set. If this property is not set 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.

string reportserver = System.Configuration.ConfigurationSettings.AppSettings["ReportServer"] + "{0}&rs:Parameters=false&rs:Command=Render&rs:ClearSession=true";
string reportUrl = String.Format("UrenVerdeling&idGebruiker={0}&sort={1}",CurrentUser.ID, sortOn);
Response.Redirect(string.Format(reportServer, reportUrl));
 

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 text 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=" + 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 :)

This entry was posted in ASP.NET, Data, Out+of+control. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Mohamedjawahir4

    its really helped me..

  • Lakshman

    populate the dropdownlist1 inside the page load and within this condition
    page_load
    {
    if(!Page.IsPostBack)
    {
       //code to populate your drop down or function to populate it
    }
    }

  • Sergio

    Would you mind upload the image that is missed? Thank you

  • As

    i want to know step by step des for passing parameter from aspx page to SSRS

  • RJ Kelly

    Shambhu:
    The only way that i know to do this is to create a local report and add the datasources to the page with appropriate parameters, stream the report into a file, and dynamically add a hyperlink to the webpage with a path to the file. Be sure to save the file in the same folder structure of your website or you will have to deal with permission issues.

    Dim warnings() As Warning = Nothing
    Dim streamids() As String = Nothing
    Dim mimeType As String = Nothing
    Dim encoding As String = Nothing
    Dim extension As String = Nothing
    Dim bytes() As Byte
    Dim str_devinfo = “” & vbCrLf & _
    PDF” & vbCrLf & _
    11in” & vbCrLf & _
    8.5in” & vbCrLf & _
    0.125in” & vbCrLf & _
    .125in” & vbCrLf & _
    .125in” & vbCrLf & _
    0.125in” & vbCrLf & _

    ReportViewer1.ProcessingMode = ProcessingMode.Local
    ReportViewer1.LocalReport.ReportPath = “{INSERT PATH TO YOUR REPORT ex: ./Reports/MyReport.rdlc”

    {add code here to call your ObjectDataSources here and drop them into datatables}

    ReportViewer1.LocalReport.DataSources.Add(New ReportDataSource(“{use the same datasource names here as in your local reports}”(string), {here use the datatable variables you created in the code above},(object))
    ‘Refresh the report after you attach the datatables to get the data
    ReportViewer1.LocalReport.Refresh()
    Dim filepath As String = This is the path and filename where you will drop the PDF be sure to add “.pdf” on the end of the find name
    ‘Stream report into a bytes object to write to filesystem
    bytes = ReportViewer1.LocalReport.Render(“PDF”, str_devinfo, “application/pdf”, encoding, “PDF”, streamids, warnings)

    Dim fs As New FileStream(filepath, FileMode.Create)
    fs.Write(bytes, 0, bytes.Length)
    fs.Close()
    ‘You need a Literal object on the page for this to work. This is the dynamic link to view the PDF from the page.
    Literal1.Text = “View Certificate

  • shambhu

    I need to generate the report directly into PDF on the click of button…kindly help ..how to do this…I am using asp.net,vb.net.
    Thanks

  • Deepti

    hi,

    i want to know how to append date in while exporting report through sql server reporting services.

    Please help me on this.

    Thanks,
    Deepti

  • http://www.ishraque.com Ishraque

    Nice comment, it really helped me and my team immensly in our project, keep up the nice work!!

  • Priya

    Hi,
    i built a report with SSRS 2005, having multivalued parameter. now i need to show a message box whn the user selects more than two values in it.
    how do i implement this?
    kindly help
    tia.

  • Swapana

    Thanks a lot :-)

    AFIK the report builder is launched in a separate window the moment we enter the report builder’s URL…

    Can u suggest how to open the report builder designer window in our own application’s web form(may be inside a frame) instead of opening it in the default SQL server report builder application window.

  • http://codebetter.com/blogs/peter.van.ooijen pvanooijen

    Whether it open in a new browser or in the webform of your app is your choice. It’s just an url like any other. You could even open it inside a frame (yuck..) of you app..

  • swapana

    hii..

    Has anybody got idea about Report Builder.Is there any way to launch it in our custom application so that it opens in own application’s web form and not in a separate window..
    Any help is appreciated…

  • Krishni

    Hi there

    I have a few reports on report manager which have drill throughs to other reports. The drill throughs are done via URLs, and when the reports are exported to excel, the drill through and is still available together with the URL. is there a way for me to hide the URL and drill thoughs when exporting reports?

    ta
    Krishni

  • Desmond

    Hi all. I’m new to reporting services. I’ve done the basic and now, i need to know how to pass parameters from applic. to reporting services.

    The integration procedure i followed is as given in the article in :

    http://www.codeproject.com/sqlrs/ReportViewer2005.asp.

    However, i’m unable to know my “ReportPath” and my “ReportServerUrl”. Anyone can help me out?

    Feel free to email me at: juzshaddup@hotmail.com

    Thanks for your time.

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    RTFaspx It’s right there under your nose in the report viewer…..

  • Chandu

    I want to display a data in pdf file in sql reporting services…Pls tell me the solution.?

  • swapna

    hi all..
    sorry pv rao for so late reply…..
    As u have asked how to pass session value for a parameter in report am making it clear over here…
    After creating a report use report viewer control to call that report in a .aspx page .From one form pass the parameter value say name entered in one of the textboxes of the form.Then retrieve this name as session value in the page where u r using report viewer control.Then set the parameter value to this session value for which u can refer the article
    http://www.codeproject.com/sqlrs/ReportViewer2005.asp
    Hope it gets clear now…

  • Raja

    I am having a problem where the report I’m trying to view via my web app has got cascading parameters. There are three parameters in total. The first parameter is a drop down with a list of options. The second parameter gets populated after the user selects an option from the first.

    My problem happens when the user picks an option for the first parameter. The page gets reloaded and that re-loads the report from the beginning. I lose the value picked for the first parameter and it becomes an endless cycle of picking parameter 1 -> page refreshed -> over and over.

    Could you please tell me how i can fix this?

  • Sanjay Modi

    Thanks , its workable

    Sanjay Modi

  • http://codebetter.com/blogs/peter.van.ooijen pvanooijen

    The print button is handled by an activeX control. Which is installed on the fly by the reporting server.
    Provided the policies allow the insatllation of activex

  • http://sau.david@gmail.com David Sau

    I’m still having the same problem ie the print button is not displayed even I’ve applied the SP2 and re-booted the system (XP).

    Your help is most appreciated.

    Thanks.

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    Invoking a report is a matter of assembling the URL. The parameters are part of that URL. What your code could do is get the session variable and insert that in the right place in the url.

  • pv rao

    swapna said:

    i integrated reporting services with asp.net page and passing parameters through that page.but when the page is viewed in browser along with the controls in the page a message is displayed ” Parameter X missing Value “.

    Though after passing parameter value with the help of controls in the page the problem is over but still for the first run it creates problem………

    ans:::i passed the parameter value as session variable to another page where report is called……..its working

    can u help how exactly to pass a session variable to a report

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    creating reports is a matter of using the reporting services webservices. That's a whole book on itself

  • http://chandusripathi@yahoo.co.in Ramesh Chandra

    actually my project is on reporting services

    probelm is i ve to pass parameters from a browser(HTML) page so that the reporting services need to take this value as parameters & generate reports based on this parameters

    Please kindly help me

    Ramesh
    9885331312 my contact no:

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    Sorry, not  yet. The moment I have sharepoint Rs experience I'll blog on that. :)

  • swapna(emergencyyyyyy)

    hi all……..
    I solved the paging problem by changing the interactive page size in report properties….
    Now am having a new problem…till now i was passing parameters to the report by writing code in asp.net.The user entered values in the textboxes in the form(in visual studio )…
    The problem is that now I have to use only Infopath forms and to publish the reports in SharePoint server…I am new to Infopath and sharepoint……and its too urgent…..
    can anyone guide me how to do this…. :(……am confused????

  • niks

    use pagination for displaying required amount of records per page

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    @suresh: I guess this is a follow up on swapna ?

    Looks like an opening. Introduce a “Page” column to group on.

  • suresh

    only thing u can do is do a grouping

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    AFAIK there is no such property. What you could try is a report band which forces a new page after evry X records.

  • swapna

    I want to display only a particular number of records in a page so that the user doesn’t have to scroll down to view all the records.For e.g when there are 1000 records to be displayed I want to show 10 records per page.

    I dont know where to set this property.Do u have any idea?

    Thanx.

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    The report viewer (in the browser) does page by itself. What more do you need ?

  • swapna

    hey my problem is over……
    i passed the parameter value as session variable to another page where report is called……..its working

    Can anybody tell how paging can be enabled in a report?
    Thanx

  • swapna

    Thanx Peter

    The integration procedure i followed is as given in the article in :

    http://www.codeproject.com/sqlrs/ReportViewer2005.asp.
    all the steps are same.

    I am just calling a report on the button click event of an asp.net page which also gives the parameter value to the report. In report i have allowed null for that parameter.But the problem remains.I am not getting what to do? where to pass parameter in URL is not mentioned in that article.
    Can u help me out?
    Thanx a lot

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    In that case parameter X is missing in the URL for the report

  • swapna

    i integrated reporting services with asp.net page and passing parameters through that page.but when the page is viewed in browser along with the controls in the page a message is displayed ” Parameter X missing Value “.
    Though after passing parameter value with the help of controls in the page the problem is over but still for the first run it creates problem………

    Plz help me out

  • http://www.softtech.nl mark@softtech.nl

    Hi Peter, thanks for the tip, you helped me out !
    Mark

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    In case null is the default value you can omit it from the url. Have null checked, prompt user checked and leave the prompt string empty. Works for me.

  • http://sensoft2000.blogspot.com K.Senthil Kumar (Sensoft2000)

    I need to pass “Null” value as a default parameter through the URL without prompting to the user. I dont know, how to do that. That means, i need to show the report for the particular Stored procedure with paramater ‘param1=null’ by default.. anybody knows?