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.
- Drag a chart and map values to it, enable data insertion.
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.
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)
Column Chart Linking:
The destination of the drill down is to be linked to the column chart.
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.
And thats how it is done.
Thanks,
Sara
Hi Sara,
Blog is very very coloful!
Colors shows the value of the blog.
Thanks.
Regards,
Hari Suseelan
Thank You Suseelan
BR,
Sara
Hi Sara,
You are welcome. Keep posting.
Regards,
Hari Suseelan
neat trick!
very original application of the software . . . I'm going to use this one!
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
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
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
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
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.
Hi Sara,
Blog is very Attractive,The way you presented will tempt everyone to try from their side.
Regards,
J.Sakthikumar
Thank you Sakthikumar
Regards,
Sara
Nice document Sara.. Thanks for sharing it..
Nice one..
Can we do the same thing in Web Intelligence?
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
Great guide to this color chart! Thanks!