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

How to add where clause/filter into calculated member in mdx Expand / Collapse
Author
Message
Posted Saturday, March 15, 2014 7:30 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 15, 2014 10:53 AM
Points: 76, Visits: 276
I am new to MDX. I have below query:

WITH MEMBER [Student Study Length Hours]
AS ([Measures].[Study Length Minutes]/[Measures].[Students]/60.0)
SELECT {[Student Study Length Hours]
,[Measures].[Study Length Minutes]
,[Measures].[Students} ON COLUMNS
FROM [Classroom]
WHERE {[Student Participants].[Student Type].&[100], [Student Participants].[Student Type].&[400]}

In the cube, I then created the below calculated member, using the info from the query above, and I wanted to included the
conditions I have in the where clause above, but I do not know how to add those conditions into the calculated member below,
as I understand the WHERE clause is not allowed in the calculated member. Any help and input you can provide would be greatly appreciated.

CREATE MEMBER CURRENTCUBE.[Measures].[Student Study Length Hours]
AS ([Measures].[Study Length Minutes]/[Measures].[Students]/60.0),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Student Study';

I have googled all over, but I have not been able to find one example addresses the where clause I have as above.
In SQL, it would be the where clause with StudentType IN(100,400). But how do I do that in calculated member?
Post #1551534
Posted Thursday, March 20, 2014 12:04 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
First try moving the set in the WHERE clause to the "ON ROWS" axis. Do you then get what you want?
Post #1553177
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse