I have been asked countless times to explain the value of Reverse Selector, which is actually difficult to articulate unless you have spent some time trying to build complex calculators with many inputs. This add-on component for Xcelsius 2008 is one of the few that Centigon Solutions sells on the website. Hopefully this will clarify how this component is useful to your dashboard projects.

Reverse selector provides a unique functionality to Xcelsius and is designed specifically for dashboards/calculators that require multiple data inputs. Generally, the Reverse Selector is best used for calculators or forms containing many variables that an end user controls. Coupling single value components or input text boxes with a Reverse Selector will significantly improve the configuration process, enabling end users to input an unlimited number of variables with ease.

To understand the value of this Xcelsius plugin, we will address a use case where Reverse Selector can reduce overhead, save development time, and streamline a dashboard:

Use Case
In the calculator below we want to first filter through sales reps by region, then calculate how increasing each rep’s sales can contribute to the overall target. The technical challenge for the Xcelsius model is allowing a single slider to dictate a value for each sales rep, and then persist the values so they can be used in the calculation.

Download Source Files- Requires Reverse Selector
>>Get Reverse Selector Trial

Problem

The traditional solution to this problem included stacking multiple sliders on top of each other, then assigning dynamic visibility to each slider based on the selected sales rep. With a 1-1 relationship between a single value component and the cell it controls, scaling this solution is a major challenge. While 2 or 3 stacked sliders are not a problem, 10 or 20 quickly generates a dashboard project that is heavy and difficult to maintain. Furthermore, the time to configure each selector could lead to hours of un-necessary work.

Solution
Reverse selector is a great alternative to simplify design and management of calculator functionality within a dashboard. In this example, the Reverse Selector will use one slider to insert and persist the values based on the selected sales rep. The following directions illustrate how to setup a Reverse Selector to achieve the following dashboard.

Source Value- The Source Value is a single cell where you will also link your single value component or input text.

Destination Range- The destination range is the column where you will insert and distribute “Source Value.”

Row Index- The Row index will dictate where the Source Value will be placed within the destination range.

With these three properties set, it is extremely important to set the appropriate behavior for the Reverse Selector.

Update Destination Range- This behavior option dictates when the Reverse Selector will insert the "Source Value" into the "Destination Range." The most commonly used behavior is "When Source Value Changes." This option triggers Reverse Selector to insert any time the source value changes. In other words, as the slider modifies the source value, Reverse Selector will insert its value into the destination range based on the "Row Index."

Source Value Lookup- This behavior is the most important feature making Reverse Selector unique because it enables bi-directional communication between the Source Value cell and the Destination Range. As the Row Index changes, the slider component will need to reference the persisted value within the destination range. The Source Value Lookup will retrieve this value and place it in the Source Value cell. Without this capability, the reverse selector will simply overwrite the destination cells.

Tips for Using Reverse Selector

• When using this component, remember to check the appropriate behaviors.
• Do not have a selector insert into the Source Value component.
• Check out the templates on the Centigon Solutions website to see some of the different ways for implementing this component.

Recommendations for future releases:

• Allow reverse selector to insert into a range, rather than a single row or column.
• Make the Source Value Lookup behavior checked by default.
• Rename the Reverse Selector component.

>>Get a free Reverse Selector Trial

During my webinar last week Xtreme Makeover, I showed a demo dashboard where a user could drill down from a pie chart down to a bar graph and pass the color from the selected slice to the chart below. This is a simple technique for creating consistency with color, which facilitates intuitive analysis; if it were only as simple to setup in Xcelsius… In essence, I am using chart alerts to dynamically change the color for a single series chart. This method will only work with bar and column charts.

Download Source Files

Inside of my spreadsheet I associate a multiplier for each product line (1,2,3…). When I enable the pie chart drill down feature, I include this multiplier in my source data and data insertion range.

Directly under the insertion range where we link the bar chart’s data, I perform a simple calculation using the multiplier (=[value]/[target multiplier]). These rows highlighted in green within the spreadsheet are the bar chart alert targets.

Inside of the bar chart’s alerts tab, I color coded alert levels for each value in my pie chart.

With everything hooked up, you get a nice effect as someone clicks on the pie chart. What would be a better solution?...Having an ability to bind colors to the spreadsheet… As a user community you should definitely request this functionality on the BOBJ support and forum sites. With an ability to bind colors in the spreadsheet, you could enable this functionality for all components within Xcelsius, and in turn facilitate better analysis for end users.

On November 12 at 10AM PST, I will be conducting a 30 minute webinar with Business Objects sharing practices for developing better looking dashboards with Xcelsius. This presentation is based on some of the articles that I have written about dashboard design. The goal is to shed light on some of the building blocks for building dashboards that not only look good, but also promote non-intrusive easy to use interfaces for interactive dashboards. You can view and sign up for the event using the link below, and I look forward to sharing some helpful information with the community.

https://www.sdn.sap.com/irj/sdn/businessobjects?rid=/webcontent/uuid/f0a68ded-5d87-2b10-6682-c541930da65e

Xcelsius’ Excel paradigm for binding data inherits Excel’s absence of dynamic cell formatting. In Excel, you define a cell format as text, percentage, value, date, etc... When you bind a component to a cell or range, it will automatically inherit the formatting. If your hoal is to filter data by measures, you may have found that there is no way to dynamically change number formats on the fly. For example, if you have a table that is filtered by sales, volume, and growth %, dynamic formatting becomes a problem. While there is a good workaround for displaying values in a table, there is no good option for graphs.

The solution for displaying dynamically formatted tabular data is converting values to text using the TEXT() formula. The beauty of the text formula is the option to define number formatting. TEXT(value,text_format)

TEXT(A1,$#,##)
TEXT(A1,##%)
TEXT(A1,#,###)

The following example illustrates how this text formatting can be utilized to present tabular data in the correct format without using multiple components. Hopefully at some point we will see this dynamic formatting built into Xcelsius as standard functionality for charting. One of the few drawbacks of Xcelsius’ Excel paradigm is the borrowing of Excel’s lack of dynamic cell formats. In Excel, you define a cell format as text, percentage, value, date, etc… When you bind a component to a cell or range, it will automatically inherit the formatting. In most cases this is a good feature. In others where you are filtering data by measures, it creates a problem. For example, if you have a table that is filtered by sales, volume, and growth %, dynamic formatting becomes a problem. While there is a good workaround for displaying values in a table, there is no good option for graphs.

The solution for displaying dynamically formatted tabular data is converting values to text using the TEXT() formula. The beauty of the text formula is the option to define number formatting. TEXT(value,text_format)

TEXT(A1,$#,##)
TEXT(A1,##%)
TEXT(A1,#,###)

The following example illustrates how this text formatting can be utilized to present tabular data in the correct format without using multiple components. Hopefully at some point we will see this dynamic formatting built into Xcelsius as standard functionality for charting.

Download Source Files

It was wonderful putting faces to the many blog readers who attended the BOBJ user’s conference this week in Dallas. Though the least technical topic on my list was chosen by BOBJ for me to present, it was fun showing off some of the new Centigon Solutions plug-ins. This was definitely a coming out party for my company since there was a lot of Xcelsius buzz through-out the conference. I appreciate the compliments on the blog and the work I have done in the last year. I certainly came back re-energized to ensure I continue to keep writing helpful content for you to leverage as you build dashboard solutions for your business.

Thank you all for your support,

Ryan Goodman