Activate

Click on “Activate” from the Exsion 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 Exsion installation.

Menu options

In the Exsion ribbon the following items are available:

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

Connection

Create or modify database connections.

This option shows the following window:

The “Save” button stores a new connection or changes an existing one. The “Delete” button removes an existing connection.

Field	Description
Authentication	SQL Server Authentication Windows Authentication Active Directory - Integrated Active Directory - Password Active Directory - Universal with MFA support OAuth 2.0 (v2)
ID	This is the connection number. The minimum is 1 and the maximum is 9999. The number will be generated when creating a new connection but can be changed at will. If you do so, a new connection will be stored.
User ID	Username to logon to the Dynamics NAV database.
Password	The password for the above Username.
Server	The name or IP address of the SQL Server where the Dynamics NAV database is installed.
Database	The name of the Dynamics NAV database within the above SQL Server.
Tenant	The tenant of the Dynamics NAV database within the above SQL Server.
Company	The company name you want to use.
Networktype	The network type to use when connecting to the SQL Server.
Client	The folder name where the Dynamics NAV classic client is installed.
Service	The server address. When using the “Role Tailored Client (RTC)” enter “Server:Portnumber/Instance”. For the “Webclient” enter the URL like “https://server:portnumber/instance”.

Database structure

This option will create a roadmap from your Dynamics NAV database to provide information about tables, fields, indexes, datatypes and possible joins. When the Excel format box is checked, the information will be displayed in an Excel document instead of a web page.

When you click the “OK” button, all information is shown about the chosen connection.

Click on a tablename to see the details about that table.

Column	Description
Column 1	Row 1 holds the tablename. The other rows are the fieldnames in that table.
Column 2	The field numbers. These can be used to find a table when creating a dynamic download or function.
Type	The field’s datatype.
Size)	Maximum field length.
Class	This defines if a field must contain a value or can be left blank.
Index	The indexes as defined for this table.
SumFields	The SUM fields as defined for this table.

Function library

The function library contains the User Defined Functions. These functions act like any other Excel or Exsion function and will be available for all Excel documents. This library can be shared among all Exsion users.

Activating this function will show the following screen that will be empty at first time use:

Click “New” to create a new function or “Edit” to modify an existing one.

Field	Description
Table	The name of the table that will be accessed by this function.
Field	The name of the field from which information will be retrieved.
Method	The calculation method for that field (Sum, Average, Count, Minimum or Maximum).
Index	The name of the index to use. This will be an argument to the function.
Name	Name of this function.
Description	A brief description for this function. This will be shown by Excel’s function wizard.
Display	Show table/field name or number.
Connection-ID	The number of the connection to use.

Click the “>>” button to show the function arguments. The information displayed here depends on the chosen index.

Field	Description
Name	If the field name is not very clear a different name can be entered here. This will be used by Excel’s function wizard.
Description	A brief description for this argument. This will be used by Excel’s function wizard.
Filter (fixed)	This can be used to enter a fixed filter. If this is not blank, this argument will not be shown in Excel’s function wizard.
Hide	When checked, the argument will be hidden even if no fixed filter is used.

Click the “OK” button to store the function in the function library.

The function will now be available in Excel’s function wizard under the heading “Exsion library”.

Function definition

Functions can also be created without storing them in the Function Libray. The advantage is that it also can be used by Exsion users where the Function Library is not available.

These so called static functions use a range definition as an argument that includes the Table, Field and Index from which information is to be retrieved.

To create a static function click “Function definition” in the menu. Here you can enter the information as described above.

After you click “OK” a name can be entered for this function.

If the selected field is a flowfield, Exsion will ask if the source table for this field must be used.

The function will now be stored in the worksheet in a readable format with the active cell as the top-left one.

The chapter “Database functions” shows a list of Exsion functions that can use static functions.

Formulas to values

This option is used to replace all Exsion functions in your document by their current values so the document can be send to Excel users that have no access to Exsion.

NOTE: Make sure to have a backup from the document before doing this because it cannot be undone.

Show details

This option is used to provide detailed information for the Exsion formula in the active cell. The way this information is displayed depends on the “Excel drilldown” option in the user settings.

If the option in the user settings is unchecked, a Dynamics NAV window will be opened to display the information. Otherwise a new worksheet will be created to display the information.

Dynamic download

A Dynamic Download is used to get data from the Dynamics NAV database in the Excel sheet. As opposed to functions, this will get more data then just one item. If the cursor sits in the output range of an existing download definition the cursor will be placed in the download definition instead of directly displaying the interface as described next.

Field	Description
Add	Shows a list of accessible table in the Dynamics NAV database.
Search	Enter table name or number to filter the list of tables.
Display	Choice to display table/field Name or Caption.
Connection	The connection-ID for the database to use.
Buttons to select	The button “>>” add all fields. The button “>” only add the selected field. The buttons “<” and “<<” deselect either a single field or all fields.
INS	Insert a none table column that can be used for a user defined calculation.
JOIN	If more then one table is used, they must be joined.
[___]	Join type (INNER, OUTER, INNER TOP 1, OUTER TOP 1 or NOT EXISTS)
Fields	Shows a list of fields used by the download.
Name	By default the field's actual name is used. This can be changed here. This name is shown in the (pivot)table.
Method	The calculation method for that field (SUM, MIN, MAX, AVG or CNT).
Hide	If checked, this will hide the field in the download definition.
Table/Field	Tablename/fieldname for the above selected field.
Start pivottable wizard for this download	After a download has been used for the first time, it can be modified to use it for creation of a picot table. Click on a cell in the download definition and then the Dynamic download menu option to modify the existing download. Now select the box: Start pivottable wizard for this download.
Open	Opens a EDD (Exsion Download Definition) file.
Save	Saves a EDD (Exsion Download Definition) file.
OK	Click “OK” to save the download definition in the current worksheet.

Options from the tab “Settings”.

Field	Description
Type	Option are “TABLE” or “PIVOTTABLE”.
TOP	The maximum number of records to retrieve from the database.
Unique records	This will force unique output over all used fields.
Exclusive field names	Don't place field names on top of the record-set.
Order by	Sort on a maximum of three fields.
Descending	Sort output descending.
Export to worksheets	This forces the creation of a new worksheet for every record retrieved by the download definition. A template sheet can be used to create the layout for these sheets.
Template	Name of template sheet.
Unfold tabs	Spread output to one sheet per unique item.

To add a table double-click on a tablename in the list of tables. Exsion will then create a new tab for that table where you can select the fields to be shown on that tab.

Joining tables

After adding a second table Exsion will show a JOIN proposal:

Field	Description
Join fields	The list of fields that can be used to join the tables.
Join type	The different Join types.
OK	Join the tables.

The download definition will be changed accordingly.

Field	Description
Connection	The connection-ID. A Dynamics NAV type filter can be used here to have the download work for more then one company.
Table	The table number(s) to download from.
JOIN TYPE	If more then one table is used, the join type will be shown here.
FILTER	A filter for the field (See: Filter usage).
HIDE	Fields can be show or hidden in the output location.
SORT	To sort the output, a maximum of three fields can be designated to sort on. Use 1, 2, or 3 on the fields to sort on in that order.
METHOD	The calculation method for that field (SUM, MIN, MAX, AVG or CNT).
EXPRESSION	Here, a Exsion function can be used to speed up the download.
Fields	The fieldnames will be shown in the order as created in the download definition. The first row with Connection and the number -2 is mandatory. The number can be changed to a Dynamics NAV type filter for connection ID’s the have the download work for more then one company. Also change True to False in such a case.

Click the “Refresh data” button in the ribbon menu to activate the download. If this is the first time this download is used, it will ask for a output location.

Each download definition has a name which starts with “EXSION_DATA_” followed by the tablename.

Join types

There are 5 jointypes to choose from.

Type of join	Description
1=Inner	Include only records where the joined fields from both tables are identical.
2=Outer	Include all records from the first table and only those records from the second table where the joined fields are identical.
3=Inner top 1	Displays the first record where the joined fields from both tables are identical. With regard to the first record, it can be influenced by sorting in descending or ascending order.
4=Outer top 1	Include all records from the first table and only the first record from the second table where the joined fields are identical. With regard to the first record, it can be influenced by sorting in descending or ascending order.
5=Not Exists	Include only records from the second table where the joined fields are not identical.

PivotTable

After a download has been used for the first time, it can be modified to use it for creating a PivotTable.

Click on a cell in the download definition and then the Dynamic download menu option to modify the existing download.

Now select the box: Start pivottable wizard for this download

When you click “OK”, Exsion wil ask you for the location of the pivottable.

The PivotTable name will be “EXSION_<tablename>”. Because of this, the pivottable will be refreshed when you click the “Refresh data” button in the Exsion menu.

Refresh data

The “Refresh data” button will activate all dynamic downloads and force a recalculation of all formulas so that all information will reflect the database information at that time. Should you want to refresh only the download definition that the cursor sits in, you can click the down arrow on that button and choose to refresh the current download only. This can be usefull for testing purposes. The date and time of the last refresh is always displayed in the header of the download definition.

Help

Contains all the information needed to use Exsion.

Info

Tab

Description

Settings

Field	Description
Language	Choices are NL, EN, FR or DE. The language setting is saved per user.
Excel drilldown	With this switched on Exsion will create a new worksheet to display a function's detailed information when “Show details” is clicked, instead of starting the Navision client.
Library path	The path where (shared) library functions are stored. If this is empty then Exsion will use the location as stored in the Exsion Initialization file (Exsion.ini).
Metadata directory	The path where the metadata is stored.
Language	Metadata language.
Closing date	The closing date character.
Number of calculations	The amount of calculations since Excel was started.

Support

Our helpdesk can provide remote support using TeamViewer.

Version

Field	Description
Version	The Exsion version number.
Feedback	Exsion365 B.V. is constantly working to further improve its products. Therefore we collect meta-data about the use of Exsion. 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 programme you can change the settings of Exsion, so no meta data is transmitted to us. The results of our improvements are communicated through a newsletter, we sent to your e-mail address. If you do not want to receive this newsletter any more than sent an email to office@exsion365.com.
Exsion	The path where Exsion is installed.
Release notes	The release notes of the most actual Exsion version.

Licence

Your license information.

Functions

Standard functions
Advanced functions
Library functions

Standard functions

Function	Description
EXSION_ACCBAL	Returns the balance or budget of a G/L account
EXSION_ACCNAME	Returns the name of the G/L account
EXSION_ACCTOT	Returns the totalling of the G/L account
EXSION_ANVBAL	Returns the balance of an analysis view (Navision 3.01b and above)
EXSION_COMPANY	Returns the company name of a database connection
EXSION_CONCATENATE	Returns the combined text back from a range.
EXSION_CUSBAL	Returns the outstanding balance of a customer
EXSION_CUSCITY	Returns the customer location
EXSION_CUSNAME	Returns the customer name
EXSION_DATE	Returns a date filter
EXSION_DI1NAME	Returns the global dimension 1 name
EXSION_DI2NAME	Returns the global dimension 2 name
EXSION_DIMNAME	Returns the shortcut dimension name (Navision 3.01b and above)
EXSION_VENBAL	Returns the outstanding balance of a vendor
EXSION_VENCITY	Returns the vendor location
EXSION_VENNAME	Returns the vendor name
EXSION_WEEKNUM	Returns the weeknumber of a date

These functions can be found in Excel’s function wizard under the category “Exsion standard”.

