TabReference with Response Lables

 

Another interesting use case for retrieving additional cell information is access to the cell response labels. Let’s say that our connection is dynamic and it queries two most recent periods, which means we do not know from the start what are the years that will appear in the results.

 

Still, we want to use the Dataset year in the table header or somewhere in the text box/page title. We can automate this by retrieving the Dataset year used in Query via TabReference.

Let’s take a look at the following Grid, where columns with data refer specifically to 1st or 2nd year from the data. We decided not to use dynamic columns on purpose to better illustrate this use case.

 

 

Since switching countries can change the years used, we cannot simply put the year into the header manually. In this scenario, we need to use the ResponseLabels property in our expression. We can refer to any cell within the Query to retrieve all labels of categorical variables associated with this cell. Let’s try with the first year and Sample total for makes.

 

 

You can see that it is not directly usable. This is because there may be more than one label attached to a given cell - in our case, it can be year or make. The result of ResponseLabels query in such a case is a list of those values, in our example list containing “2020” and “Sample total”.

 

In this case, we need to create an expression that will retrieve a specific item from the list - we can use the ItemAt function for this. Since the year label is in 2nd place in our list, we need to use the ItemAt function asking for items at position 2.

 

 

We can then use a similar formula for the second year. Note that expressions can be copied and pasted between different places on the slide, so once we have the formula for 2020 working, we can copy it to the second cell and switch the Dataset year position to 2.

 

 

Note that at this moment expressions manager does not provide any preview of the list of values provided (i.e. the available response labels) so it may require us to try ItemAt for different positions to find the right number to retrieve what we want. Once we know this value, it will not change if the Query definition does not change, so we can safely keep it in the expression and reuse it in multiple places. To simplify the search, especially in cases of longer lists, we can use the Join function to temporarily display all response labels at the bottom of the Edit expression window. This way we can quickly identify that the one we are looking for is at position 2.

 

Another important thing to remember is that ResponseLabels returns a list of values in each case, even if there is only one value available. This means that even if we have a simple table with just one label per cell, we need to use ItemAt(response labels, 1) to retrieve this label.