Help with MDX query

Hi,

I have the following mdx query:

SELECT NON EMPTY { [Measures].[FlowAmount] } ON COLUMNS, 

NON EMPTY { ([Dim Date].[Fiscal].[Fiscal Quarter].ALLMEMBERS * [TransferCase].[Tfr Case Ref].[Tfr Case Ref].ALLMEMBERS * [Dim Wrappers].[Fac Group].[Fac Group].ALLMEMBERS ) } ON ROWS

FROM ( SELECT ( { [Dim Date].[Fiscal].[Fiscal Year].&[FY 13/14] } ) ON COLUMNS

FROM [Cube])

It returns the following:


How can I get a count of the Tfr Case Refs by Fac Group and summed Flow Amount?

So in SQL

 Select Fiscal Quarter, Fac Group , count(distinct TfrCaseRef), Sum(FlowAmount)

Group by Fiscal Quarter, Fac Group

TransferCase is a Fact Dimension based on Fact Table which contains the FlowAmount measure.

Thanks Jon


  • Edited by JLJ1976 14 hours 16 minutes ago
August 24th, 2015 12:51pm

Hi Jon,

According to your description, you want to get the distinct count of Case reference and sum of FlowAmount on Fiscal Quarter and Fac Group level. Right?

For getting the distinct count of Case reference, I suggest you create a measure on Case Ref column, set the aggregate type "Distinct Count". Go to Dimension Usage and define the relationship with Fac dimension and Date dimension.

Then you can directly call the Measure in your MDX query, but you can't include Case Ref members on your rows.

SELECT NON EMPTY { [Measures].[FlowAmount], [Measures].[Case RefDistinct Count] } ON COLUMNS, 


NON
 EMPTY 
 ([Dim Date].[Fiscal].[Fiscal Quarter].ALLMEMBERS * [Dim
 Wrappers].[Fac Group].[Fac Group].ALLMEMBERS )  ON ROWS

FROM
 ( SELECT ( { [Dim Date].[Fiscal].[Fiscal Year].&[FY 13/14] } ) ON COLUMNS

FROM
 [Cube])
Regards,

Free Windows Admin Tool Kit Click here and download it now
August 24th, 2015 11:00pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics