Storing crytal data - then accessing later

[GaryWilsonCPA]

Let me describe what i do in access first. I run a query, then append it to a table for temp storage.

I run a second query off the stored data.

Our data is strange and sometimes needs major cleanup before a final query can be run, thats the reason for running two separate query.

query1 cleans up and stores data in table
query2 queries that data and is outputted to report.

Can someone tell me a clean way to do this in crystal 8.0 or version 10.00

We have both versions 8.0 and 10.00

Thanks for your help

 

[synapsevampire]

Crystal isn't a database, nor a development language, I think you misunderstand it's purpose.

Since you need to clean up data, you need another tool to do so.

If the data is already stored in Access, then maintain the currentprocesses, but expose a final Access Query (you need to turn on the Options->View to see them) as the data source for the Report(s).

-k

 

[GaryWilsonCPA]

Unfortunately we are not able to access the database. its not access or MSSQL. Its only open through a rigged up odbc connection.

The database is poorly written, but it contains 500,000 thousand records.

 

[synapsevampire]

You should still be able to perform the current processes through MS Access, and use CR to report.

-k

 

[GaryWilsonCPA]

The data has "illegal characters" and can not be read by microsoft access.


Also we need our reports to be seemless, meaning that the reports management uses via a viewer do not require additional steps.

 

[IdoMillet]

Gary,

I agree with SV that you should be able to do the data cleaning using queries, SPs, Views, or other tools (such as Data Junction).

Still, if you wish to do this step within Crystal, here's one approach (requiring a Crystal scheduling tool and a UFL that provides a function for appending info to a text file):

Step 1: schedule a report to run and generate the cleaned up information. Use a UFL to append the clean information to a text file (comma delimited).

Step 2: schedule a 2nd report to run after the 1st one and use the text file (Text ODBC driver) as a data source.

I've used this approach in the past to create snapshots of data (every x minutes) and it works very nicely.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

You should be able to determine what these illegal charaters are and work around them.

There just isn't any technical information in your posts, so it's difficult to assist you.

Try posting the Database, ODBC driver used, any errors returned, and you might even try posting in an Access forum.

-k

 

[lyanch]

You might be able to do this in 10. In add command you could try the call to the first stored proc, followed by the SQL to read from the table.

I hope this works because I need to do something similar in Oracle.

Lisa

 

[IdoMillet]

Lisa,

Is there a difference between CR 9 & CR 10 in that regard?

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

Lisa: Consider just altering the SP to both update the data and return the data in one process.

-k

 

[dtempleton]

Gary,

Your query1 is responsible for cleaning up the data and appending it to a table. It really sounds like you could convert query1 from a maketable query to a select query, then run your reports off of it without ever making a new table. Is that not the case?

 

[lyanch]

SV-

In oracle sp's for reporting, you can't call another SP.

Ido, I am assuming in 9 that you can call one SP in add command then run SQL for the data output? The documentation I have looked at appeared to support this idea... I still could be offbase. This project got put back to the way back burner again.. so It could be a while before I work on it again.

Lisa

 

[synapsevampire]

Lisa: I know that used to be true, not sure if it still is, but that wasn't what I was suggesting, I stated "Consider just altering the SP to both update the data and return the data in one process"

Meaning 1 SP.

-k

 

[lyanch]

Ah.. one is a system sp that evaluates spatial data.. so they won't let me update that one. When I previously asked about copying that one into another that I would use, they weren't happy with that solution either (essentially recreating provided functionality) from a maintenance point of view.
Lisa