CA Identity Manager Administration Guide › Reporting › How to Create and Run a Custom Snapshot Report › Create the Report Parameter XML File › Define SQL Queries for the Parameter List Box

Define SQL Queries for the Parameter List Box

You can define SQL queries as part of a list box or drop-down box in the Report Parameter XML file. When you associate a parameter to the report and create a report task, the parameter is displayed in the list box or drop-down box to the user. To use SQL in the drop-down box or list box parameter, provide a valid SQL statement in the sql attribute.

Example:

<param id="lstlastname2" displaytext="Name" name="lstlastname2" type="sqlstr" multiselect="true" sql="select lastname, lastname from tblusers where firstname like 'S%/> 

In the previous example, all the last names of users with a first name that starts with S are provided to the report.

However, the condition of the first name that starts with S is a static one. This query is not flexible enough for a user to load the value-based on the parameter value entered in one of the previous screens that was used in the same report parameter group. In order to use the previous value that was entered in another screen, the SQL statement can be augmented with ##<parameter id>##.

For example, if you have a parameter with the id=User, which was of type String:

<param id="User" displaytext="First Name" name="firstname" type="string"/>

If you want to use the input value for that parameter in SQL, the SQL statement could be as follows:

<param id="lstlastname2" displaytext="Name" name="lstlastname2" type="sqlstr" multiselect="true" sql="select lastname, lastname from tblusers where firstname like '##User##’/> 

CA Identity Manager replaces ##User## with the value entered for the parameter with the id=User.

Note: The parameter value to be substituted cannot be in the same screen as the SQL parameter. For example, if the lstlastname2 is in screen 3, the User parameter should be in one of the previous screens.