Object Change Request and WorkOrder in EQL

I created a report "Change Request Tracking" , but 

I have a problem to show Approved By from Change Request object ( if change request status is APPROVED, i need to show who person approved it )

see my eQL  and report below for ref :

START WITH WorkOrder 
SELECT 
   Status,
   Code,
   Description,
   ChangeRequests.ChangeRequest.Id,
   ChangeRequests.ChangeRequest.Code CR_Code,
   CAST( ChangeRequests.ChangeRequest.DateRegistered AS STRING'dd, MMM, yyyy' ) CR_Registration,
   WorkTasks.Code,
   WorkTasks.Person.FirstName,
   WorkTasks.Person.Surname,
   WorkTasks.Description,
   WorkTasks.ActEndDate,
   ChangeRequests.ChangeRequest.Description,
   CASE WorkTasks.Status WHEN 'A' THEN 'Active' WHEN 'I' THEN 'Inbox' WHEN 'C' THEN 'Completed' ELSE 'OTHER STATUS' END,
   ChangeRequests.ChangeRequest.Class.Path,
   DISPLAY_NAME( ChangeRequests.ChangeRequest.Status ) CR_Status,
   CAST( ChangeRequests.ChangeRequest.DateApprOrReject AS STRING'dd, MMM, yyyy' ) CR_Apr_Date,
   ChangeRequests.ChangeRequest.Attributes["Global""Originator"].Value 
   
WHERE 
   ChangeRequests.ChangeRequest.Class.Path = 'ECR Process'
   AND ChangeRequests.ChangeRequest.DateRegistered >= @DateFrom
   AND ChangeRequests.ChangeRequest.DateRegistered <= @DateTo
   AND ChangeRequests.ChangeRequest.Code LIKE @CR_Code
   AND ChangeRequests.ChangeRequest.Attributes["Global""Originator"].Value LIKE @Origin
   AND ChangeRequests.ChangeRequest.Status LIKE @Status
   AND        (
          WorkTasks.Status IN  ( 'A''I' ) 
          OR              (
                WorkTasks.Status = 'C'
                AND WorkTasks.Code = 'E533C-APPROVED CLOSING PROJECT'
             )
       ) 
ORDER BY 
   ChangeRequests.ChangeRequest.Code

Does anyone know how to solve this issue ?

best regardsl,

Bambs

Parents
  • Hi Bambs,

    Can you check any of your approved change request and expand the audit details and confirm if the details are recorded in the eB system when the status changed to "Approved" ?
    When you expand, it will show "Approved by" and name of the user against the same.

    Also, I can see you creating your eQL as START WITH WorkOrder, which means eQL can provide you all the details related to Work order. "Approved by" is 2nd level information which is related to Change Request, not directly related to Work Order. So I am afraid you can not bring that infromation in the same result table.

    Also, WorkOrder and Change Request are two different tables from where we are getting our records and as eQL is designed for simple searches directly from one table at a time, I am not sure if you can bring records from different tables in same search.

    Please let me know if that answers your question.

    Regards,
    Manav B.



  • Hello Manav,

    Thanks for your response.

    it's was solved a couple days ago. 

    Thanks

    Answer Verified By: Bambang Mardiyanto 

Reply Children
No Data