SSRS: Report that displays each line in a different color and sorts each column differently

Question

I want to create a report in SSRS. This report will have max 30 lines. Each line represents a different product. Each product line has a different color. So for product A the line is red. In the columns are the revenues for different years. For example the first column is the year 2014. The last column will show a total for the product. Now each column (except the product name and the sum) should be sorted descending and each line muss hold the color of the product. In the following is a picture of the example data.

The formatted Report should look like this:

So here is the example with some sample data to show the sorting:

I hope the problem is understandable.

First, how can I achieve the colorcoding? My first guess is to set the color with for example a code and use IIF(Fields!Product.Value = 'product A', "Yellow", "White"). This seems to work, but I don't think it is practical to hard code the formatting for each product into the report. Is there an elegant and dynamic way for the formatting?

Second, how can I sort each column differently and save the relation to the product? Do I have to create a tablix for each column and hide the product name?

If I am not wrong, you are trying something like product ranking for each year. I think that this approach would make it hard for someone to understand the data. Maybe using a chart would be a better option — niktrs
A good, long-term, solution for the colour problem would be to store the value in the database on the colour record. That way, when a new product is added, a colour can picked. Can you change the database/application? — Paul Bambury
@niktrs Yes the product ranking is the output that should be achieved. I considered using for example a line chart, but the requirements are specifing the visualization in a tabular form. — Christoph
@PaulBambury Yes I can change the database. So if I understand you correctly I should create a table with columns product and color? — Christoph
It can be done, but it's very hard. Also exporting will not look so good. — niktrs

Alan Schofield Alan Schofield · Accepted Answer · 2017-06-19T10:58:19

I didn't see the final column when I put this together but it will be simple enough to add.

I've created two tables, One for products with a hex colour associated and another containing some sample normalised data.

Finally there is a query to pull it all together. I suggest once you have this running that you edit the query to make it dynamic so you don't need to hard-code the values but it should give you an idea of the approach I took.

DECLARE @prod TABLE (ProductID int, Productname varchar(50), HexColor varchar(7))
INSERT INTO @prod
VALUES
(1, 'Bikes', '#FF0000'),
(2, 'Cars', '#00FF00'),
(3, 'Vans', '#0000FF')


DECLARE @t TABLE (ProductID int, PeriodID int, Amount int)
INSERT INTO @t
VALUES
(1, 2014, 10),
(1, 2015, 12),
(1, 2016, 14),
(2, 2014, 12),
(2, 2015, 9),
(2, 2016, 8),
(3, 2014, 11),
(3, 2015, 16),
(3, 2016, 4)



SELECT ' Name' as ColName, p.ProductName, 0 as Amount, ROW_NUMBER() OVER(ORDER BY t.ProductID) AS RankID , HexColor
    FROM (SELECT DISTINCT ProductID FROM @t)t
    join @prod p on t.productid = p.productid
UNION ALL
SELECT '2014' as ColName, p.ProductName ,amount, ROW_NUMBER() OVER(ORDER BY Amount) AS RankID , HexColor
    FROM  @t t
    join @prod p on t.productid = p.productid
    where periodID = 2014
UNION ALL
SELECT '2015' as ColName, p.ProductName, amount, ROW_NUMBER() OVER(ORDER BY Amount) AS RankID , HexColor
    FROM  @t t
    join @prod p on t.productid = p.productid
    where periodID = 2015
UNION ALL
SELECT '2016' as ColName, p.ProductName, amount, ROW_NUMBER() OVER(ORDER BY Amount) AS RankID , HexColor
    FROM  @t t
    join @prod p on t.productid = p.productid
    where periodID = 2016

If you use this as your dataset in SSRS then you can add a simple matrix as below

The rows group done on RankID and the Column Group is done on ColName.

We then need an expression to choose between showing the Productname or the sum of amount. The expression looks like this.

=IIF(Fields!ColName.Value=" Name", Fields!ProductName.Value, Sum(Fields!Amount.Value))

Finally the BackgroundColor property of the same cell is also an expression as follows.

=Fields!HexColor.Value

You could drop the RankID column from the matrix (but not the group!) if you like but I've left it in here for clarity. The final output looks like this.

SSRS: Report that displays each line in a different color and sorts each column differently

1 Answers