5 Oracle Identity Manager Reporting

Oracle Identity Manager includes a custom reporting engine that enables you to run predefined reports against the Oracle Identity Manager transactional database or a secondary database if one is configured. You can add new reports without editing any Java code, and you can obtain reporting data by invoking a stored procedure.

This chapter discusses the following topics:

• Reporting Features

• List of Reports

• Exception Reports

• How to Create a Report

• Working with Third-Party Reporting Tools

• Date Ranges in Historical Reports

• Limitations

Note:

The Oracle Identity Manager reporting engine is not meant to be a replacement for enterprise reporting solutions. The Oracle Identity Manager reporting engine is not optimized for very large data volume and does not provide the rich features you would find in an enterprise reporting application.

For large-scale deployments, especially those taking advantage of the extensive auditing capabilities of Oracle Identity Manager, it is highly recommended that you deploy a dedicated enterprise-class reporting solution. A solution based on tools such as Oracle Business Intelligence Enterprise Edition can provide the flexibility, automation, and performance required for a large-scale enterprise.

Reporting Features

The following are Oracle Identity Manager reporting features:

• Select and view reports from a predefined list in the Administrative and User Console.

• Use a delegated administration model to control the reports available to a user and the information included in those reports.

• Filter report information.

• View reports on-screen.

• Export reports to CSV files.

• Provide interactive reports.

• Run reports from a secondary database.

• Provide support for Crystal Reports.

The following sections explain data storage for reporting at the data, XML, and API layers in Oracle Identity Manager.

• Data Layer

• XML Metadata

• API Layer

Data Layer

You can change the database schema and add stored procedures in the data layer.

The REP and RPG tables support reporting. The REP table contains the following:

• A list of all reports in the system

• The report name

• The report code

• The report type

• The report description

• The name of the stored procedure for the report

• The name of the data source

• The maximum report size

• The number of filters to be displayed on the report page

• XML metadata for each report

The RPG table is a link table between the REP and UGP table. This table stores information about the reports and the groups that have access permissions for the reports.

Each report is associated with a stored procedure. To run a report, you run the associated stored procedure with relevant arguments. You cannot run a report based on a database query.

There can be many reports in the system. As a result, the stored procedure follows rules to enable the report to be invoked generically. See "How to Create a Report" for details.

Each stored procedure has a set of required generic parameters. These parameters provide standard information, for example, starting row, page size, filter columns, and so on. The stored procedure can also have any number of report-specific parameters. Each stored procedure returns two values: a result set representing a page of the entire report data, and a total count for the report data. The standard format for a stored procedure and the XML metadata enable you to add and run any report without changing any Java code.

XML Metadata

XML metadata for each report is stored in the REP table for the report. The metadata provides the following information for the report:

• Layout information

• Representation of all the input parameters and their association with the corresponding stored procedure parameters

• Support for user-defined parameters (operational reports only)

• Display information for each report input parameter, for example, a field label or field type (for example, TextField, LookupField, and so on)

• Location of each column on the report display page

• Display information for each report data column

• Columns to be included in the filter drop downs

• Clickable columns for interactive reports

For details on the metadata structure, see "How to Create a Report".

API Layer

The API layer provides all back-end reporting functionality. The back end is not tied to the front end. You can create custom user interfaces by using the reporting APIs.

List of Reports

This section lists the operational and historic reports that are available.

Table 5-1 lists the names and description of the operational reports.

Table 5-1 List of Operational Reports

Name	Description
Entitlements Summary	Lists the number of users for each status within each resource.
Policy List	Displays a snapshot of all policies defined within the system.
Delegated Administrators By Organization	Lists all the delegated administrator user groups for organizations.
Attestation Requests by Reviewer	Lists attestation requests by reviewer.
Approval Status By Approver	Provides a summary of all approval tasks.
User Resource Access	Lists the access rights to resources for selected users.
Resource Access List	Lists all users who have access to a selected resource.
Policy Detail	Lists complete details about specific policies defined within the system.
Group Membership Profile	Lists the number of users in different numbers of groups.
OIM Password Expiration	Lists users whose Oracle Identity Manager passwords are about to expire.
Group Membership	Provides a snapshot of users in each group.
Resource Password Expiration	Lists users whose resource passwords are about to expire (as determined by Oracle Identity Manager).
Organization Structure	Lists the hierarchical organization structure and user memberships.
Requests Initiated	Lists all requests initiated in a specified time interval.
Requests Details By Status	Returns details of all requests with a specified status.
Attestation Process List	Provides a snapshot of all defined attestation processes.
Attestation Requests by Process	Lists attestation requests by process.
Attestation Request Detail	Lists complete details of selected attestation requests.
Financially Significant Resources	Lists complete details of financially significant resources.
Delegated Administrators & Permissions By Organization	Lists all administrator user groups and permissions for organizations.
Delegated Administrators & Permissions By Resource	Lists all administrator user groups and permissions for resources.
Delegated Administrators By Resource	Lists all administrator and authorizer user groups for resources.

Table 5-2 lists the names and description of the historical reports that are available.

Table 5-2 List of Historical Reports

NAME	DESCRIPTION
User Resource Access History	Returns the history of a user's resource access.
Resource Access List History	Returns a history of all users who have had access to a selected resource.
User Profile History	Returns the history of a user's profile.
User Membership History	Returns the history of a user's memberships in a user group.
Group Membership History	Returns the history of a group's memberships.
Resource Activity	Returns the history of all provisioning and approval activities for a resource.
Task Assignment History	Returns the history of all task assignment based on the tasks.
Password Reset Success Failure	Returns the password change metrics for Oracle Identity Manager users.
Account Activity In Resource	Lists all account activities in each resource.
Rogue Accounts By Resource	Lists all the rogue accounts in each resource.
Fine Grained Entitlement Exceptions By Resource	Lists all fine-grained entitlement exceptions by a resource.
Users Created	Lists all users created in a specified time interval.
Users Deleted	Lists all users deleted in a specified time interval
Users Disabled	Lists all users disabled in a specified time interval.
Users Unlocked	Lists all users unlocked in a specified time interval.

See Also:

These reports are also listed in Chapter 14 of the Oracle Identity Manager Administrative and User Console Guide.

Exception Reports

In Oracle Identity Manager, exception refers to the difference between accounts that a user is entitled to and the accounts that are actually assigned to a user. The user is assigned these accounts as a result of access policies, provisioning of resources, approval requests, and reconciliation events. Any difference of these accounts assigned to a user in the target system and the ones assigned to the user in Oracle Identity Manager comprises an exception.

The following exception reports have been introduced in this release:

• Rogue Accounts By Resource

  This report returns a list of all the rogue accounts existing in a resource. The following exceptions are reported:

  • An account that exists in the target system but is not provisioned to the corresponding user in Oracle Identity Manager

  • An account that exists in the target system but has been deprovisioned for the corresponding user in Oracle Identity Manager

  • An account that exists in the target system but the corresponding user to whom the account is provisioned has been deleted in Oracle Identity Manager

  The following exception is not reported in spite of being a rogue account:

  • An account that exists in the target system but the corresponding user to whom the account is provisioned has never existed in Oracle Identity Manager

• Fine Grained Entitlement Exceptions By Resource

  This report returns a list of all the accounts in a resource for which the process form data being reconciled is different from the expected values. It means that this report returns any account existing in the target system that is also provisioned to the corresponding user in Oracle Identity Manager, but for which the process data does not match.

  Note:

  • After completion of initial target reconciliation, all account-related activities performed directly on a target resource are tracked as exception activity. Account-related activities include account creation, account modification, and entitlement assignment/revocation. The exception reports should be used only if the organization policies enforce that all account-related activities in target resources would always be initiated in Oracle Identity Manager. In addition, remember that exception detection and recording are an extension of account data reconciliation and, therefore, may result in a drop in performance during reconciliation.

  • Both the exception reports depend on reconciliation data. Therefore, these reports will not display any data if the corresponding reconciliation events are archived.

By default, these two exception reports are not enabled. In order to enable them, you must set the value of the XL.EnableExceptionReports property to TRUE in the system configuration. If the value of the property is FALSE (default value), then the exception reports will not be available on the Historical Reports page of the Administrative and User Console. As a result, the data for the exception reports will not be populated in the RCX, UPA_UD_FORMS, and UPA_UD_FORMFIELDS tables. These reports rely on reconciliation data to be populated.

Suppose you do not enable these exception reports immediately after Oracle Identity Manager installation. Instead, you enable them sometime after you start using Oracle Identity Manager. In such a scenario, the UPA_UD_FORMS, and UPA_UD_FORMFIELDS tables must be populated with baseline form data before you can run the exception reports. To do this, you must use the UPA Form Data Upgrade Utility. See "Using the UPA Form Data Upgrade Utility" for instructions.

Using the UPA Form Data Upgrade Utility

The UPA Form Data Upgrade Utility populates the UPA_UD_FORMS and UPA_UD_FORMFIELDS tables with baseline data that acts as a starting point for the exception reports. You must run this utility whenever you enable the exception reporting feature. This utility runs through all the entitlements. For provisioning instances that have a resource with either a process or an object form attached, the utility reads the data on the form and stores them in the two database tables. This forms the baseline. Subsequent changes to the form are automatically captured because exception reporting is enabled. With the baseline available, the exception reports start reporting on exceptions. The utility creates a log file named UPA_Form_Data_Upgrade_Timestamp.log after the process is complete.

To use the UPA Form Data Upgrade Utility:

1. Copy the UPA Form Data Upgrade Utility files to your local computer.

   Before using the utility, copy the UPAFormDataUpgradeUtility directory from the following directory on the installation media to your local computer:

   For Oracle Database:

   installServer/Xellerate/db/oracle/Utilities/UPAFormDataUpgradeUtility
   

   Note:

   In the rest of this procedure, the full path and name of the local directory containing the UPA Form Data Upgrade Utility files is referred to as UPAFORMDATAUPGRADEUTILITY.

2. Configure the scripts.

   Settings for the Oracle Database Batch/Shell file:

   • For Microsoft Windows: Edit the following file:

     UPAFORMDATAUPGRADEUTILITY\UPAFormDataUpgrade.bat
     

   • For Linux or UNIX: Edit the following file:

     UPAFORMDATAUPGRADEUTILITY/UPAFormDataUpgrade.sh
     

   Table 5-3 shows the values of the variables that must be set before you run the utility:

   Table 5-3 Variables of the UPA Form Data Upgrade Utility

   Variables	Description
   ORACLE_HOME	Oracle home directory.
   OIM_DB_REMOTE	Describes if the database is running on a remote computer. Set a value for this parameter only if OIM_DB_REMOTE = Y or OIM_DB_REMOTE = N.
   OIM_DB_ORACLE_SID	SID of the database. Set a value for this parameter only if OIM_DB_REMOTE = N.
   OIM_DB_SERVICE_NAME	TNS service name that points to the remote database. Set a value for this parameter only if OIM_DB_REMOTE = Y.

   
   

3. Compile the stored procedure.

   For Oracle Database, perform the following steps:

   1. Log in to SQL*Plus with the credentials of the Oracle Identity Manager release database schema owner.

   2. Open the following file in a text editor:

      UPAFORMDATAUPGRADEUTILITY/compile_all_XL_SP_UPA.sql
      

   3. Make the following changes in the compile_all_XL_SP_UPA.sql script:

      • Change:

        @@XL_SP_LoadFormData.sql
        

        To:

        @UPAFORMDATAUPGRADEUTILITY/XL_SP_LoadFormData.sql
        

      • Change:

        @@XL_SP_UPA_FormData_Upgrade.sql
        

        To:

        @UPAFORMDATAUPGRADEUTILITY/XL_SP_UPA_FormData_Upgrade.sql
        

   4. Run the compile_all_XL_SP_UPA.sql script.

      UPAFORMDATAUPGRADEUTILITY/compile_all_XL_SP_UPA.sql
      

4. Run the utility.

   • For Oracle Database on Linux or UNIX:

     Run the UPAFormDataUpgrade.sh file from the following location:

     UPAFORMDATAUPGRADEUTILITY/UPAFormDataUpgrade.sh OIM_DB_User_ID OIM_DB_User_Password
     

     Note:

     On UNIX, you might also want to clear the command history of the shell by running the following command:

     history -c
     

   • For Oracle Database on Microsoft Windows:

     Run the UPAFormDataUpgrade.bat file from the following location:

     UPAFORMDATAUPGRADEUTILITY\UPAFormDataUpgrade.bat OIM_DB_User_ID OIM_DB_User_Password
     

How to Create a Report

The following are tasks for creating a new report:

• Writing the Stored Procedure

• Creating the Report XML Metadata

• Modifying Property Files for Translating Label Names, Report Names, and Report Descriptions

• Creating an REP Entry and Providing Access to the Report

Writing the Stored Procedure

Each report is based on a single stored procedure. The following are rules for the stored procedure:

• Use a stored procedure, not a user-defined function.

• A stored procedure returns two values: the report data result set and the total number of rows in the report.

• The report result set is paged.

  The result set that is returned when you run the stored procedure represents one page of the entire report data. The starting row and the size of the page is specified at the time of running the stored procedure.

• The stored procedure handles filter parameters and user defined input parameters.

Each stored procedure uses generic parameters, and each can use parameters specific to the stored procedure. These parameter types are described in the following sections.

Generic Parameters

Generic parameters are common to all the stored procedures. You specify generic parameters in a required sequence before any specific parameters.

There are twelve generic parameters. All twelve generic parameters are required, even if their values are null. The following are the generic parameters in the order that you must specify them:

1. Report Result Set (type=cursor, OUT): The result set that represents the report data.

   Note:

   - For Microsoft SQL Server, the return type is Integer (int) and not cursor. In Microsoft SQL Server, data is returned in the last query. There is no actual return parameter. This parameter type meets the requirements of the stored procedure query.

   - Microsoft SQL Server is not supported in Oracle Identity Manager release 9.1.0. See ÒCertified ComponentsÓ in Oracle Identity Manager Release Notes for information about certified components.

2. User Key (type=int, IN): The key of the user who runs the report.

   This user key ensures that only records are returned for which the user has read permissions.

3. Sort Columns (type=varchar, IN): A list of comma-delimited column names on which the report result set can be sorted.

   Reserved for future use.

4. Sort Order (type=varchar, IN): The sort order (ascending or descending) for the report result set.

   Reserved for future use.

5. Start Row (type=int, IN): The row number where the result set starts.

6. Page Size (type=int, IN): The size of the result set, or the number of entries in a single page in a multi-page report.

7. Do Count (type=int, IN): Can have values 0, 1, or 2.

   A value of 0 indicates that the result set is computed and returned, but the total number of rows in the entire report data is not computed. A value of 1 indicates that the result set and the total number of rows are computed. When the value is 2, only the total number of rows is computed and an empty result set is returned.

8. Total Rows (type=int, OUT): This is an OUT parameter that returns the total number of rows when the value of the Do Count variable is either 1 or 2.

   Because the report data is paged, the value of the total number of rows is not the size of the returned result set. It is the size of the entire report.

   For example, suppose that a stored procedure returns a list of all users. There are 200 users in the system, the start row=1, and the page size=50. For this stored procedure, the size of the result set is 50, but the value of the total rows OUT parameter is 200.

9. Filter Column Names (type=varchar, IN): This is a comma-delimited list of column names on which the report data can be filtered.

   A stored procedure has no way of knowing the alias to use for the listed columns. The stored procedure expects that the column names in this list are correctly qualified with the appropriate table aliases, if needed, for example:

   usr.usr_first_name,obj.obj_name

