Dynamic parameter prompts in Crystal - Users can filter on values directly from your database

Version 3

    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.  This document runs through a step-by-step example of how to do this.

     

    In this example we will create a new report that shows incidents which were created by a certain analyst or analysts.  It will prompt the report user to pick the analyst or analysts to display on the report.  It will only display analysts for the user to pick from and not any other types of user record.

     

    • 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_analyst, and tps_user.
    • Link the tables as per below:

     

    user report linking.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_user.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_user.tps_name.
    • In Database - Select Expert - Record selection select tps_user.tps_name is equal to and then select the name of your 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