Remove leading zeros using MDX function

[Former Member]

Hello,

BO XI R3.1 SP2 FP2.5, SAP BW 7.0

This seems simple, but I cannot find a solution.

How do I remove leading zeros from a character field in a OLAP universe. Is there any MDX function?

I tried

<EXPRESSION>
Value([0MATERIAL].[LEVEL01].[[20MATERIAL]].[Value])
</EXPRESSION>

<EXPRESSION>
StrToValue([0MATERIAL].[LEVEL01].[[20MATERIAL]].[Value])
</EXPRESSION>

I can remove the leading zeros in Webi, but we require it for ad-hoc reporting? Is there a way to do this in the universe?

Thanks,

Nikhil

[Former Member]

Hi,

SAP BW MDX only allows calcuated expressions that return numeric values: this is one if the main SAP MDX limitations.

By the way there is the abilitiy to return string only for those expressions:

[Dimension].currentmember.name
[Dimension].currentmember.uniquename

So you can do this:

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

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

<EXPRESSION>
(left([0MATERIAL].currentmember.name, 3))
</EXPRESSION>

<EXPRESSION>
(left([0MATERIAL].currentmember.uniquename, 3))
</EXPRESSION>

Otherwise you can use WebI formulas as described in the thread.

Regards

Didier

[former_member793810]

How many zeros appear?

Do you want all leading zeros to disppear?

Here is quick solution to remove leadin zero at WebI,

You can create Variable Formula using variable editor and then users can use it by dragging and dropping to the report. It will be reusable.

=Replace(LeftTrim(Replace([Your Object];"0";" "));" ";"0")

You can use righttrim too...

Or you can use at universe level...

<EXPRESSION>RIGHT([([0MATERIAL].[LEVEL01].[[20MATERIAL)</EXPRESSION>

Regards,

Bashir Awan

[Former Member]

I would suggest this too. To do this in WebI, not in the OLAP Universe level.

[Former Member]

<expression>
right([0MATERIAL].[LEVEL01].[[20MATERIAL]].[Value],8)
</expression>

At universe level above expression does not work.

I already gave the Webi report option, but users want to do ad-hoc and need the zeros trimmed at get go.

[former_member793810]

Nikhil,

You can trim leading zeros at BW query.

You also wrote that you can trim at WEbI level then why don't you create a reusable variable at WebI level and user can reuse it every time he creates a new report.

REgards,

Bashir Awan

[Former Member]

Hi Bashir,

How to do it at BW query level? I assume you mean BEx query. Can you give an example?

Variables created in a WebI report are not globally available. To be globally available, it has to changed at universe.

Nikhil

Edited by: Nikhil Khasnis on May 27, 2010 2:57 PM

Also,

FormatNumber(ToNumber([String_Object]);"#")

works as well. I think it's more user friendly than having them use Trim and Replace functions.

[former_member793810]

Hi Nikhil,

Sorry it is my bad. At BEx Level you cannot control leading zeros but you can control Number of Decimal places.

In WebI you can create reusable filter and user will not have to create it again and again. They will just drag and drop like other objects.

You are right the best place is Universe.

Regards,

Bashir Awan

[Former Member]

Hi All,

It seems there is a conversion routine (MATN1) in SAP that when turned on supresses the zeros. We tested and this works.

Anyone else used this approch? Any cons?

Nikhil