Automate delivery of Crystal Reports With a Windows Service
page 2 of 5
by Vince Varallo
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 52178/ 124

Step 1: Create the Database

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.


View Entire Article

User Comments

Title: great article!   
Name: mira minkova
Date: 2013-01-07 6:09:56 AM
Comment:
very detailed and easy to follow! Thank you!
Title: get or set accessor expected?   
Name: Mike
Date: 2010-07-29 12:52:00 PM
Comment:
I'm trying to use this example to code a service and I'm receiving a "get or set accessor expected" exception in my RunReport method when I try to instantiate a ReportDocument object...

Any ideas on what the problem might be?

Product Spotlight
Product Spotlight 



Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-03-23 8:59:51 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search