Importing Crystal Report 2

[bradth]

I have a form created in VB that allows the users to input a date range and from the range, a crystal report is called up with all the data from that date range. Is there a way to take the information or the crystal report itself and import it into a SQL table in SQL server? What I would like to do is:
1) Have the user input the dates, and they show up in the crystal report (Which it already does)
2) Then have the data in that crystal report saved to a SQL table each time the date range is executed. Is this possible in any way? And if this doesn't make sense, just ask and I'll try to explain it better. Thanks.

 

[vidru]

Why not just go VB to SQL Server since you have the date range, and presumably the know the data that the report should return? Or, why save the data at all? Why not just save the date range?

-dave

 

[bradth]

See, I have a crystal report created with a pile of formulas and such. So the date range just kind of filters the data for what is needed between date A and B. The VB code uses the crystal report to pull the data, so I'm not sure if that would work. But if for some reason it does, how do you go from VB to SQL server??? Thanks.

 

[Mike Lewis]

Bradth,

I've never done this, but I believe you can export a report to any ODBC data source, which could include SQL Server.

Choose the Export command from the File menu within CR. Then chose your ODBC DSN in the "Format" drop-down. This will create a new table in the database with the data from the rows and columns in the report. Obviously, it won't show any headings or summaries, just the data.

Of course, this assumes you have the appropriate permissions to create tables and insert rows on the server.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[vidru]

Mike's suggestion will work, but you can't append to a table in that manner - each export would have to be its own table. Maybe you could export the report as a PDF, then upload the pdf into the db. You'd need to create a table with an IMAGE data type for one column. When the reports are run, you could automatically export the report behind the scenes to a Temp folder, then upload the exported file into the database.

If you want more info on how to do that, post back. But before you do, make sure you really want to do this. Why not just export the reports and save them locally (to disk)?

-dave

 

[bradth]

I'm working with other software right now, Great Plains, and I need the data in a SQL table in order for the customization that I am doing to work properly. So any info on how to do this would be much appreciated. I tried Mike's idea to export it to the database that I have selected, but have had no such luck b/c I keep getting the error "Failed to export report: Column names in each table must be unique. Column name 'GF1_strItemNumber' in table 'BRADTEST' is specified more than once." And ya that makes sense that you'd need to export it to a new table each time you choose a date, unless there was some way that when you excute the the date range, it brings up a new crystal report in the viewer (which it already does), and automatically exports to SQL? If only there was a way to make a macro in Crystal Reports! So any other info or ideas would be great. Thanks for the help. Much appreciated!

Brad

 

[vidru]

So to clarify, do you want a snapshot of the report (e.g. a PDF of the report), or do you want the data generated by the report in a table (columns for each field)?

-dave

 

[bradth]

I need the data generated in the crystal report to populate a SQL table. The data in the crystal report is in rows and columns (like an excel spreadsheet) and I want it to be displayed the same way in a SQL server table.

Ex:

Item Number Item Description Qty

CS010a-01 Capacitor 5
RS550-a Resistor 10
etc...

So all I need to do is get my crystal report to come up in a SQL table. Thanks again.

 

[vidru]

I don't like this solution at all, but here's a thought...

First off, the error you were getting when you tried to export to ODBC is addressed in this KB article:

http://support.businessobjects.com/library/kbase/articles/c2005551.asp

Secondly, perhaps you could do the export to ODBC, then immediately insert the results that were inserted into by the ODBC export into another table (this being the table that holds all of the data from previous runs of the report), then drop the table that was exported to (so the next export doesn't cause a conflict).

So, the first thing you'd need to do, is successfully export the report to ODBC, then have a look at the resulting table to see what the export looks like (a little wacky). After that, decide what fields you want to have in your 'static' table (i.e. the table that will hold the data from each report run), and create the table.

If I haven't lulled you to sleep yet, post back if you're interested in where this is going...

-dave

 

[IdoMillet]

You could schedule a DTS process in SQL Server that looks for the new table (exported through ODBC from Crystal), and if the table is found, append its content to a fixed table, and delete the export table (so the next export succeeds).

hth,
- Ido

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

http://www.MilletSoftware.com

 

[synapsevampire]

Or, you could just use DTS or VB for everything, which makes a lot more sense.

Using Crystal here is a case of the wrong tool for the job. I've exported to ODBC om many occasions, but only on a one off request, I would never consider using it in a production environment like this.

As for the formulas, I think that you'll find converting them to SQL isn't that difficult, and it will run faster. The idea of pivoting the data in a SQL Server table could also be handled nicely using an SP or a View, you wouldn't need a table at all, and virtually any process could call it as it would a table.

-k

 

[bradth]

Thanks for all your help from everyone, appreciate it much. What I ended up doing is taking the stored procedure and creating a grid on my form. The grid is then created by the date range and number entered in. Then there is a save button on the page which allows the data from the grid (which is the same as the crystal report) to be imported to the form on GP. Synapsevampire that's a good idea on the using entirely VB, which I decided to do. All I need to do is write my SQL query now and figure out all the tables that I need. I think I should have it under control now, or at least I hope. Thanks much again.

Brad

 

[vidru]

Wish I had thought of that...

Why not just go VB to SQL Server since you have the date range, and presumably the know the data that the report should return?

-dave

 

[bradth]

I missed that before. I was trying to make things more complicated then they were. For that, you get a star too!

 

[synapsevampire]

<chuckling at Dave>

Genius is so often overlooked

-k