cancel
Showing results for 
Search instead for 
Did you mean: 

Calculation of Result Row

Former Member
0 Kudos

HI All,

We are in the process of building a Report. We have three Key Figures - KF1, KF2 and KF3. There is an requirement where we have to create a formula (KF4). Formula is as below:

KF4 = 100 + ((KF2 - KF1) / KF1) * 100

But the client needs the Result row for KF4 to be calculated differently. Below is the formula:

KF4(Result Row) = Sum of All records( KF2 * KF3) / Sum of all records (KF3)

Please suggest if we can calculate the Result row differently as above.

Regards,

Ranjan

Accepted Solutions (1)

Accepted Solutions (1)

former_member185132
Active Contributor
0 Kudos

Hi,

Use the LEAF function. This is a function in BEx that returns 1 on individual (detail) rows and 0 on result rows. Using this, you can implement the logic you want.

From a birds-eye view, your formula for KF4 will look like this:

( ( LEAF( any_KF ) == 1 ) * (Formula_for_detail_rows) ) + ( ( LEAF( any_KF ) == 0 ) * (Formula_for_result_rows) )

Do bear in mind that LEAF works only if your output is in Analyser or WAD. Leaf does not work if you have to expose the query for OLE DB access.

Regards,

Suhas

Former Member
0 Kudos

Hi Suhas,

Thanks for your quick reply, We tried above logic and the Detail rows are populating correctly

but we are not able to populate the Result row and it is coming as Blank.

Our understanding from your second half of the formula ( ( LEAF( any_KF ) == 0 ) * (Formula_for_result_rows) )  is that  LEAF( any_KF ) == 0 will result in 1 for the Result row.

Is it because there will be no Key figure value in the Result row?

We would also like to know if the summation of the Key Figure in the Result row is causing an issue?

Please suggest.

Regards,

Ranjan

former_member185132
Active Contributor
0 Kudos

Hi,

Ranjan Thakur wrote:

Our understanding from your second half of the formula ( ( LEAF( any_KF ) == 0 ) * (Formula_for_result_rows) )  is that  LEAF( any_KF ) == 0 will result in 1 for the Result row.

Is it because there will be no Key figure value in the Result row?

No, this is because of the behaviour of LEAF. LEAF( any_KF) should have a value of 0 on the Result row, doesn't matter which KF you choose. You can test it if you want, by using just the leaf formula in a test query.

You're right, the summation property of the formula is probably causing the problem. In the formula, in the calculation tab, for the Calculate results as, select "Nothing defined".

Regards,

Suhas

Former Member
0 Kudos

I think this is not real time requirement cause client never ask go for like this and for these what is the use????

Thanks & regards

Jagadeesh Naidu R

Former Member
0 Kudos

Hi Suhas,

Thanks alot for quick and useful response. Everthing is working fine. But we are facing one another  issue. With using your second half of the formula ( ( LEAF( any_KF ) == 0 ) * (Formula_for_result_rows) ) we have to Populate the OverAll Result Row of a Formula used in our report. I am unable to achieve that.

Please suggest.

Regards,

Ranjan

Answers (0)