simple sort issue

[aspnetuser]

I have a formula that calculates a percentage in the detail section, then in the group section. I need to sort on this percentage but it is not available in the top n expert, sort expert, or anywhere?

How can I accomplish this?

CR 8.5

Thanks!

 

[Katy44]

I've had this problem before, have you tried deleting the formula and then recreating it? In my case that was all it took for it to be available to group and sort etc.
Hope it works for you.

 

[aspnetuser]

that did not work?

 

[lbass]

If this is a row type percentage involving a summary, e.g.,
sum(fieldA,grp1) % sum(fieldB,grp2), one of the only solutions I've found is the use of SQL expressions. If you provide more information, I would be glad to provide more detail on the solution.

First you would need to verify that you can use a SQL expression, and then please provide the formula you used for the percentage and also the group structure--are there multiple groups? What are the group fields. Your record selection criteria would also be important to know.

-LB

 

[aspnetuser]

HEY LB!
You own this joint...

2 groups
id
name

formula in suppressed details:
Sum ({@Win_Number}, {P.Username}) / (Sum ({@Win_Number}, {P.Username}) + Sum ({@Loss_Number}, {P.Username})) * 100

Then I place in the group footer #2 username.

I can use sql expressions.

sql 2000 backend
cr 8.5

 

[lbass]

Now I'm not so sure. What are the contents of {@Win_Number} and {@Loss_Number}? Does this refer back to that complex report you were working on before in thread767-985669?

-LB

 

[aspnetuser]

Sort of. this is more watered down.
if two fields match, 1 for win else 0.

then i group on the person to get total wins.

 

[lbass]

The exact formulas, please?

-LB

 

[aspnetuser]

IF {P.Pick} = {vw.R} THEN "W" ELSE "L"

then another formula that says
if the above formula is a W then 1 else 0

The sum of that field will be total wins by username.

 

[lbass]

Try this {%winpercent}:

(select count(AKA.`Pick`) from P AKA, vw B where
AKA.`UserName` = P.`UserName` and
AKA.`Pick` = B.`R`)/
(select count(AKA.`Pick`) from P AKA, vw B where
AKA.`UserName` = P.`UserName`) * 100

Place the result in the details section and then right click and insert a summary (Maximum) on it. Then go to topN/group sort and choose "maximum of {%winpercent}".

-LB

 

[aspnetuser]

it craps out at select count( .....

 

[lbass]

It would help if you provided more feedback. Please note for error messages for SQL expressions, the cursor often will appear just before the aggregate function--even if the error has nothing to do with that. So please copy your expression into this thread so we can troubleshoot it.

-LB

 

[aspnetuser]

(select count(AKA."Pick") from P AKA, vw B where
AKA."Username" = P."Username" and
AKA."Pick" = B."Result")/
(select count(AKA."Pick") from P AKA, vw B where
AKA."Username" = P."Username") * 100


invalid object name p

 

[lbass]

I guess "P" isn't your actual table name? Substitute your actual table names for "P" and "vw".

-LB

 

[aspnetuser]

I don't understand your post?

What is vw B?

 

[aspnetuser]

I got it to run but it returns 0 everytime.

(select count(AKA."Pick") from vw AKA where
AKA."Username" = vw."Username" and
AKA."Pick" = vw."Result")/
(select count(AKA."Pick") from vw AKA where
AKA."Username" = vw."Username") * 100


There is only one view as the datasource.

 

[lbass]

This is where I got the "P" and the "vw":

IF {P.Pick} = {vw.R} THEN "W" ELSE "L"

When you run the mouse over the fields, what do you see as field names? Do you see {vw.Username}, {vw.Result}, and {vw.Pick}? The SQL expression must use the exact table and field names.

-LB