Using Crystal Reports from a DTS package

[MikeMcKeown]

Hi,

I have not used crystal reports before and dont really know where to start.

I have a SQL Server database with a Stored Procedure which pulls out all the information needed for the reports which i would like to generate. I plan to run this each night based on a SQL Server scheduled job so the number of reports will vary.

I would then like to connect to crystal reports and generate the number the number of reports required whether it be 10 or 20. Stored them in a folder and then automatically print them.

However I am not really sure how to connect to the crystal reports to SQL server and generate the correct number of reports.

Any help greatly received.

Thanks in advanced.

 

[pgtek]

hi check with kenhamady's
he has 3 party tool for that

cheers

pg

pgtek

 

[MikeMcKeown]

Hi - I not quite sure what you mean? Is there a website.

I have found the following code on the internet
(

http://www.sqlservercentral.com/forums/shwmessage.aspx?messageid=108832)

i have put this into an ActiveX script object in the DTS package, however I am getting an error:-

Set App = CreateObject("CrystalDesignRuntime.application") ' This is on a machine with Crystal 8.5

However it is saying that it is unable to create this object. Do I have to install something else or is this not possible in VBscript.

Any ideas??

'Format types others have to be defined, you can use VB to find out the constant value if not here.
' Note: some types not valid with some crytal runtimes.
Const crEFTExcel80 = 29
Const crEFTWordForWindows = 14
Const crEFTText = 8
Const crEFTCommaSeparatedValues = 5
Const crEFTExactRichText = 35
Const crEFTPortableDocFormat = 31

' Destination Types, again you can use VB to get other values for const def if you need to.
Const crEDTDiskFile = 1


Dim Conn 'ADO Connection
Dim Comm 'ADO Command
Dim Rcdset 'ADO Recordset

Dim App 'Crystal Application
Dim Report 'Crystal Report
Dim DB 'Crystal Database
Dim Tables 'Crystal DatabaseTables
Dim Table1 'Crystal DatabaseTable

'On Error Resume Next ' This example was designed without error handling you need to add your own.

' -------------------- Build and get our recordset for our report. --------------------
Set Conn = CreateObject("ADODB.Connection")
Set Comm = CreateObject("ADODB.Command")
Set rcdset = CreateObject("ADODB.Recordset")

Conn.Open "Provider=SQLOLEDB.1;Initial Catalog=Database_Name;App=Crystal Export;Data Source=(local)","sa", ""



'You're database connection
Comm.ActiveConnection = Conn

Comm.CommandText = "SELECT * from final_reports_last_generated"
'Your sql string or SP.

Set Rcdset = Comm.Execute

' Process for no records to be exported.
If Rcdset.EOF Then
' -------------------- Clean up all our objects we created so far. --------------------

' ADO Objects
Rcdset.Close
Set Rcdset = Nothing
Set Comm = Nothing
Conn.Close
Set Conn = Nothing

Main = DTSTaskExecResult_Success ' Set as success as I don't need to know for my needs, set to fail it you do.
Exit Function
End If

' -------------------- On to processing a crystal report. --------------------

' Set our application runtime environment for this process. Make sure you set this to the right runtime value.
Set App = CreateObject("CrystalDesignRuntime.application") ' This is on a machine with Crystal 8.5
' Open our report.
Set Report = App.OpenReport("Z:\report1.rpt") ' The path of your crystal report.

' Discard any existing data that was saved with the report.
Report.DiscardSavedData

' Set our base objects for processing this report.
Set DB = Report.Database
Set Tables = DB.Tables
Set Table1 = Tables.Item(1)

' Set our data set to the recordset we pulled.
Table1.SetPrivateData 3, Rcdset

' Read the records in.
Report.ReadRecords

' Set our export options
Report.ExportOptions.DestinationType = crEDTDiskFile
Report.ExportOptions.FormatType = crEFTPortableDocFormat
Report.ExportOptions.DiskFileName = "C:\CrystalExports\diditwork.pdf" ' Path to where to drop PDF.
Report.ExportOptions.PDFExportAllPages = True

' Export report without user interaction.
Report.Export false

' -------------------- Clean up all our objects we created. --------------------

' Crystal Objects
Set Table1 = nothing
Set Tables = nothing
Set DB = nothing
Set Report = nothing
Set App = Nothing

' ADO Objects
Rcdset.Close
Set Rcdset = Nothing
Set Comm = Nothing
Conn.Close
Set Conn = Nothing

Main = DTSTaskExecResult_Success
End Function

 

[addi2d]

We use a program to run all of our repeating reports via a schedule. we process about 30 reports every night and more at week end and month end.

http://www.scheduledreports.com

We downloaded the free trial and had it running in minutes.


Hope you like it !! worked for us!