Getting error when create custom report in HQL query

amanKsingh · July 19, 2024, 12:13pm

Hi all,
When I am creating custom report I am getting error in HQL query.
ERROR:

Exception encountered while executing Report. Exception: Error preparing ProjectionDataSource

HQL QUERY:

from IdentityRequestItem iri, IdentityRequest ir, Bundle bu
where iri.value = bu.name
and bu.name in (‘Customer Services Agent (Level 1)’)

code:

 <?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE TaskDefinition PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<TaskDefinition executor="sailpoint.reporting.LiveReportExecutor" name="test_roleREPORT" progressMode="Percentage" resultAction="Rename" subType="Lifecycle Manager Reports" template="true" type="LiveReport">
  <Attributes>
    <Map>
      <entry key="auditEventTypes">
        <value>
          <List>
            <String>AccessRequest</String>
            <String>EntitlementsRequest</String>
            <String>RolesRequest</String>
          </List>
        </value>
      </entry>
      <entry key="report">
        <value>
          <LiveReport title="Access Request Status Report">
            <DataSource type="Hql">
              <Query>

              from IdentityRequestItem iri, IdentityRequest ir, Bundle bu
                where iri.value = bu.name
                  and bu.name in ('Customer Services Agent (Level 1)')
  
              </Query>
              <QueryParameters>
                <Parameter argument="application_name" property="application_name" valueClass="java.util.List"/>
                <Parameter argument="targetIdentities"/>
                <Parameter argument="requestors"/>
                <Parameter argument="entitlements"/>
                <Parameter argument="status"/>
                <Parameter argument="requestedDateStart"/>
                <Parameter argument="requestedDateEnd"/>
                <Parameter argument="completionDateStart"/>
                <Parameter argument="completionDateEnd"/>
              </QueryParameters>
              <QueryScript>
                <Source>
                  // iri.identityRequest = ir.id and
                  //and  iri.value = bu.displayName
                  // System.out.println("1");
                  import java.util.*;
                   import sailpoint.object.*;
                    import sailpoint.api.SailPointContext;
                    import sailpoint.tools.GeneralException;
                    import sailpoint.object.QueryOptions;
                    import sailpoint.object.Filter;
                      import java.util.Iterator;
                  
                  
                  List application_name = args.get("application_name");
                 
                  String roleString="";
                 System.out.println("applicationNames::::"+application_name);
                  

                if(application_name != null &amp;&amp; !application_name.isEmpty()){
                  
                     
                  
                   QueryOptions qo = new QueryOptions();
                    System.out.println("Insdie if::::::::::::::::::::::");
                   String appName=application_name.get(0);
                   qo.addFilter(Filter.eq("application_name", appName));
                    
                 System.out.println("QO::::::::::::::::"+qo);
                  Iterator roleList= context.search(Bundle.class,qo); 
                  
                   System.out.println("appName::::::::::::::::"+appName);
                  
                  Set&lt;String> application_name_set = new HashSet();
                  String str="";
                  while(roleList.hasNext()){
                   System.out.println("Inside while:::::::::::::");
                  
                  
                    Bundle s=(Bundle)roleList.next();
                    str =s.getName().toString();
                    if (roleString.equalsIgnoreCase("")){
                     roleString="'"+str+"'";
                  }
                  else {
                   roleString= roleString+","+"'"+str+"'";
                  }
                    System.out.println("roleList1::::::::::::::::"+roleString);
                  }
                 
                  
                  try{
                   String ss="Customer Services Agent (Level 1)";
                  //query = query + " and bu.name in"+"("+ss+")";
                   System.out.println("query::::"+query);
                 
                  }catch(Exception e){
                  System.out.println("1000::::::::::::::::"+e.getMessage());
                  
                  }
                

                  } 
                   System.out.println("Final query::::"+query);

                 return query;

                </Source>
              </QueryScript>
            </DataSource>
            <ReportForm>
              <Reference class="sailpoint.object.Form" id="0aaa250d90b415c28190b4d75ab108b6" name="Access Request Status Form - roleform"/>
            </ReportForm>
            <Columns>
              <ReportColumnConfig field="requestId" header="Access Request ID" property="ir.name" sortable="true" width="110"/>
              <ReportColumnConfig field="requester" header="Requester" property="ir.requesterDisplayName" sortable="true" width="110">
                <RenderScript>
                  <Source>
                    System.out.println("requester"+value);

                  </Source>
                </RenderScript>
              </ReportColumnConfig>
              <ReportColumnConfig field="requestee" header="Requested For" property="ir.targetDisplayName" sortable="true" width="110">
                <RenderScript>
                  <Source>
                  System.out.println("requestee"+value);
                   

                  </Source>
                </RenderScript>
              </ReportColumnConfig>
              <ReportColumnConfig field="requestDate" header="Request Date" property="ir.created" sortable="true" width="110"/>
              <ReportColumnConfig field="operation" header="Operation" property="iri.operation" sortable="true" width="110"/>
              <ReportColumnConfig field="application_name" header="rept_app_activity_col_action" property="application_name" sortable="true" width="110"/>
              <ReportColumnConfig field="entitlementValue" header="Roles" property="iri.value" sortable="true" width="110"/>
              <ReportColumnConfig field="status" header="Status" property="iri.approvalState" sortable="true" width="110"/>
              <ReportColumnConfig field="completionDate" header="Date of approval" property="ir.endDate" sortable="true" width="110"/>
              <ReportColumnConfig field="employeeType" header="Employee Type" property="ir.targetDisplayName" sortable="true" width="110">
                <RenderScript>
                  <Source>
                 
                         System.out.println("2.4");

                  </Source>
                </RenderScript>
              </ReportColumnConfig>
              <ReportColumnConfig field="costCenter" header="Cost Center" property="ir.targetDisplayName" sortable="true" width="110">
                <RenderScript>
                  <Source>
                            System.out.println("3"+value);

                  </Source>
                </RenderScript>
              </ReportColumnConfig>
            </Columns>
          </LiveReport>
        </value>
      </entry>
      <entry key="reportTitle" value="rept_lcm_entitlement_request_status_title"/>
    </Map>
  </Attributes>
  <Description>Displays information about access requests from LCM in detailed format.</Description>
  <Signature>
    <Inputs>
      <Argument multi="true" name="application_name" type="string">
        <Description>rept_input_app_activity_report_action</Description>
      </Argument>
      <Argument multi="true" name="approvers" type="Identity">
        <Description>rept_input_lcm_request_status_report_approver</Description>
      </Argument>
      <Argument multi="true" name="requestors" type="Identity">
        <Description>rept_input_lcm_request_status_report_requester</Description>
      </Argument>
      <Argument multi="true" name="entitlements" type="sailpoint.object.IdentityItem">
        <Description>rept_input_lcm_request_status_report_entitlements</Description>
      </Argument>
      <Argument multi="true" name="targetIdentities" type="Identity">
        <Description>rept_input_lcm_request_status_report_target_identity</Description>
      </Argument>
      <Argument name="status" type="string">
        <Description>rept_input_lcm_request_status_report_status</Description>
      </Argument>
      <Argument name="requestDateRange" type="daterange">
        <Description>rept_input_lcm_request_status_report_status</Description>
      </Argument>
      <Argument name="completionDateRange" type="daterange">
        <Description>rept_input_lcm_request_status_report_status</Description>
      </Argument>
    </Inputs>
  </Signature>
