Grid: Multiple Dynamic Columns

Creating a Grid with Dynamic Columns

This example uses the basic grid we created and adds dynamic columns to include data for different brands.

The contents of the dynamic rows (response labels and values) are tied to the column label.

Crosstab

For reference, this is the crosstab setup in Query:

Start by adding a grid to a new page. Grid Widget can be found under Advanced Visuals in the left ribbon.

Drag and drop the Grid Widget on the empty page.

Click the Edit on the new grid to get into Edit mode.

For this example, the “purchase reason” label will be added to the first column, and the “purchase reason” value (data) will be added to the second column.

The third column is not needed, so it needs to be deleted.

Click on the third column of the grid (Header 3).

In the bottom menu bar, click the grid icon with the red X, then select the Delete column option.

Defining the dynamic column and dynamic row expressions

As a reminder, a Dynamic Row is needed because we want to create a reference for the purchase reason listed in the first row and have the rows for all the remaining purchase reasons populate automatically (and dynamically).

Start with defining the Dynamic Column;
Click and select both the Header 1 and Header 2 cells;
This will make the grey bracket located above Header 1 and Header 2 expand to include both columns instead of just appearing over one column;

Important: Ensure the grey bracket includes both columns before proceeding.

After selecting the bracket, it will turn blue and a menu bar will appear below the grid.

After turning on the Dynamic Column option, the bracket will turn red to indicate that the data range needs to be defined.

After switching on the Dynamic Column, click the Expression option (EXPR) in the menu bar.

Use the tabReference expression to create reference for the table.

Here are the steps to create a tabReference for our table:

1. Type in tabReference and open a bracket;

2. Type in @ and select the tab where you have your purchase reasons analysis.

3. Then add:

Dataset year as the first question;
Specific year included in the chosen dataset as the answer to the first question;
Make of new car as the second question;
*All as the answer to the second question;
Reasons for purchase as the third question;
1 - which means the first item that will be available in the query results for this question;
% of column, sample base as your statistic;
Value as the property.

4. Close the bracket and preview the results of the newly created table.

Since *All was selected for the Make of new car question, all makes are listed in the table.

Note: If all Purchase Reasons were shown instead of makes in this table preview, it most likely would be caused by the *All selection being used on the Purchase Reason question instead of the makes question. The tabReference would need to be revised and corrected.

As the tabReference syntax is shown as correct with a green check mark, you can click the OK button.

The bracket that was previously red is now blue. The reference for the Dynamic Column was created successfully.
Turn the Dependent Rows option on.
Change the Compare drop-down box from 10 to 5. 10 is the default number of columns that will be shown across the grid.
We will change this to 5 to make the contents of the grid more manageable and easier to read.

We will use an expression to fill the column cells with vehicle make labels later. The next steps will be creating additional expressions to define the Dependent Rows. Dependent Rows are needed because we want to create references for the purchase reasons listed in the rows that are associated only with the specific vehicle make shown in the column headers.

Below the column headers, hover over the left border of the first cell and a blue bracket will appear;
Click the blue bracket to display the menu for the Dynamic Row, then turn it on.

A Dynamic Row is needed because we want to create a reference for the purchase reason listed in the first row and have the rows for all the remaining purchase reasons populate automatically (and dynamically).

Turning on the Dynamic Row option will make the bracket red. This indicates that the data range for the Dynamic Row will need to be defined.

After turning on the Dynamic Row, click the Expression option (EXPR) in the menu bar.

The expression editor window will appear.

Use the tabReference expression to create reference for the table.

Here are the steps to create a tabReference:

1. Type in tabReference and open a bracket;

2. Type in @ and select the tab where you have your purchase reasons analysis;

3. Then add:

Dataset year as the first question;
Specific year included in the chosen dataset as the answer to the first question;
Make of new car as the second question;
colProperty(@id) in order to match the ‘make of new car’ responses in columns automatically;
e. Reasons for purchase as the third question;
*All - which will include all the responses in the dynamic selection, here *All is being used to include all purchase reasons for each of the matched columns that we previously selected;
% of column, sample base as your statistic;

h. Value as the property;

4. Close the bracket and preview the results of the newly created table;

5. Select OK.

Note: colProperty allow retrieval of any property (i.e. id, label or value) from a given row/ column within the dynamic entity.

The bracket on the left border that was previously red is now blue. The data reference for the Dynamic Rows was created successfully.

The next step is to create additional expressions to fill the cells with some content.

As a reminder, the setting for the dependent rows option was previously turned on. This means that the contents in the dynamic rows are now connected to the dynamic columns. For this example, we used colProperty expression, which allows retrieval of any property (i.e. id, label or value) from a given column within the dynamic entity. The id option looks up the reference and creates associations by matching codes that are used internally. The id option should be used when working with data references and questions from the same dataset and/or study family.

When working with data references and questions from different datasets and/or different study families, it is recommended to use the label instead.

Internal codes can vary between study families, so in this scenario codes are not the ideal criteria to create matches on. The label option ignores the internal coding and uses the actual response label text to create matches.

