Learn How to use XLReport for Easy Real Time Excel Reporting

XLReport Query Objects

The XLReport Query is the core of this Excel Add-on.

You can create Queries from database objects like tables and views and also from existing XLReport Query Objects.

You can also create a new Query  from combining database tables / views and existing XLReport Query Objects.

New Query

Click "New" on the "XLReport Query" menu.



Select the Data Connection

Note: You can also create a new data Connection from this window by clicking the "New" button.

The XLReport Query  window will open.



Visual Query Builder Interface


Diagram Pane

The Diagram pane allows to visually select tables / views and its fields to be included in the Query.

Grid Pane

The Grid pane allows to select fields, assign expressions, sort orders, aggregate functions and apply criteria and parameters.

SQL Pane

The SQL pane allows to edit the SQL syntax manually.

Important! When working with complex queries and sub-queries, synchronization between panes might slow down your work . Therefore  it is recommended to turn the SQL pane off. To turn the SQL pane off, unselect "View" and "SQL Pane" on the menu bar.

Query Synchronization

XLReport allows full synchronization between Diagram, Grid and SQL panes.

Once you change any setting in any pane, all other panes will synchronize automatically.

Tip! You can also write an SQL syntax manually or paste an existing SQL code and the Diagram and the Grid pane will synchronize.

Add tables / Views and Query Objects

Drag and drop tables / views from the Data Objects pane to the Diagram pane or double click the object to add.


Note: XLReport allows to add database tables / views and also existing XLReport Query Objects.

videoWatch the Video - Create a Query from an existing Query

Select "Add Database Objects" or "Add XL Report Queries" on the menu.


Or toggle between the two option at the bottom of the Data Objects panel.



When adding an existing XLReport Query Object to the Query, the Sub-Query will be displayed in another tab.


Linked / Unlinked Sub-Query

By default a Sub-Query is linked to the original Query Object's underlying Query.


Note: That means if you edit the original Query, then  its changes will be reflected immediately at all Queries which use it.

Edit Sub-Query

If you change the structure - SQL, of the Sub-Query it will become "unlinked".

Note! When a Sub-Query becomes unlinked and the original Query changes, then it will not inherit those changes.

Joins - Relationships

Automatics Joins

XLReport will add relationship joins automatically if detected in your database schema.

You can edit the relationship joins or add them manually.

Create Joins manually


Select the field to join from one table and drag it to the field to join of the another table.

This will create an INNER join.

Edit Joins

Double click the join line.


Choose between LEFT, RIGHT and INNER joins.

A INNER join will be presented like this:


A LEFT join will be presented like this:


A RIGHT join will be presented like this:


Remove Joins

To remove a join, select the join line, right click and select "Remove".


Add fields

Select from Table - Diagram Pane

Add fields to the Query by selecting them from the table object.


Select from Table - Grid Pane

You can also select fields from within the Diagram pane.

Select the object name.

Select the field to be included.



Alias names

XLReport allows to assign friendly alias names to field names.

Note! Sometimes database fields have very strange and funny names and are very hard to be identified by end-users.

Once an alias name is assigned it will be shown in all Query Objects instead of the real field name.



Add expressions - formulas by double clicking the "Table" column or by selecting "<Expression>" in the "Table" column.



You can enter an expression either manually or by double clicking the appropriate field and operator.

Note! XLReport will allow to enter any expression - SQL - supported by the database you are connected to.

Note: If your Query is built from more then one database then the "MS Access SQL syntax" is required.

Click "Ok" and the expression will be added.


Convert Field Type Expression

If you need to convert a field type to another type then you can use the "Convert Expression" builder.

Note! This functionality only applies to connections to MS SQL Server, MS Access, MySQL and Oracle databases.

Add a new expression.

Select the table and field.

Click "Convert Expression".


Select a field type to convert the expression to.


Click "Ok" to insert the expression.

Date Expression

If you need to convert a date value into a Year, Month, Week or Quarter value, XLReport will automatically insert the required expression for you.

