Hi All, I have a SSRS report which is using AS cube as its datasource. Ther report contains cascading parameters. Each parameter is having its own dataset. The parameters are taking a lot of time in loading. Cascading parameters and their queries are as follows: Parameter 1: Selling Dept WITH MEMBER [Measures].[ParameterCaption] AS [SalesDiv].[SalesDivName].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [SalesDiv].[SalesDivName].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [SalesDiv].[SalesDivName].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [SalesDiv].[SalesDivName].ALLMEMBERS ON ROWS FROM [AS_Cube] Parameter2: Customers WITH MEMBER [Measures].[ParameterValue] AS [Customer].[CustomerName].CURRENTMEMBER.UNIQUENAME MEMBER [MEASURES].[Filter] AS SUM(STRTOSET(@SalesDivName,CONSTRAINED),[Measures].[Total_Cost]) SELECT {[Measures].[ParameterValue]} ON COLUMNS, FILTER(DESCENDANTS ([Customer].[CustomerName].[CustomerName].ALLMEMBERS ) ,[MEASURES].[Filter] <> 0) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [AS_Cube] CELL PROPERTIES VALUE After selecting 1st parameter which is multi-valued, the second parameter takes approximately 5 mins to load. What can be done to improve the performace for MDX point of view.-Nitin Pawar

Can you try the query below and see first, whether the results are the same... and then could you write down the time also WITH MEMBER [Measures].[ParameterValue] AS [Customer].[CustomerName].CURRENTMEMBER.UNIQUENAME MEMBER [SalesDiv].[SalesDivName].[Total Sales] as sum(strtoset(@SalesDivName)) SELECT {[Measures].[ParameterValue]} ON COLUMNS, FILTER([Customer].[CustomerName].[CustomerName].ALLMEMBERS* [SalesDiv].[SalesDivName].[Total Sales], [Measures].[Total_Cost] <> 0) ON ROWS FROM [AS_Cube] If you have a lot of customers, try the query below also and tell me the time WITH MEMBER [Measures].[ParameterValue] AS [Customer].[CustomerName].CURRENTMEMBER.UNIQUENAME MEMBER [SalesDiv].[SalesDivName].[Total Sales] as sum(strtoset(@SalesDivName)) SELECT {[Measures].[ParameterValue]} ON COLUMNS, FILTER(nonempty([Customer].[CustomerName].[CustomerName].ALLMEMBERS,[Measures].[Total_Cost]) * [SalesDiv].[SalesDivName].[Total Sales], [Measures].[Total_Cost] <> 0) ON ROWS FROM [AS_Cube] Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Hi Jason, I tried your solution, but the queries are not giving same number of records as I get from my original query for parameter 2. My query returns 421 rows & 2 columns. Solution given by you returns 24320 rows & 3 columns. This is due to cartesian join. Can you suggest any changes that I can implement to get exact number of records. Thanks.-Nitin Pawar

Hi Jason, I tried your solution. However, I am not getting same no. of records with the modified qurey. My query returns 421 rows & 2 columns. Solution proposed by you returns 23358 rows & 3 columns. Can you suggest how can I implement the queries suggested to get exact records. Thanks. -Nitin Pawar