Join issue - Requestor and Approval data

[Dan Solberg]

Trying to show approval task information with requestor/customer information.

We are unable to show approval task information and requestor information in the same result.  It appears that there is no join between requestor/customer and approval data.  Is there any way for us to create this join.

[Ed Contini - newScale]

Hi Dan,

This should not be a problem with the Custom Reports Data Model. For instance, one could build a simple informational report displaying Requisition Number, Service Name, Customer Name, Approval Task Name and Approver Name as follows:

ServiceRequestFact - RequisitionID (fact)
Service - ServiceName (dimension)
Customer - Customer Full Name (dimension)
Authorization Tasks - Task Name (fact)
Performer - Performer Full Name (dimension)

As an FYI -- In the event that you do need to build a report containing data on 2 query subjects that do not have an explicit relationship (not the case here), you can do so by building a Master Detail Report in Report Designer.

[Dan Solberg]

Thank you Ed.  I ran the report and prompted for one requisition found that as long as I do not add the Customer field to the report the results that are returned are correct.  Once I add the customer field to the report, it returns every approval task for the customer regardless of requisition.  It seems to be that the joins might be incorrect.

[Ed Contini - newScale]

Hi Dan,

I just want to double-check something. Did you replace the RequisitionID from the Authorization Tasks fact table with the RequisitionID from the ServiceRequestFact table? Asking because, when I do that, the repeating requisition numbers problem is fixed.