Administration Guide › Reporting › How to Create and Run Custom Reports › Create the Report Parameter XML File › SQL

SQL

You can define SQL queries as part of a list box or drop-down box in the Report Parameter XML file. 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” will be provided to the report.

However, the condition of the first name that starts with “S” is a static one. This 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 a value that was previously 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"/>

and 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 IdentityMinder will replace ##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.