# sign in BEx report

[Former Member]

Hi,

Does any one know how to eliminate # sign appearing in BEx report for undefined values.

Any help appreciated.

Thanks!

[Former Member]

I have a similar request

We do not want to suppress any rows but the users would like the field to be left completely blank instead of '#' and "Not assigned" when no values are found.

Any suggestions on how to accomplish this in Bex without using a VB Script on a workbook?

thanks,

Adam

[Former Member]

Hi Adam,

watch this topic:

Here is a special solution for your problem, too.

1) Execute your Query in BExAnalyzer

2) select all

3) go to the Excelmenu

FORMAT -> Conditional Formatting

4) Condition 1)

Cell value is equal to ="#"

select as font color your backgroundcolor

add a second condition 2)

Cell value is equal to ="Not assigned"

select as font color your backgroundcolor

5) go to the Excelmenu

Business Explorer->Tools->All Queries in Workbooks->Delete result

6) save as a new BW Workbook

7) be lucky (without VB Script on a workbook)

best regards Jens

[Former Member]

Thanks for the suggestion Jens.

I was unable to get the Excel Conditional Formatting to refresh with the Bex query. Despite saving the workbook, I still needed to 'select all' and then activate the conditional formatting after each refresh.

In the end, I used a VB macro to remove the '#' and 'Not assigned' values. The other topic you suggested was helpful. Here is my code:

***************************************************

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

If queryID = "SAPBEXq0001" Then

resultArea.Select

'Remove '#'

Selection.Cells.Replace What:="#", Replacement:="", LookAt:=xlWhole, _

SearchOrder:=xlByRows, MatchCase:=False, MatchByte:=True

'Remove 'Not assigned'

Selection.Cells.Replace What:="Not assigned", Replacement:="", LookAt:=xlWhole, _

SearchOrder:=xlByRows, MatchCase:=False, MatchByte:=True

End If

' Set focus back to top of results

resultArea(1, 1).Select

End Sub

***************************************************

Thanks,

Adam

[Former Member]

Hi Adam,

this is a curious behavior of the conditional formatting.

Before I have posted the solution, I have tested it without problems. Now I have try out with an other query and I have the same behavior you described?!?

My first test with a masterdata report already works.

But the VBA coding is ok and I think it solves your requirement and I am glad that I could help you.

best regards

Jens

[Former Member]

Sorry, Dirk is correct. answered before checking the values in SPRO.

[Former Member]

You can change the # in Transaction SPRO

[Former Member]

Where do I change the #? I can change the X or * if key figures are not calculated correctly but I haven't found the switch for #. Is it in 3.5?

Best regards

Dirk

[Former Member]

I think you mean not the rows, but really eliminate the sign. My idea: Maintain the info-object, place a space in "decription", and show your report with description only for that info-object.

[Former Member]

Filter the # value in your characteristic.