Parameterize the OLAP Reports in Client Applications

In this post let’s see how you can pass the parameters into your OLAP report. The parameters have to be passed dynamically through the WinForm application.

Here we will NOT be using the report parameters which you would have added on the report at the design time.

The report is displayed through report control in winApp (or webApp).

Final output:

Steps-A: ( Design a parameterized report using BIDS and publish on to the report server)

1. Create a report server project and design a Pivot table report

2. Set the DataSource for the report as follows and also set the required parameters. Note that setting parameters is not must at design time. You can add parameters at runtime also.:

3. Preview the report in BIDS to check if everything is fine before you publish it

4. Deploy the report solution

Steps-B: Accessing report in the Client App

1. Create a winForm App and add a report viewer control. The screen will look something like this:

2. Here comes the real stuff. How to populate the parameter controls in winform from the dimension/hierarchies members and to set the report viewer control properties to show the report.

The code will be as follows:

a. In form load:

reportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote;

reportViewer1.ServerReport.ReportServerUrl = new Uri(@"https://localhost/reportserver");

reportViewer1.ServerReport.ReportPath = "/OLAP Reports/OLAP Report-CalculatedMeasures";

reportViewer1.ShowParameterPrompts = false;

this.reportViewer1.RefreshReport();

GetParam();

b. To populate the parameter control:

public void GetParam()

{

server = @"https://localhost/reportserver";

report = "/OLAP Reports/OLAP Report-CalculatedMeasures";

bool forrendering = true;

string historyId = null;

ParameterValue[] values = null;

DataSourceCredentials[] credentials = null;

ReportParameter[] parameterSSRS = null;

parameterSSRS = rs.GetReportParameters(report, historyId, forrendering, values, credentials);

ValidValue[] pvs = null;

ArrayList[] a =new ArrayList[parameterSSRS.Length];

for(int i=0;i<parameterSSRS.Length;i++)

{

a[i] = new ArrayList();

if (parameterSSRS[i].ValidValues != null)

{

pvs = parameterSSRS[i].ValidValues;

foreach (ValidValue pv in pvs)

{

a[i].Add(new ComboItem(pv.Label, pv.Val

Technorati Tags: SQL Reporting Services,Report,OLAP Report,Parameter,SSRS,WinForm

ue));

}

}

}

//The ValueMember is required to get the fully qualified hierarchy path for the members used as the parameter value

cmbCountry.DataSource = a[0];

cmbCountry.DisplayMember = "Display";

cmbCountry.ValueMember = "Val";

cmbYear.DataSource = a[1];

cmbYear.DisplayMember = "Display";

cmbYear.ValueMember ="Val";

}

c. And finally to load the report in report viewer control

private void btnRefresh_Click(object sender, EventArgs e)

{

Microsoft.Reporting.WinForms.ReportParameter[] rp = new Microsoft.Reporting.WinForms.ReportParameter[2];

rp[0] = new Microsoft.Reporting.WinForms.ReportParameter();

rp[1] = new Microsoft.Reporting.WinForms.ReportParameter();

rp[0].Name = "DeliveryDateCalendarYear";

rp[1].Name = "CustomerCountry";

if (cmbYear.SelectedValue != null && cmbYear.SelectedValue.ToString() != string.Empty)

{

rp[0].Values.Add(cmbYear.SelectedValue.ToString().Trim() );

}

if (cmbCountry.SelectedValue != null && cmbCountry.SelectedValue.ToString() != string.Empty)

{

rp[1].Values.Add(cmbCountry.SelectedValue.ToString().Trim());

}

reportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote;

reportViewer1.ServerReport.ReportServerUrl = new Uri(@"https://localhost/reportserver");

reportViewer1.ServerReport.ReportPath = "/OLAP Reports/OLAP Report-CalculatedMeasures";

reportViewer1.ServerReport.SetParameters(rp);

reportViewer1.ShowParameterPrompts = false ;

this.reportViewer1.RefreshReport();

}

3. Now you can run the winApp and change the parameter values to see how the report’s columns and rows changes accordingly.