Sql Server Reporting Service(SSRS) By Subbareddy(Microsoft Certified): Custom colors for chart report items in SQL Server Reporting Services 2008

During the latest task I've been working on, I had to apply custom colors to pie chart graph. Implementing a custom palette was not the only requirement - on top of that, the pie chart items were supposed to be shown in same color each time report was run.

To show you how to custom-color the charts, I created a sample project (you can find it attached to this post. Make sure to edit the connection string to point it to your SQL server instance). The database is AdventureWorksLT2008R2 (downloadable from the web). I'll use only 4 tables, as shown at the diagram below:

Our project contains 5 reports. This is the SQL query used in the first report (Chart1_0.rdl):

SELECT

ProductModel.Name AS ProductModel,

CAST(SUM(ISNULL(SalesOrderHeader.SubTotal, 0)) AS decimal(10,2)) AS SubTotal,

p.Rnk

FROM

SalesLT.ProductModel

INNER JOIN SalesLT.Product ON Product.ProductModelID = ProductModel.ProductModelID

LEFT JOIN SalesLT.SalesOrderDetail ON SalesOrderDetail.ProductID = Product.ProductID

LEFT JOIN SalesLT.SalesOrderHeader ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID

INNER JOIN

(

SELECT

ProductModelID,

ROW_NUMBER() OVER(ORDER BY ProductModel.Name) AS Rnk

FROM SalesLT.ProductModel

WHERE ProductModel.ProductModelID <= 10

) p ON p.ProductModelID = ProductModel.ProductModelID

WHERE

ProductModel.ProductModelID <= 10

GROUP BY

ProductModel.Name,

Rnk

ORDER BY

ROW_NUMBER() OVER(ORDER BY ProductModel.Name)

It lists 10 product models and their totals. I'll explain later why I included the ranking function.

The results after the query is run:

This is the first report in the design view:

This is how the report looks like when we run it. Although it looks good, no one can guarantee us that every item will be shown in the same color each time the report is being run.

The second report (Chart1_1.rdl) will rectify this issue. It has the same query as the first report. Ok, but what I did here? First, this is why I used the ranking function from the query above. It assigns a number to product models (sorted by name). It doesn't matter which filter we use, every product model will always get the same number. And this number will be used to identify the model and assign a color to it. But how do we do that? Look at the designer of our second report:

To access the property that assigns colors, first click on the chart area of the report twice (this will select properties of chart series). Now, find the "Color" property and click on "Expression".

The expression above links each Rnk value to a color.

This is how the second report looks when we run it:

Now, what happens if we exclude some items from the first query? Look at the following query:

SELECT

ProductModel.Name AS ProductModel,

CAST(SUM(ISNULL(SalesOrderHeader.SubTotal, 0)) AS decimal(10,2)) AS SubTotal,

p.Rnk

FROM

SalesLT.ProductModel

INNER JOIN SalesLT.Product ON Product.ProductModelID = ProductModel.ProductModelID

LEFT JOIN SalesLT.SalesOrderDetail ON SalesOrderDetail.ProductID = Product.ProductID

LEFT JOIN SalesLT.SalesOrderHeader ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID

INNER JOIN

(

SELECT

ProductModelID,

ROW_NUMBER() OVER(ORDER BY ProductModel.Name) AS Rnk

FROM SalesLT.ProductModel

WHERE ProductModel.ProductModelID <= 10

) p ON p.ProductModelID = ProductModel.ProductModelID

WHERE

ProductModel.ProductModelID <= 10

AND

SalesOrderDetail.OrderQty BETWEEN 5 AND 10

GROUP BY

ProductModel.Name,

Rnk

ORDER BY

ROW_NUMBER() OVER(ORDER BY ProductModel.Name)

It picks only product models that have a child SalesOrderDetails entries with OrderQty value between 5 and 10. Let's look at the results:

You'll notice that it returns less product models than the first query, but all of them still have the same Rnk value.

This query is used in our third report (Chart1_2.rdl). Now, let's run the report and look at the results:

As you see, items still have the same color (Classic Vest is shown in yellow, Cycling Cap in blue etc.)

But what if you want to asign the colors, not by name, but by the values? The following query will be used in our fourth report (Chart2_1.rdl):

SELECT TOP 10

ProductModel.Name AS ProductModel,

CAST(SUM(ISNULL(SalesOrderDetail.UnitPrice, 0)) AS decimal(10,2)) AS UnitPrice,

ROW_NUMBER() OVER(ORDER BY SUM(ISNULL(SalesOrderDetail.UnitPrice, 0)) DESC) AS Rnk

FROM

SalesLT.ProductModel

INNER JOIN SalesLT.Product ON Product.ProductModelID = ProductModel.ProductModelID

LEFT JOIN SalesLT.SalesOrderDetail ON SalesOrderDetail.ProductID = Product.ProductID

GROUP BY

ProductModel.Name

ORDER BY

ROW_NUMBER() OVER(ORDER BY SUM(ISNULL(SalesOrderDetail.UnitPrice, 0)) DESC)

The ranking function in this example sorts the product models by subtotals in descending order and assigns number 1 to a product model with greatest subtotal, 2 for the next etc.

The fourth report has the same "Color" expression as the previous two reports. This is how it looks when it is run:

But what if we change this query a bit? We still want the item with greates subtotal to appear in yellow! Look at the following query:

SELECT TOP 10

ProductModel.Name AS ProductModel,

CAST(SUM(ISNULL(SalesOrderDetail.UnitPrice, 0)) AS decimal(10,2)) AS UnitPrice,

ROW_NUMBER() OVER(ORDER BY SUM(ISNULL(SalesOrderDetail.UnitPrice, 0)) DESC) AS Rnk

FROM

SalesLT.ProductModel

INNER JOIN SalesLT.Product ON Product.ProductModelID = ProductModel.ProductModelID

LEFT JOIN SalesLT.SalesOrderDetail ON SalesOrderDetail.ProductID = Product.ProductID

WHERE

SalesOrderDetail.UnitPrice BETWEEN 500 AND 1000

GROUP BY

ProductModel.Name

ORDER BY

ROW_NUMBER() OVER(ORDER BY SUM(ISNULL(SalesOrderDetail.UnitPrice, 0)) DESC)

We will use this in our fifth report (Chart2_2.rdl)

As you see, product model with greatest subtotal (HL Mountain Frame) is shown in yellow. In the previous example it had the sixth greatest subtotal so it was shown in slate grey.