First we'll create a database to store the information about
the reports and the report schedule.
1.
Open SQL Server Management Studio and create a new database called
AutomatedReports.
2.
Create a new SQL Login and grant them access to this database. The
easiest way to give them access is to put them in the db_owner role. For this
example I created a user called aspalliance and gave them a password of
aspalliance.
There are three tables in this database.
Table: Report
|
Field
|
DataType
|
Allow Nulls
|
Description
|
|
Id
|
Int
|
N
|
Identity, Primary Key
|
|
ReportFile
|
Varchar(50)
|
N
|
The Crystal Report File Name
|
|
ReportName
|
Varchar(50)
|
N
|
A friendly name for the report. This will appear in the
subject of the email.
|
|
RecordSelectionFormula
|
Varchar(max)
|
Y
|
Optional. The selection formula for the report.
|
|
ExportFormat
|
Tinyint
|
N
|
Maps to the CrystalDecisions.Shared.ExportFormatType
enumeration
1=Crystal Report
2=RichText
3=WordForWindows
4=Excel
5=PortableDocFormat
6=HTML32
7=HTML40
8=ExcelRecord
|
|
Server
|
Varchar(50)
|
N
|
The database server that the report should connect to.
|
|
DBName
|
Varchar(50)
|
N
|
The database that the report should connect to.
|
|
UserId
|
Varchar(50)
|
N
|
The user id the report should connect the database as.
|
|
Password
|
Varchar(50)
|
N
|
The password for the user id.
|
Table: Schedule
|
Field
|
DataType
|
Allow Nulls
|
Description
|
|
Id
|
Int
|
N
|
Identity, Primary Key
|
|
ReportId
|
Int
|
N
|
Foreign Key to the report table
|
|
NextRunDateTime
|
DateTime
|
Y
|
The next date and time the report is supposed to run.
|
Table: Recipient
|
Field
|
DataType
|
Allow Nulls
|
Description
|
|
Id
|
Int
|
N
|
Identity, Primary Key
|
|
ReportId
|
Int
|
N
|
Foreign Key to the report table
|
|
EmailAddress
|
Varchar(50)
|
N
|
The email address to send the report
|
|
RecipientType
|
Tinyint
|
N
|
0=To
1=CC
2=BCC
|
3.
Create each table in the database.
This article
will allow the user to automatically deliver an Invoice report in both PDF and
Excel format. The report will be sent as an attachment in an email. You'll
need to add two records to the Report table to accomplish this.
|
Report
File
|
Report
Name
|
RecordSelection
Formula
|
Export
Format
|
|
Invoice.rpt
|
Invoice for
Abel, Catherine.pdf
|
{SalesOrderHeader.ContactID}
= 2
|
5
|
|
Invoice.rpt
|
Invoice for
Abel, Catherine.xls
|
{SalesOrderHeader.ContactID}
= 2
|
8
|
Be sure to set
the correct Server, DBName, UserId, and Password for the AdventureWorks
database since the Invoice report is built using the AdventureWorks database.
Now add records
to the Recipient table. You simply need to add the Id of the Report record and
a valid Email Address. You should create at least one record for each report.
Next you should
add a record in the Schedule table for each report. All you need to do is add
the Id of the Report and a DateTime in the NextRunDateTime field. Make the
NextRunDateTime less than the current date so the report will run the first
time you run the service.
