Positoning based on a percentage 1

[Woodyuk]

I have a report, where Im grouping by location. I’m pulling out sales info for one product and then comparing it as a percentage, against another product sales.
I want to do a position for the highest conversion rate. The report looks like this so far:

Location Product1 Product2 Conversion
1 £1,168.62 £34,050.35 3.43%
2 £1,768.31 £43,793.78 4.04%
3 £918.69 £28,721.55 3.20%
4 £1,018.66 £24,668.50 4.13%
5 £1,158.27 £35,145.39 3.30%
6 £898.73 £21,022.54 4.28%
7 £559.26 £17,055.75 3.28%
8 £759.25 £15,096.83 5.03%
9 £768.95 £16,612.75 4.63%
10 £439.26 £10,531.10 4.17%

I would like to add a position next to conversion, based on the highest conversion. So hopefully it would look like this:

Location Product1 Product2 Conversion Position
1 £1,168.62 £34,050.35 3.43% 7
2 £1,768.31 £43,793.78 4.04% 6
3 £918.69 £28,721.55 3.20% 10
4 £1,018.66 £24,668.50 4.13% 5
5 £1,158.27 £35,145.39 3.30% 8
6 £898.73 £21,022.54 4.28% 3
7 £559.26 £17,055.75 3.28% 9
8 £759.25 £15,096.83 5.03% 1
9 £768.95 £16,612.75 4.63% 2
10 £439.26 £10,531.10 4.17% 4


The sum for conversion looks like this, and is called {@Conversion}:

if Sum ({@product1}, {location})<>0 then
if Sum ({@product2}, {location})<>0 then
Sum ({@product1}, {location})/Sum ({@product2}, {location})<>*100

I’ve got the grouping sorted by location, and would like to keep it in this order, but its not crucial. I’ve looked at a few other posts, and can’t do the nthlargest function which was mentioned on one, and there was another mentioning about doing a subreport, and then using the group number function. But I don’t know how to get group number to work on my {@Conversion} formula.

Can any one help or point anything out too me about this?

Im using Crystal 10, and using an ODBC link. In the detail section of the report im bringing out the sales of product1 and product2

 

[lbass]

What is the content of your two product formulas?

-LB

 

[Woodyuk]

It is an if statement. Bascailly it says if the product is product1 then add together the vat and net values together, as these are stored this way in the DB

 

[lbass]

I asked for the actual formulas, because the solution involves creating commands that return the sums per location. I'll describe it in general terms:

In the subreport, create two commands, with each returning the sum per location. Something like:

Select sum(table.`vat`+table.`net`) as prod1,table.`locationID`
From `table`table
Where table.`product` = 'Product1'
Group by table.`locationID`

Link the commands to the table on the location ID field. Then create a formula {@conversion}:

{command.prod1}%{command.prod2}

Place this in the detail section and insert a maximum on it. Even though the value will be the same in the detail and group section, you need to do this so that topN/group sort becomes available. Sort the subreport groups by "maximum of {@conversion}. Then follow the steps in thread767-1113149.

-LB

 

[Woodyuk]

OK for prouduct 1 it is this:

if{OR_SANALT.SALES_TYPE}='G' then ({OR_SANALT.NET_VALUE}+{OR_SANALT.VAT_VALUE})

for proudct 2 its this:

if{OR_SANALT.SALES_TYPE}='P' then ({OR_SANALT.NET_VALUE}+{OR_SANALT.VAT_VALUE})

I think I see what your trying to do, so I will have a look into this

 

[Woodyuk]

Forgot to say, I then do a sum of these, to get the totals by location

 

[lbass]

So the command for ProdG should be something like:

Select sum(OR_SANALT.`VAT_VALUE`+ OR_SANALT.`NET_VALUE`) as prodg,OR_SANALT.`locationID`
From `OR_SANALT`OR_SANALT
Where OR_SANALT.`SALES_TYPE` = 'G'
Group by OR_SANALT.`locationID`

Do a second command replacing the G with a P. Also replace "locationID" with our exact field name. I'm assuming it is from the same table.

-LB

 

[Woodyuk]

Thanks Lbass. Sorry for the delay in replying. It seems to work, however has made the report run alot slower than it use too, due to the subreport. May have to rethink how to do this.

But it did work like you said