RANKING By a Formula Field instead of Table Field!!!

[oanion]

I'm having another ranking issue, expect this time, I'm trying to rank on a formula field instead of a table field. Here are the facts:

Database = SQL Server 2000
Crystal Reports version 10

The report uses one parameter, which is the numeric representation of the year.

The report is grouped on a client name

The Liq Rate formula based on two fields in a database table: TotalPaid/BalancePlaced.

The report is can be considered a manual crosstab because I had to create an individual forumula to calculate the LiqRate for Each month based on the grouping. For example, the syntax in Crystal to calculate the Liq rate for January is:

if month({vwBatchTrackMaster.PlacedDate}) = 1 then
(Sum ({vwBatchTrackMaster.TotalPaid}, {vwBatchTrackMaster.client})/Sum ({vwBatchTrackMaster.BalancePlaced}, {vwBatchTrackMaster.client}))*100

I have the exact formula for each month in the year.

I'm trying to create a report that will show the LiqRates for each month, AND calculate the LiqRate separately for each month. As I have 12 LiqRate formulas, (one for each month), I know I will have to have 12 Ranking Formulas. The format of the report is as follows:


JANUARY FEBRUARY
LiqRate Ranking LiqRate Ranking
client1 10.5% 4 27.3% 3
client2 7.2% 5 17.4% 4
client3 1.6% 6 52.3% 1
client4 15.5% 3 6.5% 6
client5 77.4% 1 45.2% 2
client6 51.7% 2 7.6% 5

I'm displaying only 2 months as an example, however, all 12 months need to be calculated.

Help from anyone would be greatly appreciated.,

 

[lbass]

Were you able to use my solution in thread149-1031740? If so, it can be adapted for this situation. You would need separate subreports for each month for the rank, and you would need to adapt the SQL expression in each case to reflect the month under consideration.

I just noticed that you are using CR 10. You might have to create the SQL expression directly as a subquery using the "add command" feature as a datasource for each subreport for it to work.

-LB

 

[oanion]

Ibass,

I tried your approach from thread149-1031740?, however, the SQL Expressions would not compile. I'm not sure of the syntax I need in CR 10 that will give me the equivalent to your version 8.0. Additionally, I did not follow the AKA verbage that you used. I'm just totally lost for that manner. One more thing, this report does call for a parameter for the year. I not sure how this would fit the SQL Expression.

 

[lbass]

I think there is a way of handling the parameter issue. I don't really think there is a way of doing the ranking without using the subreport/SQL expression method when you are working with summaries, other than directly creating the summaries in the SQL query itself by using the Add Command feature. I'd be interested if you found another way.

In a few days I'll have my CR 11.0, and can perhaps respond again with further help if no one else jumps in before then.

-LB

 

[oanion]

Thanks Ibass, I appreciate all of your efforts. If you can, please repond again after you receive CR11. I've been trying to conquer this issue for a while with little success.

 

[lbass]

You should follow my extended suggestion in thread149-1031740, but instead of using the SQL expression editor, use "Add Command" as your datasource. What you might want to do is go into your current report and copy the query from "Show SQL Query" and then paste it into the add command area in a new report. Then add the following subqueries to the Select part of the query:

[the following will result in an expression in the field list that represents {%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`)} = {fn Year(vwBatchTrackMaster.`PlacedDate`)})

[the following will result in an expression in the field list that represents {%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`)} = {fn Year(vwBatchTrackMaster.`PlacedDate`)})

Be sure that the expressions are enclosed in parens as shown, and add a comma after each one if there are additional fields following. These fields will receive automatic names like "Expression1001" and "Expression1002" in the field explorer, which you will then use to create the formula {@JanLiq}:

if {Expression1002} <> 0 then
{Expression1001} % {Expression1002}

You would need to add two expressions to the SQL query for each month, and then create 12 formulas like the above. You might want to test this out with one month to see how it works. Now you should be able to do the necessary sorts on the percentages in order to create the ranks as outlined in the other thread.

You will use the year parameter to limit records in the report by adding something like the following to your record selection formula:

{vwBatchTrackMaster.PlacedDate}>= date({?year},01,01) and
{vwBatchTrackMaster.PlacedDate} <= date({?year},12,31)

This will automatically limit the expressions returned to those that fall within that year.

Finally, you are using "client name" in this thread, while in the other thread you were referring to agency and product. If this is a different report, with different groupings, the expressions will have to be adjusted.

-LB