Wildcard on dimension member.

• Crispin Proctor

  SSCoach

  Points: 18161

  More actions

  October 11, 2007 at 3:00 am

  #375616

  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!

• stevefromOZ

  SSC-Forever

  Points: 43646

  More actions

  October 12, 2007 at 10:26 am

  #741677

  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.