none
mdx filter with properties

    Question

  • Hi All,
    I have the following MDX query


    WITH
    MEMBER Measures.Suburb AS
       [MemberAddress].[Member Address].Properties( "Suburb" )
    SELECT {Measures.Suburb }  ON 0,
    [MemberAddress].[Member Address].[Member Address].ALLMEMBERS ON 1
    FROM [Fund]

    (1) I would like to created a filter for Suburb. (we can only use the properties to do this as the attributes for Suburb have been disabled and made invisible)
    (2) I would like to create use this in Reporting Services as a parameterised query. Instructions on the best way?

    Thanks
    Wednesday, April 08, 2009 8:13 AM

Answers

  • What are the property values for "Suburb"? Is it just a flag that indicates whether the address is a "Suburb" or not or is it the city/subdivision? The issue with creating a filter for Suburb if the cardinality is high is that a query such as the one above will return many duplicates as you will be one row for each [MemberAddress].[Member Address] member rather than one row for each distinct Suburb value. If the Suburb property is a flag that indicates whether the Address is a suburb or not, the easiest way in SSRS to create the parameter list is to just specify the possible values when creating the list rather than populating the list with a query. Within the SSRS report, you can use the parameter in the second argument of the Filter function.
    WITH MEMBER Measures.Suburb AS 
         [MemberAddress].[Member Address].Properties( "Suburb" )
    SELECT {
              Measures.Suburb 
         } ON 0,
         Filter(
              [MemberAddress].[Member Address].[Member Address].ALLMEMBERS,
              [Measures].[Suburb] = @SuburbValue
         ) ON 1
    FROM [Fund]

    HTH,

    Martin

    Wednesday, April 08, 2009 12:38 PM

All replies

  • What are the property values for "Suburb"? Is it just a flag that indicates whether the address is a "Suburb" or not or is it the city/subdivision? The issue with creating a filter for Suburb if the cardinality is high is that a query such as the one above will return many duplicates as you will be one row for each [MemberAddress].[Member Address] member rather than one row for each distinct Suburb value. If the Suburb property is a flag that indicates whether the Address is a suburb or not, the easiest way in SSRS to create the parameter list is to just specify the possible values when creating the list rather than populating the list with a query. Within the SSRS report, you can use the parameter in the second argument of the Filter function.
    WITH MEMBER Measures.Suburb AS 
         [MemberAddress].[Member Address].Properties( "Suburb" )
    SELECT {
              Measures.Suburb 
         } ON 0,
         Filter(
              [MemberAddress].[Member Address].[Member Address].ALLMEMBERS,
              [Measures].[Suburb] = @SuburbValue
         ) ON 1
    FROM [Fund]

    HTH,

    Martin

    Wednesday, April 08, 2009 12:38 PM
  • Thanks Martin,
    This was a great help.
    One further question. If one wanted to get a distinct set of SUBURBS (they are a city/subdivison) not a flag.
    How would one do this?

    THe following does not give a distinct list. (There are still duplicates).

    WITH

     

    MEMBER Measures.Suburb AS [MemberAddress].[Member Address].Properties( "Suburb" )

    SELECT

     

    distinct(Measures.Suburb) ON 0,

    [MemberAddress].[Member Address].[Member Address].

    ALLMEMBERS ON 1

    FROM

     

    [Fund]

    Thanks

    Wednesday, April 08, 2009 9:34 PM
  • The easiest thing to do in to get a unique list of suburbs would be to enable the attribute hierarchy for the suburb attribute.

    If you are building a parameter list for a report and your users want to do analysis by suburb then you are better off having it enabled. This would also help the performance of your previous query as you could put the suburb in the where clause and the engine could take advantage of the structures in the dimension to produce a more efficient query plan.


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Thursday, April 09, 2009 2:28 AM