Rank Formula for Manual Crosstab

[oanion]

Agency Liq Rank
CREDITORS INTERCHANGE INC

1ST UNION MASTERCARD 1.34% 0
1ST UNION VISA 0.00% 0

ARBOR 1.17% 0
ARBOR MASTERCARD 1.84% 0
ARBOR VISA 0.56% 0
BANK OF AMERICA 0.85% 0
BANK PLUS 9.42% 0
BANKPLUS 0.00% 0
CHASE MANHATTAN BANK 0.84% 0
FIRST UNION 0.85% 0
HOUSEHOLD 1.05% 0
HOUSEHOLD MASTERCARD 0.77% 0
MERCURY 0.00% 0
MERCURY VISA 2.23% 0
METRIS 0.00% 0
ORCHARD 0.00% 0
ORCHARD DIRECT MAIL MC 1.10% 0
ORCHARD DIRECT MAIL VISA 2.00% 0
ORCHARD MASTERCARD 1.34% 0
ORCHARD VISA 1.67% 0
STUTSMAN 0.66% 0
UICI 0.00% 0

Facts:

1. There are 2 groups. The 1st group is the name of the agency (Creditors Interchange Inc). The 2nd group are the product names (1st Union Mastercard, 1st Union Visa, etc)

2. The formula for Liq Rate is calculated from the groups with the following forumula:

if month({vwBatchTrackMaster.PlacedDate}) = 2 then
(Sum ({vwBatchTrackMaster.TotalPaid}, {vwBatchTrackMaster.Product})/Sum ({vwBatchTrackMaster.PlacedBalance}, {vwBatchTrackMaster.Product}))*100

3. The above mentioned formula is re-created for each month 1 for Jan, 2 for Feb, etc.


4. I’m trying to create a formula for the rank column (last column, showing all zeros) that will look at the liq rate column and calculate the rank without having to sort. The product column will stay in the same order but show the ranking number. For instance, Bank Plus rank = 1, Mercury Visa = 2, etc.

Any Suggestions?

 

[lbass]

I'm assuming you are creating a rank for each month. To do this, you will need to insert a second group header section GH#2_b where you will place a subreport. In the subreport, create two SQL expressions:

[{%TotalPd-Jan}]:
(select sum(AKA.`TotalPaid`) from vwBatchTrackMaster AKA where
AKA.`Product` = vwBatchTrackMaster.`Product` and
AKA.`Agency` = vwBatchTrackMaster.`Agency` and
{fn Month(AKA.`PlacedDate`)} = 1 and
{fn Year(AKA.`PlacedDate`}} = 2005)

[{%PlacedBal-Jan}]:
(select sum(AKA.`PlacedBalance`) from vwBatchTrackMaster AKA where
AKA.`Product` = vwBatchTrackMaster.`Product` and
AKA.`Agency` = vwBatchTrackMaster.`Agency` and
{fn Month(AKA.`PlacedDate`)} = 1 and
{fn Year(AKA.`PlacedDate`}} = 2005)

Then create a formula {@JanLiq}:

if {%PlacedBal-Jan} <> 0 then
{%TotalPd-Jan} % {%PlacedBal-Jan}

Insert groups on agency and product. Place {@JanLiq} in the subreport detail section and insert a maximum on it at the product level. Then go to report->topN/groupsort->product tab and choose "maximum of @JanLiq} as your summary, Descending order. Then insert a running total that uses a distinctcount of product, evaluate for each record, reset on change of group (agency). Place this in the group header of footer. Suppress all subreport sections except this group section and all fields except the running total field. Link the subreport to the main report on the agency (but NOT on the product). Place the subreport in GH#2b. This will now show ranks for all products for each product.

Next, in the main report, create a formula and place it in GH#2a:

whileprintingrecords;
shared stringvar product := {vwBatchTrackMaster.Product};

Then in the subreport, go to the section expert->group section containing the running total->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar product;

{vwBatchTrackMaster.Product} <> product

This should suppress all but the rank appropriate to the product in the main report. Finally, in the main report, go to the section expert->GH#2a->check "Underlay following sections" in order to align the rank with the product.

This works in 8.0. It's possible that higher versions provide a way of doing group sorts on formulas like yours without using SQL expressions.

-LB