Saturday, November 10, 2012

Dynamic Color Coding in SSRS Charts

In this article, I will explain how to change the color of a chart dynamically. In other words, how to provide custom color palette feature at run time.

Lets start off with an example including step by step operations.

Step1. Open or create new Report Server Project. I am creating a new Report Server Project using SQL Server 2012 and saved it with LearnSSRS2012 name.

Step2. Add new Shared Data Source by right click on "Shared Data Sources" folder.

 Now give any name to Share Data Source (Local, in my case) and click on Edit button to define Server Name and Database Name. In this example, I will use static data sources so I am using "." (you can also use "(local)") as ServerName. I am not defining any Database name so I will keep it blank (default will be msater). You can define any database, if you want. Now click on OK.

Step3. Now create a new report by clicking on Reports --> Add --> New Item --> Report --> Add. I don't like default report name like Report1, Report2, Report3 so I have renamed it to ChartColorCode.rdl.

Step4. Create Data Source for ChartColorCode report. To create a new Data Source, right click on "Data Sources" followed by "Add Data Sources" under Report Data as shown below:

We will use Shared Data Source we created above. So click on "Use shared data source reference" and select Local data source created at step 2. Give any name to this report level data source. I will assign "Local" again. Click OK to proceed.

Step5. Now lets create couple of datasets. To create a new dataset, right click on "Datastes" followed by "Add Datastes" under Report Data as shown below:

I will use two data sources in this example, one for Color Palette and another for Chart.

To create new Dataset for ColorPalette, Right click on DataSets --> Add Dataset... and type "ColorPalette" in Name textbox. Select "Use a dataset embedded in my report" option and select Local as Data Source.

Select Text option in "Query Type" and type following query in Query window:

SELECT 'DarkOrange' AS ColorName UNION
SELECT 'Green' AS ColorName UNION
SELECT 'Blue' AS ColorName UNION
SELECT 'Olive' AS ColorName UNION
SELECT 'SeaGreen' AS ColorName UNION
SELECT 'Brown' AS ColorName UNION
SELECT 'Gray' AS ColorName UNION
SELECT 'Tomato' AS ColorName

Click OK to proceed.

Create one more dataset using following query and name it Chart.ds:

SELECT 2010 AS [Year], 12000 Amount UNION
SELECT 2011 AS [Year], 15000 Amount UNION
SELECT 2012 AS [Year], 13000 Amount

Step6. Drag and drop "3D Cylinder"  bar chart from toolbox. Drag and drop Amount field from Chart.ds to value field (Y axis) and Year field to Category Groups as shown below.

To make the chart more meaningful and dainty, do following formatting:
1. Replace Chart Title with "Year vs Amount".
2. Replace Y Axis Title with Amount.
3. Repalce X Axis Title with Year.
4. Remove the Chart Legend.
5. Change the font size and color as you wish.
6. Right click on the bar and select "Show Data Labels". Select Times New Roman in data labels font because numbers are better visible in this font.

After incorporating all the above changes, click on Preview tab to view the report. My report looks like one shown below:

Step7. Now I will explain how to change the chart color at run-time.
First of all, I will create a report parameter for dynamic color. Click on Parameters folders in Report Data and then click on Add Parameter... Define pColor as report parameter Name and Color as Prompt. Select parameter visibility Hidden. You can keep it as Visible if you wish to do so.

Click on Available Values under Report Parameter Properties window. Select "Get values from a query" option under "Select from one of the following options". Select ColorPalette in Dataset box, ColorName in "Value field", and ColorName in "Label field".

Click on Default Values under Report Parameter Properties window. Select "Get values from a query" option under "Select from one of the following options". Select ColorPalette in Dataset box, and ColorName in "Value field". Click OK to proceed.

Step8. Drag and drop Table control from toolbox.
Do set following properties for table control.
a) Table control will have three default columns. Delete one column.
b) Merge Table Header and write "Color Palette" in the textbox.
c) Write following expression in the second column of Table Details.
=IIF(Parameters!pColor.Value=Fields!ColorName.Value, "◄","")
 and write following expression in background property of same text box:

d) Select Table Details and then right click --> select "Row Group" --> select "Group properties"

e) In "Group Properties" window, click on expression button under "Group on" box and write following expression =Fields!ColorName.Value.

f) Select first text box of Table Details and then right click to select "Test Box Properties" as highlighted below:

g) in "Text Box Properties" dialogue box, click on Action and select "Go to report" under "Enable as an action" and "ChartColorCode" in "Specify a report" box. Also select report parameter pColor in Name and [ColorName] in Value as shown below:

Step9. Now we are at the final step where we need to change the properties of bar chart to change the color at run time. Right click on bar chart and select "Series Properties".

In "Series Properties" window, click on Fill. and then click on expression button to open Expression window. Double click on pColor under parameter or write following expression  manually as shown below:

Step10. Its time to run the report and see the results.

First Preview:

Second Preview: Following report will be displayed once we click on Red color under:

Third Preview: Following report will be displayed once we click on DarkOrange color under:

