Invoke SSRS reports from ASP.NET and or sharepoint webpart application by passing parameters

Hi All,

After a good long break getting back to what like to 🙂
In this post I’m planning to take you through the execution of SSRS reports from an ASP.NET application, now what is so great about this (simple its my first post after long break 🙂 ) because we will see how to pass parameters to the report. And these reports are bit special/unique because they are driven by an SSAS cube.
Ok so not to waste more time…
Here is the code for doing so:


using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Reporting.WebForms;

namespace MyWebApp
{
public partial class DummyReportViewer : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
_reportViewer.ProcessingMode = ProcessingMode.Remote;
// Report Server URL
_reportViewer.ServerReport.ReportServerUrl = new Uri(“http://%5Bservername:portnumber%5D/ReportServer/”);
// Report Name
_reportViewer.ServerReport.ReportPath = “/[reportpath]/[rdl_file_name]”;
// set true to show report parameters else false
_reportViewer.ShowParameterPrompts = true;
_reportViewer.ShowPrintButton = true;
// Below code demonstrate the Parameter passing method. User only if you have parameters into the reports.
Microsoft.Reporting.WebForms.ReportParameter[] reportParameterCollection = new Microsoft.Reporting.WebForms.ReportParameter[4];
reportParameterCollection[0] = new ReportParameter(“prmUniversityZone”, @”[DIM Zone].[Zone Hierarchy].[Zone NM].&[All]”);
reportParameterCollection[1] = new ReportParameter(“prmEducationStream”, @”[DIM Education].[Education Hierarchy].[Stream DSC].&[Engineering]”);
reportParameterCollection[2] = new ReportParameter(“prmPeriod”, @”[DIM Time PERIOD].[Period Hierarchy].[Year DSC].&[2009]”);
reportParameterCollection[3] = new ReportParameter(“prmResult”, @”[DIM Result].[Result Hierarchy].[Result NM].&[All]”);

_reportViewer.ServerReport.SetParameters(reportParameterCollection);
_reportViewer.ServerReport.Refresh();
}
}
}
}

Ok so what in case of multiple values for a single parameter. So in that case instead of sending string of parameter value pass an string array of multiple values.
i.e.

//instead of single parameter as:
reportParameterCollection[2] = new ReportParameter("prmPeriod", @"[DIM Time PERIOD].[Period Hierarchy].[Year DSC].&[2009]");

//create an string array of Year and pass it:
string[] years = new string[] { “2008”, “2009”, “2010”, “2011”, “2012” };
reportParameterCollection[2] = new ReportParameter(“prmPeriod”, years);

You may ask how do I extract this information of parameters. The simple is open the report goto parameters, open its property and check for Default Values in it. You will know what is required to be passed to the report parameter.

One more thing this is applicable to ASP.NET as well as SharePoint web part application

Following could be helpful resource for reports related stuff:

• http://msdn.microsoft.com/en-us/library/ms251661%28v=vs.100%29.aspx