Ranking Formula 3

[oanion]

Good Afternoon All,

I'm trying to create a formula that will rank records based on one field, however, I'm not sure how to get started. I did a little research and found out that rank is a function that can be used if there was an OLAP database, however, I using ODBC to connect to a SQL Server 2000 database. For instace, if I have the following database records:

Client Payments Ranking
001 $7,881,370.46 2
003 $1,190.00 5
004 $56,698,177.44 1
005 $3,944,003.27 3
006 $341,016.69 4

I want the records to be soreted in the order of the client, however, I want the ranking to be calculated based on the amount of the payment WITHOUT sorting the clients. Please remember that the number records can easily go over 500. Therefore, I wouldn't be able to do a simple if statement. I know I need a loop, however, I'm not sure how to develop it.

If you have any ideas. PLEASE HELP!!! or point me in the right direction

 

[UHsoccer]

Have you looked under menu Report / "Group Sort Expert". It wil allow you to sort the group as it displays.

In the menu, try the drop down "All" and fill in the fields as needed

 

[oanion]

I'm sorry, I forgot to mention that the Payments column is NOT a database field, it is a calculated field that is generated from 2 fields in the database. When I try to pull up the "Group Sort Expert" it will not allow me to choose a calculated field. It only gives database fields to choose from.

 

[lbass]

Please provide the formula for the calculated field.

-LB

 

[oanion]

In the database, I actually have a field called TotalPaid. The calculated field, "Payments" is the result of the sum of the "TotalPaid" field which is grouped by the "Client" Field.

Thanks

 

[dgillz]

Look under the TopN/Sort group Expert and you can sort by groups.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[oanion]

DGILLZ,

Thank you for your repsonse

However, I'm not trying to sort, I'm trying to populate the RANK field based on the amount of Payments for a particular Client. The ranking field I've listed above is empty now, I need it populate it with the ranking numbers. I just filled in the numbers above to give an idea of what I'm trying to accomplish.

Thanks

 

[synapsevampire]

Since you do not want the rows sorted according to the Top N, I fear you may need another process such as a correlated subquery or as a last resort, a subreport to return each ranking using a shared variable being passed over and back to get obtain the ranking. Th downside is that you'd have to run the subreport for everyone each time, slow and ugly.

I think that you could create a TOP N View in SQL Server and return the rownumber (can't recall the syntax in SQL Server) as your ranking and join that View to your data.

-k

 

[bdreed35]

This can turn into a lengthy formula, but you have a 100 clients or less in your result set, you can use the NthLargest:

if nthlargest(1,{table.payments}) then "1"
else if nthlargest(2,{table.payments}) then "2"
else if nthlargest(3,{table.payments}) then "3"
else if nthlargest(4,{table.payments}) then "4"
else if nthlargest(5,{table.payments}) then "5"
.
.
.
else if nthlargest(100,{table.payments}) then "100"
else "> 100"

You need to fill in the rest. It is a little bulky, but it will work.

~Brian

 

[lbass]

The other approach you can use, since you have so many clients, is to insert a subreport. In the subreport, use the same record selection criteria as the main report. Group on {table.client} and insert a sum on {table.totalpaid}. Then, still in the subreport, go to report->topN/group sort and choose "sum of {table.totalpaid}". Add the special field "groupnumber" to the group footer section (this becomes the rank) and suppress the inserted sum of Total Paid.

In the main report, group on {table.client} as well. Insert the subreport in the group footer. Do NOT link the subreport to the main report (let me know if you have parameters in your record selection formula, because then you will want to create a link on parameters). In the main report, create a formula:

whileprintingrecords;
shared stringvar client := {table.client};

Place this in the group header for client and suppress it.

In the subreport, go to the section expert (format->section)->group footer->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar client;

{table.client} <> client

Suppress all other sections of the subreport except the group footer. This should now give you the correct rank in the main report.

-LB

 

[oanion]

Good Morning Ibass,

First of all, I would like to thank you for your assistance with each of my issues. I have another question regarding this ranking formula. I tried the solution that you suggested above, however, when I run the report, instead of showing only the group number for a particular client, I see all of the group numbers for all clients. It seems that the portion of the section expert forumula:

whileprintingrecords;
shared stringvar client;

{table.client} <> client

is not working properly. Any other ideas?

 

[lbass]

Did you place the following in the group header?

whileprintingrecords;
shared stringvar client := {table.client};

The subreport must be in the group footer (or at least in a section below the one in which you have placed the shared variable formula. And of course make sure that you are entering the section suppression formula in the group footer->suppress->x+2 area of the subreport, while suppressing all other subreport sections.

-LB

 

[oanion]

In the main report, I placed

whileprintingrecords;
shared stringvar client := {table.client};

in a formula and then I placed the forumula in the group header.

I followed the directions exactly how you placed them in the 11/15 posting.

 

[lbass]

I've tested this so know it works. Can you verify that the subreport is in a lower section of the main report than where you have placed the shared variable? You also might check to make sure that you are using "shared" whenever you reference the variable and that the variable name is exactly the same in both places.

-LB