All geek questions in one place

Mondrian: filter summary calculation

I have this MDX request:

with member [Measures].[count] as '[Measures].[NUMBER]' ** member [Measures].[total] as 'sum({[RECORD_TYPE.VALUES].[All VALUEs].children})' ** member [Measures].[% records] as '([Measures].[count] / [Measures].[total])', format_string = "0.0%" select {[Measures].[count], [Measures].[% records], [Measures].[total]} ON COLUMNS, [RECORD_TYPE.VALUE].[1] ON ROWS from [RECORDS] where Crossjoin({[CITY].[LONDON]}, {([DATE.DATE].[20171101] : [DATE.DATE].[20171130])})

I found out that this [final] member only considers filtered records by date (records data between 20171101 and 20171130). If I switch this value to:

 member [Measures].[total] as 'sum({[DATE.DATE].[All DATEs].children})'

I get all the records in the cube, regardless of the city.

I would like this total to be the total number of entries for [CITY]. [LONDON] in a cube, regardless of the type of record. Could you tell me how to do this?

+5
source share
1 answer

I am not a Mondry expert, but I assume the following:

  with member [Measures].[count] as '[Measures].[NUMBER]' ** member [Measures].[total] as 'sum({[RECORD_TYPE.VALUES].[All VALUEs].children} * [CITY].[LONDON])' ** member [Measures].[% records] as '([Measures].[count] / [Measures].[total])', format_string = "0.0%" select {[Measures].[count], [Measures].[% records], [Measures].[total]} ON COLUMNS, [RECORD_TYPE.VALUE].[1] ON ROWS from [RECORDS] where Crossjoin({[CITY].[LONDON]}, {([DATE.DATE].[20171101] : [DATE.DATE].[20171130])})
+1
source

Source: https://habr.com/ru/post/1273565/

More articles:


All Articles