10. Filter Column Values (type=varchar, IN): This is a comma-separated list of column values that have a one-to-one correspondence with the column names listed in the previous parameter.

    If the previous parameter contains a comma-separated list with two column names, this parameter is a comma separated list of two values. You can use a wild card (%) character, for example, Jo%,Laptop.

11. User-Defined Column Names (type=varchar, IN): This is a comma-separated list of column names that represent user-defined columns on system forms.

    These names must be appropriately aliased if needed, for example:

    USR.USR_UDF_SSN

12. User-Defined Column Values (type=varchar, IN): This is a comma-delimited list of column values for user defined fields.

    These values have a one-to-one correspondence with the column names listed in the previous parameter. You can use the wild card (%) character, for example:

    1234567890

Specific Parameters

Specific parameters are specific to each report. These parameters have a one-to-one correspondence with the report input parameters, except for the date range input parameter and user-defined parameters.

You must add specific parameters after the generic parameters. Each specific parameter represents one report input parameter on the Report Input page.

All specific parameters that are of the varchar2 type support the wild card (%) character.

Other Stored Procedure Notes

Each time an error is encountered, an exception is thrown. The exception contains an error code. The calling Java code receives the error as a SQLException with the error code embedded in it. The stored procedure checks the code for errors based on the following rules:

• The value of Start Row cannot be 0 or null.

• The value of Page Size cannot be 0 or null.

• The value of User Key cannot be 0 or null.

• The value of Do Count can only be 0, 1 or 2.

• There is a one-to-one mapping between Filter Column Names and Filter Column Values.

• There is a one-to-one mapping between User-Defined Column Names and User-Defined Column Values.

Even if there is no data to return for a report, an empty result set is returned.

Example of a Stored Procedure Signature

Example 5-1 shows the signature for the User Resource Access report stored procedure for Oracle Database:

Example 5-1 Signature of the User Resource Access Stored Procedure for Oracle Database

PROCEDURE XL_SP_UserResourceAccess (
    csrresultset_inout            IN OUT   sys_refcursor,
    intuserkey_in                 IN       NUMBER,
    strsortcolumn_in              IN       VARCHAR2,
    strsortorder_in               IN       VARCHAR2,
    intstartrow_in                IN       NUMBER,
    intpagesize_in                IN       NUMBER,
    intdocount_in                 IN       NUMBER,
    inttotalrows_out              OUT      NUMBER,
    strfiltercolumnlist_in        IN       VARCHAR2,
    strfiltercolumnvaluelist_in   IN       VARCHAR2,
    strudfcolumnlist_in           IN       VARCHAR2,
    strudfcolumnvaluelist_in      IN       VARCHAR2,
    struserlogin_in               IN       VARCHAR2,
    strfirstname_in               IN       VARCHAR2,
    strmiddlename_in              IN       VARCHAR2,
    strlastname_in                IN       VARCHAR2,
    struseremail_in               IN       VARCHAR2,
    strorgname_in                 IN       VARCHAR2,
    strusergroup_in               IN       VARCHAR2,
    strmgrfirstname_in            IN       VARCHAR2,
    strmgrlastname_in             IN       VARCHAR2,
    struserstatus_in              IN       VARCHAR2,
    struseremptype_in             IN       VARCHAR2
)

In Example 5-1, the first twelve parameters (upto strudfcolumnvaluelist_in) are generic parameters. The remaining parameters are specific parameters.

Example 5-2 illustrates the Microsoft SQL Server signature for the User Resource Access stored procedure:

Note:

Microsoft SQL Server is not supported in Oracle Identity Manager release 9.1.0. See ÒCertified ComponentsÓ in Oracle Identity Manager Release Notes for information about certified components.

Example 5-2 Microsoft SQL Server Signature for the User Resource Access Stored Procedure

CREATE PROCEDURE  XL_SP_UserResourceAccess
(@csrResultSet_inout                  INT OUTPUT,
@intUserKey_in                        INT,
@strSortColumn_in                     VARCHAR(4000),
@strSortOrder_in                      VARCHAR(4000),
@intStartRow_in                       INT,
@intPageSize_in                       INT,
@intDoCount_in                        INT,
@intTotalRows_inout                   INT OUTPUT,
@strFilterColumnList_in               VARCHAR(8000),
@strFilterColumnValueList_in          VARCHAR(8000),
@strudfcolumnlist_in                  VARCHAR(8000),
@strudfcolumnvaluelist_in             VARCHAR(8000),
@strUserLogin_in                      varchar(256),
@strFirstName_in                      varchar(80),
@strMiddleName_in                     varchar(80),
@strLastName_in                       varchar(80),
@strUserEmail_in                      varchar(256),
@strorgname_in                        varchar(256),
@strUserGroup_in                      varchar(30),
@strMgrFirstName_in                   varchar(80),
@strMgrLastName_in                    varchar(80),
@strUserStatus_in                     varchar(25),
@strUserEmptype_in                    varchar(255)
)

Creating the Report XML Metadata

