SSRS - How do you join data that uses the fusion link table?

Version 1


    When working with building a custom report, you may run into a relationship that is established through a link table (fusionlink).

    A link table allows the application to store many to many relationship information between objects.

    Due to that, linking the data together in SQL is not simple and requires a join through the fusion link table.




    You want to show all CI items linked to an Incident. 
    Incident to CI is a many to many relationship that uses the fusionlink table.

    1. Open the relationship that you are attempting to use via 'Configure Application'
    2. The main information that needs to be taken from the relationship screen is the relationship 'Display Name'
    3. The display name is used in the query to search for FusionLink.RelationshipName

    SELECT Incident.IncidentNumber, Incident.CreatedDateTime, CI.CIType, CI.Name
    FROM FusionLink 
    INNER JOIN Incident ON FusionLink.sourceID = Incident.RecId
    Inner join CI ON FusionLink.targetID = CI.RecId and FusionLink.targetbase = 'CI' and FusionLink.SourceBase = 'Incident'
    WHERE FusionLink.RelationshipName = 'IncidentAssociatesCI
    AND FusionLink.SourceID in 
      Select Incident.RecId 
      from Incident 
    Order by Incident.IncidentNumber desc