Color cell when row and column are the same

Question

I'm trying to create a matrix in Reporting Services /SSRS, similar to a table that's often made for competitions.

My table looks a bit this:

Home Points Away
NL    3     DE  
DE    2     FR  
FR    5     NL  
NL    3     DE 
DE    4     FR 
FR    6     NL 

I want my matrix to look like this:

         Away
       NL DE  FR
     NL X  6   0
Home DE 0  X   6
     FR 11 0   X

Where every value is the sum of the individual numbers for the given combination

NL -> DE = 3+3,

DE -> FR=2+4,

FR -> NL =5+6

How do I get the X-es where the columns and the rows are equal? (And by extension, I want these cells to have an alternate color)

Answer 1

I don't think you can do what your looking for with the SSRS matrix (or at least I couldn't). I ended up creating a pivot table.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX), @PrettyColumns NVARCHAR(MAX);
SET @columns = N'';
SET @PrettyColumns = N'';


SELECT @columns += N', p.' + QUOTENAME(Home)
  FROM (SELECT p.Home FROM dbo.Table_1 AS p
  GROUP BY p.Home, p.Away) AS x;

SELECT @PrettyColumns += N', CASE WHEN (p.Home = ''' + Home+''') THEN ''X'' ELSE convert(varchar(max), p.' + QUOTENAME(Home) + ') END as ' + Home
  FROM (SELECT p.Home FROM dbo.Table_1 AS p
  GROUP BY p.Home, p.Away) AS x;  


SET @sql = N'
SELECT p.Home, ' + STUFF(@PrettyColumns, 1, 2, '') + '
FROM
(
  SELECT p.Home, p.Points, p.Away
   FROM dbo.Table_1 AS p
) AS j
PIVOT
(
  SUM(Points) FOR Away IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

Answer 2

Dataset

declare @tb as table (Home Varchar(5), Away Varchar(5), Points Varchar(5))

insert into @tb 
select 'NL','NL','X' union
select 'NL','DE','3' union
select 'NL','FR','5' union
select 'DE','DE','X' union
select 'FR','FR','X' union
select 'DE','FR','2'

select * from @tb

SSRS Table Design & Grouping

• Row group by Home
• Column group by Away
• Expression : =IIF(String.IsNullOrEmpty(Fields!Points.Value),"0",Fields!Points.Value)
• Right click the expression textbox -> properties -> background color -> Enter Expression : =IIF(Fields!Points.Value="X","Red",0)

Result