Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Wildcard on dimension member. Expand / Collapse
Author
Message
Posted Thursday, October 11, 2007 3:00 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 14, 2014 3:32 PM
Points: 917, Visits: 412
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,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Post #409400
Posted Friday, October 12, 2007 10:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: 2 days ago @ 7:18 AM
Points: 1,836, Visits: 3,544
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.
Post #410123
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse