Cascading Dynamic parameter prompts in Crystal - Filter prompts based on other selections

Version 2

    Environment: Crystal 2008 (supplied from Service Desk version 7.5)

     

    As version 7.5 of Service Desk uses the Crystal 2008 runtime, you are now able to design your Crystal reports for Console and Web Access to take advantage of the ability to dynamically fill a list of values from your database for selection on a parameter prompt.  Here is a document which runs through a step-by-step example of how to do this and it is worth reading that document before you read through this one if you haven't created Dynamic prompts before.  You can also take Dynamic prompts a stage further to have Cascading Dynamic prompts and what this means is to have one list prompt filter on what was selected in another list.  This is what we'll cover in this article.

     

    In this example we will create a new report that shows incidents which were created by a certain group and analyst/analysts.  It will prompt the report user to pick from a list of groups and then to pick the analyst/analysts to display data for on the report.  After picking the group, it will only display those analysts who are members of the chosen group.

     

    • Create a new blank report in Crystal reports.  Select Create a new connection - OLEDB connection - Pick your Provider (for example Microsoft OLE DB Provider for SQL Server) and then complete your database connection details.
    • Select the tables for the report.  In this instance we will select: im_incident, pm_process, tps_group, tps_user_group, tps_analyst, and tps_user.
    • Link the tables as per below:

     

    cascading1.PNG

     

    • Using the field explorer in Crystal, drag the required fields onto the details section of the report when on the Design tab.
    • In field explorer create a new parameter field.  Leave it as a string (as the field we will be filtering against is tps_group.tps_name and this is a string field),
    • Select Dynamic Parameter.
    • Select Add and then pick the database field that you want to populate for the user running the report to select from when prompted.  In this example it would be tps_group.tps_name.
    • Next to this click the "Click to create parameter" section:

     

    cascading2.bmp

    • On the next line pick the database field that you want to populate for the user running the report to select from when prompted.  In this example it would be tps_user.tps_title. 

    PLEASE NOTE: Crystal 2008 unfortunately won't allow you to pick the same column name for two dynamic parameters as it will complain that they are not unique.  For example you would not be able to select tps_group.tps_name and then select tps_user.tps_name because Crystal would not see these as different (it ignores the table name and just looks at the column name.)  I raised this with SAP support.  In the meantime, if you need to do this then you could look at creating a database view to select your column name as another unique name.

    cascading3.bmp

    • In Database - Select Expert - Record selection select tps_user.tps_name is equal to and then select the name of your new cascading dynamic parameter.

    NOTE: If you don't see your dynamic parameter here in the list then you didn't select the correct field type when you created the parameter as this list will only show parameters of the same field type as the database field that you are on for example datetime, string, etc..

    • When running the report your users can now see a list of current analyst records to pick from and these will be limited by the group that you select:

    cascading4.bmp

    cascading5.bmp