Note! This only applies to database connections to MS SQL Server, MySQL, Oracle and MS Access.

Add a new expression.


Select the table / view and field which holds the date value and click the "Expression - Date Function" button.


Select a "Date Function" and click "Ok".


Add Sort order

Select the "Sort Type" column and assign the sort order either to "Ascending" or "Descending".


Change the sort order in the "Sort Order" column.


Clear Sort order

To clear a Sort order select the blank option on the "Sort Type" column.


Aggregate functions

Group data and add aggregate functions by selecting it from the "Group By" drop down field.


Change any other "Group By" aggregate function by selecting it from the drop down field.

Filter - Criteria

XLReport allows to add any criteria - filter  supported by the database you are connected to.

Note: If your Query is built from more then one database then the "MS Access SQL syntax" is required.

Select the field "Criteria" and double click it.



To add an "Operator" double click it.

You can enter values manually or use the "Values" functionality.

Click "Show Values" to load all values contained in the database.

To select a given value double click it.

Click "Ok" to add the expression to the Query.

Date Filter Functions

XLReport offers many preset date functions you can use.

Example! If you need to filter your Query or Report data from only "Last Month", entering a Date Filter Function might be useful.

Note! Date functions can only be applied to "date" type fields.

videoWatch the Video - Date Filters

To define a filter based on a XLReport a Date Filter Function select the field "Criteria" and double click it.



Double click the "Date Function" from the "Values" box.

Click "Ok".


Every time the Query; Data Grid, Pivot Grid or Report runs it will filter data which applies to the defined date filter.


Define a Query as a Parameter Query where the user will be asked toenter parameters each time the object is inserted into Excel.
You can assign unlimited parameters.

videoWatch the Video - Parameter Queries

No SQL or programming knowledge is required to design Queries.

Select the field "Criteria/Parameter" and double click it.

Single select Parameter


Click  the "Insert Parameter" button.

Multiple select (IN) Parameter


Click  the "Insert Multiple Values Parameter" button.

Customize Parameter

To customize a parameter click the "Customize Parameter" button.



Alias name

Enter an "Alias" name to be displayed in the input field instead of the field name.

Default Value

Select  a "Default Value".

You can either manually assign a "Default Value" or click the "List Values" button to choose from.


If you click on the "List Values" button then a new window will pop up.


You can select any given value from that window.

Option Show Values


Click  "Option Show Values" and click the button "Configure List Value"


You can edit the underlying Query for the default values just as you would do in the Query Builder.

Note! For example you could alter the sort order for values in the parameter window or assign a new filter for the parameter.

Option <All Values>
Select the option "<All Values>" check box if you want to show the "All Values" option in the parameter window.


Query Properties

Click the "Properties" button.




To select only a certain number of top records click the "TOP" field and enter a value.

Assign "DISTINCT" or "DISTNCTROW" option.

Union Queries

To create a union Query right click on the Diagram pane and select "Union".


Select "New union sub-query".

Select the left "Q".


Drop the table / view or Report Object to the Diagram pane.

Select fields to be queried.


Drop the table / view or XLReport Query Object to the Diagram pane.

Select fields to be queried.

Click on the "Union" symbol and select the union type.


Heterogeneous Queries

XLReport allows creating Queries form more than one Data Source and also from different databases.

For example you can create a Query mixing data from databases like SQL Server and Oracle.

Proceed as usual creating a Query.

Add tables / fields form one database and then from another database.

XLReport will automatically detect and build the heterogeneous Query.

Important! When creating a Query from more than one database then you need to apply the "MS Access SQL" syntax.

Preview Data

To take a preview of the data returned by the Query click "Preview Data".



Save the Query

To save the Query click "Save" on the "Query" menu.


Save the Query As

To save a variation of a Query click "Save As" on the "Query" menu.


videoWatch the Video - Edit a Query and save it as a new Query


Delete a Query

To delete a Query Object, select it, right click it and select "Delete XLReport Query Object".


videoWatch the Video - XLReport Query Builder

See also: Recycle Bin