Create new dimension on top of SAP BW data

[Former Member]

Hi expert,

I'm having a problem to create a new dimension on top on SAP BW data. I have a dimension details object called 'L01 G/L Account Key'. When I drag this object to the result object in the web intelligence, it display full data like '1000/30100'. How can I trim the data so it shows '30100'?.Is there any formula that support in universe?

I don't want to do the formula inside the web intelligence report because I want to use this dimension to other reports as well.

Please expert....help me!

Thanks & Regards,

Syahida-

[Former Member]

Thanks guru...now I know there has a limitation on BW itself and I cannot do anything about it. But if anybody know about it..most welcome to let me know..thanks

[Former Member]

Hi,

SAP BW MDX does not support calculations that return string or date: this is a limitation of SAP BW OLAP engine.

By the way I found exceptions that satisfy your requirement.

For instance you can manipulate a substring of a dimension only with NAME and UNIQUENAME attributes.

Example, you can use these expressions:

<EXPRESSION>right([OD_COUNTRY].currentmember.name, 4)</EXPRESSION>

<EXPRESSION>right([OD_COUNTRY].currentmember.uniquename, 4)</EXPRESSION>

<EXPRESSION>left([OD_COUNTRY].currentmember.name, 4)</EXPRESSION>

<EXPRESSION>left([OD_COUNTRY].currentmember.uniquename, 4)</EXPRESSION>

<EXPRESSION>mid([OD_COUNTRY].currentmember.uniquename, 2,4)</EXPRESSION>

Regards

Didier

[Former Member]

Dear Didier,

Thanks for your advised but somehow I don't really understand on the example given. Really appreciate if you can explain on more detail. For example, if I have a dimension detail called 'L01 G/L Account Key' and it select statment is '[0GL_ACCOUNT HTP INCOME STATEMENT].[LEVEL01].[[20GL_ACCOUNT]].[Value]', which one will be replace a 'currentmember', 'NAME' or UNIQUENAME?

right(OD_COUNTRY.currentmember.name, 4)

right(OD_COUNTRY.currentmember.uniquename, 4)

Thanks & Regards,

-Syahida-

[Former Member]

Hi,

You can only use currentmember with a hierarchy or a characteristic that is the default hierarchy.

You can't use a hierarchy attribute or a level.

So assuming that 0GL_ACCOUNT HTP INCOME STATEMENT is the definition of your characteristic / hierarchy, the syntax could be:

right( [0GL_ACCOUNT HTP INCOME STATEMENT].currentmember.NAME, 4)

or

right( [0GL_ACCOUNT HTP INCOME STATEMENT].currentmember.UNIQUENAME, 4)

Maybe the solution I provide is not relevant in your case because of SAP BW limitations.

By the way, as I said SAP BW calculations cannot return string or numeric results (this is only possible using User Exit written in ABAP).

Didier