Important: When you use a dynamic row or column, it only affects the Grid structure by adding rows or columns, not the content within the Grid. The data from this Table is available for you to use in the Grid, but it's up to you to decide which data to include in the final report and how to present it.

Adding data to your grid structure

Click on the first (left) cell. It will turn blue to indicate that it is currently selected;
A menu bar will appear at the bottom of the page;
Click the arrow next to the EXPR option and select RowProperty(Label);
The Purchase Reason label is now shown in the left cell;

Next, click in the right cell. It will turn blue to indicate that it is currently selected;
A menu bar will appear at the bottom of the page;
Click the arrow next to the EXPR option and select RowProperty(Value);
The Purchase Reason value is now shown in the right cell.

With the right cell still selected, click the percentage icon (%) in the lower menu bar.
This will change the format of the values to percentages.
Next, click the .0 icon with the left arrow to reduce the number of decimals shown.

The format of the row value will now be shown as a percentage.

Adjusting header of the Grid

Select both cells of the top header.
Merge the Header 1 and Header 2 cells into one cell by using the Merge cells options.

Select the “Header 1” text and click the arrow next to the EXPR option;
Select ColProperty(Label);

Alpina should be visible in the header:

Click Close to exit Edit Mode.

This is what the newly created Grid will look like:

Recall the initial steps for creating this Grid.

In the Dynamic Column menu bar, we changed the Compare drop-down box from 10 to 5.

10 is the default number of columns to include across the grid.

We changed it to 5, so that is the reason why we have 5 columns in the Grid now.

Further Grid formatting

The following steps include additional formatting changes to the Grid Widget.

Select Grid and click Edit to return to Edit Mode to change the percentages to bars.

Select the right cell with the data value.

On the menu bar, click the ABC menu to expand the options.
Select the Chart option - The percentages will be changed to bars.

Double-click on the cell to change the color of the bars.
Click the paint bucket icon to specify a different color.

To format the range of the bars, click the Range option, located to the right of expression editor (EXPR).

The minimum and maximum values can be changed, along with the base (value at which the bar will begin plotting).

After completing the bar format changes, click the Close tab, at the top of the Grid, to exit Edit Mode.

This is an example of a Grid with bars.

Format the column headers

Select Grid and click the Edit tab to get back into Edit Mode.

Select the column header cell with the vehicle make label.
Go to the menu bar at the bottom of the page and select the bold font option and increase the font size.
Click the Close tab to exit Edit Mode.

Interactive Mode options

Filtering Sample Total

While in Interactive Mode, scroll down to view the complete list of purchase reasons per make.

Notice at the end of each list, there is a bar for Sample Total that shows 100%.

Ideally, this Sample Total entry should be hidden or not included in the Grid.

The following steps illustrate how to filter the Sample Total entry from the Grid results.

First, exit out of Interactive Mode by clicking the Design Mode tab.

Select the Grid in Design Mode, then click the Edit tab located at the top of the Grid.

With the Grid selected and in Edit Mode, choose the left bracket to select the dynamic row.
The toolbar will appear below the dynamic row.
Click the EXPR option in the toolbar to open the expression editor.
Change *All to All responses - which do not include Sample total.

Click the Interactive Mode tab on the page.
Scroll down to the bottom of the list of purchase reasons to confirm the “Sample Total” entry is no longer included.

Sorting Grid Results

When the results of the Grid are long and require scrolling, it might be preferred to display the results sorted by the top 10 highest, instead having to scroll through a long, unsorted list.

First, exit out of Interactive Mode by clicking the Design Mode tab.
Select the Grid in Design Mode, then click the Edit tab, located at the top of the Grid.

With the Grid selected and in Edit Mode, select the left bracket to select the dynamic row.
The toolbar will appear below the dynamic row.
Click the EXPR option in the toolbar to open the expression editor.

The existing expression will be modified to include sorting.

Before “(tabReference” type the following: “(head(sort”

“head” is used with the “sort” command;
head starts at the top of the list.

Conversely, if we wanted to show the entries at the bottom of the list, we would use “tail” instead of “head.”

Next, after “Value),” insert the cursor then type @ to display the available options.
Select Value, since we want to add criteria to sort on.
After Value is inserted, add a comma, then type @ to display the available options.

Select Descending, since we want to sort with the highest value first.
Then add a closed parenthesis and a comma “),” after Descending.
Next, type in a value for the number of row responses to show after sorting.
For a Top 10 sort, enter 10.

Confirm that the syntax appears correct again with a green check mark.
Click the OK button.
Click the Close tab at the top of the Grid to exit the Edit Mode.

This is an example of the Top 10 sorted Grid.

The results are easier to read and the layout is more user-friendly.

Widening the Rows

As a final formatting step, the width of the rows can be widened, so the response text is not truncated.

If you are in Interactive Mode, switch back to the Design Mode.
Select the Grid, then click the Edit tab.

Click and select the bottom border of the row and drag it down to make it wider.
Click the Close tab to exit Edit Mode.
Switch back to the Interactive Mode.

The response labels are displayed without being truncated.

To select a different make for a particular column, confirm the page is in Interactive Mode, then click the down arrow to display the menu for the available column options.