title: SQL SSRS Mapping Results to a Matrix
body: <p>I have a number of profiles which have a certain level of permissions which are enumerated and stored within SQL.  I want to create within SSRS a report which shows all the permissions on the left side of the table and all the profiles on the top with an X or some indication of the profiles that have the appropriate permissions.  This information is stored across three tables.</p>
  
  <p>Table 1: Profiles - Gives the names of the profiles related back to a GUID</p>
  
  <p>Table 2: Profile_Priv - Gives the GUID of a profile with the permission number (only permissions actually granted to a profile appear in this table)</p>
  
  <p>Table 3: Privileges - Relates the permission number back to the name of the permission.</p>
  
  <p>I have a query that will pull the permissions assigned to a profile, and I can easily populate the blank matrix with all the possible permissions, but marrying the two is escaping me.  This is what I have in SSRS so far.  I am passing a parameter within SSRS called ProfileName to allow the query to show what ever profiles are needed for that particular report.</p>
  
  <pre><code>Select Privileges.Description, Profiles.Name
  FROM Privileges LEFT OUTER JOIN
  Profile_Priv ON Privileges.ID = Profile_Priv.ID INNER JOIN
  Profiles ON Profile_Priv.PID = Profiles.PID
  WHERE (Profiles.Name = @ProfileName)
  ORDER BY Privileges.ID
  </code></pre>
  
  <p>This doesn't populate any of the information in the table like I expected it to, and also will not do any type of data transform like turning the value to Yes.</p>
  
  <p>Any feedback or assistance is greatly appreciated. </p>
  
  <p>Addition 1:</p>
  
  <p>Even when changing it to a Full Join, doing an individual Profile only returns the Permissions that the specific Profile has.</p>
  
  <p>What I am looking to create is something like this in a report:</p>
  
  <pre><code>               Profile 1     Profile 2     Profile 3    Profile 4
  Permission 1   X                           X                     
  Permission 2   X             X             X            X
  Permission 3                 X                          X
  Permission 4                                        
  </code></pre>
  
  <p>Currently I get this using the example from above if I was searching for Profile 2:</p>
  
  <pre><code>               Profile 2                              
  Permission 2   X             
  Permission 3   X               
  </code></pre>