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.

After releasing my course materials and my “Expert” series I realized that I had forgotten one item: My set of calendars. This three report set, with instructions, allows you to generate 3 different styles of calendar from your data (continuous, monthly and daily). The first two allow you to show multi-day events as boxes that stretch from the beginning to the end of the event.

The reports are designed so that you can swap in your fields instead of the sample data fields. You can read more about them in my original blog post, or download the set and try them out.

Several of my customers read Excel spreadsheets, Microsoft Access MDB files and even text files using DAO connections. This are simple connections made by pointing directly to the data file. Unfortunately, these direct connections are no longer supported in Crystal Reports 2020. I was hoping that might be a temporary situation, but this link on the SAP website provides confirmation:

Crystal Reports 2020 is a 64bit application, and therefore it is no longer possible to connect to Excel or Access using DAO, since there is no 64bit version of this Microsoft Technology. This is why the option “Access/Excel (DAO)” is not available in Crystal Reports 2020.

You can still connect to these data sources using ODBC or OLEDB, but you will need the new 64-bit Microsoft driver. OLEDB actually works in a similar way to DAO, but you will need to do a “Set Datasource Location” in each report to convert it from DAO to OLEDB. I posted instructions in February for using OLEDB to connect to the Xtreme Sample Database (mdb) and the steps would be similar for other types of DAO connections.

If you run both 32-bit and 64-bit versions of CR you might want to switch to ODBC connections (DSNs). With ODBC you can create a 64-bit DSN on one PC and a 32-bit DSN on another PC using the same DSN name. That way you can maintain one rpt file and it can run in both the 32-bit and 64-bit Crystal environments.