DataSource: Retrieving Report Data

The <QueryParameters> element is used most often. QueryParameters is a map of argument values used to create the queryOptions object that controls the search. They can be specified based on report arguments, hard-coded values, or calculated values. QueryParameters contains a list of <Parameter> elements, each of which defines one of the criteria. These <Parameter>s are "anded" together to make the where clause.

<QueryParameters>
   <Parameter argument="correlatedApps" property="application.id"/>
   <Parameter defaultValue="false" property="identity.correlated" valueClass="Boolean"/>
   …
</QueryParameters>

There are several different options for specifying parameters in a set of QueryParameters. These options are described below, illustrated with example Parameters. Most of these examples (except where noted) were taken from the Entitlement Owner Access Review Live Report which queries against the sailpoint.object.CertificationItem object, so all of these parameters relate to that object.

• Referencing a report argument: generally processed as "property = argument"; this parameter looks for certificationItems with a parent.certification.certificationGroups.id value in the report argument "certificationGroups"
  
  <Parameter argument="certificationGroups" property="parent.certification.certificationGroups.id"/>

When arguments are multi-valued, parameters based on them are automatically evaluated with "in" rather than "equals."

• Specifying a default value: generally processed as "property = argument or defaultValue (if argument is null)"; this parameter looks for CertificationItems with a parent.certification.type equal to the report argument "type"; if none is provided, it defaults to the type "DataOwner"
  
  <Parameter argument="type" defaultValue="DataOwner" property="parent.certification.type" valueClass="sailpoint.object.Certification$Type"/>

This example also illustrates usage of the valueClass attribute; this attribute is not necessary for string or object comparisons but is for other types, such as enumerations (such as Type in this example), Booleans, Dates, Lists, etc.

• Specifying a hard-coded value: an attribute can also be hard coded to be evaluated against the defaultValue by not including an argument, as shown in this parameter from the Uncorrelated Accounts Report. This is processed as "property = defaultValue," in this case cast as valueClass (not required for strings).
  
  <Parameter defaultValue="false" property="identity.correlated" valueClass="boolean"/>

• Specifying different operations: this example illustrates how to create evaluation conditions other than equals (or in) relationships; operation can be specified as GT, GE, LT, or LE (greater than, greater than or equal to, less than, less than or equal to)
  
  <Parameter argument="createStartDate" operation="GT" property="parent.certification.created"/>

• Using a ValueScript: processed as "property = return value from ValueScript"; this parameter performs processing based on the argument to return a different value that should be used in the criterion; this example uses a ValueScript to get the application name that corresponds to the applicationID in the "application" report argument; in a ValueScript, the argument is accessed through the variable name "value."
  
  

<Parameter argument="applications" property="parent.application">
   <ValueScript>
      <Source>
         import sailpoint.object.*;
         import sailpoint.api.ObjectUtil;
         if (value != null){
            return ObjectUtil.convertIdsToNames(context, Application.class, value);
         }
         return null;
      </Source>
   </ValueScript>
</Parameter>

Since object references are stored in the customized report instance XML (and passed to report input arguments) as ID values and many comparisons need to be done based on name, this convertIdsToNames() utility method is frequently used in ValueScripts in the standard reports.

• Using a QueryScript: used to specify any custom filter and add it into the queryOptions object that is used in the datasource filter; parameters using a QueryScript do not need to specify a property because the queryScript overrides any property on the parameter; the argument specified on the parameter can be accessed within the script through the "value" variable
  
  Group and populations are stored in groupDefinitions objects as a filter, so this example (from the Identity Forwarding Report) shows how a group or population selected as a report parameter is built into the datasource filter through a QueryScript.
Copy

<Parameter argument="groupDefinitions">
   <QueryScript>
      <Source>
         import sailpoint.object.*;
         import sailpoint.reporting.*;
         Filter f = ReportingLibrary.getGroupDefinitionFilter(context, value, false);
         if (f!=null) {
            queryOptions.addFilter(f);
         }
         return queryOptions;
      </Source>
   </QueryScript>
</Parameter>

• ValueRule and QueryRule: These two can be specified in place of ValueScript and QueryScript, respectively, to encapsulate the beanshell of a script into a reusable rule. (These two examples were not pulled from a standard report; they represent the appropriate syntax if the reports using the ValueScript and QueryScript specified above had encapsulated those scripts into rules.)
  
  

<Parameter argument="applications" property="parent.application">
   <ValueRule>
      <Reference class="sailpoint.object.Rule" id="4028460238ed9b8e0138ed9beff9090f" name="App Value Rule"/>
   </ValueRule>
</Parameter>

<Parameter argument="groupDefinitions">
   <QueryRule>
      <Reference class="sailpoint.object.Rule" id="4028460238ed9b8e0138ed9beff90900" name="Group Query Rule"/>
   </QueryRule>
</Parameter>

Another way to specify the filter contents is though a <Query> element. The contents of Query element are specified as a filter string and can only specify hard-coded criteria with no variable substitution (i.e. report arguments cannot be specified within a Query element). Query allows the specification of "or" criteria, as shown in the example below:

<Query>IdentityEntitlement.name=="assignedRoles" || IdentityEntitlement.name=="detectedRoles"</Query>

Query and QueryParameters can be specified for the same DataSource. When both are specified, the Query filter and the Parameter filters are "anded" together to create the final where clause.

QueryScript creates a filter string through a beanshell script. It is designed so it can append additional criteria, including those requiring variable substitution, onto a Query element's contents. The script has access to the string value of the Query element (in a string variable called "query") and must explicitly append the additional criteria to it; otherwise, the original query string is overwritten with the QueryScript's return value. The QueryScript shown below actually comes from an HQL datasource report (the Account Group Membership Totals Report), but the QueryScript syntax is the same for all datasource types.

