ReportServer Forum

FLU73

Member

Registered: 2018-10-25

How to use MDX functions in caluclated measures / members ? (like YTD)

Hello,

Is there some documentations about adding calculated members within the pivot mode of dynamic lists ?

Following several tests, it's possible to create calculated measures like difference in ratio % format by the mean of the "predefined calculations" button.
This way works fine to calculate variation of a measure according a dimension break.

There is also a possibility to calculate a variation between 2 measures (like computed columns where an aggregation is applied), by writing a formula and specifying a decimal percentage format.

These types of calculation are really interesting but seem not described in the documentation or blog, ... maybe I missed something

Now, I would like to create a measure for which sum is done on a field table according to the date : like from 01/01/2018 to today,
then another one from 01/01/2017 to today last year,
....

MDX functions seem to provide such possibility, but it's difficult to use it without examples.

The "documentation" link related to YTD() is refering to microsoft SQL documentation
https://docs.microsoft.com/en-us/sql/md … erver-2017

The basic example they are showing is :
Ytd is frequently used in combination with no parameters specified, meaning that the CurrentMember (MDX) function will display a running cumulative year-to-date total in a report, as shown in the following query:

WITH MEMBER MEASURES.YTDDEMO AS
AGGREGATE(YTD(), [Measures].[Internet Sales Amount])

SELECT {[Measures].[Internet Sales Amount], MEASURES.YTDDEMO} ON 0, [Date].[Calendar].MEMBERS ON 1
FROM [Adventure Works]

I 'am trying to adapt this example in reportserver, and I'm wondering if "MEMBER MEASURES.YTDDEMO" would have to be defined as a "calculated measure".
Then, this calculated measure may be used in the pivot visualizer : by selecting this new measure, the select we see above would be triggered.

But it's not working with my example,
After defining a "time" dimension that contain dates (yyyymmdd) under the year (yyyy), I get the message :
"MondrianException: Mondrian Error:Argument to function 'Ytd' must belong to Time hierarchy."

I don't know how to define a hierarchy in the dynamic list functions (I see how to do in Saiku report with the schema definition).
I would say that a default hierarchy should exist when defining a time dimension, ....

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: How to use MDX functions in caluclated measures / members ? (like YTD)

Hi FLU73,

dynamic lists with pivot view create an xml schema by default. So, under the hoods, there is still a mondria schema being created.
Can you please provide a simple example where this would make sense? maybe with a simple table definition or better with the sample data tables...

Regards,
Eduardo

jakob

Member

Registered: 2018-01-24

Re: How to use MDX functions in caluclated measures / members ? (like YTD)

It is possible to define one dimension as a "Time dimension" in Mondrian. This is an option that you can select when designing a mondrian schema yourself using for example the schema workbench (https://mondrian.pentaho.com/documentat … kbench.php)

Currently, the cube designer in the dynamic list Pivot view has no support for advanced options like this  (which would not be too hard to add i believe).

Last edited by jakob (2018-12-07 09:26:45)

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: How to use MDX functions in caluclated measures / members ? (like YTD)

Hi jakob,

I raised ticket RS-3217 for this (let the user define a dimension as a time dimension in pivot reports).

Regards,
Eduardo