Hello Friends, I wanted to remove cascading option between parameters since this option is effecting the query performance.I tried to modify the query from parameter dataset.But this is not working. Is there any other way to remove cascading option from MDX? In advance I appreciate your suggestions. Thanks Tarakq

Can you be more specific about what the query looked like originally and what it looked like after you tried to modify it?

HI Thanks for the response. I would like to remove cascading between the parameters. So to do that, I deleted the reference parameters in the parameter datsets as follwoing. WITH MEMBER [Measures].[ParameterCaption] AS [T Open Closed Po Fact].[Exposed].CurrentMember.Member_Caption MEMBER [Measures].[ParameterValue] AS [T Open Closed Po Fact].[Exposed].CurrentMember.UniqueName MEMBER [Measures].[ParameterLevel] AS [T Open Closed Po Fact].[Exposed].CurrentMember.Level.Ordinal SELECT { [Measures].[ParameterCaption] ,[Measures].[ParameterValue] ,[Measures].[ParameterLevel] } ON COLUMNS ,[T Open Closed Po Fact].[Exposed].ALLMEMBERS ON ROWS FROM [Exposure]; Though I modified this query, I am able to see the cascading. I am using the following query for the main dataset. I think in the main dataset I have cascading. How to get rid of this. SELECT NON EMPTY { [Measures].[MATCHED AMOUNT] ,[Measures].[RECEIPTED AMOUNT] ,[Measures].[PO AMOUNT] ,[Measures].[OPEN PO AMOUNT] ,[Measures].[MATCHED OVERAGE] ,[Measures].[MATCHED AMT NOT RECEIPTED] ,[Measures].[RECEIPTED AMOUNT NOT INVOICED] } ON COLUMNS ,NON EMPTY { [OCP Branch Details].[Org].[Org].ALLMEMBERS* [OCP Branch Details].[Region Name].[Region Name].ALLMEMBERS* [OCP Branch Details].[District Name].[District Name].ALLMEMBERS* [OCP Branch Details].[Branch ID].[Branch ID].ALLMEMBERS* [OCP Branch Details].[Branch Name].[Branch Name].ALLMEMBERS* [Nq Supplier Details Open Closed PO].[Supplier Number].[Supplier Number].ALLMEMBERS* [Nq Supplier Details Open Closed PO].[Supplier Name].[Supplier Name].ALLMEMBERS* [OCP Date].[Standard Date].[Standard Date].ALLMEMBERS* [T Open Closed Po Fact].[PO_#].[PO_#].ALLMEMBERS* [T Open Closed Po Fact].[Exposed].[Exposed].ALLMEMBERS } DIMENSION PROPERTIES MEMBER_CAPTION ,MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT StrToMember (@FromOCPDateStandardDate ,CONSTRAINED ) : StrToMember (@ToOCPDateStandardDate ,CONSTRAINED ) ON COLUMNS FROM ( SELECT StrToSet (@TOpenClosedPoFactExposed ,CONSTRAINED ) ON COLUMNS FROM ( SELECT Filter ( [T Open Closed Po Fact].[PO_#].[PO_#].ALLMEMBERS , Instr ( [T Open Closed Po Fact].[PO_#].CurrentMember.Properties('Member_Caption')@TOpenClosedPoFactPO ) > 0 ) ON COLUMNS FROM ( SELECT Filter ( [Nq Supplier Details Open Closed PO].[Supplier Name].[Supplier Name].ALLMEMBERS , Instr ( [Nq Supplier Details Open Closed PO].[Supplier Name].CurrentMember.Properties('Member_Caption')@NqSupplierDetailsOpenClosedPOSupplierName ) > 0 ) ON COLUMNS FROM ( SELECT Filter ( [Nq Supplier Details Open Closed PO].[Supplier Number].[Supplier Number].ALLMEMBERS , Instr ( [Nq Supplier Details Open Closed PO].[Supplier Number].CurrentMember.Properties('Member_Caption')@NqSupplierDetailsOpenClosedPOSupplierNumber ) > 0 ) ON COLUMNS FROM ( SELECT StrToSet (@OCPBranchDetailsBranch ,CONSTRAINED ) ON COLUMNS FROM ( SELECT StrToSet (@OCPBranchDetailsDistrictName ,CONSTRAINED ) ON COLUMNS FROM ( SELECT StrToSet (@OCPBranchDetailsRegionName ,CONSTRAINED ) ON COLUMNS FROM ( SELECT StrToSet (@OCPBranchDetailsOrg ,CONSTRAINED ) ON COLUMNS FROM [Exposure] ) ) ) ) ) ) ) ) ) WHERE IIF ( StrToSet(@OCPBranchDetailsBranch,CONSTRAINED).Count = 1 ,StrToSet (@OCPBranchDetailsBranch ,CONSTRAINED ) ,[OCP Branch Details].[Branch].CurrentMember ) CELL PROPERTIES VALUE ,BACK_COLOR ,FORE_COLOR ,FORMATTED_VALUE ,FORMAT_STRING ,FONT_NAME ,FONT_SIZE ,FONT_FLAGS; I appreciate your help in advance. Thanks Tarak

The main dataset that you use has no effect whatsoever on cascading parameters, assuming that we mean the same thing with regard to cascading parameters. The main dataset only uses the results of selections made for the parameters, but doesn't cause them to cascade. By cascading, I mean that you cannot see the list of values for parameter #2 until you make a selection from the list of values for parameter #1. To remove the cascading effect, you must remove references to parameter #1 query in the dataset for parameter #2. I just tried this in a report using SQL Server 2008 R2, and by removing the subselect in the dataset for parameter #2 so that I was left with a query that removed the cascading effect from the parameters. To be more specific, with cascading I had this query: WITH MEMBER [Measures].[ParameterCaption] AS [Product].[Subcategory].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Product].[Subcategory].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Product].[Subcategory].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Product].[Subcategory].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@ProductCategory, CONSTRAINED) ) ON COLUMNS FROM [ResellerSales]) To remove cascading, I had to use this query (removing the text in bold above): WITH MEMBER [Measures].[ParameterCaption] AS [Product].[Subcategory].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Product].[Subcategory].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Product].[Subcategory].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Product].[Subcategory].ALLMEMBERS ON ROWS FROM [ResellerSales] If you mean something different by cascading, then you need to be more specific in your description and explain what you mean when you say that you are able "to see cascading".

HI Stacia, Previously I did the same thing what you did but still I have cascading in the report. So, How to get rid of this? Following is the reference what I did previously. "HI Thanks for the response. I would like to remove cascading between the parameters. So to do that, I deleted the reference parameters in the parameter datsets as follwoing. WITH MEMBER [Measures].[ParameterCaption] AS [T Open Closed Po Fact].[Exposed].CurrentMember.Member_Caption MEMBER [Measures].[ParameterValue] AS [T Open Closed Po Fact].[Exposed].CurrentMember.UniqueName MEMBER [Measures].[ParameterLevel] AS [T Open Closed Po Fact].[Exposed].CurrentMember.Level.Ordinal SELECT { [Measures].[ParameterCaption] ,[Measures].[ParameterValue] ,[Measures].[ParameterLevel] } ON COLUMNS ,[T Open Closed Po Fact].[Exposed].ALLMEMBERS ON ROWS FROM [Exposure]; Though I modified this query, I am able to see the cascading. I am using the following query for the main dataset. I think in the main dataset I have cascading. How to get rid of this." Any suggestions would be appreciated. Thanks Tarak

The cascading behavior is driven by the presence of a query parameter in the dataset for the report parameter. Right-click the parameter that has the cascading effect and check the parameter properties. On the Available Values page, check the name of the dataset that is assigned to the parameter. Make sure that is the same dataset that you are modifying by removing the subselect in the query. If you don't see it, right-click the Datasets folder and select Show Hidden Datasets (but don't select it if you already have a checkbox next to Show Hidden Datasets). The only other possibility is if you have a query parameter configured for the parameter's dataset. In the Report Data pane, right-click the dataset (the one that is identified in the Available Values for the parameter) and go to the Parameters page. If there is an item listed there, you'll need to remove it. It should go away automatically when you modify the query to remove the reference to the first parameter - but maybe you have it set to something else?

Thanks Stacia, I think in the parameter properties ---Available Values---under LABEL field it is selected Parameter Caption Intended. When I removed and use the column value, it is working without parameter.

Hi Stacia Misner, I got another issue while removing cascading.To remove cascading completely, I deselected the Parameter Caption Intended in report parameter properties and selected Exposed. Query Results are Exposed ParameterCaption Parameter Value ParameterLevel (null) All [T Open Closed PO Fact].[All] 0 No No ----- 1 Yes Yes ----- 1 Unknown Unknown -------- 1 This is the result set what I got it from the query. However when I select the Exposed column as parameter label field in the Parameter Properties, it is showing [T Open Closed PO Fact].[All] in the drop down list. How to get All instead of this. I appreciate your time. Thanks Tarak.