Topcount with total of all members

Question

I'm creating a query which shows all continents, their countries and the top 5 users in these countries (1 dimension) with their orders (measure). Now I want to show to total of all users in these countries instead of only those in the top 5.

I followed the answer in the following thread https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e65c498c-ecf7-4099-a242-64873b8c3613/mdx-topcount-with-non-filtered-totals?forum=sqlanalysisservices But the syntax doesn't seem right for Mondrian.

Here's my query:

WITH MEMBER [Measures].[Total] AS 
'SUM([USER].[Country].currentmember, (USER].[UserName].Members, [Measures].[Orders]))'
SELECT NON EMPTY {[Measures].[Orders], [Measures].[Total]} ON COLUMNS, 
NON EMPTY {Order(Hierarchize({[USER].[Continent].Members, Generate([USER].[Country].Members, TopCount(Hierarchize({[USER].[Country].currentmember, [USER].[UserName].Members}), 6.0, [Measures].[Orders]))}), [Measures].[Orders], DESC)} ON ROWS 
FROM [Products] 
WHERE [Time].[Time].[2014]

Mondrian gives the following error for this part 'SUM([USER].[Country].currentmember, (USER].[UserName].Members, [Measures].[Orders]))':

Caused by: mondrian.olap.MondrianException: Mondrian Error:No function matches signature '(<Set>, <Member>)'
    at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:969)
    at mondrian.olap.ValidatorImpl.getDef(ValidatorImpl.java:205)
    at mondrian.olap.fun.FunUtil.resolveFunArgs(FunUtil.java:2023)
    at mondrian.mdx.UnresolvedFunCall.accept(UnresolvedFunCall.java:102)
    at mondrian.olap.ValidatorImpl.validate(ValidatorImpl.java:79)
    at mondrian.olap.fun.FunUtil.resolveFunArgs(FunUtil.java:2020)
    at mondrian.mdx.UnresolvedFunCall.accept(UnresolvedFunCall.java:102)
    at mondrian.olap.ValidatorImpl.validate(ValidatorImpl.java:79)
    at mondrian.olap.Formula.accept(Formula.java:106)
    at mondrian.olap.ValidatorImpl.validate(ValidatorImpl.java:155)
    at mondrian.olap.Query.resolve(Query.java:551)
    at mondrian.olap.Query.resolve(Query.java:446)
    at mondrian.olap.Query.<init>(Query.java:200)
    at mondrian.olap.Query.<init>(Query.java:161)
    at mondrian.olap.Parser$FactoryImpl.makeQuery(Parser.java:927)
    at mondrian.parser.MdxParserImpl.selectStatement(MdxParserImpl.java:1241)
    at mondrian.parser.MdxParserImpl.statement(MdxParserImpl.java:1074)
    at mondrian.parser.MdxParserImpl.statementEof(MdxParserImpl.java:188)
    at mondrian.parser.JavaccParserValidatorImpl.parseInternal(JavaccParserValidatorImpl.java:57)
    at mondrian.olap.ConnectionBase.parseStatement(ConnectionBase.java:96)
    ... 72 more

Thanks for your help.

whytheq whytheq · Accepted Answer · 2015-05-05T15:07:49

This is a standard error.

This is invalid:

Sum
(
  [USER].[Country].CurrentMember
 ,(
    USER.[UserName].MEMBERS
   ,[Measures].[Orders]
  )
)

To be more specific this bit is invalid

 ,(
    USER.[UserName].MEMBERS
   ,[Measures].[Orders]
  )

The braces () create a tuple and a tuple is created like so:

  (<member_expression>, <member_expression>, <member_expression>, ...)

None of the arguments are allowed to be set expressions.

I'm assuming you want the Total across all the UserNames - so just use the All member of that hierarchy in the tuple:

Sum
(
  [USER].[Country].CurrentMember
 ,(
    USER.[UserName].[All User Names] //<<<<replace this with whatever the correct name is of your all member
   ,[Measures].[Orders]
  )
)

Topcount with total of all members

2 Answers