Hi. Complex situation here (for me anyway). 
I have a report of project names and costs for each project, broken out by expense total and capital total, then project total. I need to sort the report by project total high to low.
Here is why it is so complex: The records are summed at a group level because there are multiple records for each project where costs are concerned.
To complicate matters furhter, there are many different fields, from 2 different tables that are summed to get the expense total and same with the capital total and of course, project total.
When I try to create a group on the summed project total, that formula/field is not available. The only fields that come close to what I need are the sums of each table fields, which only gives me part of the picture. I end up with odd records that are not sorted properly, because their data comes from the OTHER table that is not grouped/sorted.
So, here is the formula that I want to sort on:
{@OL Budget Screen} + {@Hdwe Budget Screen} + {@Sftw Budget Screen} +
{@Telecom Budget Screen} + {@Travel Budget Screen} +
{@Train Budget Screen} + {@Other Budget Screen} + {@Cap Budget Screen} +
Sum ({@All Expense Target}, {planning_entity_7.short_name}) +
Sum ({@Capital Target}, {planning_entity_7.short_name})
Here is the @OL Budget Screen formula:
if isnull({custom_data.tenestol}) then 0
else {custom_data.tenestol}
The next 7 formulas are the same idea as this one. (Anything with "Budget Screen" in the total.)
The @All Expense Target formula (which is summed):
if {vw_ace_rpt_budget_data.period_finish_yearmo} startswith "2010" and
{vw_ace_rpt_budget_data.version_code} = "207707" and
{vw_ace_rpt_budget_data.cost_father_code} in ["192","207588"] and
not isnull ({vw_ace_rpt_budget_data.value}) then
{vw_ace_rpt_budget_data.value} else 0
@Capital Target is similar.
I'm not sure if I've explained this adequately or left out any vital information. Any help you can provide would be great.
Thanks, Skystar
I have a report of project names and costs for each project, broken out by expense total and capital total, then project total. I need to sort the report by project total high to low.
Here is why it is so complex: The records are summed at a group level because there are multiple records for each project where costs are concerned.
To complicate matters furhter, there are many different fields, from 2 different tables that are summed to get the expense total and same with the capital total and of course, project total.
When I try to create a group on the summed project total, that formula/field is not available. The only fields that come close to what I need are the sums of each table fields, which only gives me part of the picture. I end up with odd records that are not sorted properly, because their data comes from the OTHER table that is not grouped/sorted.
So, here is the formula that I want to sort on:
{@OL Budget Screen} + {@Hdwe Budget Screen} + {@Sftw Budget Screen} +
{@Telecom Budget Screen} + {@Travel Budget Screen} +
{@Train Budget Screen} + {@Other Budget Screen} + {@Cap Budget Screen} +
Sum ({@All Expense Target}, {planning_entity_7.short_name}) +
Sum ({@Capital Target}, {planning_entity_7.short_name})
Here is the @OL Budget Screen formula:
if isnull({custom_data.tenestol}) then 0
else {custom_data.tenestol}
The next 7 formulas are the same idea as this one. (Anything with "Budget Screen" in the total.)
The @All Expense Target formula (which is summed):
if {vw_ace_rpt_budget_data.period_finish_yearmo} startswith "2010" and
{vw_ace_rpt_budget_data.version_code} = "207707" and
{vw_ace_rpt_budget_data.cost_father_code} in ["192","207588"] and
not isnull ({vw_ace_rpt_budget_data.value}) then
{vw_ace_rpt_budget_data.value} else 0
@Capital Target is similar.
I'm not sure if I've explained this adequately or left out any vital information. Any help you can provide would be great.
Thanks, Skystar