Automat report generation 2

[CindyN]

I need some general info (where to look?) or any tips & tricks.

I have a report in CR8.5 with an Oracle backend. I'm using 4 subreports within the report; so, to get my dataset smaller (+100K records/month), I am using the Crystal SQL Designer to create qry files. This works OK, however, I need to run the reports once/month using the data from the previous month.

How do I automate the report generation on:

1) the SQL queries (the dates need to change each month).
2) the monthly reports.

I would like the reports to kick off automatically sometime after the 1st of the next month. I have access to VB, but it's been a long time since I used it.

I've looked for subjects like this in this forum, in help & in my Complete Reference book, but have not seen anything that is like what I want to do. Am I off base? Is there another way to go about this?

 

[jjames]

The SQL snippet below will return records that fall into the date range 1st day of last month and last day of last month.

WHERE
.
.
.
AND YOUR_DATE_FIELD BETWEEN
TRUNC(ADD_MONTHS(SYSDATE, -1), 'Month')
AND
TRUNC(SYSDATE,'Month')-1
.
.
.

 

[CindyN]

Thanks jjames, that's very helpful.

But how do I make it automatically refresh the data each month?

 

[jjames]

We use Crystal Info to automate all our reporting. If you do not have Crystal Info then you can write a VB app that automates or you can manualy run the report or find other ways. A important point to remember though...do not Save Data with your Report or SQL. I learned the hard way that if you do this, Crystal will ignore all and just display the saved data.

 

[kenhamady]

When you run a report against a QRY that doesn't have saved data, it will refresh automatically.

You will need a front end program to schedule the report to run each month. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[CindyN]

I'm confused...Right now I'm opening Crystal SQL Designer, running my 4 reports (1 for the main report and 3 for the subreports) and saving them to different files, and then I'm opening Crystal Reports to run each of the reports separately. How do I run the reports against 4 QRYs that don't have saved data?

 

[CindyN]

The SQL snippet posted above does not return the data for the last day of the month. For the month of May it includes May 1 through May 30 (May 31 does not appear). How do I include May 31?

 

[CindyN]

Got it! To include the last day of the month, the code is:

WHERE
.
'
AND YOUR_DATE_FIELD BETWEEN
TRUNC(ADD_MONTHS(SYSDATE, -1), 'Month')
AND
TRUNC(SYSDATE,'Month')
'
'

(without the last -1)
.

 

[CindyN]

I think the point on my previous thread got lost.

Ken, You stated:

"When you run a report against a QRY that doesn't have saved data, it will refresh automatically".

Did you mean that the report doesn't have saved data?...Or the QRY doesn't have saved data? How do you run a query without saving the data?

Will VB have to push off the QRYs 1st and then run the reports? As you can tell, I'm lost.

 

[TheName]

Once you have the reports set up it's easy to write a VB app to automatically export them to a file.

General VB code to export a report to Excel:

Dim crApp as new CRAXDRT.Application
Dim crReport as CRAXDRT.Report
Dim crExport as CRAXDRT.ExportOptions

Set crReport = crApp.OpenReport(REPORT PATH AND FILENAME)
Set crExport = crReport.ExportOptions
With crExport
.DestinationType = crEDTDiskFile
.FormatType = crEFTExcel80
.DiskFileName = DESTINATION PATH AND FILENAME
End With
crReport.Export False

Set crExport = Nothing
Set crReport = Nothing
Set crApp = Nothing

That's the basics of it. Good luck!

Bejamin Scott
thename@mindless.com

 

[kenhamady]

When you save the QRY file, you can also save it with or without data. If you save the QRY without data the report will run the QRY's SQL statement to get data. At least it will when you are running from the design environment.

This may not work exactly the same from a VB application. Seagate doesn't recommend using the QRY process when you are in an application.

Within an application you have the option of having VB generate a record set and passing that recordset to the report. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[CindyN]

Thanks for all the help. It's going to be a challenge, but now I feel like its one I can handle. s-)