Why can't I group using this formula field?

[skystar70]

Hi. I feel like I've asked a similar question before, but here goes. I am on version 9.

I am trying to group my report by the @Percent Variance formula and the formula is not available as a group. I am trying to group it because I actually want to sort by it, and I can't because I have another group that is listing and summing the records and this sort needs to before before that group. Not sure if that makes sense.

Here is the formula that is not showing up as an option to group by:
if not ({@Baseline- Expense (No AL) Sum} = 0) then
({@Variance: Baseline- Current Spend} / {@Baseline- Expense (No AL) Sum}) * 100
else 0

Since this formula references two other formulas, I will list them as well:

@Baseline- Expense (No AL) Sum:
Sum ({@Baseline - Expense (No AL)}, {planning_entity_7.short_name})

@Variance: Baseline- Current Spend:
({@Baseline- Expense (No AL) Sum}) - (Sum ({@Spend - Target or Budget (No AL)}, {planning_entity_7.short_name}))

Now both of these formulas reference even more formulas:

@Baseline - Expense (No AL):
if {vw_ace_rpt_budget_data.version_code} = "207706" 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


@Spend - Target or Budget (No AL):
if {vw_ACE_Work_Data.ace_execution_stage} in ["Executing", "Deploying", "On Hold", "Complete"] then
(if {vw_ace_rpt_budget_data.version_code} = "207707" and
{vw_ace_rpt_budget_data.cost_father_code} in ["192","207588"]then
{vw_ace_rpt_budget_data.value})
else if
{vw_ACE_Work_Data.ace_execution_stage} in ["Planning", "Initiating"] and {@Group Budgeted/Unbudgeted}
in ["In 2010 Budget"] then
(if {vw_ace_rpt_budget_data.version_code} = "207709" and
{vw_ace_rpt_budget_data.cost_father_code} in ["192","207588"]then
{vw_ace_rpt_budget_data.value})
else if
{vw_ACE_Work_Data.ace_execution_stage} in ["Planning", "Initiating"] and {@Group Budgeted/Unbudgeted}
in ["Unbudgeted"] then
(if {vw_ace_rpt_budget_data.version_code} = "207707" and
{vw_ace_rpt_budget_data.cost_father_code} in ["192","207588"]then
{vw_ace_rpt_budget_data.value})
else 0

Any insight that can be provided is great. Again, I really want to sort by the this formula field, but so far, I can't do sorting or grouping. Thanks!

Skystar

 

[lbass]

You can't group on formulas that contain summaries, and you can't do group sorts on formulas that do calculations using summaries. You could try to create the summaries using SQL expressions, but this looks complicated with all of your nested formulas. You are not even showing the contents of all of them.

-LB

 

[skystar70]

Sorry. I may have missed one of the formulas. I was trying to include them all. Thanks for your comments. I guess this is beyond my level of experience.