How to get OLAP universe to understand CurrentDate()

[Former Member]

Hello,

I have a BOXI 3.1 base version OLAP universe built against MSAS2008

cubes.

I am trying to find a way for the query to automatically take

CurrentDate or CurrentMonth or CurrentYear. However since we cannot

control the MDX, does anyone have any idea on how this could be done?

e.g. in query panel we should have something like Date = NOW

() which can be run against MDX cube and returns the results automatically if scheduled to run everyday.

Any suggestions would be very much appreciated.

Regards

[Former Member · Accepted solution]

Hi,

You can create calculated measures and predefined filters using MDX expressions.

So in your case you will be able to use Now() function.

Here is a sample based on Adventure Works that set to 1 the data members that are between system date and system date - 365 days.:

<EXPRESSION>IIF( CDate([Date].[Calendar].CurrentMember.MemberValue) >= Now()-365  AND CDate([Date].[Calendar].CurrentMember.MemberValue) <= Now(),1,0 )<EXPRESSION>

Then you can build a filter based on this expression and only return rows where the calculaeted measure is equal to 1.

Regards

Didier

[Former Member · Accepted solution]

Didier,

Just to clarify - We should create expressions that are valid MDX and then create a filter using them on cube based universe?

Can something similar be created on the cube and just dragged to the query panel? I am not sure if that's feasible but i would say a variable which says date = "*MDX formula*" when dragged into the query panel should provide a similar effect.

Thanks

[Former Member · Accepted solution]

Hi,

Yes, you can do it.

In fact it is better to push the calculation on the database.

But it also depends on the user demand. If you have to create a calculation, update the cube rebuild the aggregations only for one or two users: it is too costly.

But if you want to use dynamic calculations using prompts then the universe is the best place to do it.

Regards

Didier