Live Chat Icon For mobile
Live Chat Icon

Bold Reports

Bold Reports Designer: Query Builder Quick Overview

A quick overview of query builder

The Bold Reports designer provides an interactive SQL query builder that helps you create queries to feed data to a report in less time. It’s an interface between the actual database and the end user. The queries will not affect the actual database; they only fetch data from it.

This blog post takes you through the process and options involved in building queries with this interface.

Getting to the Query Builder

First, launch the Bold Reports on-premise server demo site, refer to the “Steps to create a report” article, and create a new report.

Bold Reports On-Premise report designer
Bold Reports On-Premise Report Designer

To access the query builder in the Report Designer, you must connect a report to a data source.

In the data pane, click the New Data button.

New Data source connection
New Data-Source Connection

From the supported data-source connection types, choose the SQL connection.

Supported data source connections

Choosing Connection Type

Provide the necessary connection information and click the Connect button.

Data source connection panel

Data Source Connection Panel

This opens the query builder in the Report Designer, which provides both graphical and query editor support for a SQL data-source connection. For other data-source connection types, it provides a query editor only.

Query Builder-Bold Report Designer
Query Builder Interface

Parts of the Query Designer

The query designer interface is divided into four sections:

  • Toolbar
  • Schema area
  • Query designer area
  • Preview area
Query builder user interface
Parts of Query Builder

Toolbar Options

OptionDescription
DataSourceAllows users to switch data sources.
NameAllows users to edit the name of a data set.
RunExecutes the query and displays the results in the preview area.
JoinOpens the Joiner dialog.
ExpressionOpens the Query Expression dialog.
FilterOpens the Query Filter dialog.
ParametersOpens the Parameters dialog.
Switcher buttonSwitches between graphical and text-based design views.
Auto preview checkboxAutomatically displays table data in the preview area, if checked, when changing a query.
FinishSaves the query to a data set in the report.
CancelCancels the data set create or edit action.

Schema Area

Based on a given SQL connection string, the schema area displays a hierarchical view of tables, views, and procedures available in the respective database. It also contains a search field so that you can easily search for a table.

Schema Area
Schema Area

Query Designer Surface

The query designer surface is where you set up queries. It contains both a graphical and query editor to create SQL queries.

Graphical editor

In the graphical editor, drag-and-drop tables from the schema area onto the design surface to generate queries for your data set. You can rename columns and hide columns from the query in the graphical editor. To remove a table from the design surface, click the close icon.

Graphical Editor
Graphical Editor

Query editor

The query editor lets you write queries in a query syntax that is supported by the SQL data source. You can write a SQL query directly or modify a SQL statement created by the graphical editor. When you switch to the query editor from the graphical editor, the table structure is automatically converted into SQL statements. The query editor automatically formats the query with highlighted keywords.

Query Editor
Query Editor

Preview Area

The preview area displays results of the query execution in the grid layout (limited to the first 100 data records). To load more records, you can use the Load more option provided in the grid header. When designing the query, you can click the Run icon in the toolbar anytime to preview the records.

Preview Area
Preview Area

Transforming data

In the query builder, you can transform data after retrieving it from the data source with the following options:

Query Filter

Filter out specific data from a database using the query filter option. To specify the filter condition, click the Filter button in the toolbar. This action will open the Query Filters dialog.

Query filter dialog
Query Filters Dialog

Filter conditions are used to generate a SQL WHERE clause. Refer to the “Query Filters” documentation to create, edit, and delete filters in query builder.

Query Expression

Use the query expression option to manipulate existing data columns using operators and built-in functions to create a new calculated measure field. Refer to the “Query expression” documentation to create, edit, and delete query expressions in the query builder.

Query expression dialog
Query Expressions Dialog

Parameters

In the query builder, you can build a parameterized query. A parameterized query generally prompts a user to enter a value before the query is executed to determine the type of data to be displayed in the result set. Data set parameters are created based on the query parameters provided in the data set query. You can set parameters in the query by using both graphical and query editors. Refer to the “Define query parameters” documentation to create, edit, and delete query parameters in the query builder.

Parameters dialog
Parameters Dialog

Join Tables

Joining tables is required when the query design of the data set demands more than one table. While dropping a table on the design surface, the query builder joins tables automatically based on foreign-key relationships, or they can be joined manually. The query builder provides a dialog interface to manage relationships between tables.

Query joiner dialog
Query joiner dialog

Refer to the “Join Tables” documentation for more information on joining tables.

Designing a SQL Query

Now that you’re well-versed with the query builder interface, start building Sales data. To do so, you’re going to use the Order Details and Products tables from the Northwind database.

Drag the Order Details table from the dbo schema to the graphical query designer surface.

Adding table in design surface
Adding Table to Design Surface

Similarly, drag the Products table from the dbo schema to the graphical query designer surface. As you can see, the query builder automatically joined the two tables by using the ProductID column.

Adding multiple tables in design surface
Adding Multiple Tables to Design Surface

Now, hide the unnecessary columns from both tables. To do that, click the eye icon in each column name on the table surface.

Hide columns
Hiding Columns

Execute to see the results in the preview area.

Execute query
Execute Query

Click the Finish button; the query will be saved as a data set in the report.

Dataset listed in data pane
Dataset Listed in Data Pane

You can use this data to create stylish and interactive reports with a rich set of report items, making it easy to collaborate with colleagues.

Conclusion

I hope this blog provided a clear overview about the query builder and how to build a query using it in the Report Designer. To explore further, go through our sample reports and Bold Reports documentation.

If you have any questions, please post them in the comments section. You can also contact us by submitting your queries through our contact page, or if you already have an account, you can log in to submit your support query.

Bold Reports now comes with a 15-day free trial with no credit card information required. We welcome you to start a free trial and experience Bold Reports for yourself. Give it a try and let us know what you think!

Stay tuned to our official Twitter, Facebook, LinkedIn, Pinterest, and Instagram pages for the announcement about the release.

Leave a Reply