Please be patient, I feel like a total idiot on this.... I am new to MDX and how to report against cubes. I have a cube called 'Visit'. I also have a dimension named 'Department'. On a SSRS report I have a parameter so that they can pick the department; however, all values show in the dropdown even if no fact records use that value in the cube... I would like the dropdown to only show values which exist within the 'Visit' cube. Can someone please walk me through doing this step by step. I have the main dataset 'Dataset 1' and the 'Department' dataset which currently populates the department parameter. Please be very precise because I am totally lost on this.... Thanks.

Can you write a query that shows the distinct departments in the "visit" with a where clause that only pulls departments that have values.

Not sure how to do this in MDX.... The parameter code is as follows: WITH MEMBER [Measures].[ParameterCaption] AS [Att Phys].[Physician Department].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Att Phys].[Physician Department].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Att Phys].[Physician Department].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Att Phys].[Physician Department].ALLMEMBERS ON ROWS FROM ( SELECT ( { [Dim_MeasureBenchmark].[MB ID].&[0] } ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromDateDschDateFull, CONSTRAINED) : STRTOMEMBER(@ToDateDschDateFull, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Dim_Visit].[Visit Type].&[I] } ) ON COLUMNS FROM [Visit]))) The 'Physician Department' is what the dropdown is pulling, but I only want it to show if it is used in the 'Visit' cube.....

Hi BKHoward2001, You can define a parameter in the Report Parameter page. To get the "the dropdown to only show values which exist within the 'Visit' cube", call Exists MDX function, its syntax is Exists( Set_Expression1 , Set_Expression2 [, MeasureGroupName] ). For example against the AdventureWorks database, Customers with sales: SELECT [Measures].[Internet Sales Amount] ON 0, EXISTS( [Customer].[Customer].[Customer].MEMBERS , , "Internet Sales") ON 1 FROM [Adventure Works] See more details about this mdx function at http://msdn.microsoft.com/en-us/library/ms144936.aspx . thanks, Jerry