October 11, 2007 at 3:00 am
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!
October 12, 2007 at 10:26 am
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