Wildcard on dimension member.

  • Hi Folks,

    I have a measure which must only be aggrigated for certain members in a dimension. There are around 13k members in the dimension and I only want the measure summed whos members start with 8. eg: 800, 8923843, 8, 821212 but not 7834

    So far, I have got as far as filter and instr but it runs like a dog (Not returned anything yet so I don't even know if I am on the right path)

    Select

    [Measures].[OS Comm Amt] on Columns,

    [Purchase Order Detail].[Order Number].Children ON rows

    From

    (Select Filter([Purchase Order Detail].[Order Number], instr(1, settostr([Purchase Order Detail].[Order Number].children), '8')) on COLUMNS FROM [Policy And Programme Support]

    )

    MDX is like greek to me so take it easy 🙂

    Thanks.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • may be taking your example too literally, but is there a reason why you can't add a new attribute to the dim, have this effectively be a flag as to whether the member should be in this set (even if you simply put a right(field, 1). Set the attribute visibility to false but then use it within your query to filter the set? Doing the substringright/left on the inbound SQL will prob be faster than trying to determine it on ever single query run.

    Steve.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply