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

Filtering Members based on their names from the set(MDX) Expand / Collapse
Author
Message
Posted Thursday, June 03, 2010 7:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 27, 2013 12:01 AM
Points: 8, Visits: 129
Hi All. Can any one help me for this requirement:

I have the Query:

select [Measures].[Revision Number] on 0,
[Dim Product].[Color].[Color].members on 1
from [Adventure Works DW]

The Result is:

Revision Number
Black 9843
Blue 3970
Grey (null)
Multi 3926
NA 28919
Red 4949
Silver 3424
Silver/Black (null)
White 568
Yellow 4799

Now I want to remove the members that name starting with 'B' and 'S". i.e. I want to remove the members Black,Blue, Silver & Silver/Black from the result.


Thanks in advance...
Post #932124
Posted Thursday, June 03, 2010 9:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:08 PM
Points: 5,469, Visits: 23,464
Hope this helps:
CREATE TABLE #Dw(Color VARCHAR(20),[Revision Number] INT)
INSERT INTO #Dw
SELECT 'Black', 9843 UNION ALL
SELECT 'Blue', 3970 UNION ALL
SELECT 'Grey', (null) UNION ALL
SELECT 'Multi', 3926 UNION ALL
SELECT 'NA', 28919 UNION ALL
SELECT 'Red', 4949 UNION ALL
SELECT 'Silver', 3424 UNION ALL
SELECT 'Silver/Black', (null) UNION ALL
SELECT 'White', 568 UNION ALL
SELECT 'Yellow', 4799

SELECT Color,[Revision Number] FROM #Dw WHERE Color NOT LIKE 'b%' AND Color NOT LIKE 's%'
/*Results:
Color Revision Number
Grey NULL
Multi 3926
NA 28919
Red 4949
White 568
Yellow 4799 */



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #932244
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse