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

Topic		Replies	Views
Custom Report not working ( SQL query is working but when tried in report it is throwing syntax error) IIQ Discussion and Questions identityiq , entitlements	5	101	March 3, 2025
SQL conversion to HQL for custom report IIQ Discussion and Questions identityiq	8	217	October 22, 2025
Creating a Custom Report With HQL IIQ Discussion and Questions identityiq , reports	5	272	November 23, 2024
Table in Custom Report HQL IIQ Discussion and Questions identityiq , reports	3	129	March 17, 2025
How to Generate Report Using HQL or Java IIQ Discussion and Questions identityiq	4	320	May 27, 2024

Getting error when create custom report in HQL query

Related topics