How to do postions on a report

[Woodyuk]

I have a report which i need to do positions on. I have tried doing the top N sort, but cant seem to do it ont fields I want too.
I have 2 figures, and I would like to say on both which is the highest out of all the others. and have a number for there position, ie 1st, 2nd, 3rd, etc.
The fields on the report im trying to report on are sums by the way.
Can anyone help?!?!?!?

 

[lbass]

Please provide a sample of your report and show us the content of the formulas you are trying to do a topN on.

-LB

 

[synapsevampire]

You're generally better served to provide a mini-spec to include thechnical information:

Crystal version
Database/connectivity used
Example data
Expected output

If you have only 2 figures, why would you be comparing them to "all of the others?"

-k

 

[Woodyuk]

Right hope this makes sense. In example one is the type of report im doing. Example 2 is the same except it is showing what i want to add to the report, the position based on ResultC.

Example 1
FigA FigB ResultC
5 56 8.93%
26 89 29.21%
68 70 97.14%
2 85 2.35%
3 8 37.50%

Example 2
FigA FigB ResultC Position
5 56 8.93% 4
26 89 29.21% 3
68 70 97.14% 1
2 85 2.35% 5
3 8 37.50% 2

Im using Crystal verison 8.5, and using an ODBC as connectivity. Hoping to get crystal verison 10 soon, but I dont think this even does it

 

[GJParker]

take a look at this thread thread767-816143 where lbass came up with a solution. Hopefully this should suit your needs.



Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

Not sure what version you are using, but if you can, create a SQL expression like the following {%percent}:

(SELECT (sum(AKA.`FigA`) / SUM(AKA.`FigB`) * 100)
FROM Table AKA
WHERE AKA.`GroupField` = Table.`GroupField`)

You would replace "Table" with your table name and "GroupField" with the field name of your group. Leave "AKA" as is, since it is an alias.

Next place {%percent} in the detail section. Right click on it and insert a maximum on it. Even though the result will be the same at the detail and the summary level, you need to insert the maximum on it so that you can topN. Next go to report->topN/group sort and select "maximum of %percent".

Now to add the ranks, you might be able to simply add the "Special Field" groupnumber (insert->special field->groupnumber) if you only have one group. Otherwise you can create a formula:

whileprintingrecords;
numbervar rank := rank + 1;

Place this in the group header or footer that you are ranking.

-LB

 

[synapsevampire]

You'd be better served to supply complete technical information:

Database used
Example data
Expected output

As usual, LB's solution looks good to me, give it a go.

-k