Issue passing multiple parameters to SP in MDX

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

Development Tools

Reporting Services Development

Issue passing multiple parameters to SP in MDX

Author

Topic

srihari
Starting Member

2 Posts

Posted - 2011-10-13 : 02:26:39

Hi,

There was a requirement to use multiple columns on X-axis in a chart. Unfortunately, this is not possible in SSRS. So i am trying to create a pivot in SSRS, calling a sql stored procedure, which will call my MDX query.Creating a group in the dataset for different columns in a calculated field will not work for my requirement.

I was facing an issue with passing multiple parameters to SQl Stored procedure. My Stored procedure is calling MDX query(I have creaetd a linked server). I am attaching a sample report and the proc i have created.

I have created a sample solution on AdventureWorks db and Adventure Works DW 2008R2 SE. The given below are the steps to replicate the issue :

1. Open the adventure works db

2. Create the below SP

CREATE

PROCEDURE usp_MDX_MultiValue

(

)

AS

BEGIN

declare

@sql varchar(8000)

set

@sql=

'

SELECT

[Date].[Month of Year].[Month of Year].MEMBERS

ON 0,

{

[Geography].[Country].[Country].MEMBERS *

[Geography].[City].[City].MEMBERS *

[Geography].[State-Province].[State-Province].MEMBERS

*

{

[Measures].[Reseller Tax Amount]

}

}

on 1

FROM (SELECT {'

+@pCountry+

@sql1 nvarchar(4000)

declare

@sql2 nvarchar(4000)

SET

@sql1=('select * from OPENROWSET(''MSOLAP'',''Data Source=SERVER_Name;Integrated Security=SSPI;Initial Catalog=Adventure Works DW 2008R2'',''')

set

@sql2=(@Sql1 + @sql +''')')

declare

@sql3 nvarchar(max)

set

@sql3= @sql2

exec

(@sql3)

END

3. Execute the below code to see the result

EXEC usp_MDX_MultiValue '[Geography].[Country].&[Australia],[Geography].[Country].&[Canada]'

?4. The given below is the code for the SSRS sample report i have created. Copy and paste this XML code in a report's view code page.

?http://msbiqueries.blogspot.com/2011/10/sample-report.html

?5. In the above report the pCountry parameter gets the values from Adventure Works DW 2008R2 C?ube

?6. The data set(dsMain) executes the stored procedure with the values selected in the pCountry parameter.

?7. Run the report by selecting any one value from the dropdown. The report will execute.

?8. Run the report by selecting multiple parameters. You will get an error.

?9. Capture the code that is hitting the sqlserver engine when selecting single and multiple values in pCountry parameter by running an sqlserver profiler.

?10. There is a significant difference in the code that is getting passed to the sql server engine.

I am stuck on this requirement for quite some time. I need to deliver these reports on urgent basis. ?Please let me know how can we pass multiple values to this kind of stored procedure.

?Let me know if you need any details from my end.

?Thanks,

?Srihari

Thanks,
Srihari Takkelapati,
CapGemini India,Mumbai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 02:29:10

how is it currently passing value when you select multiple?

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

srihari
Starting Member

2 Posts

Posted - 2011-10-28 : 02:09:33

Hi,

I am giving u the code i have captured by passing single and multiple values. I am passing 6 parameters to the procedure. 1. @pSBU 2. @pBU 3.@pSubBu 4. @pLevel 5.@pYear 6.@pMonth.

The below is the code i have captured in trace when i am passing a single value. After EXEC usp_BIM_Ppl_Monthly_Distribution_Test1 , the parameters are coming in the order when i am passing single value.

exec sp_executesql
N'EXEC usp_BIM_Ppl_Monthly_Distribution_Test1
@pSBU,
@pBU,
@pSubBu,
@pLevel,
@pYear,
@pMonth',
N'@pMonth nvarchar(53),
@pSBU nvarchar(33),
@pBU nvarchar(28),
@pSubBu nvarchar(32),
@pLevel nvarchar(26),
@pYear nvarchar(30)',

@pMonth=N'[Actual Period].[Year Month Week].[Month].&[2011]&

',
@pSBU=N'[Business Units].[Sbu].&[AppsOne]',
@pBU=N'[Business Units].[Bu].&[BIM]',
@pSubBu=N'[Business Units].[Sub Bu].&[BIM]',
@pLevel=N'[Level].[Level Name].[All]',
@pYear=N'[Actual Period].[Year].&[2011]'

This is the code being captured in trace on passing multiple values. Have a look at the parameters being passed after EXEC usp_BIM_Ppl_Monthly_Distribution_Test1 .From the report i have passed two values for the @pSBU parameter. Hence SBU part of the code is being disturbed. Instead of the @pSBU the actual value is getting passed. The same is the case for any parameter with multiple values.

exec sp_executesql N'exec sp_executesql
N''EXEC usp_BIM_Ppl_Monthly_Distribution_Test1
N''[Business Units].[Sbu].&[AppsOne]'',N''[Business Units].[Sbu].&[AppsTwo]'',
@pBU,
@pSubBu,
@pLevel,
@pYear,
@pMonth'',
N''@pMonth nvarchar(100),N'''',N''[Business Units].[Sbu].&[AppsTwo]'' nvarchar(100),@pBU nvarchar(100),@pSubBu nvarchar(100),@pLevel nvarchar(26),@pYear nvarchar(100)'',
@pMonth,
N''[Business Units].[Sbu].&[AppsOne]'',N''[Business Units].[Sbu].&[AppsTwo]'',
@pBU,
@pSubBu,
@pLevel,
@pYear',N'@pMonth nvarchar(53),@pBU nvarchar(28),@pSubBu nvarchar(32),@pLevel nvarchar(26),@pYear nvarchar(30)',
@pMonth=N'[Actual Period].[Year Month Week].[Month].&[2011]&

',
@pBU=N'[Business Units].[Bu].&[BIM]',
@pSubBu=N'[Business Units].[Sub Bu].&[BIM]',
@pLevel=N'[Level].[Level Name].[All]',
@pYear=N'[Actual Period].[Year].&[2011]'

Let me know if u need any info from my end. Have a nice day.

Thanks,
Srihari

Thanks,
Srihari Takkelapati,
CapGemini India,Mumbai

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources