Selection formula Problem 1

[jonbrown]

I am reporting against a database that stores date times as the number of seconds since 1/1/1970.

I can create a formula to convert a datetime in crystal into seconds since that date - no problem.

However, when I use the conversion formula in the selection formula to narrow down the selection, Crystal retrieves all rows before applying a filter. with 100,000+ rows in the result set, this is impractical.

(e.g.) {Start Date} IN {@StartFormula} TO {@EndFormula}

Does anyone know of a way that I can make the conversion formula evaluate before retrival. (Using BeforeReadingRecords does not seem to work)

Thanks

 

[jfrost10]

Hi Jon, and welcome to the fun filled, slower than the next ice age, world of CR!

Seriously though, we're dealing with speed issues too. Here's what we've found to be the problem:
Picture you go to the store and say, "I want only red apples", and the store owner gives you a bucket of red and green apples and tells you to pick the red ones out yourself. Thats how CR works with databases like Accees: ALL the records get returned, and then CR goes through and gets what it wants.
If you went to the SQL Server grocery store, it would be totally different: you'd only get red apples, no green.
(oracle, IBM, etc. databases, I'm not sure about...I'm an MS boy )

So here's how we got around it: instead of CR doing all the work, we did it from within our vb applicatoin, through the records we wanted into a dummy db table, and just had crystal directly mirror the table. No formulas, no complicated selection statements, just show the fields.

If anyone else reading this has done something different to speed up their reports, PLEASE LET BOTH ME AND HUGH KNOW!!!

I think eventually every CR developer meets teh speed issue.

Hope this helped somewhat,

Jack

 

[jonbrown]

Thanks for that. I have done it a similar way in the past by doing the record selection in a stored procedure and running the report against the procedure, however, we have a customer who won't let us create the procedures!!

Jon

 

[kenhamady]

Crystal CAN pass criteria to the server, depending on what the criteria is. The problem is that CR functions in formulas usually cannot be converted by CR into SQL. You can display the SQL that a report is passing to see what is going into the WHERE clause. Use Database - Show SQL.

Here are some techniques to getting more criteria into SQL. Your situation may not benefit from these but they might give you some ideas. They are ranked in ascending order of the kludge factor:

1) Write the formulas a different way to see if it will make it into the SQL. Many formulas can be accomplished with different approaches. Dates are hard because you almost always have to use functions.

2) Write the formulas (if possible) as SQL expressions within CR. These always gets passed to the SQL. I am not sure if your SQL function list will allow this date conversion.

3) Prompt them for 3 numeric inputs 2001;8;31 and use a mathematical formula to convert it to seconds without functions. Use the seconds value for selection. This is more likely to get passed to the SQL.

4) If they usually run current info that doesn't need to examine old records, you could add a third 'starting point' parameter that has a default value of the number of seconds as of the starting point (ie beginning of the year or period). Use this as an additional rule in the selection formula, and tell them to override this with 0 when they need to go back before that starting point. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/