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

Version 1

    Details

    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.

     


    Resolution

    Scenario:

    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


    Example:

    Raw: