SQL Server Replication Verification

INTEGRATING CRYSTAL REPORTSSQL STORED PROCEDURESPARAMETERS

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.

Previous article
Next article

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles