ASP.NET MVC – Handling SSRS reports with ReportViewer – Part I


Reports provides first level of Business Intelligence for an application. They are generally thought as low hanging fruit till the other functionality is developed but suddenly becomes the most critical part of the app as we approach the project milestones.

Mostly – you will create a SSRS report and deploy it to SSRS server and view it via a report link which points to the deployed report.

What if – we don’t have SSRS server to get the reports deployed and still we need to show those reports via an ASP.NET MVC application?

Well – you can debate over and say we need a server to deploy reports.. what kind of infrastructure is this where you don’t have a server for reports.. and so and so forth…

Now – lets think like a good customer focused software developer. The scenario says:

  • We don’t have a SSRS server to deploy reports
  • We need the reports to be displayed in ASP.NET MVC app

Solution:

  • We can still use SSRS to design/develop the report files (.rdl)
  • In ASP.NET MVC – unfortunately there is no direct way to do it. You would need to use the ASP.NET ReportViewer control to display the report on a page.
  • You would need to utilize ADO.NET to provide the data for the reports via executing the same stored procedure that you used to create the report.

If you have multiple reports which takes similar kind of parameters, you might want to look at creating a small Reporting Framework within your application. For simplicity, I am assuming we have 3 reports which takes Start Date and End Date as parameters and displays some data that the business wants us to display ( I am not going to show you the SPs here..)

1. Total Sales By Category
2. Total Sales By Product
3. Total Sales By Person

To make my life simple, I’ve decided to use following naming conventions for SP, Report Names, rdl files and DataSet Names:

Report Name : TotalSalesByCategoryReport.rdl
Data Set Name : TotalSalesByCategoryReport
Stored procedure Name : SP_GET_TotalSalesByCategoryReport
Parameters: @StartDate , @EndDate
Target DB: ProductDB

Here are the step-by-step instruction to get to a working solution:

1. Create the stored procedure and design the report. Follow the naming convention as mentioned above.

2. Add all your rdl files under the “Reports” folder in your web app

image

3. Create a Report.aspx page outside your views folder. This is your plain ASP.NET page. It is not the ASP.NET MVC view.

4. Add a ReportViewer Control in the Report.aspx page.

5. Write code in Report.cs (code behind) on Page_Load(). This code assumes that you will pass three parameters string rptName, DateTime startDate and DateTime endDate to make it work:


	    ReportViewer1.Visible = true; 
            ReportViewer1.LocalReport.ReportPath = string.Format(@"Reports\{0}.rdl", rptName); 

            SqlCommand cmd = new SqlCommand(); 

            cmd.Parameters.Add(new SqlParameter("@StartDate", startDate)); 
            cmd.Parameters.Add(new SqlParameter("@EndDate", endDate)); 

            thisConnectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString; 

            SqlConnection thisConnection = new SqlConnection(thisConnectionString); 

            cmd.Connection = thisConnection; 

            cmd.CommandText = string.Format("GET_{0}", rptName); 
            cmd.CommandType = CommandType.StoredProcedure; 
            SqlDataAdapter da = new SqlDataAdapter(cmd); 

            System.Data.DataSet thisDataSet = new System.Data.DataSet(); 

            /* Put the stored procedure result into a dataset */ 
            da.Fill(thisDataSet); 

            /* Associate thisDataSet  (now loaded with the stored 
               procedure result) with the  ReportViewer datasource */ 
            ReportDataSource datasource = new ReportDataSource(rptName, thisDataSet.Tables[0]); 

            ReportViewer1.LocalReport.DataSources.Clear(); 
            ReportViewer1.LocalReport.DataSources.Add(datasource); 
            List lst = new List(); 
            ReportParameter rptParam1 = new ReportParameter("StartDate", startDate.ToShortDateString()); 
            ReportParameter rptParam2 = new ReportParameter("EndDate", endDate.ToShortDateString()); 

            ReportParameter rptParam3 = new ReportParameter("LOBCode", lobCode); 
            lst.Add(rptParam3); 
            ReportViewer1.LocalReport.SetParameters(lst); 
            if (thisDataSet.Tables[0].Rows.Count == 0) 
            { 
                lblMessage.Text = "Sorry, no rows returned for this report!"; 
            } 

            ReportViewer1.LocalReport.Refresh();

6. Make sure you have following settings in your web.config:

<system.web>
<assemblies>
<add assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral,

PublicKeyToken=B03F5F7F11D50A3A" />
<add assembly="Microsoft.ReportViewer.Common, Version=9.0.0.0, Culture=neutral,

PublicKeyToken=B03F5F7F11D50A3A" />
</assemblies>
<buildproviders>
<add type="Microsoft.Reporting.RdlBuildProvider, Microsoft.ReportViewer.Common, Version=9.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a" extension=".rdl" />
</buildproviders>

<httphandlers>
<add type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=9.0.0.0,

Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" path="Reserved.ReportViewerWebControl.axd" verb="*" />
</httphandlers>

</system.web>


 

7. Compile the project. Pass the required parameters to the Report.aspx and see the magic.You should be able to view the reports within ReportViewer Control.


Comments (5)

  1. srikanth.k says:

    <buildproviders>

         <add type="Microsoft.Reporting.RdlBuildProvider, Microsoft.ReportViewer.Common, Version=9.0.0.0, Culture=neutral,PublicKeyToken=b03f5f7f11d50a3a" extension=".rdl" />

       </buildproviders>

    Where is put the above tag

    I followed above steps.Iam using Asp.netMVC Application

  2. srikanth.k says:

    <buildproviders>

         <add type="Microsoft.Reporting.RdlBuildProvider, Microsoft.ReportViewer.Common, Version=9.0.0.0, Culture=neutral,PublicKeyToken=b03f5f7f11d50a3a" extension=".rdl" />

       </buildproviders>

    Where is put the above tag

    I followed above steps.Iam using Asp.netMVC Application

  3. Srikanth.k says:

       * An error occurred during local report processing.

             o The definition of the report 'Main Report' is invalid.

                   + The report definition is not valid. Details: The report definition has an invalid target namespace 'schemas.microsoft.com/…/reportdefinition& which cannot be upgraded.

    Iam getting this error how to solve this error(i was  following above steps)

    Thank you,

    Srikanth.k

  4. Nirmit Garg says:

    Hi,

    I am trying to use reportviewer in my MVC 3 web application. My report is hosted on report server. My report is being displayed without any problem.

    The problem I am facing is – My report contains cascading dropdowns. So, when the report is rendered and I select a value from drop down, controller action is called again and the whole page is refreshed and hence my report goes back to the initial stage.

    Could you suggest how can I control this.

  5. No says:

    What does this have to do with MVC???

    Oh… I see… NOTHING