Highest Number 1

[AyJayEL]

How can I find the top two values for a range of numbers.

i.e. I have a count for children based on their National Curriculum Year (NCY). I want to know now which two NCY's have the most children in them.

Learn something new every day
Using Crystal 8, Oracle Database Paint Shop Pro X1 Nikon D80

 

[Madawc]

Have you tried TopN

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[AyJayEL]

Yes all that shows me is the highest NCY. It does not show which NCY has the highest number of students in it. (Unless I am using TopN incorrectly).

Learn something new every day
Using Crystal 8, Oracle Database Paint Shop Pro X1 Nikon D80

 

[AyJayEL]

Sorry my answer was wrong. What I had tried was actually NthLargest. I am trying to reproduce a report that is created manually at the moment. It says "The NCY with the most students is NCY 11" (for instance). I have a manual cross tab broken down by gender on the rows with NCY in the columns and counting how many students are in each group. There is a total for each NCY. The client would like the statement above written below the crosstab.

Learn something new every day
Using Crystal 8, Oracle Database Paint Shop Pro X1 Nikon D80

 

[lbass]

I think you would need to create a SQL expression {%cnt} that returns the count directly, as in:

(
select count(A.`student`)
from table A
where A.`NCY` = table.`NCY`
)

Then you could create a formula {@Nth1}:
whileprintingrecords;
stringvar x1;
stringvar x2;
if {%cnt} = nthlargest(1,{%cnt}) then
x1 := {table.NCY} else
if {%cnt} = nthlargest(2,{%cnt}) then
x2 := {table.NCY};

Then create a formula for your note:

whileprintingrecords;
stringvar x1;
stringvar x2;
"The NCY with the most students is " + x1 + ". The NCY with the second most students is " + x2 + "."

-LB

 

[AyJayEL]

I'm falling at the first hurdle. This is what I written;-
(
select count("STUDENT"."STUD_ID")
from table "STUDENT"
where "STUDENT"."NCY" = "STUDENT"."NCY"
)

The where statement doesn't look right to me. My NCY field is in the same table as the STUD_ID.

When I try to save it I get a message 'Missing left parenthesis'.

The cursor then blinks in front of count.

Learn something new every day
Using Crystal 8, Oracle Database Paint Shop Pro X1 Nikon D80

 

[AyJayEL]

DOH! Just noticed I'd left the word table in the statement. DOH!

Learn something new every day
Using Crystal 8, Oracle Database Paint Shop Pro X1 Nikon D80

 

[AyJayEL]

Okay, nearly there. I don't get any results for x1 or x2. I still think this is because of the where statement in the %cnt SQL statement but I'm not clever enough to sort it.

Learn something new every day
Using Crystal 8, Oracle Database Paint Shop Pro X1 Nikon D80

 

[lbass]

Is your table called Student? Then the SQL expression should look something like this:

(
select count(A."STUD_ID")
from "STUDENT" A
where A."NCY" = "STUDENT"."NCY"
)

The punctuation depends upon your datasource/connectivity. You could go to database->show SQL query to see what punctuation to use.

-LB

 

[AyJayEL]

Yes, STUDENT is my table. I've tried your example like this:-

(
select count(A."STUD_ID")
from "STUDENT" A
where A."NCY" = "STUDENT"."NCY"
)
and like this;-
(
select count("A"."STUD_ID")
from "STUDENT" "A"
where "A"."NCY" = "STUDENT"."NCY"
)
But when I try to run it I get 'Variable not in list' message.

@Nth is like this;-
whileprintingrecords;
stringvar x1;
stringvar x2;
if {%cnt} = nthlargest(1,{%cnt}) then
x1 := totext({STUDENT.NCY}) else
if {%cnt} = nthlargest(2,{%cnt}) then
x2 := totext({STUDENT.NCY});

My @Statement says this;-
whileprintingrecords;
stringvar x1;
stringvar x2;
"The NCY with the most students is " + x1 + ". The NCY with the second most students is " + x2 + "."

I've posted all the formulae in case you can spot a stupid mistake in what I've done.

Thank you for all your help.

Andrea

Learn something new every day
Using Crystal 8, Oracle Database Paint Shop Pro X1 Nikon D80

 

[lbass]

I've never seen an error message like "Variable not in list"--where are you getting this message? For what formula? Are you creating the SQL expression in field explorer->SQL expression->new?

-LB

 

[AyJayEL]

I get the message when I try to save the SQL expression %cnt when I have the formula @Nth in the report. If I take it out it runs without giving me that message but it doesn't put any data in the x1 or x2 places in my Statement formula. (That's why I put the @Nth in the report, to try to see what's happening. I probably shouldn't?)

I created it via field explorer SQL expression New.

Learn something new every day
Using Crystal 8, Oracle Database Paint Shop Pro X1 Nikon D80

 

[lbass]

I just tested this and it worked perfectly. Start over and create the SQL expression first. If you are able to save this and display it, then add the remaining formulas.

Also, where are you placing the formulas? {@Nth} has to be positioned before (in an earlier section, e.g, details) than the text formula you want to display, which should be in the report footer.

-LB

 

[AyJayEL]

Ah, that's what I was doing wrong probably. I'm on leave for a fortnight now (YAY) so I'll check it out when I get back.

Thank you for all your help.

Learn something new every day
Using Crystal 8, Oracle Database Paint Shop Pro X1 Nikon D80