Custom Report not working ( SQL query is working but when tried in report it is throwing syntax error)

IdentityIQ (IIQ) IIQ Discussion and Questions
,
1

I am trying to convert a SQL query into HQL and custom report but getting error all the time.

The error being different on SQL syntax

SQL Query :

<WITH ExtractedData AS (
SELECT
description,requester,completer,target_name,
CAST(attributes AS XML) AS ParsedAttributes,
CAST(attributes AS XML).value(‘(/Attributes/Map/entry[@key=“identityDisplayName”]/@value)[1]’, ‘VARCHAR(100)’) AS displayName
FROM spt_work_item_archive
WHERE
type = ‘Form’
AND (
description LIKE ‘Identity%’
OR description LIKE ‘%New Hire Onboarding%’
OR description LIKE ‘Set Legal%’
)
AND DATEADD(SECOND, created / 1000, ‘1970-01-01 00:00:00’) BETWEEN ‘2024-10-01 00:00:00’ AND ‘2024-10-30 23:59:59’
)
SELECT
COUNT(*) AS TOTAL_COUNT,
description,requester,completer,target_name,displayName
FROM ExtractedData
GROUP BY
description,requester,completer,target_name,displayName;/>

2

Hi @Himanshu_singh03,

how you have converted the query? can you post the hql query?

3

I am getting error all the time with this XML :

SELECT wi.description AS description, wi.requester AS requester, wi.completer AS completer, wi.target.name AS targetName, entry.value AS displayName 
       FROM
           sailpoint.object.WorkItem wi
       JOIN
           wi.attributes entry
       WHERE
           entry.key = 'identityDisplayName'
           AND wi.type = 'Form'
           AND (
               wi.description LIKE 'Identity%'
               OR wi.description LIKE 'New Hire Onboarding%'
               OR wi.description LIKE 'Set Legal%'
           )
           AND wi.created BETWEEN :startDate AND :endDate
       GROUP BY
       wi.description, wi.requester, wi.completer, wi.target.name, entry.value
   </Query>
        </DataSource>
        <Columns>
          <ReportColumnConfig field="description" header="Description" sortable="true" width="110"/>
          <ReportColumnConfig field="requester" header="Requester" sortable="true" width="110"/>
          <ReportColumnConfig field="completer" header="Completer" sortable="true" width="110"/>
          <ReportColumnConfig field="targetName" header="Target Name" sortable="true" width="110"/>
          <ReportColumnConfig field="displayName" header="Display Name" sortable="true" width="110"/>
4 
          < SELECT
           wi.description AS description,
           wi.requester AS requester,
           wi.completer AS completer,
           wi.target.name AS targetName,
           entry.value AS displayName
            
       FROM
           sailpoint.object.WorkItem wi
       JOIN
           wi.attributes entry
       WHERE
           entry.key = 'identityDisplayName'
           AND wi.type = 'Form'
           AND (
               wi.description LIKE 'Identity%'
               OR wi.description LIKE 'New Hire Onboarding%'
               OR wi.description LIKE 'Set Legal%'
           )
           AND wi.created BETWEEN :startDate AND :endDate
       GROUP BY
       wi.description, wi.requester, wi.completer, wi.target.name, entry.value
   </Query>
        </DataSource>
        <Columns>
          <ReportColumnConfig field="description" header="Description" sortable="true" width="110"/>
          <ReportColumnConfig field="requester" header="Requester" sortable="true" width="110"/>
          <ReportColumnConfig field="completer" header="Completer" sortable="true" width="110"/>
          <ReportColumnConfig field="targetName" header="Target Name" sortable="true" width="110"/>
          <ReportColumnConfig field="displayName" header="Display Name" sortable="true" width="110"/>
5

more or less all is wrong :sweat_smile:

  • delete SELECT statement, IIQ add automatically a select * in front of the query
  • attributes on WorkItem is not an object, you can use wi.attributes directly on ReportColumnConfig
  • delete entry.key = 'identityDisplayName'. It doesn’t make sense and it doesn’t work
  • delete the GROUP BY statement
  • on ReportColumnConfig add the property values

PS when you work on HQL follow the Object Model file to understand the structure of object and how you can do