MDX question about Parameters and two different types of hierarchies....

I was looking at creating cascading parameters within a report I am developing in BIDS.

The parameters I am creating are for an organization structure that looks something like this.

Organization Hierarchy

===========

-Division

-Community

The problem is Division and City are part of a parent child hierarchy inside of a separate DIM named Organization.

Community is part of an attribute hiearachy defined in another DIM named Lot.

I want to be able to filter down the Communitys based upon which Division is selected. The code below returns all Communitys. I try to use a CrossJoin but I cant get it to filter the Communitys down correctly.

Code Snippet

WITH MEMBER [Measures].[ParameterCaption] AS '[Lot].[Community Rollup].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Lot].[Community Rollup].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Lot].[Community Rollup].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Lot].[Community Rollup].Levels(1) ON ROWS FROM [Cube]

Since Division and Community are in different dimensions, I assume that you want to select Communities with cube data for the selected Division(s). You could try NonEmpty(), like:

WITH MEMBER [Measures].[ParameterCaption] AS '[Lot].[Community Rollup].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Lot].[Community Rollup].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Lot].[Community Rollup].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

NonEmpty([Lot].[Community Rollup].Levels(1), CrossJoin(StrToSet(@.Community, CONSTRAINED), {[Measures].[FilterMeasure]})) ON ROWS

FROM [Cube]

where [Measures].[FilterMeasure] is a cube measure from the measure group which is used to determine whether a Community has data.

|||Thank you, I will give this a try and let you know how it turns out.