Crystal Reports and SP's / Cross-Tabs

[jjpark75]

How do you utilize a stored procedure from MS SQL Server that requires user parameter input in Crystal Reports? Also, any tips on creating a Cross-Tab query in Crystal Reports? Thanks again!

Jung

 

[AWithers]

Its very straight forward really

create your sp (ensure you have execute rights)

open Crystal, then using the wizard connect to the database (via one of a number of options e.g. native sql, ole-db or odbc) I have found OLE/ODBC best as thE native sql driver has trouble with large text fields

Once connected choose your stored procedure, once selected you will be prompted to provide a value for each parameter

In many of my reports I have set up the sp with mainly text based parameters (with a default value of '%') and use the parameter in the where clause

e.g.
CREATE PROCEDURE [Country_Report]

@country varchar(50) = '%'
as

SELECT dbo.ClientData.ClientName
from
dbo.ClientData
INNER JOIN
dbo.Country ON
dbo.ClientData.BNYCountryID = dbo.Country.BNYCountryID

WHERE dbo.country.countrydescription like @country

Thus you need to supply, in this case, either % (or a specfic country name e.g. france)

Once you have built your report verytime you refresh it you will be asked whether you want to supply new parameters which you can

I hope this is of some help, sorry I have never used the cross-tab function

Andy

 

[jjpark75]

Thanks for the help! I'll figure out the cross-tab part...

Jung