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 !!

To report this post you need to login first.

95 Comments

You must be Logged on to comment or reply to a post.

    1. ASHUTOSH SINGH 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 !!

      (0) 
        1. ASHUTOSH SINGH 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.

          (0) 
        1. ASHUTOSH SINGH 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

          (0) 
    1. ASHUTOSH SINGH 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

      (0) 
    1. ASHUTOSH SINGH 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

      (0) 
    1. ASHUTOSH SINGH 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

      (0) 
  1. Manna Das

    Hello Ashutosh,

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

    Kind Regards

    Manna Das

    (0) 
  2. David Luttrell

    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?

    (0) 
    1. ASHUTOSH SINGH 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

      (0) 
  3. 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

    (0) 
  4. 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

    (0) 
    1. ASHUTOSH SINGH 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

      (0) 
        1. ASHUTOSH SINGH 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 #.

          (0) 
  5. 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.

    (0) 
  6. 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 πŸ™‚

    (0) 
  7. Ankith Jain

    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.

    (0) 
  8. 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?

    (0) 
      1. 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?

        (0) 
        1. 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

          (0) 
          1. 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

            (0) 
            1. ASHUTOSH SINGH 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

              (0) 
  9. S.B. Singh

    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

    (0) 

Leave a Reply