YouTube

1 Basic functions

2 Download with joins

3 Download with Method and Expressions

Activate

The first time ExsionBC is used, it must be activated.

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

Menu options

In the ExsionBC ribbon the following items are available:

Connection
Search Company
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 “OK” button stores a new connection or changes an existing one. The “Delete” button removes an existing connection.

Field	Description
Name	A name can be given to the connection here.
Type	Choose from “Access Key”, “OAuth 2.0 (v2)” or “Windows”
User Name	User name
Web Service Access Key*	Web Service Access Key to access Microsoft Dynamics 365 BC.
Language ID	Language selection for retrieving table/field names.
Multi environment (beta)	Download: Multi environment help
SOAP-URL	URL of the extension inside Microsoft Dynamics 365 BC.
Webclient-URL*	Enter the correct URL, if “Show details” opens the url.
SelectLatestVersion	TODO Forces the latest version of the database to be used. “Microsoft documentation”
TransactionType	Choose from “Browse”, “Snapshot”, “UpdateNoLocks”, “Update” or “Report”. “Microsoft documentation”
Download symbols	TODO
Developer Service-URL*	TODO
OAuth 2.0-Login Prompt	Asks again for credentials after “OK”

* On-Prem

Search Company

When you click on “Search company”, a screen opens that makes all companies from the database visible.

Then click the company you want to include in the report and press “Copy to active cell” you can add this per cell, until you have added all the companies that needs to be visible in the table.

You will then see the chosen companies in the cell you selected. When you select multiple companies, these companies will all be placed in the same cell. If you don’t want this, you must select a new cell and press “Search company” to copy the next company.

Please note! You do need to select the correct cell before pressing “Search company”. If you don’t do this, the company will be placed on the then selected cell.

Database structure

This option will create a roadmap from your Microsoft Dynamics 365 BC to provide information about tables, fields, indexes, datatypes and possible joins.

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

You can create a library of functions which can be used in as an expression in a “Dynamic Download”. These functions will always be present no matter which Excel file you work in.

When installing ExsionBC, it is possible to choose a shared path where the function library is stored, so all users have access to same functions.

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.

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 “ExsionBC library”.

See “Using a function from the library” for an example.

Function definition

It is possible to include a function in an Excel file. This function can then be used in the “Dynamic Download” to retrieve data from multiple tables.

Expression functions take as argument (definition) a range containing the table, index fields, and the field name whose value is to be retrieved.

To create a definition for the range in which the table and index fields are included, you must choose the option “Function definition”. In this screen, the table, the field and the index must be specified in sequence.

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

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

See the chapter “Expression functions”, for a list of functions that use this function definition.

See “Using a function definition” for an example.

Formulas to values

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

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

Show details

This menu option gives you insight into details behind an ExsionBC formula of the current cell. The way of displaying the details is determined by the setting “Show details” in the Info menu.

If “Excel” is selected for this setting, ExsionBC will create a new workbook. This workbook shows the details.

If the setting is set to “Webclient”, ExsionBC will immediately open Microsoft Dynamics 365 BC.

Dynamic download

A Dynamic Download is used to get data from Microsoft Dynamics 365 BC in the Excel sheet. As opposed to functions, this will get more data than 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 tables from Microsoft Dynamics 365 BC.
Search	Enter table name or number to filter the list of tables.
Display	Choice to display table/field Name or Caption.
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 than one table is used, they must be joined.
[___]	Join type (INNER, OUTER, INNER TOP 1, OUTER TOP 1, NOT EXISTS or METHOD)
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, CNT, Year, Quarter, Month, Week, Day, Period or INT).
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.
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”.
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.
Sort 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. ExsionBC 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 ExsionBC 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 on which the download is based.
Table	The table number(s) to download from.
JOIN TYPE	If more than 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, CNT, Year, Quarter, Month, Week, Day, Period or INT).
EXPRESSION	Here a ExsionBC function can be used to simplify the download.
Company	The first line always contains a dummy field “Company” with field number “-2”. This field can be used if multiple companies are used to report from a specific company. To do this, change the hide to “FALSE”, the output will then show the “Company” from which the data is retrieved.
Fields	All fields that are selected are displayed below each other.

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 “EXSIONBC_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. Regarding 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. Regarding 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.
6=Method	TODO

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”, ExsionBC will ask you for the location of the pivottable.

The PivotTable name will be “EXSIONBC_<tablename>”. Because of this, the pivottable will be refreshed when you click the “Refresh data” button in the ExsionBC 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 useful 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 ExsionBC.

Info

Tab

Description

Settings

Field	Description
Language	Choices are NL or EN. The language setting is saved per user.
Show details	Option between “Webclient” or “Excel”.
Library path	The path where the (shared) library functions are stored. If this is empty then ExsionBC will use the location as stored in the ExsionBC initialization file (ExsionBC.ini).

Support

Our helpdesk can provide remote support using TeamViewer.

Version

Field	Description
Version	The ExsionBC version number.
Feedback	HB Software is constantly working to further improve its products. Therefore we collect meta-data about the use of ExsionBC. 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 ExsionBC, 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@hbsoftware.nl.
ExsionBC	The path where ExsionBC is installed.
Release notes	The release notes of the most actual ExsionBC version.

Licence

Your license information.

Functions

Standard functions
Expression functions

Standard functions

Function	Description
EXSIONBC_ANVBAL	Returns the balance of an analysis view
EXSIONBC_COMPANY	Returns the display name of a company
EXSIONBC_DATE	Returns a date filter
EXSIONBC_DI1NAME	Returns the global dimension 1 name
EXSIONBC_DI2NAME	Returns the global dimension 2 name
EXSIONBC_DIMNAME	Returns the dimension name
EXSIONBC_CUSNAME	Returns the customer name
EXSIONBC_CUSBAL	Returns the outstanding balance of a customer
EXSIONBC_CUSCITY	Returns the customer location
EXSIONBC_VENNAME	Returns the vendor name
EXSIONBC_VENBAL	Returns the outstanding balance of a vendor
EXSIONBC_VENCITY	Returns the vendor location
EXSIONBC_ACCNAME	Returns the name of the G/L account
EXSIONBC_ACCBAL	Returns the balance or budget of a G/L account
EXSIONBC_ACCTOT	Returns the totaling of the G/L account
EXSIONBC_CONCATENATE	Returns the combined text back from a range.
EXSIONBC_WEEKNUM	Returns the week number of a date

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

EXSIONBC_ANVBAL

Returns the balance of an analysis view.

Argument	Description
Company	Company.
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.

EXSIONBC_COMPANY

Returns the display name of a company.

Argument	Description
Company	Company.

EXSIONBC_DATE

Returns a date filter.

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

EXSIONBC_DI1NAME

Returns the global dimension 1 name.

Argument	Description
Company	Company.
Dimension code	Dimension 1.

EXSIONBC_DI2NAME

Returns the global dimension 2 name.

Argument	Description
Company	Company.
Dimension code	Dimension 2.

EXSIONBC_DIMNAME

Returns the dimension name.

Argument	Description
Company	Company.
Dimension group	Dimension group.
Dimension code	Dimension value.

EXSIONBC_CUSNAME

Returns the customer name.

Argument	Description
Company	Company.
Customer No.	Customer No..

EXSIONBC_CUSBAL

Returns the outstanding balance of a customer.

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

EXSIONBC_CUSCITY

Returns the customer location.

Argument	Description
Company	Company.
Customer No.	Customer No..

EXSIONBC_VENNAME

Returns the vendor name.

Argument	Description
Company	Company.
Vendor No.	Vendor No..

EXSIONBC_VENBAL

Returns the outstanding balance of a vendor.

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

EXSIONBC_VENCITY

Returns the vendor location .

Argument	Description
Company	Company.
Vendor No.	Vendor No..

EXSIONBC_ACCNAME

Returns the name of the G/L account.

Argument	Description
Company	Company.
G/L Account No.	G/L account.

EXSIONBC_ACCBAL

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

Argument	Description
Company	Company.
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.

EXSIONBC_ACCTOT

Returns the totaling of the G/L account .

Argument	Description
Company	Company.
G/L Account No.	G/L account.

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

EXSIONBC_WEEKNUM

Returns the week number 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=\.

Expression functions

Function	Description
EXSIONBC_CNT	Returns the number of records of a table which corresponds to the used filter(s).
EXSIONBC_AVG	Returns the average value of a field in a table that corresponds to the used filter(s).
EXSIONBC_MAX	Returns the largest value of a field in a table that corresponds to the used filter(s).
EXSIONBC_MIN	Returns the smallest value of a field in a table that corresponds to the used filter(s).
EXSIONBC_VAL	Returns the (first) value of a field in a table that corresponds to the used filter(s).
EXSIONBC_SUM	Returns the summed 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 “ExsionBC expression”:

EXSIONBC_CNT

Returns the number of records of a table which corresponds to the used filter(s)..

Argument	Description
Factor	Factor by which to multiply the result.
Function Definition	Function definition (Range).
[Filter 1]	Filter 1.
[Filter 2]	Filter 2.
[Filter 3]	Filter 3.
[Filter 4]	Filter 4.
[Filter 5]	Filter 5.
[Filter 6]	Filter 6.
[Filter 7]	Filter 7.
[Filter 8]	Filter 8.
[Filter 9]	Filter 9.
[Filter 10]	Filter 10.
[Filter 11]	Filter 11.
[Filter 12]	Filter 12.
[Filter 13]	Filter 13.
[Filter 14]	Filter 14.
[Filter 15]	Filter 15.
[Filter 16]	Filter 16.
[Filter 17]	Filter 17.
[Filter 18]	Filter 18.

EXSIONBC_AVG

Returns the average value of a field in a table that corresponds to the used filter(s)..

Argument	Description
Factor	Factor by which to multiply the result.
Function Definition	Function definition (Range).
[Filter 1]	Filter 1.
[Filter 2]	Filter 2.
[Filter 3]	Filter 3.
[Filter 4]	Filter 4.
[Filter 5]	Filter 5.
[Filter 6]	Filter 6.
[Filter 7]	Filter 7.
[Filter 8]	Filter 8.
[Filter 9]	Filter 9.
[Filter 10]	Filter 10.
[Filter 11]	Filter 11.
[Filter 12]	Filter 12.
[Filter 13]	Filter 13.
[Filter 14]	Filter 14.
[Filter 15]	Filter 15.
[Filter 16]	Filter 16.
[Filter 17]	Filter 17.
[Filter 18]	Filter 18.

EXSIONBC_MAX

Returns the largest value of a field in a table that corresponds to the used filter(s)..

Argument	Description
Factor	Factor by which to multiply the result.
Function Definition	Function definition (Range).
[Filter 1]	Filter 1.
[Filter 2]	Filter 2.
[Filter 3]	Filter 3.
[Filter 4]	Filter 4.
[Filter 5]	Filter 5.
[Filter 6]	Filter 6.
[Filter 7]	Filter 7.
[Filter 8]	Filter 8.
[Filter 9]	Filter 9.
[Filter 10]	Filter 10.
[Filter 11]	Filter 11.
[Filter 12]	Filter 12.
[Filter 13]	Filter 13.
[Filter 14]	Filter 14.
[Filter 15]	Filter 15.
[Filter 16]	Filter 16.
[Filter 17]	Filter 17.
[Filter 18]	Filter 18.

EXSIONBC_MIN

Returns the smallest value of a field in a table that corresponds to the used filter(s)..

Argument	Description
Factor	Factor by which to multiply the result.
Function Definition	Function definition (Range).
[Filter 1]	Filter 1.
[Filter 2]	Filter 2.
[Filter 3]	Filter 3.
[Filter 4]	Filter 4.
[Filter 5]	Filter 5.
[Filter 6]	Filter 6.
[Filter 7]	Filter 7.
[Filter 8]	Filter 8.
[Filter 9]	Filter 9.
[Filter 10]	Filter 10.
[Filter 11]	Filter 11.
[Filter 12]	Filter 12.
[Filter 13]	Filter 13.
[Filter 14]	Filter 14.
[Filter 15]	Filter 15.
[Filter 16]	Filter 16.
[Filter 17]	Filter 17.
[Filter 18]	Filter 18.

EXSIONBC_VAL

Returns the (first) value of a field in a table that corresponds to the used filter(s)..

Argument	Description
Factor	Factor by which to multiply the result.
Function Definition	Function definition (Range).
[Filter 1]	Filter 1.
[Filter 2]	Filter 2.
[Filter 3]	Filter 3.
[Filter 4]	Filter 4.
[Filter 5]	Filter 5.
[Filter 6]	Filter 6.
[Filter 7]	Filter 7.
[Filter 8]	Filter 8.
[Filter 9]	Filter 9.
[Filter 10]	Filter 10.
[Filter 11]	Filter 11.
[Filter 12]	Filter 12.
[Filter 13]	Filter 13.
[Filter 14]	Filter 14.
[Filter 15]	Filter 15.
[Filter 16]	Filter 16.
[Filter 17]	Filter 17.
[Filter 18]	Filter 18.

EXSIONBC_SUM

Returns the summed value of a field in a table that corresponds to the used filter(s)..

Argument	Description
Factor	Factor by which to multiply the result.
Function Definition	Function definition (Range).
[Filter 1]	Filter 1.
[Filter 2]	Filter 2.
[Filter 3]	Filter 3.
[Filter 4]	Filter 4.
[Filter 5]	Filter 5.
[Filter 6]	Filter 6.
[Filter 7]	Filter 7.
[Filter 8]	Filter 8.
[Filter 9]	Filter 9.
[Filter 10]	Filter 10.
[Filter 11]	Filter 11.
[Filter 12]	Filter 12.
[Filter 13]	Filter 13.
[Filter 14]	Filter 14.
[Filter 15]	Filter 15.
[Filter 16]	Filter 16.
[Filter 17]	Filter 17.
[Filter 18]	Filter 18.

