ExSION for SQL

Easily retrieve data from a MSSQL database with ExSIONSQL.

A product of:
Exsion365 B.V.
Boerhaavelaan 40
2713 HX Zoetermeer
The Netherlands
T +31 182 441 340

For more information please visit https://exsion365.com/en/

Preface

ExSION for SQL, short ExSIONSQL is an Excel add-in designed specifically for quick and easy presentation of data from a MSSQL database. It combines the functionality and presentation power of Excel with a real-time link to the database. So you can refresh short A4 reports on any moment within a few seconds for different companies and different periods.

With ExSIONSQL you are no longer dependent on the static form of reporting with SQL, but you can determine how a report is defined and in which presentation format. By making use of colors and graphs, the presentation value can be increased considerably.

Suppose there are already reports created in Excel in some kind of format. Every period the data will be entered or copied from SQL. It is now possible to refresh such data in a simple way with ExSIONSQL reports. This is done by replacing all the fields that would normally be typed in, to ExSIONSQL functions that retrieve the data from the database.

ExSIONSQL has three methods to retrieve data from the database:

By means of a dynamic download
Using pre-defined functions from the function library
By user defined functions

These methods are described in the following chapters.

Activate

Click on “Activate” from the ExSIONSQL menu to open the activation form.

Click on the “ACTIVATE” button to activate the license. During activation, a Internet connection is used to check the license information and to register this ExSIONSQL installation.

Menu functions

ExSIONSQL can be identified by the ExSIONSQL tab in the Ribbon. Clicking that tab will show the menu below.

The functionality of menu options from the ExSIONSQL menu are described below.

Connection
Database structure
Function library
Function definition
Formulas to values
Show details
Dynamic download
Refresh data
Help
Info

Connection

First a connection must be created to connect ExSIONSQL the MSSQL database. The first time ExSIONSQL is used, you can only choose for the menu item “New connection”.

After a choosing an item from this submenu, the following screen will appear:

By pressing the “SAVE” button, the connection is saved. The “REMOVE” button deletes a previously saved connection.

Field	Description
ID	The Identification of the database connection is a number between 1 and 999 and is the first argument in all the ExSIONSQL functions. To retrieve data over the connection that you create, you should use the number from the ID field. By default the ID will increase by one for each new database connection.
Authentication	SQL Server Authentication Windows Authentication Active Directory - Integrated Active Directory - Password Active Directory - Universal with MFA support OAuth 2.0 (v2)
User-ID	User-ID with access to the MSSQL database.
Password	Password associated with the User-ID.
Server	MSSQL Server name (or IP address) where the MSSQL database is installed.
Database	The name of the MSSQL database.

Database structure

This function maps the complete database structure, including custom tailored modules. It works as a street guide of your SQL application. It can give you a deeper insight of the data model of your SQL environment.

After activating the menu item, the following screen is displayed.

After the “OK” button is pressed, the database structure is shown for the selected connection.

By pressing on the table name, the detail information is shown.

Function library

You can build a library of functions, which will always be present regardless in what Excel file you are working. An advantage of creating functions by this method is that you can add descriptive text to the function and its arguments.

A disadvantage is that the Excel files are not default exchangeable between users. Other users must also define this function(s) in their function library. When installing ExSIONSQL you can choose to put the function library in a shared folder, so that all users have access to these functions.

After starting this function you have an overview of functions from the library. If no functions are defined, this list is empty.

You can choose for “New” to create a new function or “Modify” to modify an existing function.

In this example we'll change the function “CUSTTURNOVER”:

Field	Description
Table	The table from which the function retrieves data.
Field	The field to be retrieved using this function.
Method	Choice of calculation type on the field (SUM, MIN, MAX, AVG or CNT).
Index	The index to be used. The fields in the index will be used as arguments to the function (see step 2).
Name	Name of the function which will be defined.
Description	A brief description of the function (this description will be displayed in the function wizard of Excel).
Display	Choice for displaying the table/field description or name from the database.
Connection-ID	The ID number of the database connection.

After the button “>>”, the function arguments are displayed (for the selected index):

Field	Description
Name	By default the field name of the table will be used, but can be changed to increase the user’s experience.
Description	A brief description of the argument (this description is displayed in the function wizard of Excel).
Filter (fixed)	When entering a fixed filter, the argument is no longer displayed in the function wizard of Excel.
Hide	Indicates whether the argument should be hidden in the function wizard of Excel.

After pressing the “OK” button the function is added to the function library.

The created function can be found in the function wizard of Excel under the category “ExSIONSQL Library”:

Example of the newly created function:

Function definition

Database functions are using as second argument (Definition) a range in which the table and index fields are defined and a third argument (Field) which defines the field name whose value is to be retrieved. This menu option easily defines both ranges.

Definition

To define the range in which the table and index fields are included, you can choice the option “Definition”.

The following screen is displayed after entering this menu option.

The “Cust. Ledger Entry” table is selected with Index “Customer No., Posting Date, Currency Code, Entry No.”.

After pressing the “OK” button, ExSIONSQL asks to enter a name, which will be given to the function definition:

And the table- and index information will be copied to the active cell in your worksheet. The following screen shows this on A1 to B5.

The next step is to create a field range, you can do this by choosing for the menu option “Field”.

Field

In order to create a range in which the field name is defined, you must choose the option “Field”:

In the next screen, you can search for a field within a table.

After you have pressed on the “OK” button, the field with the description is copied to the active cell:

The defined ranges may be used in the function “ExSIONSQL_DBSUM”.

See chapter Database functions, for a list of functions that use function definitions.

Formulas to values

This option will remove all ExSIONSQL functions while preserving their resulting values and can be used if you want to send your worksheet with the current values to a non ExSIONSQL user.

Note: This is not reversible so it is important to first save your worksheet before performing this action.

Without the ExSIONSQL functions the data cannot be refreshed anymore.

Show details

This option will show information based on the ExSIONSQL formula in the active cell. After activating the button “Show details”, a new workbook will be created with two worksheets.

The first sheet is called “DATA”, and will have the data that was retrieved from the database as defined by the function in the active cell of your worksheet.

The second one is called “ExSIONSQL” and will have a “Dynamic download” definition based on the same function.

You can use the “Refresh data” button to refresh the data on the “DATA” sheet.

See example below:

The new workbook with the “DATA” and “ExSIONSQL” worksheets:

As you can see, the download definition is based on the function arguments:

Dynamic download

By means of a dynamic download data can be retrieve from the database in to Excel. In contrast to the use of functions, large amount of data can be retrieved at ones.

When the function is called for the first time, the following screen is shown.

Field

Description

Add

This lists all the tables from the database.

Here you can enter a search filter to find the table(s) you are looking for.

Filter

Filter on type TABLE or VIEW.

Display

Choice for displaying the table/field description or name from the database.

Connection-ID

The ID number of the database connection.

Adds all fields from the table to the selected fields list.

Add a field from the table to the selected fields list (can also be done by double-click on the field).

Removes a field from the selected fields list. You can also double-click to select and remove a field.

Removes all fields from the selected fields list.

JOIN

When multiple tables are selected, relationships between tables must be specified. This is by standing on a field on the left tab and select the relationship field on the right tab.

OUTER

When a relationship is created between two tables, you can indicated here whether the data should exist from the selected table (INNER JOIN).

Type	Description
OUTER	Records from the other tables are retrieved, regardless of whether records are present in this table.
INNER	Records from the other tables are only retrieved when there are records present in this table.
EXCLUDE	Records from the other tables are only retrieved when there are no records present in this table.

Fields

The selected fields from the selected table(s).

Name

The default is the field name of the table, but can be adjusted here. The custom name is shown in the Table or PivotTable.

Method

Choice of the calculation on the selected field, there are two types of calculations.

Type 1 grouped the visible or join fields in the table on which a method is applied.

Method	Description
SUM	Returns the summed value from a field in a table.
MIN	Returns the smallest value from a field in a table.
MAX	Returns the largest value from a field in a table.
AVG	Returns the average value from a field in a table.
CNT	Returns the number of records from a table.

Type 2 only applies to fields of a type date and is intended to reduce the number of records in a PivotTable.

Method	Description
Year	Always returns “January 1” as date including the year in the field. For example “May 20, 2018” will be changed to “1 January 2018”.
Quarter	Calculates the date back to the first day of the quarter (“January 1”, “1 April", “1 July” or "October 1”).
Month	Results in the first day of the month.
Week	Returns the first Monday of the week.
Day	Date/time fields will be displayed without the time.

Hide

Check this box if the selected field should not be visible on the worksheet. This is useful is the field is used for filtering only.

Table/Field

This will show the table and field’s original name as it appears in the database. It's possible join table/field will also be shown here.

Open

Opens a EDD (ExSIONSQL Download Definition) file.

Save

Saves a EDD (ExSIONSQL Download Definition) file.

Close the window and create or modify the download definition on your current worksheet.

Continuation on the screen shown above, now with the options from the tab “Settings”:

Field	Description
Type	Choose your download type here: TABLE or PIVOTTABLE.
TOP	The maximum number of records to be retrieved.
Sort by	There’s a maximum 3 fields you can use to sort records.
Descending	There’s a maximum 3 fields you can use to sort records.

To add a table to the download definition, double-click on the table name in the left column (within the list of tables in the “Add” tab). A new row will appear with the name of that table. Click on that row to see the list of fields in the table.

In the following example the table “Customer” with the fields “No.” and “Name” and the table “Cust. Ledger Entry” with the fields “Customer No.” and “Sales (LCY)” with the “SUM” method are added to the download definition. Field “No."” is hidden and both tables must contain records (INNER JOIN).

After adding the 2nd table “Cust. Ledger Entry” comes a join proposal with the table relationships:

Here’s an example of a ready to use download definition after the “OK” button has been clicked.

Field	Description
FILTER	A filter for the field (See: “Filter usage”).
EXPRESSIE	Here, a ExSIONSQL function can be used to speed up the download.

When a download definition is used for the first time after clicking the “Refresh data” button, you will be asked where the output from this download should be placed ($A$7). This will be used as the top left cell.

When this is done, the data is retrieved.

Every download definition has a unique name that starts with “ExSIONSQL_QUERY_” followed by a number. The output range for this download also has a unique name, this one starts with “ExSIONSQL_DATA_” followed by the same number.

When the output range exists, I.E. has data in it, there are settings from Excel itself that you can use. Place your cursor somewhere in this output range and click the right mouse button to access these settings by selecting Properties. This will result in the following window:

Example of Data Range Properties when output type is TABLE:

An example of a download definition of the PIVOTTABLE type:

You can access the pivot table properties by placing the cursor somewhere in the pivot table definition and clicking the right mouse button:

Refresh data

With this option, all dynamic downloads and/or formulas recalculated within your Excel document. This option can be used when your data has not been updated in the spreadsheet. The option is similar to the “refresh” of the Internet Explorer.

Help

Contains all the information needed to use ExSIONSQL.

Info

Tab

Description

Settings

Field	Description
Language	Choice of EN or EN. After changing the language settings are saved per user.
Library path	Deviant directory in which the functions of the Function library are stored.
Number of calculations	Het aantal ExSIONSQL berekeningen na het opstarten van Excel.

Support

Our helpdesk can provide remote support using TeamViewer.

Version

Field	Description
Versie	The ExSIONSQL version number.
Feedback	Exsion365 B.V. is constantly working to further improve its products. Therefore we collect meta-data about the use of ExSIONSQL. With meta data we mean data on the use of reports / Excel sheets. We try to discover trends and patterns. We emphatically do not collect the data of the reports and / or spreadsheets themselves. The manner in which we gather information has no influence on the operation and performance of the system. If you do not want to contribute to this improvement progamme you can change the settings of Exsion, so no meta data is transmitted to us.
ExSION	Shows the version and Build number of ExSIONSQL, the directory where ExSIONSQL is installed and informs you if a new version is available.

License

Shows the company name for which a usage license is granted and a list of users which have activated a license.

General functions

Function	Description
ExSIONSQL_DATE	The result of this function can be used to filter SQL date fields.

The general functions can be found in Excel’s function wizard as category “ExSIONSQL General”:

ExSIONSQL_DATE

The functie “ExSIONSQL_DATE” returns a value that can be used as a filter for SQL date fields.

Argument

Description

Connection ID.

Type

Possible values are: 1=Day, 2=Week, 3=Month, 4=Quarter or 5=Year.

Cumulative

Yes, No or YTD (Year To Date).

Index

The index must be a number that belongs to the used Type: So if the type is Month the index can be a number from 1 to 12. The number may also be 0 or a negative number. The behavior of the index depends on the used type. Say the current date is October 2016 and the used type is Month. With an index of “-1” this function will return a date filter for September 2016.

Type	Choice
Day	1 to 31.
Week	1 to 53.
Month	1 to 12.
Quarter	1 to 4.
Year	Offset from.

Year

Year (optional, default is current year).

Example: ExSIONSQL_DATE(1,"Month","No",2,"2018") returns: “02/01/2018..02/28/2018”.

Database functions

The use of database functions is recommended for functions that relate to one Excel sheet only.

These functions don´t need to be stored in the function library.

You can find these functions in Excel´s functions wizard in the category “ExSIONSQL Database”:

Field	Description
ExSIONSQL_DB	Returns the (first) value from a field in a table that corresponds to the used filter(s).
ExSIONSQL_DBAVG	Returns the average value from a field in a table that corresponds to the used filter(s).
ExSIONSQL_DBCNT	Returns the number of records from a table that correspond to the used filter(s).
ExSIONSQL_DBMAX	Returns the smallest value from a field in a table that corresponds to the used filter(s).
ExSIONSQL_DBMIN	Returns the smallest value from a field in a table that corresponds to the used filter(s).
ExSIONSQL_DBSUM	Returns the summed value from a field in a table that corresponds to the used filter(s).

Argument	Description
ID	Connection ID.
Definition	A function definition (Range with table name and index) from where the functions gets it’s values (See: Function definition).
Field	The name of the field from which the value should be returned.
Filter 1	Filter for the first field in the function definition (See: Filter usage).
Filter 2	Filter for the second field in the function definition.
...	Etc...

