Blog Overview

Issue

In Past few years SAP BEx designer changed/improved and added few options, however never added an option like deriving characters/strings/flags or status based on business logic ( like Completed, Open, In-Progress, True Or False etc) to derive such values in BEx designer is very difficult and in most of the times it’s not possible also. Apart from this we have few alternatives to bring characters/strings in BEx Analyzer like VB Macros, HTML Script, Java Script, BEx Virtual characteristics or adjusting data targets by adding infoobjects and writing ABAP program to populate desired values as per the business requirement.


Alternatives

  1. Virtual Character
  2. Adjusting Datamodel (modifying data target by adding extra infoobjets)
  3. VB Macros

Pros and Cons:

To implement above three alternatives we  require extra maintenance/time and may lead to performance issues while ET&L and Analyzing data and also in few scenarios full data drop & reload is required.

Workaround

Let’s take below example

Table

   Sales Region

   Status

   Sales in %

   AUSTRALIA

A = Best

  1. 24.90 %

   CANADA

C = Bad

  1. 1.33 %

   CHINA

B = Better

  1. 17.34 %

   GERMANY

C = Bad

  1. 1.33 %

  INDIA

B = Better

  1. 16.20 %

   JAPAN

C = Bad

  1. 1.34 %

  N KOREA

C = Bad

  1. 1.36 %

  S KOREA

B = Better

  1. 15.96 %

  SINGAPORE

B = Better

  1. 17.34 %

  UK

C = Bad

  1. 2.50 %

In above table to derive “Status” column it may require virtual characteristics or add infoobject to data target and write ABAP program.

Solution to bring “Status” column in BEx designer without writing ABAP code/Virtual char and modifying data model.

ROWS

Add Sales Region Infoobject and below this infoobject create new structure

As like below.

/wp-content/uploads/2015/06/1_734815.png

Right click on Structure and create 3 selections (Best, Better and Bad)

A = Best

B = Better

C = Bad

Keep the all selections (A, B and C) empty (do not drag & drop any char or key figure) as like below

/wp-content/uploads/2015/06/2_734843.png

COLUMNS:

Add key figure into columns and copy same key figure two times as like below and hide one key figure

/wp-content/uploads/2015/06/3_734845.png

Now, go to “Cell” Editor TAB

/wp-content/uploads/2015/06/4_734846.png

As shown below screenshot

/wp-content/uploads/2015/06/5_734847.png

Repeat same steps remaining B = Better and C = Bad “Cells” as above screen

How to derive “Sales in %”

Go to “Cell” TAB and write logic in “New Formula”

/wp-content/uploads/2015/06/6_734848.png

My logic as like below

/wp-content/uploads/2015/06/7_734849.png

Repeat above step for B = Better and C = Bad , as per your requirement

Report Output

/wp-content/uploads/2015/06/8_734850.png

Changes for Better Look & Feel of Report output

  1. Now in report output CANADA having “Better” sales, so I do not want display remaining status like A = Best and C = Bad

Settings

Select “Structure” and Right side you able to see structure Properties

Select  “Display” TAB and un-check “Structure as Group: Only apply suppression if all elements are 0” as like below

Screenshot:

/wp-content/uploads/2015/06/9_734852.png


  Select Report Properties and select options as like below screenshot

/wp-content/uploads/2015/06/10_734854.png

Now, you can see only “Results” which are required for us.

/wp-content/uploads/2015/06/11_734855.png

This Solution will work for similar scenarios as like below

/wp-content/uploads/2015/06/12_734856.png

Thanks for reading this Blog.

Regards,

Nanda

To report this post you need to login first.

11 Comments

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

  1. LakshmiNarayana Gollamuri

    Hi Nanda,

    I am having similar requirement form the business.

    My report is already existing one , Which contains multiple Columns  and Rows already

    I have a field in Column which gives the days difference from (Self Expiry Date – Billing Date).

    Requirement is  1.Expired Returns (<=0 days)

                            2.Fresh Returns   ([1 – 180])

                            3.Fresh Returns   (>=181)

    I need to display the text against the  Days  as shown in the Below screen.

    Can please advise  

    Please see the below screen shots

    Example

    Image1.jpg

    Image2.jpg

    Thanks in advance

    Narayana

    (0) 

Leave a Reply