title: Fill color based on values from other data set in SSRS
body: <p>I'm trying to fill colors based on table records.</p>
  
  <p>From SQL Server AdventureWorks2017 database:</p>
  
  <pre><code>          select * from [HumanResources].[Department]
  </code></pre>
  
  <p>I have two dataset:</p>
  
  <pre><code>           1st : select * from [HumanResources].[Department]
  
              DepartmentID                         Name                            GroupName                                          Status
              ----------------------------------------------------------------------------------------------------------------------------------------
              1                                Engineering                    Research and Development                       Cancelled
              2                                Tool Design                    Research and Development                       Blacklist
              3                                Sales                          Sales and Marketing                            Approved
              4                                Marketing                      Sales and Marketing                            All good
              5                                Purchasing                     Inventory Management                           xxx
              6                                Research and Development       Research and Development                       yyy
              7                                Production                     Manufacturing                                  zzz
              8                                Production                     Control Manufacturing                          xxx
              9                                Human Resources                Executive General and Administration           zzz
              10                               Finance                        Executive General and Administration           aaa
              11                               Information Services           Executive General and Administration           xxx
              12                               Document Control               Quality Assurance                              yyy
              13                               Quality Assurance              Quality Assurance                              zzz
              14                               Facilities and Maintenance     Executive General and Administration           aaa
              15                               Shipping and Receiving         Inventory Management                           bbb
              16                               Executive                      Executive General and Administration           aaa
  </code></pre>
  
  <p><strong>the 2nd</strong>, I have a requirement to get the data like below and display in the SSRS report:</p>
  
  <pre><code>                Name0                                Name1                                      Name2                           Name3                                      Name4
              -----------------------------------------------------------------------------------------------------------------------------------------------------------------
              Document Control           Facilities and Maintenance                Information Services           Production Control                  Research and Development
               Engineering                Finance                                     Marketing                      Purchasing                       Sales
               Executive                 Human Resources                             Production                   Quality Assurance                   Shipping and Receiving
                NULL                     NULL                                         NULL                         NULL                               Tool Design
  </code></pre>
  
  <p>The above data I'm getting using below query:</p>
  
  <pre><code>            WITH Table1 AS
              (
                  SELECT  [Name]
                  FROM    [HumanResources].[Department]
              ), CTE AS
              (
                  SELECT  [Name], COL, ROW_NUMBER() OVER(PARTITION BY Col ORDER BY [Name]) AS Row
                  FROM    (   SELECT [Name],
                                      5 - NTILE(5) OVER(ORDER BY [Name] DESC) AS Col
                              FROM Table1  
                          ) c
              )
              SELECT [0], [1], [2], [3], [4]
              FROM CTE 
              PIVOT (MAX([Name]) FOR Col IN ([0], [1], [2], [3], [4])) AS Pvt
              ORDER BY Row;
  </code></pre>
  
  <p>Also, in addition to the above requirement, another requirement is to fill the colors in the <strong>2nd result set</strong> based on "GroupName" and "Status" which is "another dataset" values; 
  which I'm getting using :</p>
  
  <pre><code>       select * from [HumanResources].[Department]
  </code></pre>
  
  <p>I'm trying below expressions, to fill the colors for the 2nd data set:</p>
  
  <pre><code>      =IIF(LOOKUP((Fields!ID0.Value or Fields!ID1.Value or Fields!ID2.Value or Fields!ID3.Value or Fields!ID4.Value),Fields!Name.Value,Fields!GroupName.Value="Research and Development","DsetAll"),"GREEN","WHITE") 
  
        =IIF(LOOKUP((Fields!ID0.Value or Fields!ID1.Value or Fields!ID2.Value or Fields!ID3.Value or Fields!ID4.Value),Fields!Name.Value,Fields!GroupName.Value,"DsetAll")="Research and Development","GREEN","WHITE")
  </code></pre>
  
  <p>The problem is NON of the above expressions working fine.</p>
  
  <p><strong>EDIT:</strong>
  The below expression looks working good:</p>
  
  <pre><code> =IIF(LOOKUP(Fields!ID0.Value ,Fields!Name.Value,Fields!GroupName.Value,"DsetAll")="Research and Development","BLUE","RED")
  </code></pre>
  
  <p>It looks like, "OR" inside a "lookup" is not working. How can I make this work? Any other ways?</p>
  
  <p>In the <strong>2nd result set</strong>, The idea is to make: </p>
  
  <pre><code>        if groupname "Research and Development" and status is "Cancelled" then "Green"
         if groupname "Research and Development" and status is "Blacklist" then "Green"
         if groupname "Sales and Marketing" and status is "Approved" then "Green"
                  if status is "All good" then Gray
                  if groupname "Manufacturing" then Blue
                  if groupname "Control   Manufacturing" then red
                  if groupname "Executive General and Administration" then pink
                  if groupname "Quality Assurance" then Violet
                  Else its should be white
  </code></pre>
  
  <p>The 1st expression is not showing any filled color.
  Additionally, the NULL/Blank should not have any color.</p>
  
  <p>and the 2nd expression has error:</p>
  
  <pre><code>            The Error occurred during local report processing.
              The definition of the report '/ColorTest' is invalid.
              The BackgroundColor expression for the text box 'ID0' contains an error: [BC30201] Expression expected.
  </code></pre>
  
  <p>How can I achieve this? Or, please let me know if there are any other ways to achieve same thing? Thanks</p>