Function library

The function library holds the User Defined Functions.

These functions act like any other Excel or ExSIONSQL function and will be available for all Excel documents.

This library can be shared among all ExSIONSQL users.

Filter usage

When you enter a filter, you can use all the numbers and letters that you can normally use in the field. In addition, you can use some special symbols or mathematical expressions.

Here are the available formats:

Symbol	Meaning	Sample Expression	Records Displayed
=	Equal to	377	Number 377.
		BLUE	Those with the BLUE code, for example, the BLUE warehouse code.
		01/22/2018 10:00	An exact datetime: 01/22/2018 10:00 AM.
..	Interval	1100..2100	Numbers 1100 through 2100.
		..2500	Up to and including 2500.
		..12/31/2018	Dates up to and including 12/31/2018.
!	Either/or	1200\|1300	Those with number 1200 or 1300.
&	And	<2000&>1000	Numbers that are less than 2000 and greater than 1000.
<>	Not equal to	<>0	All numbers except 0.
		<>A*	Not equal to any texts that start with A.
>	Greater than	>1200	Numbers greater than 1200.
>=	Greater than or equal to	>=1200	Numbers greater than or equal to 1200.
<	Less than	<1200	Numbers less than 1200.
<=	Less than or equal to	<=1200	Numbers less than or equal to 1200.
*	An indefinite number of unknown characters	cem	Texts that contain “cem”.
		*cem	Texts that end with “cem”.
		cem*	Texts that begin with “cem”.
?	One unknown character	?im	Texts such as Jim of Tim.
()	Calculate before rest	30\|(>=10&<=20)	Those with number 30 or with a number from 10 through 20 (the result of the calculation within the parentheses).

You can also combine the various format expressions:

Sample Expression	Records Displayed
5999\|8100..8490	Include any records with the number 5999 or a number from the interval 8100 through 8490.
..1299\|1400…	Include records with a number less than or equal to 1299 or a number equal to 1400 or greater (all numbers except 1300 through 1399).
>50&<100	Include records with numbers that are greater than 50 and less than 100 (numbers 51 through 99).
C&D	Texts containing both C and D.
co?	Texts containing “co” such as cot, cope and incorporated. “co” must be present, followed by at least one character, but there can be an indefinite number of characters before and after these.

Make sure you use valid filters because it is possible to use an invalid filter without any warning from the database and ExSIONSQL passes the filters directly to the database.

VBA Tips and tricks

ExSIONSQL offers the abillity to execute user written VBA routines before and after its Refresh function. It is also possible to call ExSIONSQL's Refresh function from within a user written routine. Use this carefully because it might effect the Refresh function.

Execute menu functions.

Application.Run "'ExSIONSQL.xlam'!ExSIONSQL_REFRESH"

This will have the same effect as clicking the “Refresh data” button in the ExSIONSQL menu.

To be ExSIONSQL indepenent you should ignore any errors:

On Local Error Resume Next
Application.Run "'ExSIONSQL.xlam'!ExSIONSQL_REFRESH"
On Local Error GoTo 0

Call ExSIONSQL “Formulas to values”:

Application.Run "'ExSIONSQL.xlam'!ExSIONSQL_VALUES"

To bypass ExSIONSQL message box use the following:

Dim s_displayAlertsSetting As Boolean

s_displayAlertsSetting = Application.DisplayAlerts
Application.DisplayAlerts = False
On Local Error Resume Next
Application.Run "'ExSIONSQL.xlam'!ExSIONSQL_VALUES"
On Local Error GoTo 0
Application.DisplayAlerts = s_displayAlertsSetting

Before Refresh

Use the following function to execute code before ExSIONSQL performs a refresh:

Sub ExSIONSQL_BEFOREREFRESH()
	Call [Your own macro]
End Sub

After Refresh

Use the following function to execute code after ExSIONSQL performed a refresh:

Sub ExSIONSQL_ONREFRESH()
	Call [Your own macro]
End Sub

Example for a Refresh after changing a cell containing a parameter

Place this macro in the ThisWorkbook section.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
	If Target = ActiveSheet.Range("Subcategory") Then
		Application.Run "'ExSIONSQL.xlam'!ExSIONSQL_REFRESH"
		ActiveSheet.Calculate
	End If
End Sub

Deactivating

To deactivate ExSIONSQL and free the license you should remove the Exsion add-in from Excel.

Press the “File” button (top left of the screen).

Select “Options”.

Choice “Add-Ins” and press “Go...”.

Uncheck the “ExSIONSQL 1.0” addin and press “OK”.

Het ExSIONSQL menu is no longer be avalailable and the license is free for another user.