Sorting on a Percentage

[Edie0802]

Hello: I have report that uses a calculation on two fields to get a percentage.

I am using Crystal Reports 10.

This report is for items my particular user sells on eBay.

I would like to sort it on the percentage. The problem I am having is that the user wants a group on a field called Title. Title is the only group.

The formulas I am using do this:

1. The first formula does a running total on the records, resetting every time Group 1 (Title) changes.

2. The second formula sums up Bids and also resets every time Group 1 changes. For example, let's say we have 6 items listed and 3 bids, some will have bids, some not. The 3rd formula would be 3/6 or 50%.

3. My third formula takes Bids divided by the Group 1 count on the title. I call it @ratio.

The user wants the report to sort on the @ratio, from highest to lowest, so he can see what he's selling well and not.

I tried a Cross-Tab. I tried a Subreport. I tried doing more groups. I got nothing that seems to work.

I told the user that the @ratio formula is calculated at run-time, and I didn't think Crystal Reports can do this sort on a second pass type of idea. He still wants it sorted because the report is several hundred pages long and he doesn't want to sit there and pick them out.

Any ideas?

I thought about exporting the report to Excel then doing a sort on it, and it works - but this is just a work around for now.

Thanks.

 

[IdoMillet]

Since LB already gave you a perfectly good solution, don't waste time on this. I was just commenting that there's an alternative approach.

- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Edie0802]

-Ido, yes I understand.

But, I wouldn't put it past this user to ask me to do what you suggest. I thought about that over a week ago.

Your solution will be here when the times comes. Oh, and I bet it will, reports never seem to stay the same and they NEVER go away, always evolving, always changing...such is the nature of reporting!

Thanks.

 

[lbass]

Edie,

I think Ido and I have a different understanding of what your ratio was supposed to do. I thought your data looked like this:
Bids Ratio (bids/count) Average Bids
Title1 Item1 5
Title1 Item2 0
Title1 Item3 4 66.7% 3.0

Title2 Item4 3
Title2 Item5 6
Title2 Item6 0
Title2 Item7 0 50.0% 2.25

That is, for each title group, you wanted the ratio of items with bids, to the total number of items. The only way to get that ratio is by a formula that does a conditional count:

//{@bids} <> ):
if Bids <> 0 then 1

Then the ratio becomes:

sum({@bids},{table.title})%count({table.item},{table.title})

This formula is not available for group sorting, and thus the SQL expression becomes necessary. Note that the SQL expression used a count because the count was being limited to non-zero bids.

If you are looking for the average number of bids per Title, then Ido is right, you could just have inserted an average on the bid field at the title level and done a group sort on that. But these are very different questions.

-LB

 

[IdoMillet]

Hi LB,

Actually, even for the scenario of "ratio of items with bids, to the total number of items" there's a simpler approach:

1. Create a formula that returns 1 if Bids > 0 and 0 otherwise.

2. Insert an average on that formula.

Cheers,
- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

Okay, Ido, you got me there. Thanks for challenging me on this--it's always better to do things more simply, and I just didn't see this.

Edie, I'm sorry for making this much more complex than it had to be!

-LB

 

[Edie0802]

-LB and Ido: You guys got me confused now.

As for the moment, there are no more than 1 bid per listing, and either there is a bid, or there is not on that single listing.

I will ask user if there will ever be a possibility that there can be more bids on a single listing. Either he's useing Buy It Now on eBay or he isn't. As you 2 probably know: on eBay "Buy It Now" will only produce 1 bid for a listing. For regular listings, many more than 1 person can bid on a single listing, and only 1 wins it.

For now, user seems happy. I did this work as a favor for him and I really appreciate the help I got to help this guy out. Thanks.

 

[rewob65]

The work here is absolutely brilliant, by all three of you.

I am just posting here so that I can pick it up tomorrow as I will work on ther average, as I think I am trying to do something similar to you Ed.

It this is the wrong etiquete please tell me but don't shoot me!

 

[IdoMillet]

You can click "Add This thread To Your Archive" or email yourself the link to the thread as a reminder.

- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com