Jun
6

Dynamic status update of Check Box components in Xcelsius: Part 1/4

Part 1: To Check or not to Check

One of the most common requests when using SAP BusinessObjects Dashboards (Xcelsius) is the ability to selectively reset Selector components back to their original state. Unlike many other selectors, the task isn’t as easy to achieve with the Check Box component. We can’t dynamically update its Checked/Unchecked status because the changes of the value in the cell that the component is bound to don’t affect the component itself. Lack of this functionality also makes it difficult to support updating the statuses of multiple check boxes at once. This post is the first of a series in which I’m going to share my solution to that challenge and examine some of its practical applications.

Xcelsius Check Box component looks similar to its Excel counterpart, but acts differently. In Excel, the connection between a Check Box control and its linked cell works both ways, as implied by the control’s property: Cell Link. Updating the Check Box status changes the value in the linked cell and vice versa. So in Excel, we can dynamically change a linked cell value and see immediate change in the control’s status. That’s not the case with the Xcelsius Check Box component. The hint is in the component’s corresponding property name: Destination. It only assigns the value to the target cell. If that cell is updated by other components, the Check Box component is not notified about the change, and its status stays the same.

Conduct a simple test and compare the results using the same scenario in Excel and Xcelsius:

1. Add a Check Box and a Spin Button control from the Excel’s Form Controls section to a worksheet and link both of them to the cell C2.

2. Set the spin button values range from 0 to 1.

3. When the check box is checked / unchecked the cell gets TRUE / FALSE values.

When the spin button is clicked Up or Down it changes values in the linked cell to 1 or 0 and at the same time the check box’s status gets toggled as well.

4. Now, let’s see what happens in Xcelsius.

Add Check Box, Spinner and Single Value components to a canvas.

5. Link all of the components to the cell C2 and open a Preview.

6. When the check box is checked or unchecked the destination cell gets values of 1 or 0.

But when the spinner updates the value in that cell the check box’s status doesn’t change.

This means that in the current version of Xcelsius there is no built-in functionality to dynamically toggle the Checked/Unchecked status of the Check Box component.

Luckily, there is a work around. Unlike Excel Form Controls, Xcelsius components have a Dynamic Visibility property. Using its magic in a conjunction with a Push Button component, we can simulate Excel’s check box functionality in Xcelsius.

Let’s create a model that demonstrates this technique:

1. Add two Push Button and two Check Box components to a canvas. We are going to extend the methods described in another post, Customizing a Push Button and combine check boxes and a push button into one custom push button. We also can use the Image Components instead, if we have a pair of good images of a checked and unchecked check box. It makes sense especially if we want to display the check boxes that look fancier than Xcelsius ones.

2. Set Item property of one of the check boxes as a Checked and the other one as Unchecked. Item property is located on the Common tab of the Behavior section. Bind Source Data for the push buttons to cell A3 and Destination to H3. The Excel formula in A3 is =1-H3 and the initial value in the H3 is 1.

3. Cell H3 will define the Dynamic Visibility Status property for both check boxes. Set the Key property for the checked check box to 1 and 0 for the unchecked one.

At this point the model set up should be similar to the following figure:

1. One of the push buttons will be displayed as is and the other one will be transparent. Its Label property should be blank and the Show Button Background property unchecked.

2. Stack check boxes and place the transparent push button on top of them.

3. Add Spreadsheet Table component to see what happens behind the preview.

Now we are ready to test.

When a preview starts we can see that the check box is checked and the values in A3 (shade in green) is 0 and in H3 (shaded in yellow) is 1.

When a user clicks on a check box, the unchecked one is displayed instead, and the values in H3 and A3 get changed to 0 and 1, respectively.

Clicking on the unchecked check box brings the model to the initial state. And, as we remember, the real click happens on the push button, not on a check box. But it’s transparent to a user.

Clicking on the shown push button updates the value in a status cell H3. But this time, unlike out-of-the-box Xcelsius behavior, the status of the displayed check box, from the user experience perspective, is also changed. In reality, we just alternated the display of the check boxes, but a user doesn’t know it (and doesn’t care).

This approach allows mimicking the Excel check box functionality in resetting the Checked/Unchecked status of the Xcelsius Check Box component. It also gives us a working model that we can extend to more complex designs that provide rules based dynamic selection of logically grouped multiple check box components. I’m going to show such an example in the Part 2 of this series: Select all or Select none.

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.

Tags: , , , , , , , , ,

11 Responses to “Dynamic status update of Check Box components in Xcelsius: Part 1/4”

  1. Ethan says:

    I recentrly had a similar need, and found this checkbox add-on to do exactly what I needed:
    http://www.forumtopics.com/busobj/viewtopic.php?p=827263#827263
    No extra checkboxes or dynamic visibility needed.

  2. Leonid says:

    @Ethan,

    You are right, the component you mention does the core job. However, there are a few issues with it.

    The component you pointed to is very basic and doesn’t allow appearance customization (colors, size etc.)
    Also, it’s not officially offered on the web site http://www.xcelsiuscomponents.com.
    We need to use a “back door” to get the component and a future availability of the download is in question.

    The method I described in the Part 1 provides more flexibility.
    It is also shows the way to deploy custom images for the check boxes.

    When we are unhappy with the existing components we can always try to
    • find needed add-on online (if we are lucky and trust the source)
    • develop a new add-on (if we have tools, skills ant time)
    • combine available components (overlay, grouping etc.) to simulate desired functionality or appearance.
    Sometimes, none of these works, and we just dream that one day the Xcelsius team will create such a component for us.

    The point I’m making with this series is that this functionality should be available out of the box.

    It will not only make check box’s behavior consistent with other selectors, but, more importantly, enrich Xcelsius user’s experience.
    This functionality has many potential applications, and I will show some examples in the following posts.
    The component you found is a good proof of concept: it’s doable and just needs to be extended.
    It’s OK if from a backward compatibility perspective the default setting stays “as is” and the linked behavior is optional.

    I hope the extended check box component will be implemented in one of the new releases.

  3. Ken Jones says:

    Great stuff Leonid. I have been frustrated by this check box component issue in some of the models I have built. I specializing in building financial ROI models based on xcelsius. Keeping consistency in the model when you have multiple check boxes controlling the same formula on the spreadsheet is a pain. I have thought about the “push button” approach, but considered it too clunky and time consuming to implement. Now, I will give it a try.

  4. Leonid says:

    @Ken,
    I’m glad you found it useful.
    I expect other parts of this series to be posted soon, so you’ll save some development time using my examples as patterns.

  5. David Lai says:

    Hi Leonid,
    Unfortunately we are required to jump through hoops to accomplish such a simple task which really doesn’t allow us to use the true checkbox component by itself. All the developers need to do is add the selected item bind button which they already have code for.

    I think that we should all make some more noise so that SAP can hear us and actually do something :)

    Not sure if you read my blog post but I’ve already started my rant.

    Regards
    David

  6. Leonid says:

    Hi David,

    I have troubles to access your blog. Is it up and running?

    Regards
    Leonid

  7. [...] 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 [...]

  8. Zaif says:

    Nice work!! Waiting for Select All/Deselect All.

  9. Leonid says:

    @Zaif, start with “Part 2: Select All or Select None” that was posted on Jul 26
    Part 3 that demonstrates merging this technique with charts is on the way.

  10. [...] of Xcelsius Check Box component’s statuses that started in the previous parts of this series: Part 1, Part 2, and Part [...]

  11. [...] 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 [...]

Leave a Reply