CODEWITHSUNDEEP
sqlssasmdx
Echo
Echo

Reputation: 1127

SSAS how to turn the following SQL script into MDX?

I want to calculate the daily number of children as a measure in SSAS. The logic should be written in SQL as follows:

Select Count(distinct ChildID)
From Child
Group by CurrentDate

How could I translate this script into MDX for calculation? I'm new to SSAS.

Upvotes: 1

Views: 192

Answers (1)

RThomas
RThomas

Reputation: 10882

It depends a lot on how your dimensions are set up but shooting from the hip you could set up a measure that is a count of childid. To do that in SSAS under the cube structure create a new measure and select count under the usage property and the proper table under the source table. You could call this measure Child Count or something like that.

With a distinct child count measure set up the MDX would be something like this:

SELECT NON EMPTY 
{ [Measures].[Child Count] } ON COLUMNS, 
{ ([Dim Child].[CurrentDate].[CurrentDate].ALLMEMBERS ) } ON ROWS 
FROM [Your Cube]

Upvotes: 3

Related Questions