on 07-09-2012 3:45 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.