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.
| #1 Ranking: Read how InetSoft was rated #1 for user adoption in G2's user survey-based index | Read More |
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.
More Articles About Data Intelligence
Better Sales Management Application - Are you looking to better manage your sales team? Using a sales management application based on InetSoft's BI platform can help. No BI developer on staff is required. InetSoft provides mentoring and a package of support hours included for all new customers. InetSoft's real-time DataBlock patented technology places the power of advanced data manipulation and mashup in the hands of non technical users, allowing the user to massage their data with a drag and drop interface, and save the resulting data mashups, to be used in dashboards, enterprise reporting, and scorecards. More than 50% of business BI users are not satisfied with their information application, due to lack of resources to build and maintain the systems and those systems slowing down, resulting in BI and analytics problems. InetSoft is dedicated to correcting common data management challenges with an open standards software complete with data grid caching, for high performance speeds on even the largest of data sources. The increased speed of answered queries allows savvier business decisions to be made...
Tracking operational metrics in Railroading - When managing a fleet of freight cars, it is very important to get a view of how smoothly operations are going. With data analytics hardware and software, you can monitor metrics such as fuel consumption, service delivery, and a variety of benchmarks. If there are any changes the industry is experiencing in any of these aspects, they will reflect in the dashboard report. For example, there are systems providing real-time fuel consumption insights. Fleet managers can use that data to identify how certain factors impact fuel expenditure and adjust budgets whenever required. When a potential problem has been identified in the freight car's condition, companies can dispatch maintenance units right away. They would not even wait for the freight car pilot to identify that issue and call the fleet yard to get a mechanic. That decreases response times and downtime incurred by fleets outfitted with this technology...
Webinar Analytics Dashboard Example - Webinars have become an indispensable tool for businesses, educators, and marketers aiming to reach broad or niche audiences with impactful content. Whether your webinar's purpose is to educate, sell, or build relationships, tracking and analyzing its performance is crucial for continuous improvement and measurable success. Webinar analytics dashboards provide the insights necessary to evaluate effectiveness and make informed adjustments. This article explores how to use webinar analytics dashboards, key performance indicators (KPIs) and metrics to track, their significance, and strategies to improve them. A webinar analytics dashboard is a centralized platform that consolidates data about your webinar's performance. These dashboards are often part of webinar hosting platforms like Zoom, GoToWebinar, and Demio or standalone analytics tools. They visualize data through graphs, tables, and summaries, allowing you to quickly interpret results...
What Factors Go Into a College Ranking? - College rankings are typically influenced by a variety of factors, with different ranking systems placing emphasis on different criteria. However, several common factors often play a significant role in determining a college's ranking: Academic Reputation: The quality of a college's academic programs, faculty, and research output is a fundamental factor in its ranking. This includes factors such as faculty credentials, student-faculty ratio, graduation rates, and the caliber of academic publications produced by faculty members. Student Selectivity: Admission standards and the competitiveness of the applicant pool are important indicators of a college's quality and reputation. Factors such as acceptance rate, standardized test scores of admitted students, and high school GPA of incoming freshmen often influence rankings. Financial Resources: The financial resources available to a college, including endowment size, research funding, and per-student spending, can impact its ability to attract top faculty, support student activities, and invest in academic programs and facilities...
What Is Tracked in a Police Dashboard? Police officers have always played a vital role in enforcing laws and reducing crime and disorder, while improving the overall quality of life for the community. Sadly, it is found that the occupational fatality rate for law enforcement is three to five times greater than the national average for the working population, which has brought to the national consciousness concerns among many researchers, law enforcement agencies, and policymakers. What are the major causes of police death? When and where do these tragedies happen the most frequently? Which states need the most attention and care? How should we allocate resources and build policies to protect the officers from death, so they can safely protect our communities...
Continued: Stored Procedures vs. SQL Query