WebI not including null or empty cells in Sum Calculation

[Former Member]

Hi Consultants , I have this issue which I need help with. Below is the description of the issue.

internal hours YTD % =( total internal hours/total hours) * 100

internal hours at months level =( month internal hours/ months hours) *100

For the two company codes B and C , the internal hours YTD% ignores the months that have no value for the hours key figure.

For example for company code B , the total hours YTD is 55 and hence the internal hours YTD%  is going to be 11/55 = 20%. In BEx query this formula works fine but in WebI , the total hours YTD used in the formula is 48 , ignoring the month value for January ( 7 hours) since it has no corresponding internal hours for January for this company code. Hence the internal hours YTD% is 23% = 11/48 which is wrong.

I have tried including the BEx query formula object that works well in BO but I do get #unavailable error because that measure is database delegated.

Although this BO report is based on multiple queries, I have combined both key figures or measures in the same BEx query but still not getting the right results in BO. For BEx query it works fine but not in BO. It still ignores month that are null for the denominator sum total.

This is a cross-tab report in WebI ( BI 4.0 SP6)

Thanks for any idea that comes up.

Afotey.

[Former Member · Accepted solution]

Issue resolved! There was a ranking function on the measures that was slashing off the Total hours for the corresponding internal  hours with null values.

The ranking function was used to eliminate rows with empty measures to enable the table sorting work well.

I removed the ranking function and uncheck show rows with empty measures in the Format Table properties of the cross tab and everything looks good.

Very simple but was a pain!

[Former Member]

try creating it at the report level manually:

Var sum_year=sum(hours)

then calculate the % manually:sum(internal hours)/sum(hours)*100

OR TRY TO POPULATE THE MISSING VALUE IN INTERNAL HOURS TABLE:

IF (ISNULL(INTERNAL HOUR) THEN 0 ELSE [INTERNAL HOUR]. Then calculate the %

[Former Member]

Avinash -  Thanks for the response.

I have tried the variable formula and creating the % manually but that do not resolve the issue. I have tried in forcing 0 both on the BW side and BO side but issue still persist.