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.

Thanks for reading and I hope that you will find my examples useful.
Tags:  
Rated 5.00, 4 vote(s).