Select All or Select None
The ability to update the status of multiple logically related Check Box components with one click is a common request when using SAP BusinessObjects Dashboards (Xcelsius).
Another frequently requested feature is the “Invert Selection” functionality, when on “one click” a user can deselect (uncheck) all of the currently selected items and select (check) those, which were not in the original selection.
The benefits of having these features might not be obvious while dealing with just 3-4 check boxes. However, when we have more of them and try to check only the few that we are interested in, such functionality becomes highly desirable.
The challenge here is making the Check Box component be checked or unchecked dynamically. In Part 1 of this series I demonstrated how to accomplish a dynamic status update (e.g. Reset) of a single Check Box component. This post is about applying that approach to multiple Check Box components.
Let’s build a model with multiple check boxes that represent geographical regions.
We want to support a selective update of individual check boxes and also a simultaneous update of all of them or just those that belong to a predefined group.
1. Set up a spreadsheet as shown in the figure below. For this example, the region “East” is associated with the group 2 and the other ones with the group 1 (see column H). These group names have no special meaning and could be replaced with other identifiers, if it’s also reflected in the dependent formulae.
2. Add to a canvas four sets of components, each of which represents the region to be selected at run time and consists of two check boxes (checked and unchecked), a label and a push button, as shown in a figure above.
3. Link Label components to the cells with the region names in the range I2:I5.
4. Bind the push buttons’ Source Data properties to the cells in column A and the Destination properties to the cells in the column G in the same row.
5. Make the push buttons transparent as explained in my Custom Push Buttons post.
6. Set the Item property of one of the check boxes in each set as a Checked and the other one as Unchecked. The Item property is located on the Common tab of the Behavior section.
7. Configure Dynamic Visibility for each check box in a set by linking their Status properties to the cells in the Status range G2:G5 (shaded in yellow). Set the Key property for the checked check boxes to 1 and to 0 for the unchecked ones.
8. Stack check boxes in each set and place the corresponding transparent push buttons on top.
9. Resize and align Label components with the check boxes.
10. Add to the model five new push buttons and label them as All, None, Invert, Group1 and Group2.
11. All push buttons will use the cells in the Status range (G2:G5) as a Destination property.
The Push Button component fires only on a click and stays neutral upon dashboard load. It allows us to use multiple push buttons pointing to the same destination. The ordinal positions of the components in the Object Browser do not impact the result.
12. Button All uses as a Data Source the range B2:B5 with values 1 in all cells.
Data Source property of the button None is bound to the range C2:C5 with values 0 in all cells.
Invert button points Data Source property to the range A2: A5 with the formula =1-G2 in the cell A2 copied down in other cells in the range.
Buttons Group1 and Group2 illustrate the selection that is based on a custom logic. They use as the Data Sources the ranges D2:D5 and E2:E5 correspondingly. The formula in D2 is =SIGN(H2=1) and the formula in E2 is =SIGN(H2=2). They are copied down the ranges and flagging regions in the Group1 and Group2.
13. For the visualization of underlying run time changes, add a Spreadsheet Table component and bind its Display Data property to the spreadsheet range shown in the figure above.
14. Open a Preview. As depicted in the following figures, we can flip the values in the cells in the Status range by selectively checking/unchecking check boxes and also can simultaneously check/uncheck check boxes in the various logical groups using the push buttons.
One of the practical applications of this functionality is a toggling of the display of a chart series based on the statuses of the corresponding check boxes. I will show how it can be implemented in one of the next posts.
Leonid Koyfman is a BI professional with over 10 years of experience in a database design and development and in delivering reporting solutions using enterprise reporting technologies. He is a big Xcelsius enthusiast passionate about dashboard design and other kinds of data visualization. In his leisure time Leonid enjoys solving mental puzzles and hiking in the Bay Area with his family.