After you create the stored procedure, you create the XML metadata for the report. All report-specific information is included in the metadata so that the report can be run and displayed correctly. The report metadata provides information such as attributes of the report-specific input parameters, the display properties of the report input parameters and the display properties of the report data, for example, report layout, display labels, and so on.

The root element of the metadata is report. This element provides the layout of the report. Three display layouts are supported: Tabular layout, Sectional layout, and Sectional with Report Header layout.

The report element has two child elements: StoredProcedure and ReturnColumns.

StoredProcedure Element

The StoredProcedure element provides information about the stored procedure-specific parameters and user defined fields. It consists of a single InputParameters element that contains multiple InputParameter elements.

Each specific stored procedure parameter corresponds to one input parameter on the Report Input page. The exception to this rule is the DateRange field, which is represented by two stored procedure parameters. Each input parameter is represented by one InputParameter element. The Report Input page can also contain user-defined fields from any system form. Each user-defined field on the Report Input page is also represented by one InputParameter element. The number of user-defined fields can change, but the number of stored procedure input parameters does not change each time. User-defined fields are represented by comma-delimited lists.

For example, suppose a report must support seven input parameters. Two parameters are user-defined fields, and five are specific parameters in the signature of the stored procedure (in addition to the twelve generic parameters). These are represented by five InputParameter tags. The two user-defined parameters are also represented by two InputParameter tags, but they do not have corresponding parameters in the stored procedure signature. Instead, they are passed as comma-delimited lists of column names and their values. Therefore, the metadata must contain a total of seven InputParameter tags.

If you want to support the DateRange input type, the Report Input page must be supported by two stored procedure parameters: one for the From date and the other for the To date.

The following are the attributes of the InputParameter element:

• name (required:Yes): The name of the input parameter.

  In case of non-user-defined input parameters, this value can be anything. However, for clarity, it should match the name of the corresponding stored procedure input parameter. For user-defined input parameters, this name is the column name of the user-defined column, prefixed by the required table alias, if needed.

• parameterType (required:Yes): The SQL data type of the corresponding stored procedure parameter.

  In case of user-defined input parameters, this value is varchar.

• order (required:Yes): The order of the report-specific input parameters.

  The ordering starts from 1. You must list the regular input parameters, and the user-defined input parameters later.

• fieldType (required:Yes): The type of the display field on the Report Input page.

  The supported input types are: TextField, Date, DateRange, LookupField, and Combobox.

• fieldLabel (required: Yes): The property value of the field label for this field.

  The property value is a value from the message resources property file (xlWebAdmin.properties in this case), which represents the actual label.

• allowedValues (required: No, unless fieldType is Combobox): Lookup codes associated with a combo box.

• required (required: No, default: false): If set to true, then the user must provide a value for this field for the report to run.

• udf (required: No, default:false): For a user defined field that is represented by the InputParameter element, this attribute must be present and have a value of true. Operational reports only.

If the attribute fieldType has a value of LookupField, a child element called ValidValues must be under the InputParameter element. The reporting functionality supports three types of lookups: Lookup by code, lookup by method, and lookup by column. The following are the supported attributes of the ValidValues element:

• lookupCode (required: No): Must be present only if the lookup is by code.

  If it is, the value of this attribute is the lookup code.

• lookupColumn (required: No): Must be present only if the lookup is by column.

  If it is, the value of this attribute is the column code of the lookup column.

• lookupMethod (required: No): Must be present only if the lookup is by class or method.

  If it is, the value of this attribute is the name of method that provides the lookup values.

• operationClass (required: No): Must be present only if the lookupMethod attribute is present.

  The value of this attribute is the fully qualified name of the class that contains the lookup method.

• displayColumns (required: No): Must be present only if the lookupMethod attribute is present.

  It is a comma-delimited list of column codes which represent columns that is displayed in the lookup.

• selectionColumn (required: No): Must be present only if the lookupMethod or lookupColumn attributes are present.

  It represents the column code of the column, the value of which is saved in the database.

Examples of InputParameter tags

• Regular TextField input parameter:

  <InputParameter name="strfirstname_in" parameterType="varchar2" order="2" fieldType="TextField" fieldLabel="report.userResourceAccess.label.firstName" required="false" />
  

• User-Defined input parameter:

  <InputParameter name="USR.USR_UDF_SSN" parameterType="varchar2" order="11" fieldType="TextField" fieldLabel="report.userResourceAccess.label.SSN" required="false" udf="true" />
  

• Input parameter of type Combobox:

  <InputParameter name="struserstatus_in" parameterType="varchar2" order="10" fieldType="Combobox" allowedValues="Lookup.WebClient.Users.Status" fieldLabel="report.userResourceAccess.label.userStatus" required="false" />
  

• Input parameter of type LookupField with lookupCode:

  <InputParameter name="struseremptype_in" parameterType="varchar2" order="11" fieldType="LookupField" fieldLabel="report.userResourceAccess.label.employeeType" required="false" >  <ValidValues lookupCode="Lookup.Users.Role"/> </InputParameter>
  

• Input parameter of type LookupField with lookupColumn

  <InputParameter name="struseremail_in" parameterType="varchar2" order="5" fieldType="LookupField" fieldLabel="report.userResourceAccess.label.userEmail" required="false" >         <ValidValues lookupColumn="Users.Xellerate Type" selectionColumn="Lookup Definition.Lookup Code Information.Decode" /></InputParameter>
  