<QueryScript>
   <Source>
      import java.util.*;
      
      List applications = args.get("application.id");
      if (applications != null &amp;&amp; !applications.isEmpty()){
         query = query + " and application.id in(:application_id) ";
      }
      return query;
                  
    </Source>
</QueryScript>

When the search must access more than one object to process the filter criteria, a <Join> element is required to connect the objects properly. One or more Joins can be specified for a single datasource.

For example, the Identity Roles Report displays the roles that each Identity is assigned. Most of the available filters for the report apply to the Identity object, but the role assignment is recorded on the IdentityEntitlement object, linked to the Identity object by the Identity ID. The Join element specifies that connection. The property is the value on the primary object (the DataSource objectType) and the joinProperty specifies the connection attribute on the second object.

<DataSource objectType="Identity" type="Filter">
   <Join joinProperty="IdentityEntitlement.identity.id" property="id"/>
   <Query>IdentityEntitlement.name=="assignedRoles" || IdentityEntitlement.name=="detectedRoles"</Query>
   <QueryParameters>
      <Parameter argument="identities" property="id"/>
      …
   </QueryParameters>
</DataSource>

The final elements available on a filter datasource are an OptionsRule or OptionsScript. These can be used to make modifications to the QueryOptions before the query is run; they can also replace the rest of the query specification (for example, eliminating the need for a Query, QueryParameters, QueryScript or Join element) by simply constructing the whole queryOptions in the rule or script.

Only one of these can be specified (the rule overrides the script if both are provided). The OptionsRule or OptionsScript is passed a SailPoint Context called "context," a queryOptions called "options" and an argument map called "args." Options contains the entire set of query criteria specified in any of the other elements (Query, QueryScript, QueryParameters, Join) and args contains the TaskDefinition argument map. The rule or script should append any additional custom queryOptions to options and return it.

<OptionsScript>
    <Source>
       import java.util.*;
       import sailpoint.object.*;
       //code to add components to queryOptions goes here. e.g.: this would
       // Apply to an Identity objectType and would get only Identities whose
       // Manager is the Identity selected in the manager filter (typically,
       // an optionsScript or optionsRule would be used for a more complex
       Filter myFilter = Filter.eq("manager.id", args.get("manager.id");
       options.addFilter(myFilter);
       return options;
     </Source>
 </OptionsScript>

An OptionsRule is specified as a reference to a Rule object:

<OptionsRule>

    <Reference class="sailpoint.object.Rule" id="4028460238ed9b8e0138ed9beff90900" name="MyReport Options Rule"/></OptionsRule>

The Query element can also specify some or all of the where clause. As on a Filter DataSource, the Query element can specify any hard-coded attribute evaluations (i.e. no variable substitution available) and multiple conditions can be specified with "and" or "or" relationships.

      <Query>from ManagedAttribute m where group=true</Query>

The HQL DataSource <QueryScript> element works just like the Filter Datasource QueryScript. It contains beanshell that returns a filter string (appending to the Query's string and returning the combined string value). However, the difference in QueryParameter processing changes the way variables are processed in the script. The queryScript has access to the task argument map (in its "args" variable), so conditional processing can be done on those arguments in determining how to build the filter string. However, the contents of those variables do not need to be built into the actual query string in the queryScript; they can be referenced as variable names that are passed to the search through QueryParameters. In an HQL datasource, the search is performed based on the query string built in the query and queryScript elements; the parameters specified as QueryParameters are passed to the search method along with that query string and are substituted into the query where variable names are found.

In the example below (from the Account Group Membership Totals Report), the QueryScript examines the application.id value from the args list and if it is non-null, it appends "and application.id in (:application_id)" to the query string. The QueryParameter application_id allows the list of applications from the task argument list to be substituted for the :application_id variable in that query string when the search is executed.

<QueryParameters>
    <Parameter argument="application" property="application_id"/>
</QueryParameters>
<QueryScript>
    <Source>
       import java.util.*;
           
       List applications = args.get("application.id"); 
       if (applications != null &amp;&amp; !applications.isEmpty()){
          // :application_id
          query = query + " and application.id in(:application_id) ";
       }
       return query;
                        
    </Source>
</QueryScript>

As explained in the QueryScript section above, the QueryParameters in an HQL datasource do not make up filter components in their own right but instead provide variables for substitution into the query string at the time the search is executed.

The Parameter elements within the QueryParameters for an HQL datasource is usually only specified with an argument and a property. The property is the variable name used in the query string and the argument is the argument map key in which the value to be used in the search is stored. A defaultValue or a valueScript (as described in the Filter datasource's QueryParameters section) can also be used to provide the value for the property, if desired. The Parameter's QueryScript option (which returns a QueryOptions object) cannot be used for an HQL datasource, as it does not provide a value for substitution; HQL datasources do not use a QueryOptions object in their searches.

Just as with the other report types, the ReportColumnConfigs within the report's <Columns> element specify the attributes to retrieve from the query for display in the report detail grid – the "Select" portion of the query. The property attributes name the fields to retrieve. The final ReportColumnConfig – the "total" column – in the Account Group Membership Totals Report shows an example of how to include a sub query in the HQL select clause. This provides additional levels of flexibility in reflecting data on the report. A calculated field like this cannot be marked as sortable.

<ReportColumnConfig field="total" header="rept_app_account_grp_memb_col_members" property="(select count(*) from IdentityEntitlement ie where ie.value = m.value and ie.application = m.application and ie.name = m.attribute and ie.aggregationState = &apos;Connected&apos;)"/>