Create a new report

You can create new reports using SQL and parameters. Using parameters in your SQL query allows you to run the same report against different values instead of creating multiple reports.

To create a new report:

  1. Sign in to the Identity Administration portal.
  2. Click Reports > New Report.

  3. Enter a name for your report.

    Names can contain letters, numbers, and underscores. Do not include special characters or white space.

  4. Use the Data Dictionary column drop-downs to define what you want the report to display.

    The data dictionary has a list of all tables. After you select a column, tables not valid for a join are automatically disabled. If more than two tables are joined, and one table no longer has any filters and columns, then only tables that can be joined together will remain selected and in the SQL. For example, if A is joined to B is joined to C, and B is removed, and A and C cannot be joined, then only A will remain in the SQL.

    Refer to https://developer.idaptive.com/docs/data-dictionary for more detail about the data dictionary.

    You can click the filter icon associated with each column to specify conditions for filtering on that column. Only the IN and NOT IN operations allows for multiple values.

  5. (Optional) Use the script editor to manually build your report.

    See Report query syntax for help with syntax.

    Write SQL queries that use parameters for arguments rather than concrete values. This will allow you to run the same report against different values. For example, you can write the following SQL query:

    select username, lastlogin from user where username like @userParam

    You now must define the “userParam” parameter to make use of the query.

  6. Click Parameters to specify parameters.

    Parameters allow you to define a report with different values. Parameters you specify must be paired with your SQL query. In our example, the SQL query in the above step uses the “userParam” parameter, so you must define the same parameter here.

  7. Click Settings to configure the report options:
    • Reports can be displayed on a map -- Enable this option to display the data on a map if the report is location related.
    • Validate reports on save (enabled by default) -- Enable this option to validate the SQL syntax when you save the report.

  8. Click Save.

    The report is saved to the My Reports folder.

When you run the report, you will be prompted for the parameter value/s that correspond to the parameter/s you have defined. For example, you can enter d% to get usernames starting with the letter “d” if you have defined a username parameter and written a corresponding SQL query.