</TaskDefinition>

AdamVentum · July 19, 2024, 12:46pm

Hi and Hello,

The error message “Exception encountered while executing Report. Exception: Error preparing ProjectionDataSource” indicates that there is a problem with the HQL query or its execution environment in the report.

HQL Syntax: Ensure the HQL syntax is correct. In your HQL query, check for any syntax errors or inconsistencies.
Aliasing and Join Conditions: Make sure that the join conditions and the use of aliases are correct.
Query Script: Ensure that the script used in the <QueryScript> tag is correctly modifying and returning the HQL query.

Maybe you can use something like that:

from IdentityRequestItem iri, IdentityRequest ir, Bundle bu
where iri.value = bu.name
and bu.name in (‘Customer Services Agent (Level 1)’)

from IdentityRequestItem iri
join iri.identityRequest ir
join Bundle bu on iri.value = bu.name
where bu.name = ‘Customer Services Agent (Level 1)’

String ss = “Customer Services Agent (Level 1)”;
query = “from IdentityRequestItem iri join iri.identityRequest ir join Bundle bu on iri.value = bu.name where bu.name = '” + ss + “'”;
return query;

also you can add more logs

System.out.println("applicationNames: " + application_name);
System.out.println("Final query: " + query);

Regards,
Adam

amanKsingh · July 19, 2024, 1:34pm

@AdamVentum, Thanks for reply but I have checked all cases you have mentioned. It is not working.

Sriindugula · July 19, 2024, 1:36pm

Hi @amanKsingh

Could you please add more loggers and share the complete logs for the better understanding

Thanks

system · September 17, 2024, 1:36pm

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Getting error for a report after upgrading to 8.4 IIQ Discussion and Questions identityiq , reports	10	487	May 12, 2024
Creating a Custom Report With HQL IIQ Discussion and Questions identityiq , reports	5	33	November 23, 2024
Custom Report Preview Throwing Exception IIQ Discussion and Questions identityiq , reports	6	220	July 4, 2024
Custom report about Identities IIQ Discussion and Questions identityiq	10	90	November 12, 2024
In custom report, can we limit the number of rows to be populated, I need only 1 row to be queried IIQ Discussion and Questions identityiq , reports	7	36	December 2, 2024

Getting error when create custom report in HQL query

Related topics