Webi: Bex Query with Structure not displayed correct in Webi (through BICS)

[former_member200447]

Hi all,

I have created a BEx query that uses a PL structure to display Revenue, Costs, Margins, EBITA, etc.

I want to display the PL structure in Rows, Fisical Period in Columns with Key Figure Actual.

PL structure	01.2012	02.2012	03.2012	Period .....
Revenue	100	150	...	...
Costs	50	60	...	...
EBIT	10	12	...	...
....

See attached picture (Bex Query) for how query is built up.

When using this query in Webi (through BICS), I'm not able to display the desired results.

The problem is as follows:

First Attemp:

I'm using a Cross Table to display data.

1. Horizontal Axis in Cross table:
   1. Uses the Dimension object of “Fiscal year/period”
2. Vertical Axis in Cross table:
   1. Uses the Dimension object of “PL Structure for Webi” --> This is my Structure in BEx that is displaying the different Reveues, Costs etc
3. Body Axis in Cross table:
   1. Uses the Dimension object of the Measure “Acutal PL”

Result:

Data in Webi report is displayed in a strange format:

• Strange and different format is used for the Measures figures:
  • 3.504454949E7 à Should be 35 044 540 NOK
  • 5485219.86 à is 5 485 220 NOK

See attached picture (Webi - First Attempt) for result in Webi

Second Attempt

I'm using a Cross Table to display data.

1. Horizontal Axis in Cross table:
   1. Uses the Dimension object of “Fiscal year/period”
2. Vertical Axis in Cross table:
   1. Uses the Dimension object of “PL Structure for Webi” --> This is my Structure in BEx that is displaying the different Reveues, Costs etc
3. Body Axis in Cross table:
   1. Uses the Formatted Value of the Measure “Acutal PL”

Result:

The same numbers is displayed for all periods (this is the sum of all periods)

See attached picture (Webi - Second Attempt) for result in Webi

Please also see attached document for screen shot of the results in the Webi report:

"Question regaring BEX Structure in Webi"

Does anybody know why this happens and why the Key Figures from the Query is not displayed correct in Webi?

regards

Oddmar

[Former Member · Accepted solution]

Hi,

what is the InfoObject data type of Actual PL?

regarding your first attempt:

I believe BW is calculating "Actual PL" with high precision, that would explain the scientific notation. In a first step, format this number in Webi to display with less decimals. In a second step after you are happy with the results, concatenate with the currency.

regarding your second attempt:

The formatted value is a Text which will not always aggregate correctly.

Best regards,

Victor

[former_member200447 · Accepted solution]

Hi Victor,

The Infoobject I use in the Query is a 09 Amount Key Figure with Sum Aggregation.

I'm not able to do number formating on the Actual PL Measure in Webi.

When I use the formulas IsNumber it returns 0 and when I use IsString it returns 1.

Apperantly the Actual PL Measure is interpereted as a String in Webi and this must be the reason why it comes out as it does in the report.

Any suggestion on why it is formated as a string in Webi?

regards

Oddmar

[Former Member · Accepted solution]

Hi,

I meant data type (decimal, floating point...), not aggregation type. The behaviour is comming from the characteristics structure, as explained on page 79 of the user guide:

http://help.sap.com/businessobject/product_guides/boexir4/en/xi4sp5_ia_en.pdf#page=79

As a first step, you need to identify the object(s) that has a different type than the others.

Best regards,

Victor

PS: Refer to SAP Note 1740384

[tanisha_gupta20]

Hi Victor,

I am not sure if that will be the case. My webi report is over a BEx query where in there is a structure in the BEx query. And I am getting the exact same issue. In my case if I go to the edit query and look at the measure data type then I find that their data type is string and the aggregation is set to delegated.

I have never come across this issue before.

Oddmar,

How did you manage to resolve this issue?

I have worked with BEx queries with structures  before but never faced this issue. I am using BO 4.1 SP03

Regards,

Tanisha

[former_member4998]

Hi  just try

Selecting Data Type: Numeric

And Function is set to “Database delegated”, change it to “Sum”.

[Former Member]

hi Oddmar

my suggestion wud be"

1> right click on the cell body where you have the keyfigure values..then you will get an option called as "Format Number" ..

2>click on "Format Number" and go to "Number" option and select you type you want to see in your report.

3> Do one more thing right click on the cell and go to "Format Cell" make it "AutoFit Width" and "Autofit Height".

Hope this will make it work...

Cheers

Jammy Z

[former_member200447]

Hi Jammy,

Unfortunatly I don't get that option when I right click on a cell in the Report Body. I only get the option to:

-Format Cell

I guess the reason for this is as I have described above.

My measure is not interperated as a Number but as a String.

So what I'm trying to figure out is why my Key Figure from Bex Query (which is a number in BW) is interperated as a String in Webi.

I guess that is has something to do with that I'm using a Structure in the Bex Query, but not sure why and how this happens.

regards

Oddmar

[Former Member]

hi oddmar

did you tried to convert them in webi using the formula : TONumber..then it might work and did you tried doing it autofit height and width ??

Regards

Jammy Z

[former_member200447]

Hi again,

I tried the function toNumber, but this only gives me a #error in the cells in the report body.

The Autofit Height and Width in "Format Cell" is also on. Not sure how this should have helped with the issue I have in my report.

regards

Oddmar

[Former Member]

Hello Oddmar,

go ahead and copy the query, in this new test, start with only one key figure in the charachteristics structure, you will see that the number is coming as a number then. After this, start adding key figure one after another until you find the one that damages the numbers and turns them to strings.

Im quite confident that this will help you forward. Please post your findings after doing those steps.

Best regards,

Victor