BW number field in WEBi as string

[Former Member]

At my client site I am having some issues with integrating WEBi with a BW datasource.  We are using BI4 SP4 on top of BI 7.02. 

Essentially I have a BW field which is a number, calculated using the % variance function in BEx.  Within WEBi query panel when I am building my WEBi query the field is pulled through as a String.

Does anyone have any thoughts / tips / tricks that could help me to understand why this number is pulling through as a string?

[IngoH · Accepted solution]

Hello Josh,

in case your underlying BEx Query contains 2 Structures and calculations using the Cell Editor then there can be a situation where you are mixing the data type for a single cell.

For example:

                    Column 1          Column 2

Row 1          Actual Revenue     Actual Revenue

Row 2          Budget               Budget

Row 3          %                         %

In the example above the "%" row is a different data type compared to the revenue.

Web Intelligence needs to assign a unique data type for the column, which in the given example is not possible.

now there are some simple options:

- instead of using the % share calculation you could do "A / B" - which is a number

- you could do a "*1" for the rows 3 calculation, which puts the data type to number.

using those two options puts the last item into a number format and you then can format it accordingly as a percent in the report.

hope that helps

Ingo

[Henry_Banks · Accepted solution]

Nice one Ingo, you nailed that one!

[Former Member · Accepted solution]

Hi Ingo

Thank you for your input.

I assume both options you present are to be done in the BW query?

Now the additional complication I need to throw into the mix is that the users will be running the BW queries as well as viewing the WEBi report.

Within the BW queries they will also need to see a % sign against the number.

Using both options you gave means we will be unable to do this.  Would I be right in thinking that we can not have the BW query showing the % sign AND have WEBi pick this value up as a number?

Thanks

Josh

[IngoH · Accepted solution]

Hello Josh,

you assumption is correct. Both of the options I mentioned above result in the actual calculation becoming a number - not a percentage.

So if you wanted to have both you would have to create 2 BEx queries.

please remember that Web Intelligence will always have limitations in those areas and that you are better off with the Analysis products.

regards

Ingo Hilgefort

[Henry_Banks]

Hi,

Are you using a BICS olap connection, or a OLAP UNV universe?

the BICS limitation note is 1611185, whereas the MDX limitation note is 820925. The reason why i mention this is that there are resitrictions around the consumption of certain calculation functions.

There is a draft note 1841862 which says that Percentage Share function(%A)  isn't supported in webi - for the reasons you describe - because when populating both a percent icon and number in a column then it will give a String.

Regards,

H

[Former Member]

Hi Henry

Thanks for the prompt response.

We are using the BICs connection.  I have reviewed note 1611185, which was useful thank you.

The rationale for your draft note makes perfect sense however I seem to see otherwise. 

My query has 9 % rows using either percentage share (%A) or percentage variance (%). 

Out of those 9 rows I have 4 which are showing in WEBi as a number.  Those 4 all use the percentage share function. All pull through to WEBi without the % sign but all show in BW with the % sign.

The remaining 5 rows for the most part use the percentage variance function although 1 does use the percentage share.

In my example it appears to be only those rows that have cell formulas / selections which are pulling through as text whereas those that are using the percentage function with no cells are pulling through correctly as a number.

So my question then becomes - why would having cells defined in the BW query make a difference? Shouldn't WEBi just pick up the numbers that are presented?

Thanks

Josh