Dynamic parameters and Report Stored Procedure

[Former Member]

Hello,

I am a SSRS developer and I want to find out how I can achieve the following in Crystal reports

I want to create a picklist which is populated using a stored procedure lets say SP_All_Countries

and then when the user selects any country from the picklist I want to pass the value selected

to a stored procedure SP_Get_Report_Data which will have a parameter called pCountry

this stored procedure will return all the Customers for the selected country

Is this achievable in Crystal Reports , I want to do filtering on the Database server as opposed to pull everything in the report and then filter it on the report, because we can have 100K+ customers in every country

Please advise

Thanks

Kenny

[Former Member · Accepted solution]

Kenny,

It's not nearly as easy in CR as it is in SSRS but it's not too bad.

The online help documentation has a decent "how to". Do a search for "Creating a parameter with a cascading list of values"

If you still have questions, just let us know.

HTH,

Jason

[Former Member · Accepted solution]

Hi Jason,

I can create the parameter but the problem is what links this paramter to the main report stored procedure

which does the filtering on the database

Please advise

Thanks

Kenny

[Former Member · Accepted solution]

I would do it by executing the stored proc from a SQL Command as opposed to adding it like a table.

The SQL would look something like this.

EXEC StoredProcName {?ParameterName}

or

EXEC StoredProcName @VariableName = {?ParameterName}

Be sure to place single quotes around '{?ParameterName}' if the parameter value is non-numeric.... Same syntax rules as normal T-SQL...

Jason

Edited by: Jason Long on Mar 26, 2010 4:03 PM

[Former Member · Accepted solution]

Does anybody know how to replace the Oracle REF Cursor call when using the SQL Command

As per documentation I defined a package which holds the REF Cursor,

this is my Oracle package which defines the REF Cursor

CREATE OR REPLACE PACKAGE test_package

AS TYPE test_type IS REF CURSOR;

END test_package;

This is what Crystal generates for a regular stored procedure not a SQL Command(Show SQL Query)

BEGIN "PETER"."GET_LOCATION"(-1, :PCURSOR); END ;

This is my Oracle stored procedure signature

CREATE OR REPLACE PROCEDURE Get_Location(plocid IN NUMBER, pcursor IN OUT test_package.test_type)

I am trying to write a SQL Command for the above stored procedure,

how do I specify the Cursor as a OUT parameter, this gives me a Oracle error

EXEC Get_Location {?pRegion},:PCURSOR

Thanks

Kenny

[Former Member · Accepted solution]

Kenny,

#1) When you said you were a SSRS developer, I just assumed that you were using a SQL Server database.

#2) There should be no reason to replace your SP with a command. Simply call the SP with the Command. I'm not an Oracle guy, so I don't know the specific syntax. Just use same code that you would use if you wanted to execute the SP in SQL Plus.

Jason