Skip to Content
Author's profile photo Former Member

Remove ‘#’ for characteristics in BEx output and display it as blank

Scenario :

Sometimes in the output of BEx report we have characteristics which appear as ‘#’ i.e.values of those characteristics for which there is no value in the underlying info provider, BEx displays it as ‘#’ if we are using ‘KEY’ and ‘NOT ASSIGNED’ if we are using ‘TEXT’ . Most people try to use VBA code or macros inside a workbook which may hamper the performance of your report .Here i will present a simple solution to display characteristics as blank in the BEx output without writing any macros in excel. There are few exceptions which will be discussed at the end.

  Lets take an example to show you the above scenario :

  Below is the output of a BEx report in which ‘#’ is appearing :

BEX1.jpg

   Below is the data from the underlying infoprovider for the above report :   

BEX2.jpg    

Below are the steps to display ‘#’ as blank for a characteristic in the BEx report output :

STEP 1 :

Go to the master data of the characteristics(in this case : ZWA_IND) for which you want the blanks instead of “not assigned” or “#”.

BEX3.jpg

STEP 2 :

Execute(F8).

BEX4.jpg

STEP 3 :

Double click on description for the blank entry and enter space .

BEX5.jpg

STEP 4:

Then save it. Do the above steps including step 4 for all characteristics which needs to be shown as blank in the BEx report output.

BEX6.jpg

STEP 5 :

Open the query in BEx Query designer and set “Display as : Text” for the characteristic “ZWA_IND” & “ZGA_IND” .

BEX7.jpg

STEP 6:

Execute the report to see the output. Notice that the “#” has disappeared for the characteristics “ZWA_IND” & “ZGA_IND”.

BEX8.jpg

Explanation :

The “#” disappeared because now the system is displaying text for the characteristics “ZWA_IND” & “ZGA_IND” . And in the text master data, a space has been stored which displays as blank in the BEx output.

Exceptions :

The above approach cannot be applied on characteristics which are of type “DATS” or “TIMS”. Because such characteristics does not have a “text master data” where we can store a space for the blank values.

Hope the above approach will be useful for you guys .

All comments and suggestion are welcome !!