• Input parameter of type LookupField with lookupMethod

  <InputParameter name="struserlogin_in" parameterType="varchar2" order="1" fieldType="LookupField" fieldLabel="report.userResourceAccess.label.userLogin" required="false" >         <ValidValues lookupMethod="findUsersFiltered"  
     operationClass="Thor.API.Operations.tcUserOperationsIntf"
     displayColumns="Users.User ID,Users.Last Name,Users.First Name"
     selectionColumn="Users.User ID"/> </InputParameter>
  

If the attribute fieldType has a value of DateRange, you must include the two child elements InputStartDate and InputEndDate. Each element must have the following attributes:

• name (required: Yes): The name of the parameter.

  This should match the name of the stored procedure parameter that represents this date parameter.

• parameterType (required: Yes): The SQL type of the stored procedure parameter that represents this date parameter.

• order (required: Yes): The order of the stored procedure parameter

• defaultValue (required: No, default: 01/01/1900 and 12/31/2049): The default value to provide if the user does not enter a date in the start or end date fields.

• format (required: No, default: reports.generic.message.internalDateFormat): The format of the default date.

ReturnColumns tag

The ReturnColumns tag represents the list of all the columns that are being returned by the result set. This tag contains multiple ReturnColumn tags, each of which represents one column in the returned result set. The attributes of the ReturnColumn tag provides information that is useful for displaying the report data.

The following are the attributes of the ReturnColumns tag:

• name (required: Yes): This name represents the column code of the result set column that is represented by this particular tag.

  If the column code is not available, it can be the alias or the column name.

• label: (required: Yes): This provides the property value of the column header/label for this column.

  The property value is a value from the message resources property file (xlWebAdmin.properties in this case), which represents the actual label.

• position (required: Yes): This attribute can have three values: Table, Sectional Header, or Report Header.

  This attribute specifies the location of each column. Each column can reside in the table (for any layout), the section header (for Sectional Layout and Sectional with Report Header Layout), or the report header (in case of Sectional with Report Header layout).

• filterColumn (required: No, default: false): This attribute specifies whether the column is a filter column.

  If the value is true, then the column name is included in the filter drop down lists at the top of the Report Display page.

• filterColumnName (required: No, unless filterColumn is present): This attribute represents the actual name of the column prefixed by the table alias, if needed.

• filterType (required: No, unless filterColumn is false): The type of the filter display field on the Report result page. The only supported input type is Combobox.

• filterLookupKey (required: No, unless filterColumn is false): Represents a lookup code associated with a combo box that is shown for a filter display field on the Report result page.

• clickable (required: No, default: false): This attribute specifies whether the column value is a link.

  This attribute provides support for action-ability of reports.

You can configure interactive reports in the report module. In an interactive report, you define column values to be links that take the user to a page in or outside of the Administrative and User Console when he or she clicks the column value. To configure the links, you add information to the metadata so that the user is taken to the appropriate page. The links can have dynamic or static locations. Clicking on a link opens a new browser window that shares the same browser session but is otherwise self-sufficient.

To configure the links, set the clickable attribute to true, and configure the ReturnColumn element and its two child elements Link and RequestParameters, as follows:

• Link element: Configure the single attribute href for this element.

  This attribute specifies the base URL of the destination page. An absolute URL begins with http, for example, http://www.xyz.com. The destination page for an absolute URL is outside the Administrative and User Console. A relative URL, for example, searchResources.do, leads to a destination page in the Administrative and User Console.

• RequestParameters element: Configure multiple RequestParameter elements for this element.

  Each RequestParameter element represents one request parameter that must be submitted for the destination page to display properly. The name attribute specifies the name of the request parameter. If the request parameter is static, that is, it does not depend on any other value in the result set, you configure its value by using the value attribute. If the value of the request parameter is dynamic, that is, it depends on another column of the result set, for example, the Resource Key, then you specify the value by using the column attribute. The column attribute contains the column code for the column whose value is to be returned.

Creating an REP Entry and Providing Access to the Report

Once the report metadata is complete, update the REP and RPG tables to make the report available.

Creating a New Entry in the REP Table

The REP table contains a list of all the reports in the system. Defining a new report requires creating a new row in the REP table representing this report. Apart from the common columns, for example, Create Date, Created By, Row Version, and so on, you must specify the columns that are populated. The values in the parentheses are examples for User Resource Access reports.

• REP_NAME: This column contains the name of the report that is displayed to the user.

  This name is unique in the REP table. (User Resource Access)

• REP_CODE: A unique code for the report. (UserResourceAccess)

• REP_DESCRIPTION: A description for the report that is displayed to the user. (Resource access rights for selected users).

• REP_SP_NAME: The name of the stored procedure that provides the data for the report. (XL_SP_UserResourceAccess)

• REP_XML_META: This column is a clob that contains the entire metadata for the report defined in the previous section.

• REP_TYPE: The type of the report. This can have two values: Operational or Historical. (Operational)

• REP_DATASOURCE: The name of the data source against which the report is run.

  This can have two values: Default or Reporting. Usually, the Operational reports are run against the Default database while the Historical reports are run against the reporting database. (Default)

