Search code examples
reporting-servicesmdxssrs-2012

Passing multiple values to SSRS using MDX

I am running the following command for getting a multi value report

StrToSet
("[Dim Branch].[HierarchyB-T-C].[Trading Code].&[" + 
Replace(
Join(
Parameters!TradingName.Value,"],"
) +"]",",",",[Dim Branch].[HierarchyB-T-C].[Trading Code].&["),",")

But I'm getting an error

'The Syntax for 'Join' is incorrect'.

I don't know what I am doing wrong. Can anyone correct me please?

If I change it to StrToSet(@TradingName, Constrained) it works for single value, but I'd like to pass multiple values.

Solution

  • To pass multiple value from parameter, i just followed the steps 1 Add parameters and name it like enter image description here

    1. Under dataset properties (shared dataset properties as well) , the Parameters tab write an expression like this way =Split(Parameters!TradingName.Value,",") enter image description here

    enter image description here

    1. in Shared dataset, write the MDX with WHERE (StrToSet(@TradingName))

    SELECT 
   	 {[Total]} ON COLUMNS 
 ,
 	{
	[Dim Account].[Account Type].&[Income] 
	}
	*
    STRTOMEMBER("[Dim Fiscal Year].[HierarchyFiscal].[E Month].&[" + @FiscalYear +"]&[" + FORMAT(Now(),"MMMM") +"].PREVMEMBER")  
	*
	 ORDER
	 (
	 {
	 [Dim Branch].[Branch Name].[Branch Name]
	 },[Total], BDESC
	 )
  ON ROWS
from [CubeProfitLoss]
WHERE (StrToSet(@TradingName))

    1. when you want to preview the multiple value, make sure you are using , to separate trading name likewise enter image description here