Product How-to: Writing Structured and Free-form Queries Using InetSoft's BI Software

Queries are written for specific data access purposes and contain logic beyond the generic data model. Queries typically utilize advanced or proprietary database features. The idea is similar to relational database views, except that queries are normally not generic enough to be created as permanent database objects or views. In the Data Modeler, queries can be quite flexible, depending on the query parameters and data selection.

For non-relational data sources, queries are often the only available means to obtain the required dataset. In this situation, generic queries can serve the purpose of database views. For relational data sources, stored procedures and other query derivatives are treated as queries.

Just like data model objects, queries are atomic data blocks that cannot be changed by end users. However, they can be transformed and combined with data model objects, for use in more sophisticated applications.

Queries are independent of data models. Queries created in the Data Modeler are shared by the InetSoft presentation and analysis front end. Note that embedded queries can also be created inside Report Designer for a single report. Queries can be created through the drag- and-drop user interface or by manually entered SQL. These queries are called structured and free-form queries, respectively.

Query Definition

A query is created by the ‘New Query’ wizard. The first dialog’s ‘Derived Query’ checkbox allows a query to be based on an existing query. This dialog goes through a sequence of self-explanatory tabs to specify SQL constructs. It is also always possible to jump to the SQL String tab to directly enter SQL.

The created query will appear as a node under the data source after completion. Selecting the query will bring up the query editing window in the right pane. At the bottom of the editing area, three tabs switch between editing modes. The Main Query tab is the visual query editing mode for structured queries. The SQL String tab is for free-form manual editing. The Preview tab displays a live sample output of the current query.

Structured Query

The query pane is organized in the same way as the ‘New Query’ option. The query definition is divided into various tabs. Each tab contains one aspect of a query, such as tables and joins, conditions, or grouping.

The main area displays the tables and the relationships defined. Dragging one column to another will create a new relationship. Accessing the right-click menu on the join line allows deletion and join type selection.

Freeform SQL

The SQL can be modified in the text area under the SQL String tab. The view can be changed back to the structured SQL view by selecting the Main Query tab. If the ‘Parse SQL’ checkbox is selected, the Data Modeler automatically parses the SQL string and reconstructs the graphical view of the query. For very complicated SQL statements that are slow to parse, you may wish to disable parsing by deselecting ‘Parse SQL.’

Query variables can be added using the $(var) construct. This variable will be translated into a SQL variable and the value will be provided at runtime.

A special string replacement variable is specified with the $(@var) construct. This allows the SQL string to be dynamically constructed, through scripting, to satisfy complex business requirements. For example, you can dynamically determine the table to select from at runtime. Define a partial SQL string, ‘select id from $(@var)’. A runtime script can replace the variable with any string such as ‘customers’ or ‘prospects’.

Due to database variations, the parser may not be able to parse all valid SQL statements. If the parser fails to parse the string, it prompts you to keep the change or modify the query. In either case, manually entered SQL always overrides the query definition entered on the visual interface. This allows any query to be constructed in the Data Modeler.

“We evaluated many reporting vendors and were most impressed at the speed with which the proof of concept could be developed. We found InetSoft to be the best option to meet our business requirements and integrate with our own technology.” - John White, Senior Director, Information Technology at Livingston International

Local Query

A local query is a query that is embedded into a report rather than being stored in the common query.xml registry file. A local query is available only to the particular report in which it is embedded. This is beneficial when the query is unique to the report, and will not be reused elsewhere.