Expression function example

An example of an expression function is retrieving the total costs of a project, per project number.
For this, a download definition is created on the project entry table in combination with a defined function (See: Function definition)
The download definition is, in addition to fields from the table, also provided with an Expression field (Total costs with field number -1):

The required function definition looks like this:

In the Total Cost line, the EXSIONBC_SUM function can now be used in the Expression column.

The function looks like this:

=EXSIONBC_SUM(1;PROJECTPOST_TOTALE_KOSTPRIJS_LV;$H$7)

In this case, this function has 3 parameters. Whereby:

Parameter 1: The multiplication factor ( 1 or -1)

Parameter 2: The name of the Function definition.
This can be filled in automatically by selecting the field number column of the Function definition during the creation of the expression function, or by choosing the correct one after pressing the F3 key.
The latter provides an overview of the available functions.

Parameter 3: The cell in the download definition where the project number will appear.

The number of parameters that can be used depends on the number of fields in the Function definition.

After filling in the expression function, the download definition will look like this:

After pressing the button “Refresh data” this will be the result on the output sheet:

In a download definition, multiple expression fields may be included, each with their own parameters.

The data for these parameters may also come from cells outside the download definition.

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.

Example customer turnover

Here are 3 ways to create a turnover overview per customer/month as shown here:

Using a method

This is the most difficult way for this reason, only Feb and Mrt are included in this example.

For each period:

a column is specified (see columns 'C' and 'D'), using the JOINTYPE '6'.
a row is specified as the date filter (see rows '8' and '9').
and a result row using the "SUM" method is specified (see rows "11" and "12").

Using a function from the library

Use a self defined function from the “Function library” in the expression column:

Using a function definition

Use a “Function definition” in the expression column:

VBA Tips and tricks

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

Automatische procedures met VBA

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

Call ExsionBC’s “Refresh data” function

Application.Run "'ExsionBC.xlam'!ExsionBC_REFRESH"

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

To be ExsionBC indepenent you should ignore any errors:

On Local Error Resume Next
Application.Run "'ExsionBC.xlam'!ExsionBC_REFRESH"
On Local Error GoTo 0

Call ExsionBC’s “Call Formulas to values”:

Application.Run "'ExsionBC.xlam'!ExsionBC_MNU_VALUES"

To bypass ExsionBC’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 "'ExsionBC.xlam'!ExsionBC_MNU_VALUES"
On Local Error GoTo 0
Application.DisplayAlerts = s_displayAlertsSetting

Before Refresh

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

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

To cancel the refresh you can use:

Sub ExsionBC_BeforeRefresh()
	If Cancel Then
		Err.Raise 513, "exsionbc_cancel"
		Exit Sub
	End If
End Sub

After Refresh

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

Sub ExsionBC_AfterRefresh()
	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 "'ExsionBC.xlam'!ExsionBC_REFRESH"
		ActiveSheet.Calculate
	End If
End Sub

Deactivate ExsionBC

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

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

Select “Options”.

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

Uncheck the ExsionBC addin and press “OK”.

ExsionBC will no longer be available and the license is free for another user.

Extension out of date

If this menu option is available, a new “Exsion Reporting” app will be available. This should then be updated in “Dynamics 365 Business Central”.

Depending on your environment, this can be done by following one of the following procedures:

MS-Cloud
OnPrem

MS-Cloud

To update the “Exsion Reporting” App (extension) in the MS-Cloud, you must have permissions to access the “Dynamics 365 Business Central admin center”.

Go to “Settings” and choose “Admin Center”:

You will be taken to the following URL “https://businesscentral.dynamics.com/[TENANT_ID]/admin”.

Choose the environment in which you want to update the “Exsion Reporting” App. In this example, “Production” was chosen

Choose “Apps”

Look for “Exsion Reporting”, select this entry and press “Install update”

Press “Yes” to update the “Exsion Reporting” app to the latest version.

Press “Refresh” repeatedly to see if the “Available Update Action” changes to “Up to Date”.

OnPrem

Open the “Business Central Administration Shell” environment and run the following commands.

cd \ExsionBC
Uninstall-NAVApp -ServerInstance BC220 -Name 'Exsion Reporting'
Unpublish-NAVApp -ServerInstance BC220 -Name 'Exsion Reporting'
Publish-NAVApp -ServerInstance BC220 -Path 'HB Software_Exsion Reporting.app'
Sync-NAVApp -ServerInstance BC220 -Name 'Exsion Reporting' -Mode ForceSync
Install-NAVApp -ServerInstance BC220 -Name 'Exsion Reporting' -Force