Eliminating Rows with No Data from a Table or Column Object Using dynaSight

Sometimes when you are displaying data from your data sources you are in a situation where there are rows containing no data (for example: no sales for a given client). However, due to constraints from your data source you cannot modify the query to eliminate those rows.

In Fig. 1 below you will see clients that do not have any sales in the given region and month. This can often make a report difficult to read and distract from those clients that actually do have sales.

Figure 1

Ideally the data should be displayed as shown in Fig. 2.

Figure 2

dynaSight provides several ways of doing this without forcing you to modify the query or any of the data on your database. One of the neat ways of doing this is using the CHOICE function.

The CHOICE function allows you to filter rows in a table or column object based on criteria of your choice.

Step 1: Define the conditions that will decide which rows are to be displayed and those which are not to be displayed. In the case of Fig. 1 we do not want to display any rows where all three data columns are empty. In Fig. 3 you can see the object numbers.

Figure 3

The condition that all rows are empty can then be expressed as follows in terms of the dynaSight script language:

NOT ( ISEMPTY ( [OBJ5;COLUMN1] + [OBJ5;COLUMN2] + [OBJ5;COLUMN3] ) )

Step 2: We can now place this formula in a column object and see the results in Fig. 4.

Figure 4

The column object on the right of Fig. 4 contains the formula described in step 1. Notice that we have now identified the rows we want by having a 1 in the new column object.

Step 3: We will place the CHOICE function in a button as follows:

CHOICE ( [OBJ7] ; [OBJ3] ; [OBJ5] )

The first argument is the object (or formula) with the criterion to filter the rows. The arguments 2 to N (you can have as many as you want) list the objects to which the selection of rows is to be applied. In our case it is the column containing the clients and the table with the data. The result is shown in Fig. 5.

Figure 5

If you click the button with the formula, the result will be what we wanted in Fig. 2. We now need to automate this so that the filtering of rows occurs dynamically whenever we change the settings in the pop-up menus.

Step 4: For each of the two pop-up menus we place the function, as shown in Fig. 6, in the On switch event of the pop-up.

Figure 6

This script refreshes the objects and then reapplies the CHOICE function. The result is that every time you switch any menu the filtering will occur on-the-fly.

At this point you might consider your work done or maybe not…

Step 5: We always like to be as economical as possible with objects so in this last step, we will get rid of the column object where the 1s and 0s are displayed (object 7). Here we will use one of the very elegant features of dynaSight: very often, formulas and objects are interchangeable as long as the dimensions of each match.

We will simply re-write the formula defined in step 3 for the button as follows:

CHOICE ( NOT ( ISEMPTY ( [OBJ5;COLUMN1] + [OBJ5;COLUMN2] + [OBJ5;COLUMN3] ) ) ; [OBJ3] ; [OBJ5] )

All we have done is to replace the first argument in the original formula with the condition formula of object 7 (which we defined back in step 1).

We can then delete the column object and are left with what you see in Fig. 7.

Figure 7

Now you can move the button to another layer and you are finished.

next tip