HowTo: Build a Pivot table

Official Content

The purpose of this document is to explain how to create a Pivot Table. However, it is not aimed at describing them in detail. To learn more about Pivot tables, read Anatomy of a Pivot table.

Step 1

To create a Pivot table, click on the New query link in the Shortcuts panel. Next, select the Pivot table icon.

GXquery4 - New query

Step 2

You will see a dialog box similar to the one shown below.

GXquery4 - New query dialog

This window allows you to set the name of the query, a description (optional) of it, and the name of the folder to save it (by default, GXquery provides the root node called Queries that will be used to save the entire structure of Folders and Queries).

Next, a screen will be displayed containing areas with labels. It is the Pivot table in Edit mode.

To the right, there's a panel containing a list of attributes of the active metadata. The list may vary depending on its authorization.

GXquery4 - MyFirstPivot in empty edit

In this exercise, a Pivot table that shows the tourist attractions of each city in each one of the countries stored in the database will be created. Once it is completed, the result should be similar to the figure below.

GXquery4 - MyFirstPivot in View mode

To do so:

Step 3

  1. Open the Attraction folder node located in the Attributes Panel.
  2. Drag the attributes Country Name, City Name and Attraction Name to the row sector (it contains the message “Drop row attributes here”).
Note: When the attribute is dragged to any of the Pivot areas, it becomes a Query Element.

The resulting screen may be similar to the image below.

GXquery4 - MyFirstPivot in Edit mode with attributes

Press the View button to see if the result is as expected. Only the column titles are not as expected, so you need to change them.

Filtering information

The GXQuery Pivot table also allows filtering information. Do the next exercise: suppose you need to see only the records of Brazil and Spain.

To do so:

Step 1

In Edit mode, drag and drop the CountryName attribute inside the filter area, labeled as "Drop filter attributes here". Immediately, you will see a window like the image below.

GXquery4 - MyFirstTable selecting filters

Step 2

Check the checkboxes of Brazil and Spain y click in OK to return to the edit mode.

Step 3

Press the View button. The resulting screen will be similar to the image below.

GXquery4 - MyFirstPivot in runtime with filters

See Also

HowTo: Build a list as a Table
HowTo: Build Charts