How to pass parameters to OLAP based SSRS report f

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

How to pass parameters to OLAP based SSRS report f

Author

Topic

shah36
Starting Member

3 Posts

Posted - 2012-02-07 : 12:19:28

Hi Guys,

I am new to SSAS and SSRS based on SSAS. I have developed an SSAS database and deployed the cube on a remote server. I have also created some reports based on the cube and deployed the reports on the server. I created the asp.net web application and have embeded the reports in it and the reports are working fine.

The reports have the parameters defined in it.When i run the reports without passing the parameters with a default value of "ALL" there is no error message and everything is fine. The problem comes when i try to pass the parameter from my web application to report.

in my MDX query designer i am defining the parameter as follows

" SELECT NON EMPTY { [Measures].[SMC Budget], [Measures].[Fact Cube Data Count], [Measures].[Cost PWP], [Measures].[T Rank], [Measures].[Avg PWP Cost] } ON COLUMNS, NON EMPTY { ([DIMPBC Practices].[Locality].[Locality].ALLMEMBERS * [DIMPBC Practices].[Practice Name].[Practice Name].ALLMEMBERS * [DIMPBC Practices].[Out Put].[Out Put].ALLMEMBERS * [Dim Budget Areas].[Budget Area].[Budget Area].ALLMEMBERS * [Dim Budget Areas].[Attendance Type].[Attendance Type].ALLMEMBERS * [DIM Source Of Referral].[Referral Type].[Referral Type].ALLMEMBERS * [DIM Treat Ment Function Code].[Title].[Title].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DIMPBCPracticesOutPut, CONSTRAINED) ) ON COLUMNS FROM [Extract Mart DW]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"

in my asp.net page my code is:

this.ReportViewer1.ServerReport.ReportServerUrl =
new System.Uri("http://localhost/reportserver");

while (this.ReportViewer1.ServerReport.IsDrillthroughReport)
{
this.ReportViewer1.PerformBack();
}

string strReport = "/PBCToolKitV3Reports/Report1";
this.ReportViewer1.ServerReport.ReportPath = strReport;
Microsoft.Reporting.WebForms.ReportParameter[] RptParameters =
new Microsoft.Reporting.WebForms.ReportParameter[1];
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote; ReportViewer1.ShowParameterPrompts = false;

string strName = "P88003";
RptParameters[0] =
new Microsoft.Reporting.WebForms.ReportParameter();
RptParameters[0].Name = "DIMPBCPracticesOutPut";
RptParameters[0].Values.Add(strName);

this.ReportViewer1.ServerReport.SetParameters(RptParameters);
this.ReportViewer1.ServerReport.Refresh();

but when i run the page i get the following error message:
The 'DIMPBCPracticesOutPut' parameter is missing a value

Am i missing some step(s)? I am trying to solve this from past four days but no luck so far. I didn't think it was going to be so much pain.
Any help would be really appreciated.

Best regards,
Ali

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 13:49:03

print the value of DIMPBCPracticesOutPut in report and see what values its getting when you select some values other than All

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shah36
Starting Member

3 Posts

Posted - 2012-02-08 : 04:43:35

Hi visakh16

thanks for your reply. Actually i am very new to all .Net technology so couldn't understand how to print the value of DIMPBCPracticeOUTPut? Sorry i beg a parden.

In the BI report designer, on the report server and in asp.net web application (if i don't pass the parameter value here in asp.net) it creates a dropdown list of the parameter and i can select any value from it and the reports run fine.
Can you please tell how to print the values of the parameter?
Kind regards

shah36
Starting Member

3 Posts

Posted - 2012-02-08 : 07:39:44

Hi All,

This problem has taken me almost five days and had given me lots of headache. But a very kind person help me to sort this out and i though to share it here if someone else encounters the same problem.

The problem comes when pass the parameters value from asp.net application to the report.

The format of the string in asp.net should be exactly the same as the parameter in out report.

For example the parameter in my report is [DIMPBC Practices].[Out Put].&[P88003]

And in my asp.net application i was passing the parameter as StrName ="P88003" and i was missing the [DIMPBC Practices].[Out Put].&[] bit.

To avoid any confusion i am going to past the code which worked for me.

ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
ReportViewer1.ShowParameterPrompts = false;
this.ReportViewer1.ServerReport.ReportServerUrl =
new System.Uri("http://perplus/reportserver");

string strReport = "/PBCToolKitV3Reports/Report1";
this.ReportViewer1.ServerReport.ReportPath = strReport;

string strName = "[DIMPBC Practices].[Out Put].&[" +this.TextBox1.Text + "]";
Microsoft.Reporting.WebForms.ReportParameter p =
new Microsoft.Reporting.WebForms.ReportParameter("DIMPBCPracticesOutPut", strName);

this.ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter[] { p });
this.ReportViewer1.ServerReport.Refresh();

Many Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources