Top N or Sort by Formula Field Work Around

[a75537]

I have a table called Score which has a field called Answer. Answer will always be either 1 for yes or 2 for no.

In my report I have a formula called @NoAnswer which is placed in the details:
if {Score.Answer} = 2 then 1 else 0

The field {Score.Answer} is also placed in the details section. The details section is suppressed - I only want to show the totals in the group footer.

In the group footer (Grouped by question - {Score.Question}), I have a Count of Score.Answer and a Sum of @NoAnswer. I then created another formula called @NoPercent which gives me the percentage of "No" answers:
Sum ({@NoAnswer}, {Score.Question}) % Count ({Score.Answer}, {Score.Question}).

I now need the report to be sorted by this percentage field - @NoPercent, but I do not get this option when I try to do a Top N, or report sort. I know this is because I'm trying to sort by a formula that uses summary fields - but my question is how do I get around this?? I also need to create a graph for the highest 10 percentages.

Does anyone have a work around for this scenerio?

Thanks.

 

[Madawc]

I had something similar, and had to write a Stored Procedure in SQL to get round it.

There is a possible work-round, that would be worth trying first. Do a summary field for Answer, and another summary field for some other field, a count of some alpah field. Both of these could be placed in the group header, and the difference between them would be the number of 'No' answers. Crystal might then allow you to work out a group percentage in the header, which could then be used.

Madawc Williams
East Anglia, Great Britain