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 Monday, August 24, 2015 4:50 PM

It turns out I need to use a date that is related to the case as well, so it rules this approach out.

However its definitely would deal with counting just the groups and handy for future reference.

Thanks

How about

with member measures.DistinctCaseRefs as

      count(NonEmpty([TransferCase].[Tfr Case Ref].[Tfr Case Ref].ALLMEMBERS,[Measures].[FlowAmount]))

SELECT NON EMPTY { [Measures].[FlowAmount],measures.DistinctCaseRefs } 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])

That's assuming FlowAmount is a physical measure. If not change the NonEmpty to a Filter(, <>0)

Hope that helps,