SQL Server Replication Verification

January 15, 2000

INTEGRATING CRYSTAL REPORTS\SQL STORED PROCEDURES\PARAMETERS

Setting Crystal Reports up to process data from Stored Procedures instead of Tables provide for a more efficient reporting process. This occurs because Stored Procedures return less data to Crystal Reports for processing. This also reduces network traffic.

Assumptions

1. Using Crystal Reports 6.0.

2. The Parameter-Driven SQL Stored Procedure (SQL 6.5) has already been created.

Set the Stored Procedure Option in Crystal Reports

1. From the File menu button, select Options.

2. Click on the SQL Tab.

3. Check the Stored Procedures box.

4. Click OK.

Create a Custom Report

1. From the File menu button, select New.

2. In the ‘Report Gallery’ Dialog, click the Custom Button.

3. Choose Custom Report (default selection) for the Report Type.

4. Click the SQL/ODBC Button for the Data Type.

5. If not logged on to the server, the ‘Logon on Server’ Dialog will appear:

A. Select ODBC DSN and click OK.

B. Enter the password and click OK.

6. In the ‘Choose SQL Table’ Dialog, select the Parameter-Driven Stored Procedure to report on and click OK.

7. In the ‘Stored Procedure Input Parameters’ Dialog:

A. Select (highlight) the parameter at the bottom of the Dialog.

B. Enter the Parameter value at the top of the Dialog.  Note: I experienced problems when using quotes.

C. Click on Update.

D. Repeat this step for each Parameter listed at the bottom of the Dialog.  Click OK when done.

8. In the ‘Insert Fields’ Dialog, select the Field to insert on the report and click the Insert Button. Place the field on the Report in the Design Window. Repeat this step for each field that you want to add to the report. Click Close.

9. To run the report, select the Report menu button and select ‘Refresh Report Data’.

Create a Standard Report using the Standard Expert

1. From the File menu button, select New.

2. In the ‘Report Gallery’, choose an Expert.

3. Click on Standard.

4. In the ‘Create Report Expert’ Dialog, choose the Data to report on.

5. Click on SQL/ODBC.

6. If not logged on to the server, the ‘Logon on Server’ Dialog will appear:

A. Select ODBC DSN and click OK.

B. Enter the password and click OK.

7. In the ‘Choose SQL Table’ Dialog, select the Parameter-Driven Stored Procedure and click Add.

8. In the ‘Stored Procedure Input Parameters’ Dialog:

A. Select (highlight) the parameter at the bottom of the Dialog.

B. Enter the Parameter value at the top of the Dialog.  Note: I experienced problems when using quotes.

C. Click on Update.

D. Repeat this step for each Parameter listed at the bottom of the Dialog.  Click OK when done.  

9.  Click Done on ‘Choose SQL Table’ Dialog.

10. Click the Fields Tab on ‘Create Report Expert’ Dialog.

11. Select the Field(s) to add to the Report and click the Add Button.

12. Click Preview Report.

To Modify the Stored Procedure’s Parameters

1. Select Database from the menu button and select Stored Procedure Parameters.

2. In the ‘Stored Procedure Input Parameters’ Dialog:

A. Select (highlight) the parameter at the bottom of the Dialog.

B. Enter the Parameter value at the top of the Dialog.  Note: I experienced problems when using quotes.

C. Click on Update.

D. Repeat this step for each Parameter listed at the bottom of the Dialog. Click OK when done.