I hope you like this article!



  1. Great and useful article. Creating content regularly is very tough. Your points are motivated me to move on.

    Manual testing training in Chennai

    Selenium training in Chennai

    Software testing training in Chennai

  2. I am expecting more interesting topics from you. And this was nice content and definitely it will be useful for many people.

    Android App Development Company
    iOS App Development Company

  3. Nice it seems to be good post... It will get readers engagement on the article since readers engagement plays an vital role in every blog.. i am expecting more updated posts from your hands.
    Fitness SMS
    Fitness Text
    Salon SMS
    Salon Text
    Investor Relation SMS
    Investor Relation Text

  4. Really Good blog post about dynamic color coding in ssrs charts.provided a helpful information.I hope that you will post more updates like this.
    Digital marketing company in Chennai

  5. great and nice blog thanks sharing..I just want to say that all the information you have given here is awesome...Thank you very much for this one.
    web design Company
    web development Company
    web design Company in chennai
    web development Company in chennai
    web design Company in India
    web development Company in India

  6. Great list bro but i was search cities dependend on countries. it look like dependent dropdown menu

    social bookmarking website
    submit a link


  7. This comment has been removed by the author.

  8. myTectra Placement Portal is a Web based portal brings Potentials Employers and myTectra Candidates on a common platform for placement assistance

  9. very informative blog and useful article thank you for sharing with us , keep posting learn more about BI Tools Tableau Online Training

  10. We need to share the important thing that we are the third party and provide independent support service and if you face any issue or trouble during the process you may contact AVG tech support to activate or install AVG products. You just need to fill the required blanks in form and our AVG Support will contact you as quick as possible.

    For more information:

    123hpcom is an online and remote printer technical support service provider to all models of HP printers including HP Officejet printer, HP Officejet pro printer, HP Envy printer, HP Laserjet printer, HP Photosmart printer, HP Scanjet Printer, HP Deskjet Printer and other printer models across the world like USA, UK, Canada, Australia and other major cities across the world.

    For more information:

  11. Xero Customer service is available 24/7 for all types of issues related to Xero. We help people to manage their financial Account for future used.


  12. Nice blog.That is very interesting; you are a very skilled blogger. I have shared your website in my social networks! A very nice guide.
    web designing company in Hyderabad
    Low cost web designers in Hyderabad

  13. Quickbooks enterprise support
    Get Quickbooks Enterprise Support from certified Quickbooks Technical Team (+1 (833) 400-1001). We are available 24*7 for your service. Our Professional experts will resolve all issues of Quickbooks.(+1 (833) 400-1001)

  14. Quickbooks enterprise support number
    Contact Quickbooks Enterprise Support Number to resolve any of your Quickbooks errors (+1 833 400-1001). Our Certified Quickbooks
    team is available 24*7. (+1 833 400-1001) to solve your Quickbooks issues.

  15. Quickbooks enterprise support Phone number
    Solve your Quickbooks errors.Contact us to get 24*7 Quickbooks Enterprise support and get Quickbooks errors fixed by our certified
    Quickbooks Enterprise Support team. (+1 833 400-1001)

  16. Nice Article, Keep it up!
    Get Daily Tech News, Free Guides, Tips & Tricks & Softwares => Global Better Ads Standards?

  17. Thank you very much for this useful article. I really liked it

    Cpa offers

  18. This is very useful for a beginner. I simply mark this article for future reference. Keep sharing that kind of message. Thank you for sharing. Good reading. Thank you for the data. It helps us.

  19. Quickbooks is designed specifically for users of any Operating system. Taking into consideration that there is a large group of spectators, QuickBooks has planned to bring QuickBooks clients. For support services, you can get help from Quickbooks Support Phone Number +1-800-901-6679.

  20. Nice Blog It is very informative to all. If you are required technical Support in Quickbooks. You can easily contacting us, on Quickbooks Payroll Support Phone Number 1800-986-4607. We provide the best support service to the user because our team members are highly enthusiastic who puts their whole effort in resolving issues.

  21. QuickBooks Payroll Support Phone Number 1-800-986-4607 offers robust solutions related to accounting issues for the smooth running of an organization. It includes information about the management of payroll, management of company expenses, accounts receivable & Payable and many more.

  22. Thanks for sharing,very useful blog.I appreciate your work to provide clear and understandable content.Keep updating us more.
    Machine learning training institute in bangalore

  23. Thanks for this blog are more informative contents step by step. I here attached my site would you see this blog.

    7 tips to start a career in digital marketing

    “Digital marketing is the marketing of product or service using digital technologies, mainly on the Internet, but also including mobile phones, display advertising, and any other digital medium”. This is the definition that you would get when you search for the term “Digital marketing” in google. Let’s give out a simpler explanation by saying, “the form of marketing, using the internet and technologies like phones, computer etc”.

    we have offered to the advanced syllabus course digital marketing for available join now.

    more details click the link now.

  24. Amazing article useful information.

    Web designing trends in 2020

    When we look into the trends, everything which is ruling today’s world was once a start up and slowly begun getting into. But Now they have literally transformed our lives on a tremendous note. To name a few, Facebook, WhatsApp, Twitter can be a promising proof for such a transformation and have a true impact on the digital world.

    we have offered to the advanced syllabus course web design and development for available join now.

    more details click the link now.

  25. Thanks for sharing such a nice information with us...
    Unani Treatment in Bangalore


Here are few FREE resources you may find helpful.