Avoiding # in BI reports

[Former Member]

Hi All,

We all aware that in BI reports wherever if the characteristic value is empty (Null) we get it as '#' in the query o/p.

I have a requirment for example, if the Gender is not maintained then for a customer/employee and if i pull out the report based on Gender wise i will have 'M', 'F' and '#' in the output. but instead of '#', i need to display some text like 'Others'.

How can i acheive this.

Thanks in advance

Prem

[Louis_Clarke · Accepted solution]

Hi Prem

You can also replace the '#' in the front end. It depends on where the reports are displayed. If you are using BEx analyzer (EXCEL) then you can use VBA to replace the value. If the reports are displayed in the WEB analyzer Javascript can be used to replace the value.

We replace the "#" with blank in all our workbooks using the following VBA macro (This is in a BEx Analyzer 3.5 environment):

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

For Each c In resultArea

If c.Value = "Not assigned" Or c.Value = "#" Then

c.Value = " "

End If

Next

End Sub

Hope this helps

Louis

[Former Member · Accepted solution]

Louis,

Am using the WAD for my reporting..

Do we have some option to address '#' for WAD reporting.

Thanks

Prem

[Louis_Clarke · Accepted solution]

Hi Prem

I think Ashutosh Singh's solution is the best for your problem. Since you only want to change the '#' for one characteristic. If the characteristic does not have master data with text enabled then you will first have to enable "With master date" and "With texts" (in your case you need only short text). Then maintain the master data. Add the short text "others" for the null case and add short text 'M' and 'F' for the other cases. Then display short text in your query for the characteristic.

[Former Member · Accepted solution]

Louis,

Sorry! for not getting you guyz properly.

Not the one specific char... today Gender might be null.. tomorrow some other char might be null, say Nationality or Customer Group or somethings else..

I have 12-15 free characteristics... still customer wants to have some attributes as Nav att. Any of these might be null... so i have apply logic to all master data and its attributes to handle Null and populate it as OTHERS. Is this is the wise way? or i can have some better option.

-Prem

[Louis_Clarke · Accepted solution]

Hi Prem

I understand your problem. See the following blog to change the value in WAD with javascript:

/people/andrey.uryukin/blog/2010/08/29/formatting-bex-queries-output-with-wad-script-webitem

Louis

[Former Member]

Hi All,

I have problem with # in WebI . How can i Avoiding # in WebI ?

How can i acheive this.

Thanks

[Former Member]

Hi Prem ,

Why don you try maintaining text of this infoobject , and for the blank entry in the master data maintain text as "OTHERS". And for others maintain the same key value as the text .

Then display text in the report .

Kind Regards,

Ashutosh Singh

[Former Member]

Dear All,

Thanks for the reply, here is my concern

Ashutosh,

I cant do maintaing the text as 'OTHERS' if not master because what if the case a Non Master characteristics IO data while loading transaction data comes Null. Even this case i will have to address.

So if am not wrong the only option is we may need to address this in reporting part only.

I am using WAD for my reporting.

Thanks

Prem

[Former Member]

Hi prem,

If Gender data is not avaialble then you intend to show Blank there without # sign. If yes then go to query properties, select display tab, in zero value dropdown select show zeros as blank. That should work for you.

Alternatively at the backend you can write a small routine to populate the field with

Other or NA

by doing a check at field level. This would be simple code just checking the value if blank replacing it with either of 2 options.

If Source_Fields-Gender = "".
Result = "Other".
endif.

Regards

Raj Rai

[former_member555685]

Hi

You can do it at backend. Write a field routine or end routine and populate the gender field where ever it is blank.

[Former Member]

Piyush,

you mean during the loading of data to cube/IO ? or during the query population?

-Prem

[former_member555685]

Better do it when you are loading the data