The purpose of this document is to explain how to create a Pivot Table; note that it is not aimed at describing them in detail. To learn more about Pivot tables, read Anatomy of a Pivot table.
To create a Pivot table, click on the New query link in the Shortcuts panel. Next, select the Pivot table icon.
You will see a dialog box similar to the one shown below.
This window allow 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 we can see a panel containing a list of attributes of the active metadata. The list may vary depending authorization.
In this exercise we will create a Pivot table that shows the tourist attractions of each city in each one of the countries stored in the database. Once it is completed, the result should be similar to the figure below.
To do so:
- Open the Attraction folder node located in the Attributes panel.
- 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.
Press the View button to see if the result is as expected. Only the column titles are not as expected, so we will change them.
The GXQuery Pivot table also allows to filter information. Let's do the next exercise: suppose that we need to see only the records of Brazil and Spain.
To do so:
In Edit mode, drag and drop the CountryName attribute inside the filter area, labeled as "Drop filter attributes here". Inmediately, you will see a window like the image below.
Check the checkboxes of Brazil and Spain y click in OK to return to the edit mode
Press the View button. The resulting screen will be be similar to the image below..