Hierarchy with CEIL function in BEx

[former_member543749]

Hi Experts,

I facing problem while using CEIL function with hierarchies in BEx query.

Example records

org unit     employee   no.of.hours(calculated kf with ceil function)

org1           emp1          2.3

org1           emp2          2.4

Here org unit is hierarchy characteristic and it is hierarchy enabled in query.

when we expand the hierarchy in output of the report it showing total value as 6 (ceil(2.3) + ceil(2.3) = 3 + 3 = 6)

But we I collapse the hierarchy it showing output as 5 ( ceil(2.3 + 2.3) = ceil(4.6) = 5).

It is giving problem for users with high count of hours mismatching under some org units.

can you please provide me the solution which  give 6 as output in both  expand and collapse actions.

Please provide solution at BEx level(requirement need to be  implement in BEx query only).

Thanks in advance

[anindya_bose · Accepted solution]

Hi  Rajani

You can try creating CKF instead of local formula.  There is a check box for "Calculate After Aggregation "  by default in Aggregation tab .  Just try to un-check that and see if that resolve your issue .

You can also try to do an exception aggregation and see if that solves the problem.

PS: Right now could not try myself as I need to prepare similar data, so could not confirm if that would work .

Regards

Anindya

[former_member543749 · Accepted solution]

Hi Anindya,

  Thank you very much your quick response.

Its working fine in BEx. But waiting for the confirmation from business as it is working as they expecting.

Thanks again

Rajani.