Distinct in MDX

[Former Member]

Hi

I've made a MDX expression, so I could select distinct values in a dimension (charactaristics). My MDX expression is:

<expression>DISTINCT(@Select(CPR NR\L01 CPR NR))</expression>

When I parse it in the univers, it says ok. When I run til univers, I get an error.

Regards,

Rikke

[Former Member · Accepted solution]

Hi,

I am not sure what you want to achieve.

By the way, Universe Designer parser does not parse MDX.

Moreover the syntw you wrote is incorrect.

So can you explain what you want to do?

Thanks

Didier

[Former Member · Accepted solution]

Hi Didier

I want to have distinct results. In non-distincts values for instance the street name appears to times. In distincts values it only appears once.

Non-distincts values

Bredgade

Vestergade

Bredgade

Bredgade

Distincts values:

Bredgade

Vestergade

Regards,

Rikke

[Former Member · Accepted solution]

Hi,

Can you provdie more details on your dimension?

Are street name members of the hierarchy or properties of a hierarchy level?

Normally all members are unique in an OLAP dimension evene if they are duplicated because the full path contains the members and its ancestors.

Regards

Didier

[Former Member · Accepted solution]

Hi

No, I can not provide more details on my dimensions. The streetname is not a part of a hierarcy. The problem is comming when I'll try to select data in a LOV in the webi before I execute the query. In the reports the data are only showing once.

/Rikke

[Former Member · Accepted solution]

Hi,

You hit a problem when dealing with level proerties or hierarchy attributes.

In fact thery are not unique within a hierarchy or a dimension.

In MDX there is no way to retrieve uniqueness of attributes unless to do post-processing after reading the data and only daisplaying unique values.

We already have a CER on that subject "ADAPT01227193" and it has been postponed from SP 3 to another service pack.

By the way this feature will be supported in Aurora.

The only workaround I suggest is:

<li>If the data source is SAP BW, then create a navigational attribute on it

<li>If the data source is MSAS 2005/2008, create a hierarchy on this attribute

<li>If the data source is MSAS 2000, create a virtuel dimension on this attribute

Regards

Didier

[Former Member · Accepted solution]

 The only workaround I suggest is:
If the data source is SAP BW, then create a navigational attribute on it

Hi Didier,

Could you shed some more light on

The only workaround I suggest is:

If the data source is SAP BW, then create a navigational attribute on it

Tried to achieve it through Navigational attribute and used the attribute via a BOBJ Universe object and still the distinct value is not displayed.

Environment: BOBJ XI3.1 SP2

[Former Member]

sorry hadn't read the lov bit

interresting hadnt realized that

Edited by: I.Heijnen on Apr 14, 2011 12:17 PM

Just a thought on where the data is going meaning webi???

Why do you need this distinct selection, this will show as just 2 addresses in webi most of the time any way.