How to Derive Status Column in BEx Designer (WITHOUT Virtual Characteristics/Modifying Data-model)

[ccc_ccc]

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	24.90 %
CANADA	C = Bad	1.33 %
CHINA	B = Better	17.34 %
GERMANY	C = Bad	1.33 %
INDIA	B = Better	16.20 %
JAPAN	C = Bad	1.34 %
N KOREA	C = Bad	1.36 %
S KOREA	B = Better	15.96 %
SINGAPORE	B = Better	17.34 %
UK	C = Bad	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.

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

COLUMNS:

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

Now, go to “Cell” Editor TAB

As shown below screenshot

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”

My logic as like below

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

Report Output

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:

  Select Report Properties and select options as like below screenshot

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

This Solution will work for similar scenarios as like below

Thanks for reading this Blog.

Regards,

Nanda