On the first blog article in this series, we saw how to overcome the problem of selecting / deselecting rows in a scorecard on Xcelcius with a very simple example. But what should be done when the number of rows returned in scorecards is unknown? This article aims to show you how to overcome this problem.
First, here is what we want to get. We want to provide the ability for users to select only the rows displayed in the scorecard, based on information that is returned by the latter. For example, for a single scorecard:
For the first scenario, six rows can be selected by the user.
For the second scenario, we want the user to select three rows.
Finally, five rows will be selected in the last scenario.
Step 1:
To achieve this, we must initially create a scorecard and put a list box on top. Then, create a Label Based Menu, which indicates the position of the selection in the Excel sheet.
Since for the same scorecard, several sets of values can be displayed, you must have a predetermined range of movement in the Excel sheet. This range should contain at least the maximum number of rows to display in the scorecard.
As for our example, we have chosen to directly integrate the three different scenarios in the Excel sheet. Note that the data could also be returned through a Web Intelligence report, which would give the same result.
Step 2:
The second step is to create formulas in the Excel spreadsheet so that data is reported against the selection of the tab corresponding to the user's choice.
Here is the formula to move the data:
While this formula seems complicated, it is actually quite simple. Depending on the selection of the scenario ("Position of the label-based menu" 1, 2 or 3), it moves the value in the cell corresponding to different scenarios, if indeed there is a value, otherwise it returns nothing.
Step 3:
This step will allow the list box to display only the required number of rows. You must create a set of cells with the following formula: = IF (CELL <>"",".","")
This is reflected in our example:
If cell L4 (the first cell in the range of movement) contains something, then put a period, otherwise leave it empty.
Then it comes to defining the labels of the list box with the values that have previously been created.
To complete this step, it is very important that you check the option "Ignore empty cells" at the end of the series.
This option allows the list box to select the number of lines corresponding to the data.
Step 4:
Simply adjust the list box perfectly on the scorecard to make sure the "." is not visible. Finally, adjust the color of labels and the background to create the illusion of the perfect selection.
It has to be noted that the solutions shown in this series of blog articles are not foolproof. For example, as components overlap, do not forget to check the display on different screen resolutions before publishing the dashboard.
Finally, do not hesitate to use creativity in the development of your Xcelsius dashboards, it will reward you well!