• REP_MAX_REPORT_SIZE: This represents the maximum number of records a report can return.

  Different reports will return different amounts of data for each record. To ensure a fast response time for a report, a maximum number of records that a report can return is enforced for each report. (5000)

• REP_FILTER_COUNT: The number of filter drop downs on the Report Display page for this report (3).

The following is an example of the insert statement that populates the REP table with the User Resource Access report data for an Oracle Database:

INSERT INTO REP (REP_KEY, REP_CODE, REP_TYPE, REP_NAME, REP_DESCRIPTION, 
                 REP_DATASOURCE, REP_SP_NAME, REP_MAX_REP_SIZE, REP_FILTER_COUNT, 
                 REP_DATA_LEVEL, REP_CREATE, REP_CREATEBY, REP_UPDATE, 
                 REP_UPDATEBY, REP_ROWVER) VALUES (rep_seq.nextval, 'UserResourceAccess', 'Operational', 'User Resource Access','Resource access rights for selected users', 'Default', 'XL_SP_UserResourceAccess', 5000, 3, 1, SYSDATE, <System Administrator User Key>,  SYSDATE, <System Administrator User Key>, HEXTORAW('0000000000000000'));

The following is the example of the INSERT statement that populates the REP table with the User Resource Access report data in Microsoft SQL Server:

Note:

Microsoft SQL Server is not supported in Oracle Identity Manager release 9.1.0. See ÒCertified ComponentsÓ in Oracle Identity Manager Release Notes for information about certified components.

INSERT INTO REP (REP_CODE, REP_TYPE, REP_NAME, REP_DESCRIPTION, REP_DATASOURCE,  
                 REP_SP_NAME, REP_MAX_REP_SIZE, REP_FILTER_COUNT, REP_DATA_LEVEL,                 REP_CREATE, REP_CREATEBY, REP_UPDATE, REP_UPDATEBY, REP_ROWVER)
VALUES ('UserResourceAccess', 'Operational', 'User Resource Access',       'Resource access rights for selected users', 'Default', 'XL_SP_UserResourceAccess',       5000, 3, 1, GETDATE(), <System Administrator User Key>, GETDATE(), <System Administrator User Key>, 0x0);

Loading XML Metadata

After you create an entry in the REP table for the report, you must load the XML metadata for the report into the REP table's REP_XML_META column. See "Creating the Report XML Metadata" for information about creating metadata.

Providing a User Group with Access to a Report

The following procedure describes how to provide access to a report.

See Also:

Oracle Identity Manager Administrative and User Console Guide

To provide access to the new report to a particular user group:

1. Search for the user group by using the Manage User application.

2. Navigate to the detail page for the user group.

3. Click the Allowed Reports link in the additional details drop down.

   The Reports page under Group Detail is displayed.

4. Click the Assign Reports button.

   The Assign Reports page is displayed in the Reports section under Group Detail. The report you have just created is listed on this page.

5. To provide access to the report, assign it to at least one group.

Modifying Property Files for Translating Label Names, Report Names, and Report Descriptions

This section describes how to modify property files for translating label names, report names, and report descriptions. It contains the following topics:

• Adding Properties for Translating Label Names

• Adding Properties for Translating Report Names and Descriptions

Adding Properties for Translating Label Names

After you write the XML metadata, you must introduce the new field label properties in the metadata file in the properties files. You add the properties to the files corresponding to the locales that you want to support. The languages that are supported in Release 9.1.0 and their associated property files are as follows:

• Chinese (Simplified) (xlWebAdmin_zh_CN.properties)

• Chinese (Traditional) (xlWebAdmin_zh_TW.properties)

• Danish (xlWebAdmin_da.properties)

• English (xlWebAdmin_en_US.properties)

• French (xlWebAdmin_fr.properties)

• German (xlWebAdmin_de.properties)

• Italian (xlWebAdmin_it.properties)

• Japanese (xlWebAdmin_ja.properites)

• Korean (xlWebAdmin_ko.properties)

• Portuguese (Brazilian) (xlWebAdmin_pt_BR.properties)

• Spanish (xlWebAdmin_es.properties)

When Oracle Identity Manager encounters an unsupported locale, it refers to the xlWebAdmin.properties file. You add to these files the properties included as fieldLabel attributes of InputParameter tags and label attributes of ReturnColumn tags. Instead of providing the actual name of the input field labels or return column labels, you specify the property name, which is then looked up from the respective properties file. This makes it simpler for internationalization.

The following examples illustrate how to set the fieldLabel attributes of the InputParameter and ReturnColumn tags.

Example 1

<InputParameter name="struserlogin_in" parameterType="varchar2" order="1"
fieldType="TextField" fieldLabel="report.userResourceAccess.label.userLogin"
required="false" />

This InputParameter tag is from UserResourceAccess.xml metadata file for the User Resource Access report. The fieldLabel attribute of this tag has the value of report.UserResourceAccess.label.userLogin. The corresponding entry for this property in the xlWebAdmin_en_US.properties file is:

report.UserResourceAccess.label.userLogin=Userid

Example 2

<ReturnColumn name="Users.First Name" label="report.userResourceAccess.label.firstName"
 position="SectionHeader" filterColumn="true"
 filterColumnName="usr.usr_first_name" />

