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.

image15.gif

image20.gif

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.

image21.gif

 

Visual Query Builder Interface

image22.gif

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.

image23.gif

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.

image24.gif

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

image25.gif

Sub-Query

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

image26.gif

Linked / Unlinked Sub-Query

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

image27.gif

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

image29.gif

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.

image28.gif

Choose between LEFT, RIGHT and INNER joins.

A INNER join will be presented like this:

image31.gif

A LEFT join will be presented like this:

image32.gif

A RIGHT join will be presented like this:

image33.gif

Remove Joins

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

image34.gif

Add fields

Select from Table - Diagram Pane

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

image36.gif

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.

image37.gif

 

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.

image38.gif

Expressions

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

image39.gif

image40.gif

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.

image41.gif

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".

image45.gif

Select a field type to convert the expression to.

image46.gif

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.

image47.gif

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

image48.gif

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

Sort

Add Sort order

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

image50.gif

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

image51.gif

Clear Sort order

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

image52.gif

Aggregate functions

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

image53.gif

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.

image54.gif

image55.gif

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.

image42.gif

image43.gif

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

Click "Ok".

image44.gif

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

Parameters

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

image56.gif

Click  the "Insert Parameter" button.

Multiple select (IN) Parameter

image57.gif

Click  the "Insert Multiple Values Parameter" button.

Customize Parameter

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

image58.gif

image59.gif

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.

image61.gif

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

image62.gif

You can select any given value from that window.

Option Show Values

image63.gif

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

image64.gif

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>
image65.gif
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.

image66.gif

.

image67.gif

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".

image68.gif

Select "New union sub-query".

Select the left "Q".
image70.gif.

 

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

Select fields to be queried.

image71.gif

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.

image73.gif

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".

image78.gif

image77.gif

Save the Query

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

image74.gif

Save the Query As

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

image75.gif

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".

image35.gif

videoWatch the Video - XLReport Query Builder

See also: Recycle Bin