Alright, if you have ever converted a report6i to a data template in bi publisher using the api's or by hand you will know one thing, removal of the integration of summary columns and formula columns or vice versa in lower groups is a total "beach."
I said it, "beach." There is a sneaky workaround, not pleasant but doable. When your report gets converted you might see something like this:
Functional flow in reports6i:
[First Group (CF_TOTAL_AMOUNT combines summaries columns)]-->[First Group has summary columns (:cs_detail_amount, :cs_detail_state, :cs_detail_federal)]
SQL Example:
select
cf_total_amount(:cs_detail_amount, :cs_detail_state, :cs_detail_federal
...
...
from ap_invoices
where blah, blah
The basic probably we have here is that cs fields are in the same group, but when the query runs the summed amounts haven't taken place, nor will they, a classic paradox. You will get nothing for the cf_total_amount. Quite the conundrum....or is it.
I guess, it;s time to pull this calculated formula out and give up. The business your working for will totally understand that there are limitations to technology. Alright, back to reality, let's make this puppy work.
Solution remove the functions and create something like this for a sqlQuery and new group :
select
cf_total_amount(:cs_detail_amount, :cs_detail_state, :cs_detail_federal
...
...
from dual
Organize your data template to do the summing below all of the other current group(s). Follow up with the new group with the calculated formula's select from dual with the summary columns. Here's the logic breakdown:
Order of execution:
1. [First Group (get all the fields that will be summed)]
2. [Summary Elements (summary columns here)]
3. [Second Group (select calculated formula(s) from dual with summary column values)]
Why:
Fields that are going to be summed must be higher than the summary columns. It's simple, we select the calculated formula's from dual with the summary bind variables.
This approach should work for those of you that are aspiring new bip developers that are converting reports6i to data templates. You will find that people believe or not will have functions calling summary columns in the same group. This example should help with other "minor" limitations with bip with summary columns and calculated formulas.
