Most Crystal parameters are static, meaning they use fixed list of values is stored in the report. But Crystal also allows you to create dynamic parameters. Dynamic parameters generate a list of values from the database each time the report is refreshed. This is useful for lists that change frequently.

Normally you can pull a dynamic parameter directly from a table or view used by the report. But if your parameter is part of a stored procedure you won’t be able to pull the parameter’s list of values from that stored procedure.  That’s because the stored procedure needs a parameter value before it can return any data. And once it returns data it the only it will have is the one value you choose in the parameter. So trying to pull all the possible parameter values from the stored procedure won’t work.

However, you can still provide a dynamic list of values for a stored procedure parameter.  But the list has to come from a separate source. You can choose another table or view that has the list you want to show. Even better, you can write a SQL command that allows you to filter the list to show the exact values you need. This other object (table/view/command) is added to the report along with the Store Procedure.  You use it for the dynamic parameter but make sure you don’t use any fields from that object for anything else in the report.  That force a separate SQL query, which is a sure way to slow the report down.

Once the object is added you can use it in the dynamic parameter to provide the values and the descriptions. If needed you can add a second level to the parameter to make the dynamic parameter into a ‘cascading’ parameter. A cascading parameter has 2 or more levels. You select values for the first level of the cascade and this determines which values appear in the next level.

For more information on using parameters you can download my Advanced course book and the free “Expert’s Guide to Subreports, Parameters and Alerts“. And if you need more help you can always call to schedule a consult.