Skip to Content
Author's profile photo Sara G Ayer

Coloring Chart Series Based on the Drill down Selection

Like the image below , the coloring series based on the selection made in the above chart (pie chart here) , the “series color” of the below chart (drill down chart-column chart here) can be changed.

This trick can be followed for any chart. To do this, follow the simple steps below.

Capture61.PNG

  1. Drag a chart and map values to it, enable data insertion.

Capture62.PNG

2.     Map the Drill down data, and destination. The destination(yellow colored row here) of this drill down will be the values shown in the column chart here.

Capture65.PNGCapture63.PNG

3.     In the appearance tab, select the series colors such that, when a particular selection is made in the main chart, that selected color is shown in the chart down below (column chart here). Note the RGB values of these colors, and put it in columns along with the data labels (column E and F here as shown below).

The RGB color formatting should be in this format

=”(255,25,1)”

In the cell F7 (yellow colored cell) write a formula to pick the selected color.

=vlookup(A15,E6:F6,2,False)

Capture64.PNG

Column Chart Linking:

The destination of the drill down is to be linked to the column chart.

Capture66.PNG

The series color of the column chart is to be bound to the cell having the formula (yellow colored cell here in the image below).

I have attached sample file with it. change the extension to “.xlf” to view the file.

Capture67.PNG

And thats how it is done.

Thanks,

Sara

Assigned tags

      15 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Sara,

      Blog is very very coloful!

      Colors shows the value of the blog.

      Thanks.

      Regards,

      Hari Suseelan

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thank You  Suseelan

      BR,

      Sara

      Author's profile photo Former Member
      Former Member

      Hi Sara,

      You are welcome. Keep posting.

      Regards,

      Hari Suseelan

      Author's profile photo Former Member
      Former Member

      neat trick!

      very original application of the software . . . I'm going to use this one!

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thanks Mike Lundquist

      Sure , try it out. It can be used in many scenarios where dynamic coloring needs to be done without using alerts.

      BR,

      Sara

      Author's profile photo Former Member
      Former Member

      Nice Blog . I am also going to try it 🙂 ...

      I am not able to open your attachment, saved it with extension .xlf still getting error. We are on SAP BI 4.1 SP1.

      Regards

      Shabnam

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Hi Shabnam

      You just need to follow the steps I ve mentioned, .xlf which I ve attached could have got corrupted. Am pretty sure the images and the description are more than enough to achieve it, try with it, lemme know if get stuck anywhere in between , I am right here to clear them. 🙂

      BR,

      Sara

      Author's profile photo Former Member
      Former Member

      Hi Sara,

      I have a small question. Here you have hard coded colour values as (225,25,0) etc, is their any way to capture the default colour which is shown in series during interaction time?

      Regards

      Shabnam

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Hi Shabnam,

      Yes the RGB color formatting should be in this format

      ="(255,25,1)"

      I am not sure about your question, if you are asking about the color code of the dynamic colors, then you can see it by mapping a spreadsheet table to the formula cell, this will show you the color code of the color appearing during the interaction.


      Author's profile photo Former Member
      Former Member

      Hi Sara,

      Blog is very Attractive,The way you presented will tempt everyone to try from their side.

      Regards,

      J.Sakthikumar

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Thank you Sakthikumar

      Regards,

      Sara

      Author's profile photo Former Member
      Former Member

      Nice document Sara.. Thanks for sharing it..

      Author's profile photo prasad seerapu
      prasad seerapu

      Nice one..

      Can we do the same thing in Web Intelligence?

      Author's profile photo Sara G Iyer
      Sara G Iyer
      Blog Post Author

      Hi Prasad,

      I don't think its possible in Webi. Unlike dashboard, in webi we cannot choose exact color for a graph. There we have defined color palettes and from the defined color palettes dynamically color has been taken. So even if you link graphs in webi, i dont think it will work in webi.

      Regards,

      Sara

      Author's profile photo Ivan Sok
      Ivan Sok

      Great guide to this color chart! Thanks!