This ReturnColumn tag is from UserResourceAccess.xml metadata file for the User Resource Access report. The label attribute of this tag has the value of report.userResourceAccess.label.firstName. The corresponding entry for this property in the xlWebAdmin_en_US.properties file is:

report.userResourceAccess.label.firstName=First Name

Note:

If the actual label names change (as a result of customization, for example), the property names need not be changed.

Perform the following steps to edit the xlWebAdmin_en_US.properties file:

1. Extract the xlWebApp.war file in the OIM_HOME/webapp directory to a temporary directory.

2. Open in a text editor the xlWebAdmin_en_US.properties file in the temporary directory where you extracted the xlWebApp.war file.

3. Add the appropriate properties as fieldLabel attributes of InputParameter tags and label attributes of ReturnColumn tags.

4. Re-create the xlWebApp.war file, and copy it to the OIM_HOME/webapp directory.

Adding Properties for Translating Report Names and Descriptions

After you create an entry in the REP table, you must add translation properties for the report name and description to the properties files. This procedure is necessary to localize the English entries in the database and must be performed even if you only want to provide support for the English locale.

To add properties for translating report names and descriptions:

1. Extract the xlWebApp.war file in the OIM_HOME/webapp directory to a temporary directory.

2. Open in a text editor the xlDefaultAdmin.properties file from the temporary directory where you extracted the xlWebApp.war file.

3. In the xlDefaultAdmin.properties file, locate the global.resultSet.Reports.Report~Name property. This property identifies the names of all reports in the system. Each report assigned to the property is separated by the vertical bar (|) character. Append a vertical bar, and add the name of the new report to the property value. Ensure that there are no spaces between the pipe character and the report name, and that there are no trailing spaces following the report name.

4. Locate in the xlDefaultAdmin.properties file the global.resultSet.Reports.Report~Description property, which contains descriptions of all reports in the system. Each report assigned to the property is separated by a vertical bar (|) character. Append a pipe character and the description of the new report to the property value. Ensure that there are no spaces between the pipe character and the description, and that there are no trailing spaces following the description.

5. Open in a text editor the properties file representing the locale for which you want to translate report names and descriptions.

6. Add a global.resultSet.Reports.Report~Name.ReportName property, which identifies the report name and its localized value. The ReportName portion of the property name represents the name of the report. Spaces in the property name are represented by tildes (~). You assign a localized value to the property. For example, you add the following property to the xlWebAdmin_en_US.properties file to represent a report named Group Information:

   global.resultSet.Reports.Report~Name.Group~Information=Group Information
   

7. Add a global.resultSet.Reports.Report~Description.Report~Description property, which contains a report description along with and its localized value. The Report~Description portion of the property name represents the default report descriptions. Spaces in the report description are represented by tildes (~). You assign a localized value to the property. For example, you add the following property to the xlWebAdmin_en_US.properties file for a description of the Group Information:

   global.resultSet.Reports.Report~Description.Description~of~the~Group~Information~report=Description of the Group Information report
   

8. Add global.ReportName.Lookup.Report-Name and global.ReportDesc.Lookup.Report-Description lookup properties for each new report. The Report-Name and Report-Description portions of each property name represent the default report name and description, respectively. Spaces in the report description are represented by hyphens (-). You assign localized values to each property. For example, you add the following properties to the xlWebAdmin_en_US.properties file for the Group Information report:

   global.ReportName.Lookup.Group-Information=Group Information
   global.ReportDesc.Lookup.Description~of~the~Group~Information~report=Description of the Group Information report
   

9. Recreate the xlWebApp.war file and copy it to the OIM_HOME/webapp directory.

Working with Third-Party Reporting Tools

Third-party reporting tools can run reports against Oracle Identity Manager by using the provided stored procedures. You do not need to understand the data model or write queries for predefined reports. You can use any reporting tool for custom stored procedures and custom reports.

Information about the snapshot and changes are stored in XML form in the UPA table. A third-party XML reporting tool can generate reports from this table. If XML is not a desired format, the reporting tool can use the reporting tables related to the user profile audit feature to retrieve data: UPA_USR, UPA_FIELDS, UPA_GRP_MEMBERSHIP, UPA_RESOURCE, UPA_UD_FORMS, and YPA_UD_FORMFIELDS.

Date Ranges in Historical Reports

The historical reports accept two different date ranges as input parameters, the Data Snapshot Date Range and the Data Changes Date Range.

The Data Snapshot Date Range is actually the existing date range for historical reports. It has been labeled this release onward. It filters records depending on whether the records are applicable in the specified date range.

The Data Change Date Range is a new feature of this release. It filters the records depending on whether the records were changed in the specified date range.

You need to specify only one date range. The Data Snapshot Date Range is a superset of the Data Change Date Range. All changes in data are automatically included in the Data Snapshot Date Range.

Note:

If you specify values in both the date ranges, then the Data Change Date Range overrides the Data Snapshot Date Range and the latter is ignored.

Limitations

The following are the limitations of reporting for this release:

• If you use the filter or input parameters while retrieving reports, the "_" character behaves as a single character wildcard.

• If the "User Id reuse" system property is set to true, a user can be created with a same ID as that of a deleted user. In case you create a new user through this feature, all the reports in which the user data is displayed in a section would show the data for both deleted and active users in a single section. This is because both the users will share the same user ID.