Question: How can I dynamically change the colors on my charts or backgrounds based on user selection?
Answer: Kudos to the Xcelsius development team SAP Business Objects for giving us so much in SP3! One of the great new features is the option to bind the color to a cell in the Excel spreadsheet, which gives us the option to dynamically set colors on backgrounds, charts and selectors. By using an IF statement in our Excel spreadsheet, we can dynamically reference html colors to take even more control of our visualizations.
In the following example, I have 2 divisions on the dashboard, East Division and West Division. I want all my selections for the East Division to show up in shades of blue and all my West Division selections to show up in shades of green.
In the past we would have needed 2 different YTD charts and 6 backgrounds to accomplish this. Now, by taking advantage of this new functionality, we can dynamically change the colors based on user selection on the dashboard.
For the backgrounds behind the Monthly charts, I set up my Excel spreadsheet to receive my color selections for the 3 different backgrounds. By using any one of the free sites on the internet to convert RGB numbers to html, I selected each shade I wanted for my backgrounds:
In this formula, B8 = the selected division, and C2 = “East Division”, so, if B8 = East Division, then use color in cell c28, else use color in c29.
The YTD Revenue “Actuals” series is also controlled dynamically based on my division selection. On the Appearance tab and Series subtab, select the “Fill” color spot:
This opens the color dialog box, where I select the “Bind to a color” option.
The formula for this cell also compares my Division selection in cell B8 to “East Division” in cell C2, and returns my html color in cell B2 if it is, else the color in cell B3 if it is not.
Finally, I made my Label Based Menu “selected” color dynamic based on the same logic.
By Binding the “Selected Color” to a dynamically changing cell, the color selection will change based on which division the user selects on this dashboard.
Not only does this new feature give the Xcelsius designer more color control options, it can also reduce the number of components need on the dashboard. As mentioned earlier, in the past, we would have needed 2 different YTD charts and 6 backgrounds rather than just 3 to accomplish this same behavior.