running crystal report with source of store procedure than view source

[ehx6]

hi,
I have created a couple of reports, most of the reports I create link to MS sql server 2000 database. I create views to be used as datasource for all my crystal reports, instead of using tables. My question is , can I create store procedures to be a source of crystal reports instead of views. and If I can , does that improve getting the data from the backend database to the crystal report via the web ( aspx )

thanks
Ehx

 

[vidru]

Yes, you can use stored procedures as the data source for reports. If a proc has parameters, Crystal adds them automatically. As far as improving performance, probably. A well written record selection formula can get data from the server as well as a stored proc, but I know from experience that there are a lot of things that are easier and more efficient to do inside a stored proc than in Crystal.

-dave

 

[DallasAggie]

We use stored procedures exclusively (sp?). We have discovered that our reports run a lot faster and with less overhead to the servers. Limiting results through sp params is also more effient because it is being performed on the server rather than the client machine.

 

[ehx6]

Hi DallasAggie, It will be helpfull to show me a one report demo of how to use parameters in store procedure. I assume you create a crystal report and make its datasource a sp. The question is if I am using crystal report to be viewed from aspx page. how would I pass the parameters to the sp and get the restuls. a demo sample will be wonderful thing to show.
thanks
Ehx

 

[vidru]

You can download the asp samples from the BO site, and have a look at the 'StoredProcedureWithParametersExample.zip' sample.
ASP/RDC Samples for Crystal Reports 8.5 (Developer Edition only)

-dave

 

[DallasAggie]

ehx6.

If you have a sp with parameters....when you use it as the datasource inside of a Crystal Report...those paramaters get created automatically in Crystal (e.g., acUserName,acPassword) ...with the correct data type as defined in the sp. See example sp below:

ALTER PROCEDURE SP_EMPLOYEE_SEL_ID
@acUserName varchar(10),
@acPassword varchar(10)

AS

SELECT EMPLOYEE_ID
FROM CR_EMPLOYEE
WHERE USER_NAME = @acUserName
AND PASSWORD = @acPassword

Now. When the report is opened, the end user will automatically be prompted to enter data for those parameters. If you want to make nicer forms for that, then you'll need to pass them to the report ... to answer your question on doing that from an .aspx page --- I haven't had the need to do that yet -- take a look at the Developers Zone on the Businees Objects web site for that.

Hope this helps...