locked
MDX question about Parameters and two different types of hierarchies.... RRS feed

  • Question

  • 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]

     

     

    Friday, April 27, 2007 9:30 PM

Answers

  • 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.

    Friday, April 27, 2007 11:19 PM

All replies

  • 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.

    Friday, April 27, 2007 11:19 PM
  • Thank you,  I will give this a try and let you know how it turns out.
    Saturday, April 28, 2007 1:57 PM