Sorting Groups on a Formula 3

[pamgdevlin]

Help!!!

I have created a report that sums the sales for 7 regions for the months of July 04 and July 03. I then created a formula to show the percentage of July 04 sales vs the July 03 sales and I want the regions to sort in descending order based on that formula. However, when I try to do a TopN sort, the formula field is not available. Any suggestions would be greatly appreciated as I am going nuts with this one. Also, if anyone has suggestions on ranking once the percentages are sorted, that would be helpful.

jul04 jul03 % to prior yr
Region 1 9,492 508,030 1.9%
Region 2 25,570 415,645 6.2%
Region 3 13,980 234,450 6.0%
Region 4 2,750 183,895 1.5%
Region 5 1,900 144,610 1.3%
Region 6 1,923 132,299 1.5%
Region 7 16,440 157,625 10.4%

Tricia D

 

[lbass]

You didn't say what CR version you are using or the type of database, but if you have the options of creating SQL expressions (in CR 8.0, this is an option in the field explorer), you can do something like the following, although I think syntax can vary depending upon your database:

Create a SQL expression {%Jul04}:

(select sum(AKA.`SalesAmount`) from table AKA where AKA.`RegionID` = table.`RegionID` and
{fn YEAR(AKA.`SalesDate`)} = 2004 and {fn MONTH(AKA.`SalesDate`)} = 7)

Repeat to create a second SQL expression {%Jul03}, just changing the year number to 2003. In each case replace "table" with your table name, and replace `SalesAmount` and `SalesDate` with the correct field names. "AKA" should be left as is, as it represents an alias table name just for the purposes of the expression.

Next go to the formula editor and create {@percent}:

if isnull({%Jul04}) or
isnull({%Jul03}) then 0 else
{%Jul04}/{%Jul03}*100

Even though this is a summary formula (it is dividing sums), place it in the detail section, right click on it and insert a summary (Maximum) on it at the group level. You will notice that the value of {@percent} is the same in the detail and group section, but because the summaries contributing to it have been created in the SQL expression, they behave as if they are hard coded--and therefore are available to summarize. And, because you CAN insert a maximum on the formula (even though it gives you the same value as the formula alone), you can now choose "Maximum of {@percent}" for a topN descending sort.

For the ranking, it depends on how you want to do it. You could just add the "Special Field" groupnumber to the group header. However, if you have some values that are the same, you might want ranks shared across groups. If that is the case, first make sure that your summaries are in the group header, not the footer, so that the following formula works correctly:

//{@rank}:
whileprintingrecords;
numbervar counter;

if onfirstrecord or
totext({@percent},4) <> totext(previous({@percent}),4) then
counter := counter + 1 else
if isnull({@percent}) or
totext({@percent},4) = totext(previous({@percent}),4) then
counter := counter;

I used the totext() function here to control the degree of equivalence (here set at 4 decimals). This will give you results like:

97.03% 1
56.88% 2
56.88% 2
42.01% 3 //Or would you want this one to show "4", with "3" skipped?

-LB

 

[pamgdevlin]

Thanks lbass, the ranking formula was especially helpful!!

In response to your question...I do need the rank to skip to the next number if there are two of the same rank; what should I do to accomodate that change?

 

[lbass]

Try:

//{@rank}:
whileprintingrecords;
numbervar counter;

if onfirstrecord or
totext({@percent},4) <> totext(previous({@percent}),4) then
counter := groupnumber else
if isnull({@percent}) or
totext({@percent},4) = totext(previous({@percent}),4) then
counter := counter;

-LB

 

[pamgdevlin]

lbass,

Below is the original formula you gave me for the rankings...works great except I need the count to skip when 2 percentiles are the same:

98.1% 1
97.3% 2
97.3% 2
95.0% 4

I tried the second formula you gave me changing line 3 to read counter := groupnumber
which didn't work. I've tried several different things but I can't get it to skip to the next number. Anything you can suggest would be helpful!!!

Thanks
___________________________________________________

whileprintingrecords;
numbervar counter;

1 if onfirstrecord or
2 totext({@PERC_TO_PRIOR},1) <> totext(previous(@PERC_TO_PRIOR}),1)then
3 counter := counter + 1
4 else
5 if isnull({@PERC_TO_PRIOR}) or
6 totext({@PERC_TO_PRIOR},1) = totext(previous({@PERC_TO_PRIOR}),1) then
7 counter := counter;

 

[lbass]

The formula I gave you worked when I tested it. It gave tied numbers the same rank and then skipped the next number. Instead of saying that something doesn't work, you should explain what error you got, or how the results were not what you expected.

Do you have more than one group on your report? That could be the problem. If this is true, please describe the group structure.

-LB

 

[pamgdevlin]

lbass,

Sorry, I'm new to all of this...however, you are an inspiration!!! As I was reading your last post, it dawned on me that I needed to change the line to read

counter := recordnumber

and it worked.

Thanks so much for your advice and patience.

 

[CuteGeek]

Hey Everyone,

This post help me to resolve my issue...Love this site...

Thanks
CuteGeek