How to change the # and Not Assigned in 0 in BEX ?

[Former Member]

Hi gurus,

I have a report with lots of # and Not Assigned fields and I was wondering if there is a posibility to change those values in 0.

Thanks you for your help,

Ramona

[maxilopez]

Hi Ramona,

There is an alternative based on changing the way BEx queries shows data in case of error:

1) Go to SPRO transaction and press "SAP Reference IMG" button.

2) Select "SAP NetWeaver" -> "Business Intelligence" -> "Settings for Reporting and Analysis" -> "General Settings for Reporting and Analysis" -> "Presenting the numeric value in the Business Explorer"

3) There, you can define the characters that are displayed in these circunstances:

• Division by zero

• Does not exist

• Num. overflow

• Mixed values

• No authorization

Take into account that this applies to all queries.

I hope this helps you.

Regards,

-

Maximiliano

[KulDeepJain]

Hi Ariel ,

This all given options will work for Keyfigures. Is we have same for characteristics values.

Please reply

[Former Member]

Hi ,

You may use macros to replace all # with 0 or blank whatever by using Macros.

1) In excel menu Tools->Macro >Macro

2) Enter a macro name (say SAPMACRO and select 'create'. It will take you to visual basic editor

4) To display '#' as 0 paste the following code

Sub SAPMACRO(queryID As String, resultArea As Range)

Dim c As Range

For Each c In resultArea.Cells

If c.Value = "#" Then c.Value = "0"

Next c

End Sub

5) Close the editor and click on refresh again.

If you want to give it as blank you may do that also with the code,just give blank instead of space.

Hope it helps:)

[former_member186696]

Hello,

Try to do the following

Create a formula with your keyfigure.

Than, plus 0 in this formula. Ex:

<key figure> + 0

Let me know if works.

Best regards,

Edward

[Former Member]

Just Maintain master data in rsa1.

choise the infoobject, maintain master data and enter "0" to the emty value

Aharon

[Former Member]

Hi Aharon,

thanks for your suggestion. I know how to do that but there is a lot of Data and I wanted to know if there is a easy way to modify it.

[Former Member]

the Question is still open

[former_member188011]

Hi Ramona:

Please take a look to the documentation below.

SAP Note 379736 - How does a MultiCube work?

SAP Note 376499 - 'Not assigned' values in queries

"How to Display Result Row in One Line While Reporting On Multiprovider", article by Sreekanth S.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70dcd7b5-6a3d-2d10-c984-e835e3707...

Another alternative to remove the "#" and "Not Assigned" values is creating a Macro (for BEx reports/workbooks) or javascript for WAD, please refer to the paper by by Amrita Goswami and the blog by Andrey Uyurkin.

"Use of VB to Enhance Standard BEx Features u2013 A Case Study"

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/a0ac5c27-7448-2c10-86aa-b513703c9...

"Formatting Bex queries output with WAD Script WebItem"

If the information above is not suitable for you, please give us more details regarding your scenario, so we can provide more appropriate suggestions.

Regards,

Francisco Milán.

[Former Member]

HI Ramona,

It is not possible to change it at Bex, better you do the changes at backend writing routine to populate required data in those fields. # and Not assigned is populate at character only whenever there is a blank.

Please let me know if you still need info.

regards,

Pra

[Former Member]

Hi Prakash,

the fields are empty in the R/3. After the upload in BW in the reports are presented as # and Not assigned. How can I replace those symbols with blanks or 0 because they look terrible in the reports.

Thanks,

Ramona