InetSoft Documentation: Dashboard Table Query

InetSoft's cloud-flexible dashboard software gives users powerful table query controls that help to speed up the creation of reports and customize the output as required. View the example below to learn more about the Style Intelligence solution.

How To Control Data Table Properties

You can change various properties of a Data Table, such the table description, maximum number of rows retrieved and displayed, and the handling of duplicate rows.
To change table properties, follow these steps:
1. Right-click the title row, and select 'Properties' from the context menu. This opens the 'Table Properties' dialog box.
2. Change the desired properties, and click 'OK' to exit the dialog box.

The table properties are described below.

“Flexible product with great training and support. The product has been very useful for quickly creating dashboards and data views. Support and training has always been available to us and quick to respond. - George R, Information Technology Specialist at Sonepar USA

Features of the Visual Query Builder

InetSoft's Visual Query Builder typically offers the following features:

1. Drag-and-Drop Interface: The tool provides an intuitive, user-friendly interface where users can drag and drop database tables and fields to visually construct their queries. This eliminates the need for users to have in-depth knowledge of SQL syntax.

2. Query Building: Users can build complex queries by selecting fields, specifying filtering conditions, sorting criteria, and defining relationships between tables using visual elements. This allows users to create intricate queries without writing code.

3. Joining Tables: The tool often supports various types of table joins, like inner joins, outer joins, and cross joins. Users can visually establish relationships between tables and define how they are connected.

4. Filtering and Sorting: Users can apply filters to narrow down the data retrieved from the database. Additionally, sorting options can be easily applied to arrange the data in the desired order.

5. Aggregation and Grouping: Users can aggregate data by performing functions like sum, count, average, etc., on selected fields. Grouping data based on specific attributes is also supported, enabling users to generate summarized results.

6. Preview and Validation: Visual Query Builders usually offer a preview feature that allows users to see a sample of the data resulting from the query before executing it. This helps users ensure that their query produces the desired outcomes.

7. Query Export and Integration: Once the query is constructed, users can often export the query results to various formats like Excel, PDF, or CSV. Some solutions also offer integration capabilities to embed the generated queries and reports into other applications or dashboards.

8. Security and Permissions: Depending on the complexity of the tool, it might include security features that allow administrators to control access to certain data sources or restrict query-building capabilities to authorized users.

Read how InetSoft was rated as a top BI vendor in G2 Crowd's user survey-based index.

Creating an Independent Query

For cases when you need greater control over data access or more sophisticated logic than that provided by a generic data model, you can create an independent query.

A query is a specific request for data from the database, and often incorporates advanced or proprietary database features. You can create a query by using simple drag-and-drop operations, or by manually entering a valid SQL string. (See Create a New Query and Edit a Query SQL String, respectively.)

Queries and data models are independent of one another. However, you can transform and combine queries with data models for use in more sophisticated applications by using the data block technology provided by Data Worksheets. (See Prepare Your Data for more details.)

To create a new query for a relational database, follow the steps below:

1. Open the Style Studio Asset panel, and expand the 'Data Source' node.

2. Right-click on the data source that you wish to query, and select 'New Query' from the context menu. This opens the 'Query Wizard' dialog box.

3. Under the General tab of the 'Query Wizard' dialog box, enter a name for the query and (optional) description. Click 'Next' to advance to the next tab.

You can also double-click fields to add them, or use the arrow buttons.

4. Under the Data tab, drag the desired database tables from the 'Data Sources' panel to the 'Tables' panel. Click 'Next' to advance to the next tab.

To manually enter SQL rather than using the Wizard, click the SQL String tab. See Edit a Query SQL String.

5. Under the Links tab, define a set of joins to relate the tables. This process is the same as defining joins for physical views. (See Create a Physical View for an explanation of how to relate tables.) Click 'Next' to advance to the next tab.

Right-click a join link and select 'Options' from the menu to open the 'Join Definition' dialog box. Here you can select a join type such as 'Equal' (inner join, the default), 'Left Outer', 'Right Outer', or one of several inequality relations.

You can also double-click fields to add them, or use the arrow buttons.

6. On the Fields tab, drag the fields that you wish the query to return from the 'Database Fields' panel to the 'Query Fields' panel.

7. Optional: To create a derived (expression) column, follow the steps below:

a. Press the 'Add Expression' button above the 'Query Fields' panel. This opens the 'Field Edit' dialog box.

Double-click the field names to add them to the expression.

b. Enter an expression to define the new column, then press 'OK'. (The expression should use the appropriate SQL syntax for the underlying database.)

c. To create an alias for the expression column, select the expression column in the 'Query Fields' panel, and type a name into the 'Field Alias' box in the bottom panel.

d. Click 'Next' to advance to the next tab.

8. On the Conditions tab, use the menus to specify a filtering condition. This condition will restrict the data returned by the query. Press the button on the right side to select a method of supplying the right-hand side of the condition.

When you enter a fixed value as the right-side of the condition, use the 'Browse Data' button to assist the selection.

The 'Field' option allows you to select a different column from the same result set. 'Expression' allows you to enter a regular expression using Perl5 regex syntax (see Regular Expressions.) 'Value' allows you to enter a fixed value. 'Subquery' allows you to specify a distinct query to return the right-side of the condition. 'Variable' allows you to enter an arbitrary parameter name. The value of the variable will either be provided by the user, by script, or by another sub-query. Check the 'Selection List' box to prompt the user with a list of values read from the field. (See Query Properties for more details about variables.)

9. Click 'Next' to proceed to the Sort tab. Drag the fields on which you wish to sort from the 'Available Fields' panel to the 'Sort Fields' panel.

An arrow to the left of the field name indicates the current sort order.

10. Select a field in the 'Sort Fields' panel, and click the 'Sort' button above the panel. Click once to sort in descending order. Click again to sort in ascending order.

The table below summarizes the operation of the different tabs in the 'Query Wizard' dialog box. The same tabs are available also when editing a query. See Edit a Query for more details.

Read how InetSoft was rated as a top BI vendor in G2 Crowd's user survey-based index.

More Resources for Query Building

Converting a Non-Embedded Table - Create a new Worksheet by clicking on the 'New Worksheet' button. Click on the 'New Object' button, and select the 'Embedded Table' option. The cursor changes to a cross. Drag across the Worksheet to highlight five rows and three columns. When you release the mouse, the selected range of the empty grid cells becomes part of the new table called 'Query1'; the first row of the selected range becomes the header row, and the second row becomes the column header row...

How to Control the SQL Query - This example illustrates how you can control the SQL query that a table sends to the database. Expand the 'TABLE' > 'SA' node of the 'Orders' data source. Drag the 'products' table to an empty location on the Worksheet. Drag the 'categories' table to an empty location on the Worksheet. Drag the 'category_id' column header from the 'categories' table onto the 'category_id' column header of the 'products' table, and release when the 'join' symbol appears. This creates a new table called 'Query' that contains the inner join between the two tables based on the 'category_id' columns...

How to Design a Subquery - Sub-queries are queries used inside a query condition expression. The result of the sub-query is used when evaluating the expression. This functionality is only supported for hierarchical data sources like XML, SOAP, etc. You cannot use this feature with a non-hierarchical data source like JDBC. This concept also exists in SQL, but there are a few important differences...

Report Table Wizard - When I start making a tabular report, I select the Table Wizard. Now the wizard takes me through a step-by-step process and handholds me and walks me through different steps. A user with permissions can even set up batch jobs. He can schedule tasks, and say, run this report every day at 6 o'clock in the morning and email it out to my boss. Here's where you set a time condition when you want to run this, daily, weekly, monthly, hourly and action...