sort report in order of formula results 1

[krysna]

I have a report that is grouped by a field, Fund, and displays a budget vs. historical variance for each fund, colored red, yellow, or green. i.e. if the percentage of the budget that has been spent so far is more than x% different from the percent that has been spent so far in previous years, it shows as red; more than y% off, it shows yellow, and otherwise shows green. I would like to sort the report so that all the red items show up first, followed by the yellow, then the green. I have x and y stored in my db as columns and compare the percent variance formula to that number to figure out what color to show. Does anyone know how I could do the sort (if it can be done)? Thanks for any help!

 

[dgillz]

Sort by the percent variance formula field, descending I would assume. So these would automatically be sorted the way you wish.

If you want some different kind of sort besides ascending or descending, please post again.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[krysna]

The field doesn't show up as a choice in any of the sorting tools. In the Group Sort Expert, it shows 4 summaries that I only have in there to make sure my formulas are doing what they should, but nothing else. The record sort expert shows only the fields in my db tables and the formulas that do not have sum() in them. None of my formulas with sum() in them show up, and the formula that I'm wanting to sort on has a sum() in it. (I'm not planning on showing any of the details; only the group headers with the percent variance for that group.)

Also, forgot to mention, I'm using XI R2, and I'm very much a novice.

 

[dgillz]

Please post the formula you would like to sort by, and any referenced formulas.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[krysna]

Here is the formula I would like to sort by:
{@SumPercentVariance}-{@SumHistPctVariance}

Here are those formulas:
SumPercentVariance:
If Sum ({BudgetRevenue.MonthAmt}, {BudgetRevenue.Element1Number}) =0 then 0
else if
Sum ({BudgetRevenue.AnnualBudget}, {BudgetRevenue.Element1Number}) =0 then 0
else
(Sum ({BudgetRevenue.MonthAmt}, {BudgetRevenue.Element1Number})/Sum ({BudgetRevenue.AnnualBudget}, {BudgetRevenue.Element1Number}))*100

SumHistPctVariance:
If Sum ({BudgetHistoricalRevenue.AveMonthAmt}, {BudgetRevenue.Element1Number}) =0 then 0
else if
Sum ({BudgetHistoricalRevenue.AveAnnualBudget}, {BudgetRevenue.Element1Number}) =0 then 0
else
(Sum ({BudgetHistoricalRevenue.AveMonthAmt}, {BudgetRevenue.Element1Number})/Sum ({BudgetHistoricalRevenue.AveAnnualBudget}, {BudgetRevenue.Element1Number}))*100

I am grouping by {BudgetRevenue.Element1Number}

 

[dgillz]

Here is the formula I would like to sort by:
{@SumPercentVariance}-{@SumHistPctVariance}

And this formula is not available in the top N/group sort expert?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[krysna]

No; the only ones available in that one are
Sum of BudgetRevenue.MonthAmt
Sum of BudgetHistoricalRevenue.AveMonthAmt
Sum of BudgetRevenue.AnnualBudget
Sum of BudgetHistoricalRevenue.AveAnnualBudget

 

[BHolly]

I can't see anything in your formula, but are you possibly declaring any of the items as Shared Variables?? I had the same issue where formula fields were not available, and it was the result of my variable declaration.

 

[lbass]

You can't use a group sort with any formula for which you cannot insert a summary. The only way to do this (at least in versions up thru XI) is to create the summaries in SQL expressions (or you could build your report based on a command and create the summaries within the command), e.g.,

[%MoAmt}:]
(
select (`MonthAmt`)
from BudgetRevenue A where
A.`Element1Number` = BudgetRevenue.`Element1Number`
)

For the historical MoAmt, try:
[%HistMoAmt:]
(
select (`AveMonthAmt`)
from BudgetRevenue A, BudgetHistoricalRevenue B where
A.`Element1Number` = B.`Element1Number` and
A.`Element1Number` = BudgetRevenue.`Element1Number`
)

...assuming that you link the two tables on the elementnumber field. The syntax/punctuation will be specific to your datasource, but check in database->show SQL query if you are unsure about punctuation.

Create the corresponding annual SQL expressions, and then create regular formulas like this:

//{@SumPctVar}:
if {%AnnBudg} > 0 then
{%MoAmt} % {%AnnBudg}

//{@SumHistPctVar}:
if {%AveAnnBudg} > 0 then
{%HistMoAmt} % {%AveAnnBudg}

//{@diff}:
{@SumPctVar}-{@SumHistPctVar}

Place {@diff} in the detail section and insert a maximum on it (this is necessary to enable the group sort. Then go to report->group sort, and you should be able to select {@diff} to sort by.

-LB

 

[krysna]

Thanks, I'll give it a try!