The checkbox is one of the most common UI elements that allow its users to have binary choices and it is available out-of-the-box in Xcelsius/SAP Dashboards. However, the Xcelsius component is limited due to the inability to control the selection or reset to its default state in run-time programmatically. In this article, I will describe how I altered my design to overcome this challenge.
My dashboard displays a table representing an item per row, filtered by warehouse location via a dropdown list and requires checkbox in each row. The design permitted pagination and a maximum of 10 rows per page. I added 10 individual checkboxes in each row of a scorecard component.
The user will click on the checkbox to indicate an item to be picked. The boxes need to:
- Insert Y when checked and N when unchecked
- Default to unchecked status when loaded
- Default to unchecked status when warehouse(dropdown) selection is changed
- Default to unchecked status when user navigates to next page
- Switch between checked and unchecked status by “Select All” check-box.
The first two requirements are achieved easily by configuring standard properties.
#5 can be achieved by using 2 checkboxes for each row – one set to default checked and one to unchecked status. If the destination of checkbox A1 and checked value is Y, then dynamic visibility of the checked boxes will be Status=A1 and key=Y and for un-checked boxes Status=A1 and key=N
#3 and #4 produced the biggest challenge. Because the checkbox component does not have any “Selected Item” property, once changed there is no way to revert the selection other than by physical mouse click. Sounds simple, but without the ability to bind the selection (status), a checked box under one warehouse will remain checked when warehouse is changed. This was also an obstacle with re-using the boxes across pages. It required setting up 200 check boxes for a 10 page dashboard. I did not like such design.
I replaced the check boxes within the table with combo boxes with 2 values – Yes and No. This allowed me to achieve all 5 required features. It still needed some tricks, which I am going to share with you.
Below is how I setup one combo box. The steps marked * need to repeat for remaining 9 combo boxes. Steps marked ** indicates the cell number should increment.
- Bind the scorecard to Scorecard!$D$1:$F$11
- Bind the Picked combo box as below *
Label to “Yes”:”No” Control!$N$2:$N$3
Insertion Type = Row
Source = Y:N > Control!$M$2:$M$3
Destination = Scorecard!$H$2 **
- In the behavior tab, the selected item property = Control!$N$8 **
- Control!$N$8 ** is calculated based on the value at N6 which is the destination of “Select All” checkbox. If the destination cell is null (default) or has “No” (when manually unchecked) then N8 ** will be “No” and if N6 has “Yes” then N8 will be equal to “Yes”. This will allow the values of all the combo boxes to change to Yes or No based on the “Select All” selection.
- The “Insert Selected Item” is bound to Control!$N$8 ** too, so that when we want to reset the combo box back to “No” it changes from “Yes” to “No” and recognizes it to be a change.
- The dynamic visibility of the combo box is:
Status = Scorecard!$I$2 ** and Key = 1
The above cell has a formula that says if there is a value in the Item Location column, then return 1, or else 0. This will ensure there are the same number of combo boxes as there are records per page.
Now let’s take a look at how to setup the Warehouse filter combo box in order to reset the Yes/No picked filters to “No” when a warehouse is changed after the user has switched to “Yes” in the previous warehouse.
- Next to the column (F) for warehouse list, I added two more columns and hard coded them with No and Yes ( I and J ) respectively.
- Label and source are bound to Control!$F$8:$F$57
- The destination is Control!$F$3
- Now I added more series, one for each of the 10 records. For all 10, Insertion Type = Row and Source = Control!$I$8:$J$57 and Destination = Control!$N$8:$O$8 **
- I added another combo-box to be hidden after configuration. This will filter the data for the rest of the columns in the scorecard as the warehouse changes.Label : Data!$D$171:$D$224 Insertion Type = Filtered Rows Source = Data!$D$171:$F$224 Destination = Data!$D$17:$F$166 Selected Item = Control!$F$3
- From the destination area Data!$D$17:$F$166 the data is filtered by pages and then displayed in the scorecard. How? Well that’s another blog.
- The Yes/No dropdowns are reset to “No” for each page too. I added another combo-box to be hidden after configuration and the label is bound to the page number column.
I also have a column which says “No” for each valid record with page number. This is the source range for the combo-box.
The destination is Control!$N$8:$N$17 which are the cells that act as default value for the Yes/No combo boxes.
Hence the dropdowns will be reset to “No” as the user navigates to next page.
Using the approach above I was able to not only to deliver the dashboard without losing any functionality of toggling the Picked column between Yes and No, but also I used only 10 Yes/No combo-boxes reusable across all the pages. Should the design need to increase number of pages, there is no need to add more combo-boxes for the new pages!!
You may wonder how the changes in the dropdowns are being captured? I used the InfoBurst Write Back feature. Again, I will save that interesting feature for another blog. The Select All checkbox did come up with similar challenges but I managed to use it as a checkbox and reset as needed. You ask how? A sequel blog maybe!
Please try the above tricks and let us know your scenario where it was useful. Also, please share this tech tip with others so everyone can benefit and save some trouble and time and be on the lookout for future tech tips!