grouping on a pseudo field

[stef]

Using MS SQL v6.5 and CR V6
Database table A has field 'UKpostcode'
and database table B has fields 'UKpostcode prefix' and 'region'.

A simple formula can be used to to convert the UKpostcode to a locally defined valid UKpostcode prefix, from which the relevant region (e.g. North , South, East and West) can be derived.

I want my report to group various information by region. The number of postcodes in the UK is vast and the allocation to each region is constantly shifting, so it is not practical to maintain a postcode to region table.

I've tried using subreports to derive the region from the postcode and then group on the subreport value, but to no avail.
I feel the right way would be to get the SQL query to do the association, but can't see how to set it up.

Any help please?!

 

[kenhamady]

Crystal can't link based on an expression in the visual linking expert. But, if you have the skills to implement this link in SQL, using SQL expressions, then you could write the query using the SQL Query designer and use that as the basis of your report.

You might even be able to modify the SQL of the report itself but there are limits on what you can do here.

You could also write a stored procedure or view in SQL itself and use this as the basis for the report.

Last, if you don't need both tables you might be able to write the formula in Crystal to derive the region for grouping.
Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[stef]

Ken,
thanks for your thoughts. It's good to know I've not overlooked the blinding obvious!

I've tried to create a stored procedure in SQL, but couldn't see how to make CR refer to it. But now I'll investigate further.

Thanks!

 

[kenhamady]

You have to set Crystal's "file - Options" to allow reporting on Stored Procedures. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal