Using Store Procedures and Crystal Reports 5

[clydejones]

Hello,

I have a complex report I need to create. Actually, there will be serveral of them. I have been suggested (from this forum)to use Store Procedures to create my data sets. My doubt is how do I populate the fields in Crystal with the correct data. I believe I can handle the Store Procedure and attaching it to the report. I'm just a little confused as to how populate the fields in Crystal. Any help (especially example code) would be appreciated.

Thanks In Advance,
Clyde

 

[jazerr]

I use SQL SPs for all my reports and this is how I do them...
Select distinct
Table.field AS Firstname,
Table2.field2 AS Lastname,
Table3.field3 AS Middlename

FROM TABLE

WHERE TABLE.field5 = 1

Basically if you do your Stored procedure right, it will look in Crystal like it is just another table, and you design the report the same way you normally would.

One important thing to remember is that when creating your SP, make sure you alias your fields descriptively, as you are probably not the only person who has to look at this, and it makes it easier to troubleshoot.

If you need more info, let me know.

 

[kenhamady]

Also make sure that you enable Stored Procedures in Crystal's "File - Options" or else they won't show up in the table list. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[kenhamady]

Also, make sure that you enable Stored Procedures in Crystal's "File - Options" or else they won't show up in the table list. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[clydejones]

Jazerr,

I created a store procedure that does a count of records in a field that contains the value 11. I know this count is 25 from checking the data base and using the SQL Server Query Analyzer. I connected to this store procedure to my Crystal form and laid it on the Crystal form like I lay data base fields on the form, but I don't get a value. Any help would be appreciated.

Thanks In Advance,
Clyde

 

[clydejones]

Jazerr,

I created a store procedure that does a count of records in a field that contains the value 11. I know this count is 25 from checking the data base and using the SQL Server Query Analyzer. I connected this store procedure to my Crystal form and laid it on the Crystal form like I lay data base fields on the form, but I don't get a value. Any help would be appreciated.

Thanks In Advance,
Clyde

 

[kenhamady]

You can't lay a stored procedure on the form like a field. Because it isn't part of the primary report's result set. I am not even sure how you got the SP to be an object, since crystal doesn't support this.

You could create another report using the SP and make it a subreport, then bring back the values you need from the subreport to the main report. That is the only way to use 2 separate result tables in one report. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[BigRetina]

salute..
I have a stored procedure that contains INSERT statements and other calculations..
the last statement is a SELECT FROM a temp table..and basically this is the recordset that I want to return!.
In Crystal Reports in VS.NET when I choose an OLEDB data source in the databases and I choose my stored procedure it does not contain any return fields..or database fields!...but if i a put ONLY ONE select statement in that stored procedure everything works fine ofcourse!
by the way..if I use this stored procedure to FILL a DATASET it works fine and it fills a table in my dataset with the rows selected from the temp table..everything is smooth!.

what can I do?..

please HELP me...i need this fast!
Thanks In Advance

 

[kenhamady]

Are you sure that the temp table exists when the SP is being run for the report? Does the SP create the temp table? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[BigRetina]

Sorry friends..
I found it..
I should put SET NOCOUNT ON before the isnerts statements!
Thanks kenhamady

 

[jazerr]

You need to make sure that the columns you are selecting have a column name or a valid alias name. If there are no names, then the columns will NOT display in CR.

 

[synapsevampire]

It appears that you're using SQL Server, which means that you have the View designer available to you.

This greatly simplifies the creation of SP's, and I encourage all of my local SQL developers here to use this tool, no matter how experienced they are, as it has a visual interface for linking, field selection, etc.

Once you have a working model, copy and paste the SQL into your SP.

There are advanced SQL features that will break the visual representation (such as a CASE in the select), but I doubt that you'll hit those soon.

Note that SP's alone will not significantly improve performance unless your database is properly indexed, and there are nuisances associated with SP's, such as not being able to readily link multiple parameters to them.

Good luck with the SQL coding, it's definitely the way to go.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[BigRetina]

Dear Friends..
I am facing a really FRUSTRATING error!.
I am using Crystal Reports.NET with VB.NET..I am connecting the report to a SQL server 7.0 (service pack 3) stored procedure via OLEDB.
The ERROR I get when I run the program from Win98 is "Failed To Open Rowset" .. This
error take place when the STORED PROCEDURE has datetime or smalldatetime input paramters!.
The problem is that everything works fine from Win2000!
what could be the problem?..please help me!!