Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 15, 2000

SQL Server Replication Verification

By Kevin Goodwyn

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date