Assigned Tags

      95 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Anshu Lilhori
      Anshu Lilhori

      Very well drafted..Nice presentation..Thanks for sharing.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Anshu for the comments and the motivation !!

      Author's profile photo Sandeep a
      Sandeep a

      Thanks for sharing .

      nice document ...

      Regards

      Sandeep

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thx Sandeep....Will be posting more documents in future on some important topics !!

      Author's profile photo Former Member
      Former Member

      Gud Approach Ashutosh. Thanks.

      Regards,

      Venu

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thx Venu . I am glad that you liked it . πŸ™‚

      Author's profile photo Former Member
      Former Member

      Hey thanks a lot for sharing this...

      I faced this problem today only and got the doc πŸ™‚

      Thanks πŸ™‚

      Regards,

      Priyanka

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hey Priyanka,

      I am glad that i am able to help your query . πŸ™‚

      Author's profile photo prabhith prabhakaran
      prabhith prabhakaran

      Hi ASHUTOSH SINGH,

      Good document. Nicely Presented.

      BR

      Prabhith

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thx Prabhit for your response !!

      Author's profile photo Krishna Chaitanya
      Krishna Chaitanya

      Good one ..... nicely presented.... thanks for sharing

      πŸ™‚

      Regards

      Krishna Chaitanya.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thx Krishna for your response !!

      Author's profile photo CH Raman
      CH Raman

      Very informative tip. Thanks for sharing Ashutosh.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hey raman. My pleasure . I am glad it'll be useful for you. πŸ™‚

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      Nice trick.. πŸ™‚

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hey Suman,

      Thx!!

      Yea it was just a matter of clicking !!

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Nice invent.

      πŸ™‚

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hey Satendra,

      Thx for the appreciation and the word "invent" πŸ™‚ !!

      Author's profile photo Anand Kumar
      Anand Kumar

      Hello,

      A very helpful tip. Thanks for sharing.

      Regards,

      Anand Kumar

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Anand !!

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Hi,

      We can filter this #  value in bex.

      Regards,

      SM 

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      If by filtering you mean excluding #.... then it will remove the complete row itself !!

      Author's profile photo Former Member
      Former Member

      Hi,

      You explored a nice tweak Ashutosh.

      Thanks for sharing. And keep exploring!

      Regards,

      Mohammed

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hey thx Mohammed !!

      Will continue exploring !!

      Author's profile photo Former Member
      Former Member

      Nice Idea. Good Document. How can we do that in production 😐 , any chance.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hey Kumar,

      Thx for the nice comments.

      You have to do it in the production also in the same way mentioned above in the document. This is not a thing which can be transported from development to production.

      Cheers !!

      Author's profile photo Former Member
      Former Member

      I mean - If the business not at all accepting for changes πŸ™‚

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi kumar ,

      I understand your concern. But this is just a solution presented in order to avoid writing excel macros and other tedious solutions.

      You need to take a permission sort of from the management or convince them with this proper document that it can be done safely in the production environment. Because this solution asks you to change a data entry and not the object itself. And data entry changes can be done in the respective system itself .

      Otherwise write an abap programme in dev system which can carry out the similar kind of changes as mentioned in the document. And transport that abap programme to prd. But again I doubt management will accept this also.

      Author's profile photo Former Member
      Former Member

      Thank you Ashutosh. You are correct. Nice explanation.

      Author's profile photo Former Member
      Former Member

      So simple but so useful.

      Regards,

      Sushant

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hey ,

      Thx Sushant .. !!

      Author's profile photo Former Member
      Former Member

      How we can implement such scenario for mass level data, If I am not wrong then we require to use routine?

      Regards,

      Sushant

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hello Sushant,

      It doesn't matter if you have a mass data or not. In the text master data there will always be a single blank entry. You just need to edit that .

      In my requirement there were 6 info objects which were showing #. So i identified those 6 info objects and did the same procedure for all the 6 info objects.

      You do not require a routine to do this !!

      Hope the above explanation was helpful.

      Regards,

      Ashu

      Author's profile photo Abdullah Qureshi
      Abdullah Qureshi

      Nice Information Ashutosh  πŸ™‚

      Thanks for sharing..

      Regards,

      Abdullah

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hey Abdullah, 

      Thanks. I am glad that you liked it.

      Cheers!! 

      Regards,

      Ashu

      Author's profile photo Former Member
      Former Member

      Good and Helpful  Document.New approach.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hey Karthick,

      Thx for you valuable comments .

      Regards,

      Ashu

      Author's profile photo Former Member
      Former Member

      Hi Ashutosh,

      Nice Document with easy approach....

      Regards

      Subbarao M

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thx Subbarao for the appreciation.

      Author's profile photo Former Member
      Former Member

      Nice document. Thanks for sharing with us..

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thx Chandra for the appreciation !!

      Author's profile photo John Hawk
      John Hawk

      I love a simple solution!

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks John .

      I will keep exploring more for simples & interesting solutions .

      Author's profile photo Gill Leo
      Gill Leo

      Is there a risk of data being overwritten if the master data source is SAP ERP?

      With regards

      Gill

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hello Gill,

      Thank you for your valuable feedback and i must add that it was a very good one.

      I dont think so there is any risk involved of data being overwritten . But i will surely look into your point as i find it very interesting to look into .

      But for sure, for those info objects which does not have any text master data loaded from any source system , this document will help .

      Regards,

      Ashutosh Singh

      Author's profile photo Prashanth konduru
      Prashanth konduru

      Nice n Well documented... Thanks for sharing ...

      Bookmarked n rated the blog.

          

      Cheers πŸ™‚

      KP

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Prasanth,

      Thanks for the feedback and the appreciation.

      I am really motivated by comments from you guys.

      I will try to post more interesting & meaningful blogs

      Regards,

      Ashutosh

      Author's profile photo Kamal Mehta
      Kamal Mehta

      Hi Ashutosh,

      Does answers lots of queries on the same.

      Bookmarked it . Very useful.

      Thanks for sharing.

      Regards

      Kamal

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Kamal,

      Thanks. Yea its a common problem which every BI person faces.

      I am glad that it'll be of some use to you.

      Please do read the comments by Gill Leo on this document, as i found that response very interesting.

      Regards,

      Ashutosh

      Author's profile photo Former Member
      Former Member

      Hello Ashutosh,

      This is one of the most annoying problems we face, thanks for this easy and cool solution πŸ™‚ .

      Kind Regards

      Manna Das

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hello Manna,

      Thanks for the appreciation.

      I am glad that it'll be of help to you.

      Regards,

      Ashutosh

      Author's profile photo Former Member
      Former Member

      We faced similar kind of Issue while doing testing ..Thanks for sharing   .Helpful  πŸ™‚

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hey Naveen,

      Thanks for the valuable comments.

      Regards,

      Ashu

      Author's profile photo Former Member
      Former Member

      Short and Simple explanation.......... πŸ™‚

      Author's profile photo Former Member
      Former Member
      Blog Post Author
      Thx Mithun. I like to keep it short and simple.
      Author's profile photo Ashok Babu Kumili
      Ashok Babu Kumili

      Hello Ashutosh Singh,

      Great read.. Indeed, Very helpful... It has been summarized very nicely.... Nice post..

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hello Ashok,

      Thanks for the appreciation. I am glad that you liked it.

      Regards,

      Ashutosh Singh

      Author's profile photo Former Member
      Former Member

      Good one. Thanks for sharing

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Raghvendra !!

      Author's profile photo Ganesh Bothe
      Ganesh Bothe

      Hi Ashutosh,

      Nice presentation..thanks for sharing πŸ™‚ .

      Author's profile photo Former Member
      Former Member

      it's nice article..

      Author's profile photo Former Member
      Former Member
      Blog Post Author
      Thanks Bharan !!
      Author's profile photo Former Member
      Former Member

      We have an issue here for called the midnight scenario, where values for 00:00:00 will display as # as the system does not know what day to attribute a particular piece of data to. Any ideas on how to get it to display as 00:00:00?

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi David,

      Can you elaborate more on your requirement . In what type of info object do you want it to display as 00:00:00 ?

      Is it of type TIMS ?

      And also david if you post your requirement in a separate thread , more people will be able to help you .

      Regards,

      Ashutosh Singh

      Author's profile photo Former Member
      Former Member

      Hi Ashutosh,

      Yes its of type TIMS, more specifically a transaction time.

      Apologies if I'm not adhering to protocol here, I'm kind of new. πŸ™‚

      Author's profile photo Former Member
      Former Member

      Hi Ashutosh,

              

            it's tricky idea, to solve it's purpose.

      Thanks a lot.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Yea its a workaround . Appreciate your feedback

      Author's profile photo Gaurav Aggarwal
      Gaurav Aggarwal

      Thanks for sharing; simple and effective solution.

      I tried this option once before but somehow system was not accepting 'blank' as a value and was still showing #, so I maintained . (dot).

      This way you will still get . but it is easy to ignore πŸ˜‰

      Regards,

      Gaurav

      Author's profile photo SVU 123
      SVU 123

      Good One Ashutosh !! Will be of Great help.

      rgds

      SVU123

      Author's profile photo Former Member
      Former Member

      Thanks for sharing the info... πŸ™‚

      Author's profile photo Former Member
      Former Member

      Nice Explaination, Ashu.!

      Author's profile photo Former Member
      Former Member

      Nice presentation .. but if i want to display only key .

      Author's profile photo Mikkel Bojsen-MΓΈller
      Mikkel Bojsen-MΓΈller

      Thanks for the nice input.

      For me it didn't bring the expeted result. I inserted space in step 3, but when saving this it was removed again. Now I just had an entry in the text table where the null value had a null text (instead of Space). In the reports now both the key and text showed '#'.

      Maybee it's because the characteristic were of type 'NUMC'. Even I didn't think that it should matter for the example above.

      My suggestion is that SAP should have made an option in the Query designer so it was possible to choose 'Show # as blank' (or in the Query  properties).

      Sometimes users just want to have shown a list and why not satisfy those users? Probably it would not be that complex for SAP to facilitate such a feature.

      Nb. It tried it out on BW 7.31 SP9.

      unassigned value example.jpg

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hello Mikkel,

      yes you are right. Sometimes i wonder if SAP could have given this option for characteristics too as they have given for key figures in BEx with "Display zero as ...." option.

      Lets hope that they bring out these features. They have taken a good step by removing the 60 character limit.

      Regards,

      Ashutosh Singh

      Author's profile photo Former Member
      Former Member

      Very well drafted..Nice presentation..Thanks for sharing.

      Author's profile photo Sahil Khurana
      Sahil Khurana

      Ashutosh,

      Can we remove # value from LOVs as well?

      Author's profile photo CH Raman
      CH Raman

      LOVs?

      Author's profile photo Sahil Khurana
      Sahil Khurana

      List of Values...

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Not sure....but you can use values from the infoprovider setting instead of values from master data table for your infoobject.

      # coms because your LOVs are looked from the master data which has #.

      Author's profile photo Former Member
      Former Member

      Good one.

      Author's profile photo Former Member
      Former Member

      Nice document and very useful information.

      Author's profile photo PAVAN SAPHANA
      PAVAN SAPHANA

      Hi Ashutosh,

      That's really a very good explanation. But my problem right now is for the date field. I should eliminate the blank values in the DSO for date field with 00000000. Because of this blank values in the DSO the report is giving an ORA error.

      How do we replace the blank values in the DSO for historical data and also the new loads?

      Please advise.

      Thank you.

      Regards,

      Pavan.

      Author's profile photo Aparajit Banik
      Aparajit Banik

      Dear ASHUTOSH

      First of all a very nice doc to avoid code and apply trick for disappearing  hash.
      The trick works with text description  but sorry to say not for the key i guess. Is there any way to make similar trick apply for the key?

      As most of times key is required to be displayed, and if the  key value is not present it will be hash. So adding blank space in description in master data is not going to help isn't it?
      Please comment on this.
      Thanks πŸ™‚

      Author's profile photo Former Member
      Former Member

      Hi,
      Nice Presentation.
      1. What do we do for Transaction data?
      2. Also what will happen when we display both key and text?

      Thanks in Advance.

      Author's profile photo Former Member
      Former Member

      Hi ASHUTOSH,

      i am working on a BW 7.4 system an space is not accepted as text, do you have an idea?

      thanks

      Eike

      Author's profile photo Lawrence Chan
      Lawrence Chan

      Thanks for sharing Ashutosh.  I have the same challenge but I'm on BW 7.4 and space is condensed out to be blank when I save.  So now the text for null become space, so now it shows # as both key and text.    Do you have any other option for 7.4?

      Author's profile photo Liza Beckman
      Liza Beckman

      Anyone found a solution to this in 7.4?

      Author's profile photo Lawrence Chan
      Lawrence Chan

      Hi Liza,

      You can use this as workaround:

      (Kudos to Martin Rapp. See http://scn.sap.com/message/16585444#16585444 for details)

      - Enter a non breaking space into the BW master data text, (instead of a normal space) via keys ALT 0160. 

      This works for me on 740.

      Lawrence

      Author's profile photo Liza Beckman
      Liza Beckman

      Thanks Lawrence (and Martin)!!

      Do I hold down the alt key while typing 0160?  It's not entering - am I doing something wrong?  Does this work on a Unicode environment?

      Author's profile photo Lawrence Chan
      Lawrence Chan

      Hi Liza,

      Yes you would keep holding down ALT key while typing 0160. It didn't look like anything has happened until you finished typing and let go of ALT, then you will see an extra "space" appeared (and then you can save the "fake" text and show then show text only on report).

      Yes it works on unicode environment as mine is.

      Lawrence

      Author's profile photo Liza Beckman
      Liza Beckman

      I couldn't use the shortcut Alt+0160, I think because of a missing registry entry, but I can't adjust that.  Here's an alternative for anyone that may find this and have the same situation:

      Go to start->search->character map

      Check the box that says advanced view

      Enter code 00A0 (in go to code box)

      Click select then copy

      You can then paste the value wherever you need it

      Author's profile photo Lawrence Chan
      Lawrence Chan

      Nice alternative. thanks for sharing

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Wow never thought of this workaroud . We recently upgraded to 7.4 . I will try to use this now.

      If it works i will edit this document to mention this for 7.4.

      Thanks

      Author's profile photo Former Member
      Former Member

      Good one....

      What about the scenario  like if we do not have Text enabled on Infoobject and it exists just as a Key like Some reference number, Name, Address or so. Any ideas?

      Cheers

      Author's profile photo VIJAYA SIMHA CHINTARLAPALLI REDDY
      VIJAYA SIMHA CHINTARLAPALLI REDDY

      Hello Nice document.But I am facing a issue like when ever i try to display a value with the text it shows me only hash value nothing else.