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.

 

[lbass]

Okay, I can't explain this, but the following seems to work:

(
select count(`bids`)
from `'custom werks#xls$'` B
where B.`bids` > 0 and
B.`title ` = `'street_flame_'`.`title `
) /
(
select count(`title `)
from `'custom werks#xls$'` B
where B.`title ` = `'street_flame_'`.`title `
) * 100

When I tested just previously, I hadn't added in the alias group clause. Using this model worked with my sample and gave the correct results.

-LB

 

[Edie0802]

Okay, I got this copied/pasted in a new SQL Expression. I get no errors when I check it.

Now, tell me exactly what to do after that like I am a dummy and don't know nothing.

Edie

 

[lbass]

As I said before:

Then place this expression in the detail section and right click and insert a maximum on it. This is necessary in order to activate the group sort feature. Then go to report->group sort and choose maximum of {%ratio} as your sort field.

-LB

 

[Edie0802]

OK. I put the new SQL Expression in Details section.

Then I right clicked on it selected Insert, then Summary from the context menu.

Then, I see the SQL Expression in the Choose the Field to Summarize section at the top. In the section below that, I select Maximum. I then opt for the default, which is to put the Summary in the Report Footer. I then click OK.

Then I go up to Report, Group Sort Expert. But I can't select Group Sort Expert because it is greyed out.

What is this dummy doing to myself?

 

[lbass]

You have to insert the maximum at the group level since that is what you want to sort by.

-LB

 

[Edie0802]

LB: Report figured out, thanks to you, and delivered to user late last night. I will now wait to see what he says.

Thank you so much for helping me with this challenging problem. You rock!!

Edie

 

[Edie0802]

LB: Now user is saying that data is getting sorted by number of bids, not by the ratio formula. Sure enough, I looked at the .pdf file I sent him last night, along with the .rpt, and it is sorting by the count of the bids. I got way into the .pdf, over 200 pages, before I saw this behavior. Ugh.

I can't look at the .rpt until I get off work - I don't dare put CR10 on my machine here.

 

[lbass]

I re-checked the SQL expression in my test report and how it worked with group sort, and again, it worked just fine. I think you should make sure that you used maximum of {%ratio} for your group sort field.

-LB

 

[Edie0802]

LB: My thoughts exactly. I noticed last night that, for some reason, it kept wanting to change from to Sum from Maximum, like it was fragile or something.

Can't wait to get to my other box to test this, wrap this up, and call it a day!

 

[IdoMillet]

While the SQL Expression is a good solution, I think this particular case could be solved by simply adding an average of number of bids at the group level. Then, Group Sort by that average.

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[lbass]

Ido, but how would you insert an average on a count?

-LB

 

[Edie0802]

LB: My user is happy with his report. Thank you again for all your help.

Edie

 

[lbass]

So what did you have to do to correct the sort issue?

-LB

 

[Edie0802]

As you know, it was pretty late the night before last before you finally figured out the solution as we were here going back and forth in this forum. When I went and looked at it yesterday, one of my earlier SQL Expressions had Sum instead of Maximum on it. I simply removed it from the report and then deleted it all together from the Field Explorer. It didn't jump out at the me the night before last because I had it suppressed.

Then I reran the report and looked through all the hundreds of pages and everything was absolutely correct. I saw it in less than 5 minutes yesterday, sent the report off, and got a sign off this morning.

 

[IdoMillet]

I thought each record provides the number of bids. If that is the case, an average on that number would give you the same sort as the SQL Expression approach.

For example
1
0
3
0

Would give you an average of 2
The SQL Expression approach seems to be a more complex way of computing that same average.

Since the problem is now solved, just ignore me...

- Ido

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

http://www.MilletSoftware.com

 

[lbass]

Ido, Maybe I misunderstood, but I didn't think the bid count was available in the form of a database field. Oh,well--it's always good to question whether there might be a simpler approach.

-LB

 

[Edie0802]

- Ido: I hope the user doesn't come back at me and say he wants a sum on the bids for the cases where there was several bids on the same item. I had actually thought about that. For example, let's say he's selling Ford Ranger Bumpers Model 2007 XLT's.

Listing 1 for Ranger Bumper: Bids = 3 Wins = 1
Listing 2 for Ranger Bumper: Bids = 0 Wins = 0
Listing 3 for Ranger Bumper: Bids = 1 Wins = 1

Bids would sum up to 4, with a total of 3 listings. 4/3 is 133.33%. I looked through his data and in no case did he have more than one bid per listing. I don't know if he strictly does "Buy It Now" or not.

I suspect one day he'll ask me to alter the report for this particular case. He hasn't asked me yet though.

 

[IdoMillet]

OK, so we do agree that the desired ratio is 4/3 = 133% in the sample case above. In that case, a simple average on the number of bids gives you that same exact number. If you don't believe me, try it out... )

As to your other question, that is quite easy to handle. You create a formula that returns the number of bids if there were more than 1 and zero otherwise. You then simply SUM that formula.

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[Edie0802]

Ido: I don't quite follow. I do understand the 133% thing, but then the user wants it sorted, from highest to lowest, on that percentage. For example:

133% Records:
Ford Ranger Bumper 2007 XLT: Bids = 4 Listings = 3

------------------------------------------

100% Records:
Pontiac Grand Am Headlights: Bids = 3, Listings = 3
Acura Integra Hubcaps: Bids = 2, Listings = 2
Ford Ranger Hubcaps: Bids = 1, Listings =1
Toyota Corolla Floor Mats: Bids = 20, Listings = 20

-------------------------------------------

90% Records:
Toyota Prius Vanity Mirror: Bids = 9, Listings = 10

--------------------------------------------

He wants to see his greatest selling stuff in the beginning of the report and all the stuff that don't sell at the end of the report. He doesn't want to pick it out.

He still wants to see stuff upfront that sells well, even if he didn't have a lot of listings so he knows to put on more listings of those types of items.

If it comes to this, I do not see how I can sort on that percentage because the percentage is done a run-time. He definately wants it grouped on Title (Ford Ranger Hubcaps) and all the detail that normally follows for each record, like data of sale and price, etc.

I believe you, I just don't understand. I don't write that many crystal reports anymore. I used to, a long time ago.

 

[IdoMillet]

1. Click on the number of bids and insert an average at the level 1 grouping.

2. Go to the Group Sort expert and you should notice that Crystal now lets you sort the groups by that average.

Cheers,
- Ido

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

http://www.MilletSoftware.com