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]

Create a SQL expression {%ratio}:

(
select count(`Title`)
from table A
where A.`Bid` is not null and
A.`Title` = table.`Title`
) /
(
select count(`Title`)
from table A
where A.`Title` = table.`Title`
) * 100

Replace "table" with your Table name and replace "Title" and "Bid" with your actual field names. Leave "A" as is, since it is an alias table name. The punctuation will be specific to your datasource. If you are unsure, take a look at database->show SQL query and observe how the punctuation is used there.

The above assumes that if there is no bid, the bid field is null. If it is 0, then change the clause to say:

where A.`Bid` > 0 and

Then place this expression in the detail section 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]

-LB

I cannot get this to work. After I got the syntax right in the SQL expression, I kept getting Failed to Open a Rowset.

I see that you are trying to, I think, count the bids in the first part before the "/". Please explain to me how this is supposed to sum up the bids.

Ultimately, I need bids divided by the count of the records in group 1, resetting as group 1 changes.

Also, before I could even get clean syntax, I had to add an alias table. I called it tableA.

 

[lbass]

Please post the content of the SQL expression that you used.

-LB

 

[Edie0802]

((
select count(`A`.`title `)
from `'custom werks#xls$'` `'A'`
where `A`.`bids` is not null and
`'A'`.`title` = `'street_flame_'`.`title `
) /
(
select count(`A`.`title `)
from `'custom werks#xls$'` `'A'`
where `'A'`.`title` = `'street_flame_'`.`title `
)) * 100

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

I am not getting the rowset error at the moment. The name of this SQL Expression is %Ratio. I still have one group, grouped on A.title.

When I put %Ratio in the details section, they all come out as 100.

For the maximum formula, the contents are:
maximum({%Ratio},{A.title })

 

[lbass]

Do you have a table named "A" in your main report??? What are your table names in the main report? You appear to be using more than one, so how are they linked?

-LB

 

[Edie0802]

LB: there are two tables in the report. This datasource is just an excel spreadsheet.

The original table is "street_flame_"

The alias from that table I renamed "A" for simplicity.

There are only 8 fields in each table.

There is but one group on A.title.

The two tables are linked 'street_flame_'.title --> A.title (Inner Join, Not Enforced, = )

The SQL expression is as follows, it is called Ratio:

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

The Formula to use the maximum idea with contents are:
maximum({%Ratio},{A.title })

 

[lbass]

Please explain which table the bids field is from. And why are you enclosing your table names in single quotes? I think using an alias in your main report is confusing things. If your Bids is from customwerks#xls$ and you are grouping on title from the other table, then I think the expression should look like this. I'm changing the alias in the SQL expression to distinguish it from your main report use of an alias. Please try this exactly as is (assuming the field names are correct):

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

Then DO NOT use a maximum formula. Instead, place the SQL expression in the detail section and right click on it->insert->summary->maximum. This is necessary to activate the group sort feature.

-LB

 

[Edie0802]

-LB: This is getting awfully confusing. Should I just remove the alias table? Again, this is an Excel spreadsheet that is the datasource. The NAME of the Excel spreadsheet is custom werks#xls$. There is ONE table, it shows up in Crystal as street_flame_

There are only 8 columns in the Excel spreadsheet and hundreds of rows.

I do not understand the B and C's you put in the SQL Expression. The alias table I speak of is just an alias of street_flame_

Do you want me to create another alias table B and C?

 

[lbass]

You don't have to change the B and C at all--they are aliases used ONLY within the SQL expression. When you go into the field explorer, what is the heading under which all the fields are listed? Is it "street_flame_" or
"custom werks#xls$"? If it is "A" then I think you should remove the alias from the report and use the actual name.

-LB

 

[Edie0802]

-LB: Thank you for making this more clear. I will try this after I leave work tonight.

I will remove the alias "A" from within the report and use only the table and fields that comes with it, that is "street_flame_".

Also, is it possible to do a sum on the bids in the first half of the SQL expression? I ask that because it is possible for more than one person to bid on an eBay item.

