MDX

[Former Member]

Hi Experts,

Created OLAP universe on SAP BW.

An item has multiple dates.I need to get only Recent (max) date for each item.

For example:

Date Item

08/22/2009 Yahoo

08/23/2009 Yahoo

08/01/2009 Google

09/01/2009 Google

09/02/2009 Google

FInally i need to pick only below records

08/23/2009 Yahoo

09/02/2009 Google

please help me to create object at universe level (not at report level).

I understand its only possible with MDX statements. I tried to use Max MDX Function and Distinct MDX Function. As lack of proper documentation i am unable to do it, it always throws error when i do parse.

Environment: BO 3.1 SP2

-Thanks,

DEEPU

Edited by: deep reddy on Sep 8, 2009 7:41 PM

[Former Member · Accepted solution]

Hi,

Try to create two objects in universe as Maxdate 1, maxdate2 with formulas as

Maxdate1 = select max(date) from ......

Maxdate2 = select to_date(max(date), 'Date format')-1 from ..........

Cheers,

Suresh Aluri.

[Former Member · Accepted solution]

Suresh,

Thanks for your response.

I am looking for MDX code.

In BO OLAP Universe Max function will be:

Max( , )

Description:

MAX("Set"[, "Numeric Expression"] )

returns the maximum value of a numeric expression that is evaluated over a set

I am bit confused here what exactly should i provide for "Set" and "Numeric Expression" as i am looking for max date for each item.

-Thanks,

Deep.

[Former Member · Accepted solution]

HI,

Do you need to only retrieve the latest date for a given product?

If it is the case try the MDX I submitted.

I will try to write a simpler MDX expression using the characteristics MDX defintion you can provide.

Didier

[Former Member · Accepted solution]

Hi Didier,

I Really appreciate for your help.

i have created a predefined condition with the following code