EXSION_ACCBAL

Returns the balance or budget of a G/L account.

Argument	Description
Connection	Connection (1 to 9999).
G/L Account No.	G/L account (filter).
[Date filter]	Date (filter).
[Budget code]	Budget.
[Dimension 1]	Global dimension code 1 filter.
[Dimension 2]	Global dimension code 2 filter.

EXSION_ACCNAME

Returns the name of the G/L account.

Argument	Description
Connection	Connection (1 to 9999).
G/L Account No.	G/L account.
[Format]	0=Standard, 1=Capital letters, 2=Lower case letters, 3=First letter or 4=Initial letters.

EXSION_ACCTOT

Returns the totalling of the G/L account.

Argument	Description
Connection	Connection (1 to 9999).
G/L Account No.	G/L account.

EXSION_ANVBAL

Returns the balance of an analysis view (Navision 3.01b and above).

Argument	Description
Connection	Connection (1 to 9999).
Analysis view code	Analysis view code.
[G/L account No.]	G/L account (filter).
[Date filter]	Date (filter).
[Budget code]	Budget.
[Dimension 1]	Code dimension 1 filter.
[Dimension 2]	Code dimension 2 filter.
[Dimension 3]	Code dimension 3 filter.
[Dimension 4]	Code dimension 4 filter.

EXSION_COMPANY

Returns the company name of a database connection.

Argument	Description
Connection	Connection (1 to 9999).
[Format]	0=Standard, 1=Capital letters, 2=Lower case letters, 3=First letter or 4=Initial letters.

EXSION_CONCATENATE

Returns the combined text back from a range.

Argument	Description
Range	Range of cells.
[Delimiter]	A character of text.
[Unique]	If TRUE, then only the unique values will be combined.
[Empty cells]	If TRUE, empty cells will be included.

EXSION_CUSBAL

Returns the outstanding balance of a customer.

Argument	Description
Connection	Connection (1 to 9999).
Customer No.	Customer No.
[Date filter]	Date (filter).
[Dimension 1]	Global dimension code 1 filter.
[Dimension 2]	Global dimension code 2 filter.

EXSION_CUSCITY

Returns the customer location.

Argument	Description
Connection	Connection (1 to 9999).
Customer No.	Customer No.
[Format]	0=Standard, 1=Capital letters, 2=Lower case letters, 3=First letter or 4=Initial letters.

EXSION_CUSNAME

Returns the customer name.

Argument	Description
Connection	Connection (1 to 9999).
Customer No.	Customer No.
[Format]	0=Standard, 1=Capital letters, 2=Lower case letters, 3=First letter or 4=Initial letters.

EXSION_DATE

Returns a date filter.

Argument	Description
Connection	Connection (1 to 9999).
Type	Choose 1=Day, 2=Week, 3=Month, 4=Quarter, 5=Year or 6=Accounting Period.
[Cumulative]	No, Yes or YTD (Year to date).
[Index]	Index of type (e.g. Month 1 to 12).
[Year]	Optional year (the current year is default).

EXSION_DI1NAME

Returns the global dimension 1 name.

Argument	Description
Connection	Connection (1 to 9999).
Dimension code	Dimension 1.
[Format]	0=Standard, 1=Capital letters, 2=Lower case letters, 3=First letter or 4=Initial letters.

EXSION_DI2NAME

Returns the global dimension 2 name.

Argument	Description
Connection	Connection (1 to 9999).
Dimension code	Dimension 2.
[Format]	0=Standard, 1=Capital letters, 2=Lower case letters, 3=First letter or 4=Initial letters.

EXSION_DIMNAME

Returns the shortcut dimension name (Navision 3.01b and above).

Argument	Description
Connection	Connection (1 to 9999).
Dimension group	Dimension group.
Dimension code	Dimension value.
[Format]	0=Standard, 1=Capital letters, 2=Lower case letters, 3=First letter or 4=Initial letters.

EXSION_VENBAL

Returns the outstanding balance of a vendor.

Argument	Description
Connection	Connection (1 to 9999).
Vendor No.	Vendor No.
[Date filter]	Date (filter).
[Dimension 1]	Global dimension code 1 filter.
[Dimension 2]	Global dimension code 2 filter.

EXSION_VENCITY

Returns the vendor location.

Argument	Description
Connection	Connection (1 to 9999).
Vendor No.	Vendor No.
[Format]	0=Standard, 1=Capital letters, 2=Lower case letters, 3=First letter or 4=Initial letters.

EXSION_VENNAME

Returns the vendor name.

Argument	Description
Connection	Connection (1 to 9999).
Vendor No.	Vendor No.
[Format]	0=Standard, 1=Capital letters, 2=Lower case letters, 3=First letter or 4=Initial letters.

EXSION_WEEKNUM

Returns the weeknumber of a date.

Argument	Description
Date	Specify a date.
[Format]	0=WW, 1=YYWW, 2=YYYYWW, 3=WWYY, 4=WWYYYY.
[Separator]	0=none, 1=space, 2=-, 3=\|, 4=/, 5=\.

Advanced functions

Function	Description
EXSION_NAVAVG	Returns the average value of a field in a table that corresponds to the used filter(s).
EXSION_NAVCNT	Returns the number of records of a table which corresponds to the used filter(s).
EXSION_NAVLST	Returns the unique value of a field in a table that corresponds to the used filter(s), separated by the separator.
EXSION_NAVMAX	Returns the largest value of a field in a table that corresponds to the used filter(s).
EXSION_NAVMIN	Returns the smallest value of a field in a table that corresponds to the used filter(s).
EXSION_NAVSUM	Returns the summed value of a field in a table that corresponds to the used filter(s).
EXSION_NAVVAL	Returns the (first) value of a field in a table that corresponds to the used filter(s).

These are functions that only work in the document where they are created. They will not appear in the function library.

They can be found in Excel’s function wizard under the heading “Exsion advanced”:

Argument	Description
Connection	Connection (1 to 9999).
Function definition	Function definition (range).
Filter 1	Filter on index field 1.
Filter 2	Filter on index field 2.
...	Etc.

Function library

The function library holds the User Defined Functions.

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

This library can be shared among all Exsion 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.
		22-1-2015 10:00	An exact datetime: 22-jan-2015 10:00:00.
..	Interval	1100..2100	Numbers 1100 through 2100.
		..2500	Up to and including 2500.
		..31-12-2014	Dates up to and including 31-dec-2014.
\|	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.

VBA Tips and tricks

It is possible to write your own functions in VBA and have Exsion use them or call Exsion functions from within your own code. The examples below use ‘Exsion.xlam’.

Automatische procedures met VBA

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

Call Exsion’s “Refresh data” function

Application.Run "'Exsion.xlam'!MENU_DATA"

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

To be Exsion indepenent you should ignore any errors:

On Local Error Resume Next
Application.Run "'Exsion.xlam'!MENU_DATA"
On Local Error GoTo 0

Call Exsion’s “Call Formulas to values”:

Application.Run "'Exsion.xlam'!MENU_VALUES"

To bypass Exsion’s message box use the following:

Dim s_displayAlertsSetting As Boolean

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

Before Refresh

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

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

To cancel the refresh you can use:

Sub Exsion_beforerefresh()
	If Cancel Then
		Err.Raise 513, "exsion_cancel"
		Exit Sub
	End If
End Sub

After Refresh

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

Sub Exsion_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 "'Exsion.xlam'!MENU_DATA"
		ActiveSheet.Calculate
	End If
End Sub

Deactivate Exsion

To deactivate Exsion 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 Exsion addin and press “OK”.

Exsion will no longer be avalailable and the license is free for another user.