I will then copy and paste your SQL into the SQL Expression, place it in the details section, and then do the ->insert->summary->maximum

I'll post later tonight to let you know what happened.

 

[lbass]

I don't follow why you would do a comparison of a sum of bids to a count. I thought you wanted to do a count of bids <> 0 to a count of title. If you really only have one table, then the SQL expression should be:

(
select count(`bids`)
from `street_flame_` B
where B.`bids` > 0 and
B.`title` = street_flame_.`title`
) /
(
select count(`title`)
from street_flame B
where B.`title` = street_flame_.`title`
) * 100

-LB

 

[Edie0802]

-LB: This continues not to work.

This is my current syntax, it will not compile. The reason I have `'custom werks#xls$'` in front of `'street_flame_'` is because if I don't, then street_flame_ is not recognized, no matter how long I play around with the quotes:

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

If I try and put the B from your syntax, like this:
select count(`bids`)
from `street_flame_` B

then it errors on the B

The error I am getting is "Too few parameters. Expected 2".

To answer an earlier question you asked me, in the Field Explorer, it says
- Database Fields
- 'street_flame_'
title
sold
format
start
end
bids
closedate
url

Again the name of the Excel sheet is custom werks.xls.

I don't know why the syntax is wanting a fully qualified name.

I have removed the alias table.

The user emailed me today wanting his report. Ugh!!

 

[Edie0802]

LB: Update...I am now getting the SQL Expression to work, no errors and I am getting the bids/count for each of the sets of records in group 1, and it is correct.

The problem I am having now is implementing the Group #2 idea. The user wants to see:

100%
Part A 4 bids, 4 listings
Part B 10 bids, 10 listings
Part C 1 bid, 1 listing
90%
Part D 9 bids, 10 listings

....and so on.

If I try to put a grouping on the SQL Expression, it will act like it is running and then I get "Failed to open a rowset" and it takes me back to the SQL Expression.

This is the SQL Expression after I fixed it:

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

Dumb question: I already have the group in place, by street_flame_.title before I put the SQL Expression in the details.

Is this correct?

Another dumb question: when I right click on the SQL Expression while it is in the details section and then go Insert>Summary>Maximum, it asks me where I want to put the summary. It doesn't seem to care if I select for Summary Location. This doesn't make sense to me.

 

[lbass]

Could you please go into database->show SQL query and paste it here so I can see how the punctuation looks there? There is no reason for an alias table to cause an error. Note that you have to enclose the subquery in parens:

(
select count(`bids`)
from `street_flame_` B
)

-LB

 

[Edie0802]

Sure:


SELECT `'street_flame_'`.`title `, `'street_flame_'`.`end`, `'street_flame_'`.`bids`, `'street_flame_'`.`url`, ((
select count(`'street_flame_'`.`bids`)
from `'custom werks#xls$'` `'A'`
where `'A'`.`bids` > 0 and
`'A'`.`title` = `'street_flame_'`.`title `
) /
(
select count(`'street_flame_'`.`title`)
from `'custom werks#xls$'` `'A'`
where `'A'`.`title` = `'street_flame_'`.`title `
) * 100
)
FROM `'custom werks#xls$'` `'street_flame_'`
ORDER BY `'street_flame_'`.`title `

 

[lbass]

You cannot reference the table within the summary in the SQL expression (in this version), and I don't think you can reference any aliases you are using in the main report, since the SQL expression will go directly to the database and doesn't "know" the alias used in the main report. So try this--exactly as is:

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

It looks like your field "title " has a space after the "e" in the database, so if it does, then use add that space consistently also.

-LB

 

[Edie0802]

Error: Two few parameters. Expected 1.

Won't compile. yes, I noticed that space after title also.

Gosh, this is frustrating. I really appreciate you sticking with me on this, though.

 

[lbass]

Please post the SQL expression you used. I just made up an excel spreadsheet and got this to work just fine. How are you connecting? Are you using Access/Excel DAO?

-LB

 

[Edie0802]

I copied and pasted your SQL Expression into a new SQL Expression, did a check and got the error.

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

Yes, I am using Access/Excel DAO.