Worksheet Systems uses queries to access data from data tables and show this data on dashboards. You can create multiple, varied queries without having to modify or copy the underlying data.
Each Worksheet Systems dashboard already contains a default query. It is a table from which you created the dashboard. However, you can add more query panels depending on the chosen layout. For more information on how to add new queries and arrange queries on dashboards, see User Interface Customization. This article will focus on how to configure the queries in Query Editor.
To edit the existing query:
- Open a dashboard.
- In the upper-right corner of the query, click the settings icon to open a Query Editor.
Let’s suppose that we need to add a pivot table to our Superstore1Orders dashboard. This pivot table will show sales data by region in the form of a bar chart. To implement this, we need to fill in the following data in Query Editor:
- Name: Enter a short but descriptive name of the query, for example, Sales by Region.
- View height: Specify the height of the resulting query panel, in pixels.
- Default View Type: Select the default view for your query. When you create a query for the first time, only the Datasheet view is available. To add more views, select the Enable Chart View checkbox.
- Description: Describe your query.
- Enable Chart View: Select this checkbox to activate the ability to view your queries as charts. As a result, you will be able to select the preferred view in the Default View Type drop-down list, for example, BarChart.
- X-Axis Field, Y-Axis Field, Show Chart Legend: When you create a query for the first time, no selections are available for these fields. To make them available, you need to fill in the data on the Table Fields/Pivot Table Fields tab.
Table Fields/Pivot Table Fields Tab
The name of this tab depends on your selection in the Query Type drop-down list. There are two types of queries in Worksheet Systems:
- Table: Table queries return rows (from columns that you specify) from one Data Table.
- Pivot Table: Just as in Microsoft Excel, pivot tables in Worksheet Systems allow you to group your data by a shared field. For example, in the data above we might want to query the revenue according to each country. This would produce a table of results showing how much revenue each country produced, without any calculations or equations. For more information about Excel pivot tables, see Microsoft Office help articles.
For our example, let’s select Pivot Table, and then fill in the following data:
- Main Table: Select the data table from which the query will take the data. In our example, we will use a table from our default query, Superstore1Orders.
- Max rows: If needed, select the maximum number of rows for your table, 10,000 by default.
- Sort: Select the sort order for the data in your table, either ASC (ascending) or DESC (descending).
- Available: This list contains all columns of the main table, in our case Superstore1Orders table. As we want to show sales data by region, we should make the following selections in this list:
- Select Region and click the button near Row Area to display the Region column in the row area in the resulting query.
- Select Sales and click the button near Data Area to display the Sales column in the data area in the resulting query. Please note that you can select only columns with numeric values for the data area.
- Filters: You can also add filters to sort the data on your dashboard and in specific queries. For more information about filtering operations, see How to Use Filter Editor. For more information about how to apply filters to dashboards and queries, see How to Manage Dashboard Settings.
When you are ready with the settings on the Table Properties/Pivot Table Properties tab, you can return to the General tab and see that now the X-Axis Field and Y-Axis Field are filled with data. You can select where to display the chart legend – on the right, on the left, or at the bottom, – and select BarChart as Default View Type. However, you can also modify these settings later from the dashboard, after you create the query.
Field Properties Tab
On this tab, you can edit display details for fields in your query, with the following options available:
- Caption: Enter the name of the field, if you want to make it different from the name that it has in the main table.
- Width: Specify the custom width of the field.
- Display Format: Define how to display your data. Options here depend on the field data type.
For numeric fields: Select the format in which you want to display your numbers, and then select how you want to sum up these numbers in the footer of your datasheet.
For text fields:
- Single Line: Default single-line text.
- Multi Line (Details dialog): Splits the long text in multiple lines for better readability in the details dialog. This dialog appears when you click the icon in the upper-right corner of the dashboard query and select Show row in dialog.
And here is an example of multiline text:
- Drop down from comma separated text: Adds a drop-down with data that you specify under Source text.
- Drop down from Data Table Source: Adds a drop-down with data that you specify under Source Data Table and Source Field.
Finally, when you are ready with the query setup, click Save Changes and check the result on the dashboard. You can activate the edit mode and place the query panel wherever you want.
You can also modify your query by clicking the settings icon and change its view type by clicking the icon. The screenshot below displays our example pivot table, Sales by Region.