IIF (Rank ([0CALDAY].[LEVEL00].Currentmember,  Order([0CALDAY].[LEVEL00].Currentmember.Level.Members, 
Rank ([0CALDAY].[LEVEL00].Currentmember,  Order([0CALDAY].[LEVEL00].Currentmember.Level.Members, BDESC))
+ Rank ([0MATERIAL].[LEVEL01].[[20MATERIAL]].[Value].Currentmember,  Order([0MATERIAL].[LEVEL01].[[20MATERIAL]].[Value].Currentmember.Level.Members , 
Rank ([0MATERIAL].[LEVEL01].[[20MATERIAL]].[Value].Currentmember,  Order([0MATERIAL].[LEVEL01].[[20MATERIAL]].[Value].Currentmember.Level.Members), BDESC)) = 
Rank ([0CALDAY].[LEVEL00].Currentmember,  Order([0CALDAY].[LEVEL00].Currentmember.Level.Members , 
Rank ([0CALDAY].[LEVEL00].Currentmember,  Order([0CALDAY].[LEVEL00].Currentmember.Level.Members), BDESC)) + 2
, 1, 0)

It parses OK, But when i used in My WEBI Query panel, It throws an error

An internal error occured while calling 'processDPCommands' API. (Error: ERR_WIS_30270)

An internal error occured while calling 'processDPCommands' API. (Error: ERR_WIS_30270)

[0CALDAY].[LEVEL00]--- L01 Calendar day

[0MATERIAL].[LEVEL01].[[20MATERIAL]].[Value] -

---

L01 Material key(Item)

Note: ofcourse i have not filtered Key figure (measure) as you said , So tried with and without Key figure (measure) into my query.

-Thanks,

DEEPU.

[Former Member · Accepted solution]

Hi Didier,

Any Updates on MDX code.

I will be very thankful to you if can help me.

Thanks,

DEEPU.

[Former Member · Accepted solution]

Hi Deepu,

The MDX I provided must be defined in a calculated measure enclosed with <EXPRESSION></EXPRESSION tags.

But the syntax is invalid, use this one instead of your (referrring LEVEL00 or LEVEL01 is invalid in this case):

IIF (Rank ([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members, 
Rank ([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members, BDESC))
+ Rank ([0MATERIAL].[[20MATERIAL]].[Value].Currentmember,  Order([0MATERIAL].[[20MATERIAL]].[Value].Currentmember.Level.Members , 
Rank ([0MATERIAL].[[20MATERIAL]].[Value].Currentmember,  Order([0MATERIAL].[[20MATERIAL]].[Value].Currentmember.Level.Members), BDESC)) = 
Rank ([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members , 
Rank ([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members), BDESC)) + 2
, 1, 0)

If you want to use it in a predefined condition you need to use the syntax in the filter or reference the calculated measure in the filter:

Solution 1:

<FILTER EXPRESSION="@Select(MyClass\MyObject)"><CONDITION OPERATORCONDITION="Equal"><CONSTANT CAPTION="1"/></CONDITION></FILTER>

Solution 2:

<FILTER EXPRESSION="IIF (Rank ([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members, 
Rank ([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members, BDESC))
+ Rank ([0MATERIAL].[[20MATERIAL]].[Value].Currentmember,  Order([0MATERIAL].[[20MATERIAL]].[Value].Currentmember.Level.Members , 
Rank ([0MATERIAL].[[20MATERIAL]].[Value].Currentmember,  Order([0MATERIAL].[[20MATERIAL]].[Value].Currentmember.Level.Members), BDESC)) = 
Rank ([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members , 
Rank ([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members), BDESC)) + 2
, 1, 0)"><CONDITION OPERATORCONDITION="Equal"><CONSTANT CAPTION="1"/></CONDITION></FILTER>

Regards,

Didier

[Former Member · Accepted solution]

Hi,

We can simplify the MDX expression to only select the latest value of Calendar Day, like this:

Rank([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members , 
Rank([0CALDAY].Currentmember,[0CALDAY].Currentmember.Level.Members), BDESC))

Last, if your members are sorted in alphabetic order, use this MDX expression:

IIF ([0CALDAY].Currentmember = [0CALDAY].Currentmember.lastsibling, 1, 0)

Then create the filter has mentioned in previous thread.

Regards,

Didier

[IngoH · Accepted solution]

Hi,

just to put a small warning here - this is sorted based on ALPHABETICAL ORDER !!!

means 01,10,02

Ingo

[Former Member · Accepted solution]

Hi Didier,

My members (list of values) are not sorted in Alphabetical order.

You code is parsing OK, Thanks for that.

But I am bit confused here where to have your code, Whether in Objects (if so Which Object Calender day or Material) Where Clause OR in a predifined Condition (Filter)

I had below code in Where Clause of a predifined Condition (Filter) ofcourse it does not have Select clause.

<FILTER EXPRESSION="IIF (Rank ([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members, 
Rank ([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members, BDESC))
+ Rank ([0MATERIAL].[[20MATERIAL]].[Value].Currentmember,  Order([0MATERIAL].[[20MATERIAL]].[Value].Currentmember.Level.Members , 
Rank ([0MATERIAL].[[20MATERIAL]].[Value].Currentmember,  Order([0MATERIAL].[[20MATERIAL]].[Value].Currentmember.Level.Members), BDESC)) = 
Rank ([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members , 
Rank ([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members), BDESC)) + 2
, 1, 0)"><CONDITION OPERATORCONDITION="Equal"><CONSTANT CAPTION="1"/></CONDITION></FILTER>

when i use this predefined condition (filter) in WEBI Query panel and Run Query; Its throwing database error.

I had below code in Where clause of L01 Calender Day Object. But no use.

Rank([0CALDAY].Currentmember,  Order([0CALDAY].Currentmember.Level.Members , 
Rank([0CALDAY].Currentmember,[0CALDAY].Currentmember.Level.Members), BDESC))

Thanks for your patience and help. I think we are close to the solution. Just please clarify what exactly i need to do.

-DEEPU.

[Former Member · Accepted solution]

Hi Didier,

Did get get a chance to look at my issue.

-Thanks,

DEEPU.

[Former Member · Accepted solution]

Hi Deepu,

The following syntax you wrote is invalid:

[0MATERIAL].[[20MATERIAL]].[Value].Currentmember

You just copied this from the universe but this is not real MDX. This is the definition of a dimension attribute and you cannot use it like this.

I recommend to use a pre-defined filter like this:

<FILTER EXPRESSION="IIF ( IIF(ISEMPTY([Measures].[0D_INV_QTY]),999,Rank([0CALMONTH].Currentmember,  Order([0CALMONTH].Currentmember.Level.Members , 
Rank([0CALMONTH].Currentmember,[0CALMONTH].Currentmember.Level.Members), BDESC)) )
  = Min ([0CALMONTH].Currentmember.Level.Members, 
IIF(ISEMPTY([Measures].[0D_INV_QTY]),999,Rank([0CALMONTH].Currentmember,  Order([0CALMONTH].Currentmember.Level.Members , 
Rank([0CALMONTH].Currentmember,[0CALMONTH].Currentmember.Level.Members), BDESC)) ) ), 1, 0)"><CONDITION OPERATORCONDITION="Equal"><CONSTANT CAPTION="1"/></CONDITION></FILTER>

The syntax is a bit complex because we need to create a ranking and compare it to the Minimum ranking for members. But we have to take into account empty value because as we are introducing a new measures all memebers will be retrieved eene those that have no data for the measure.

This is why I have added the comparision with the measure used in the query:

IIF(ISEMPTY([Measures].[0D_INV_QTY]),999, ...)

I have tested it and it works fine.

Regards,

Didier

[Former Member · Accepted solution]

I am seeing a 1 for a blank date, 2 for the max date, etc...Do you know if it is possible to exclude these nulls from the ranking?

[Former Member · Accepted solution]

Hi,

If you want to remove rows that contain measures with null values then you have to define a pre-defined filter in the universe and use it in your query panel such like this:

<FILTER KEY="@Select(MyClass\MyMeasure)"><CONDITION OPERATORCONDITION="NotEqual">
<CONSTANT CAPTION="null"/></CONDITION></FILTER>

Didier

[Former Member · Accepted solution]

Thanks! This definitly helps.

We are trying to take this one step further by having a filter compare two days. For Example, [0CALDAY].[LEVEL01].MEMBERS=[YCREDCTRL__YMAXLDDT].[LEVEL01]

I tried the below Filter, but got an error message.

<FILTER KEY="@Select(Calendar Day\Calendar Day)"><CONDITION OPERATORCONDITION="Equal"><CONSTANT CAPTION="@Select(Previous Month Date\Previous Month Date)"/></CONDITION></FILTER>

Previous Month Date will only contain one date.

[Former Member · Accepted solution]

Hi,

The filter definition you provided is oincorrect. In the CAPTION XML tag, you are allowd to only provide contsants or prompts.

The syntax would be something like this:

<FILTER KEY="IIF([0CALDAY].CurrentMember = [YCREDCTRL__YMAXLDDT].CurrentMember, 1, 0)"><CONDITION OPERATORCONDITION="Equal"><CONSTANT CAPTION="1"/></CONDITION></FILTER>

Please have a look on this document that gives you a lot of sample for defining predefined filters:

[OLAP universes best practices|https://wiki.wdf.sap.corp/wiki/display/globalregions/OLAPuniversesbest+practices]

Regards,

Didier

[Former Member]

Hi All,

First of all, thank you for this excellent solution.I have been trying to solve the same issue(running a report based on max date) and using this solution I have been able to create a filter which enables me to create the required report.

Now to the issue I am currently facing,

The report works fine when we ran it on a test environment ie :with small amount of data.

Our production environment has millions of rows of data and when I run the report with filter it just hangs.I think this is because it tries to rank all the dates(to find the max date) and thus resulting in a huge performance issue.

Can someone suggest how this performance issue can be overcome?

Thanks and Regards,

Smitha.

[IngoH]

Hi Deep,

how often will you need this kind of functionality ?

Is the date a characteristic or a keyfigure ?

thanks

ingo

[Former Member]

Hi Ingo,

Thanks for your response.

I need it for many reports.

Date is a charecteristic.

Tried working with BW Team but we can't do it at BEx Query level. Some how we should do at universe level (No exceptions)

-Thanks,

DEEPU.

[IngoH]

and you only want a single date then shown in the report. then you are down to ranking via MDX.

ingo

[Former Member]

I am looking for Recent (Max) date for a each product. We can do this at report level. We are getting huge amount of data into Report and filtering it for few rows. Lot of burden on WEBI Server and other issues.

Most of the reports are Adhoc (user's want to Revenue at each week, etc........). So we decided some how we should get this issues resolved at universe level.

I hope you undersatnd my problem.

-Thanks,

DEEPU.

[IngoH]

I do understand it and I am still thinking the best approach is to solve the problem in the BW query. you could create a SAP EXIT variable which filters the data which also means less data for WebI.

Ingo

[Former Member]

Hi Deepu,

In fact this is not a simple MDX expression to build and I did not find any simple sample on the Web.

By the way, I have built a tricky MDX expression that works.

The sample below is based on $0D_SD_C03 infocube and on the characteristics Company Code and Distribution Channel.

The result will return 1 for the max tuple value (in this sample I also avoided to use the "not assigned" node: #.

This expression is to be used in a quer filter or predefined filter to filter the result with a constant equal to 1.

Moreover you also need to create a universe predfined filter based on the keyfigure used in the query to ensure that you will not retrieve empty values. The filter will look like this:

<FILTER KEY="@Select(MyClass\MyMeasure"><CONDITION OPERATORCONDITION="NotEqual"><CONSTANT CAPTION="null"/></CONDITION></FILTER>

Here is the MDX sample:

IIF (Rank ([0D_CO_CODE].Currentmember,  Order([0D_CO_CODE].Currentmember.Level.Members , 
Rank ([0D_CO_CODE].Currentmember, [0D_CO_CODE].Currentmember.Level.Members), BDESC))
+ Rank ([0D_DIS_CHAN].Currentmember,  Order([0D_DIS_CHAN].Currentmember.Level.Members , 
Rank ([0D_DIS_CHAN].Currentmember, [0D_DIS_CHAN].Currentmember.Level.Members), BDESC)) = 
Rank ([0D_CO_CODE].Currentmember,  Order([0D_CO_CODE].Currentmember.Level.Members , 
Rank ([0D_CO_CODE].Currentmember, [0D_CO_CODE].Currentmember.Level.Members), BDESC)) + 2
, 1, 0)

Regards,

Didier