Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Wildcard on dimension member. Expand / Collapse
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: Thursday, August 27, 2015 2:56 AM
Points: 917, Visits: 414
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)

[Measures].[OS Comm Amt] on Columns,
[Purchase Order Detail].[Order Number].Children ON rows
(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 :)



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



Group: Moderators
Last Login: Saturday, November 5, 2016 7:09 PM
Points: 1,896, Visits: 3,753
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.

Post #410123
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse