locked
Creation of NONE member through MDX RRS feed

  • Question

  •  

    Hi Experts,

     

    We have a filter(in Dashboard) generated from a Hierarchy and we don’t have the option of NONE in the filter then how to add it through MDX.

    Is there any alternative way to proceed.

     

    Kindly key down your suggestions

     

    Thank you

     

    -Mus

    Wednesday, March 19, 2008 7:55 AM

Answers

  • What about adding in the 'None' value in the data source view of your cube?  It would then show up as a member, but would not be tied to any fact data.

     

    Code Snippet

     

    SELECT

    -1

    ,'None'

    UNION ALL

    SELECT

    SurrogateKey

    ,DimensionMember

    FROM

    dbo.MyTable

     

     

    Thursday, April 3, 2008 1:04 PM

All replies

  • Assuming there's only one top-level member in the hierarchy, include it in the filter.

     

    Thursday, March 20, 2008 8:16 PM
  • Thanks Ray,

     

    It will be convenient, if you throw more light on what you conveyed.

     

    With the following MDX query i can able to create a none member (in same hierarchy)

     

    with member [Product].[Product Categories].[sample] as ("None")

    select union({[Product].[Product Categories].[sample]},

    {[Product].[Product Categories].children}) on 0

    from [Adventure Works]

    But am unable to put this query in MDX Query filter(dashboard) to get the none member in Filter, while am proceeding i received the following Warning:

     

    No filter data retrieved

    Warning 1:  Filter was created, but no default data was retrieved.
    To resolve the problem, follow the step(s) below.
    1. Verify that the data source for this filter is properly configured.
    2. Check the server event log.  Additional information about this problem may have been recorded.

     

    But MDX query filter filter accepting if i give simply,

    union( [Product].[Product Categories].children, [Product].[Product Categories].[sample]  )

    But during preview filter displays only Product Categories children and nothing else.

     

    (Note: [Sample] Level is actually not in the cube).

     

    Expecting your feedback

     

    Thanks

    Mus

     

     

     

     


    Friday, March 21, 2008 11:43 AM
  • As far as I know, there's no way to use a WITH clause in a custom MDX filter.  You can only write MDX that resolves as a set.  If you really want to have an option that displays as "None", then you'll need to define it on the AS server.  Then you could have MDX that looks like:

     

    { [Product].[Product Categories].None, [Product].[Product Categories].DefaultMember.Children}

     

    Otherwise, you'll need to define your set as follows and educate your users that selecting the default member is equivalent to not having a filter:

     

    { [Product].[Product Categories].DefaultMember, [Product].[Product Categories].DefaultMember.Children}

    Friday, March 21, 2008 3:28 PM
  • Hi ray,

     

    With the first query, no reaction in the content of filter, jsut products children populated,

    With the second query, It simply added AllProducts to the products children, but it doesn't suits my need.

    Still am in the process of figuring out.

    Kindly suggest me any work around if any.

     

    thank you.

    Mus

     

    Tuesday, March 25, 2008 10:03 AM
  • What about adding in the 'None' value in the data source view of your cube?  It would then show up as a member, but would not be tied to any fact data.

     

    Code Snippet

     

    SELECT

    -1

    ,'None'

    UNION ALL

    SELECT

    SurrogateKey

    ,DimensionMember

    FROM

    dbo.MyTable

     

     

    Thursday, April 3, 2008 1:04 PM