I'm trying to fill colors based on table records.
From SQL Server AdventureWorks2017 database:
select * from [HumanResources].[Department]
I have two dataset:
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
the 2nd, I have a requirement to get the data like below and display in the SSRS report:
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
The above data I'm getting using below query:
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;
Also, in addition to the above requirement, another requirement is to fill the colors in the 2nd result set based on "GroupName" and "Status" which is "another dataset" values; which I'm getting using :
select * from [HumanResources].[Department]
I'm trying below expressions, to fill the colors for the 2nd data set:
=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")
The problem is NON of the above expressions working fine.
EDIT: The below expression looks working good:
=IIF(LOOKUP(Fields!ID0.Value ,Fields!Name.Value,Fields!GroupName.Value,"DsetAll")="Research and Development","BLUE","RED")
It looks like, "OR" inside a "lookup" is not working. How can I make this work? Any other ways?
In the 2nd result set, The idea is to make:
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
The 1st expression is not showing any filled color. Additionally, the NULL/Blank should not have any color.
and the 2nd expression has error:
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.
How can I achieve this? Or, please let me know if there are any other ways to achieve same thing? Thanks
Seeing that you have Name0 through to Name4 as static columns.. you could simply join each column back to the resource table to get the status and based on the result.. return a color and then use that color for the cell in the report.
I would approach this by first wrapping your Pivot with a cte and get a tidy output..
Something like
, my_pivot_result as (
SELECT [0], [1], [2], [3], [4]
FROM CTE
PIVOT (MAX([Name]) FOR Col IN ([0], [1], [2], [3], [4])) AS Pvt
)
,my_status as (
select my_pivot_result .*
, isnull(col1_name.status,'') as col1_status
, isnull(col1_name.groupname,'') as col1_groupname
, isnull(col2_name.status,'') as col2_status
, isnull(col2_name.groupname,'') as col2_groupname
, isnull(col3_name.status,'') as col3_status
, isnull(col3_name.groupname,'') as col3_groupname
, isnull(col4_name.status,'') as col_status
, isnull(col4_name.groupname,'') as col4_groupname
from my_pivot_result
left join [HumanResources].[Department] col1_name
on col1_name.name = my_pivot_result.name0
left join [HumanResources].[Department] col2_name
on col2_name.name = my_pivot_result.name1
left join [HumanResources].[Department] col3_name
on col3_name.name = my_pivot_result.name2
left join [HumanResources].[Department] col4_name
on col4_name.name = my_pivot_result.name3
)
Select * from my_status
now for each row, you should have a status and the groupname.. Now use a case statement to build another column for color..
something like..
case when col1_groupname = 'Research and Development'
and col1_status in('Blacklist', 'Cancelled') then 'Green'
else 'white' end as col1_color
.
.
.
Keeping building the case statement until you have all the combinations
now in the front end, use an expression for each cell to display the color and use the color field value..