Fusion Registry Wiki - User contributions [en]

FXLData

2023-02-24T10:40:38Z

Glenn: /* Download */

__NOTOC__
An Excel add-in providing a collection of functions for retrieving statistical data from a Fusion Registry directly into a worksheet

[[File:Fxldata animation.gif]]

=Download=
Download the latest version [[File:FXLData 1-2-6.zip]] 24 February 2023

See the [[FXLData Changelog]] for recent changes, updates and bug fixes.

=Quick start=
# Download the latest version and unzip the add-in which is a single file: FXLData.xlam
# [[#Installation|Install]] the add in
# Open a blank Excel worksheet
# Paste the following formula into a cell
=fxldata("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","A")
A sample 100 series of annual ECB exchange rates data will be retrieved from Metadata Technology's public demonstration Fusion Registry service and inserted directly into the sheet.

The [[FXLData Wizard]] provides tools to help with the task of building FXLData formulas.

[[File:FXLData Wizard Ribbon 058.JPG|FXLData Excel ribbon|200px]]

__TOC__

=Overview=
The FXLData family of functions are Excel ''array formulas''.

These work in the same way normal as normal single-cell funcions such as =SUM() and =STDEV() with the exception that the result is an ''array'' of series and observation values spanning a range of rows and columns.

Microsoft's [https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 documentation] provides general information on how to work with array formulas in Excel.

====Parameterised functions====
These functions take a number of separate arguments making them suitable for building sophisticated data models where Start Period, End Period and other parameters use cell references or Excel formulas.
[[File:Fxldata example1.PNG|thumb|Horizontal time-series example]]
Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations.

'''=FXLData'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes]'' ) 
Returns a selected dataset in 'horizontal' time-series format with one series per row 
* Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return
* A further nine optional arguments can be specified including: maximum number of series to retrieve, start / end period, labels, headers and whether dimensions and attributes are shown

'''=FXLDataV'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] - ignored, [IncludeBreakdown] - ignored, [IncludeAttributes]'' ) 
Returns a selected dataset in 'vertical' format with one series per column
* Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return
* A further nine optional arguments can be specified including: maximum number of series to retrieve, start / end period, and whether dimensions, attributes and metrics are shown
* IncludeSeriesKey is ignored
* IncludeBreakdown is ignored
* Set Labels=both to show IDs and dimensions / attributes
* If Labels=both, setting IncludeAttributes=TRUE will show the breakdown of series / dataset attributes in the header
* IncludeHeader controls whether the resultset metrics are shown at the top (e.g. 205/1276 series 1.78 seconds)

Examples
=FXLData("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","ECB,EXR,1.0","A...")
=FXLDataV("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","ECB,EXR,1.0","A...",1000,"round=2",2010,2020,"both",true,true,true,false)
=FXLData(C14,C4,C7,C15,C13,C5,C6,C12,C9,C8,C10,C11)
=FXLDataV(A9,B24,B25,1000,,min(D55,2010),max(D56,2020))
Like all Excel functions, arguments can either be literal values, cell references, or other formulas.

====Functions taking a Fusion Registry data query URL====
These functions take a single fully-formed Fusion Registry data query URL making them suitable for use cases where another tool such as the Fusion Data Browser has been used for data discovery, with data retrieval being performed directly in Excel.

Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations.

'''=FXLDataUrl'''( ''Url , [MaxSeries] '' ) 
Returns the selected resultset in 'horizontal' time-series format with one series per row 
* Behaves in the same way as =FXLData() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL
* A single optional argument allows the maximum number of series to be set

'''=FXLDataUrlV'''( ''Url , [MaxSeries] '' ) 
Returns the selected resultset in 'vertical' format with one series per column 
* Behaves in the same way as =FXLDataV() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL
* A single optional argument allows the maximum number of series to be set

'''=FXLDataUrlFact'''( ''Url , [MaxSeries] '' ) 
Returns the selected resultset as a [https://en.wikipedia.org/wiki/Fact_table data warehouse style fact table] 
* Takes a single mandatory argument: a fully-formed Fusion Registry data query URL
* A single optional argument allows the maximum number of series to be set
* Returns one row per observation including all series and observation attributes
* Following the traditional fact table model, only dimension and attributes codes are returned - there is not option to return code labels

Examples
=FXLDataUrl("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki>")
=FXLDataUrlV("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki>",1000)
=FXLDataUrlFact("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/.CAD+MXN+MYR...</nowiki>",20)

====Administrative functions====
'''=FXLDataVersion()'''
* Returns the software version number and build date

====Microsoft 365 Excel 2018 and later====
In Microsoft 365 versions of Excel, the FXLData functions act as [https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531 dynamic array formulas] which populate or 'spill' the result set into neighbouring cells on execution. There is no need for an explicit refresh because Excel will 'recalculate' any FXLData functions that have changed using its normal worksheet calculation behaviour. Settings Excel's calculation options to Automatic (the default) will automatically refresh an FXLData formula if the formula or something it depends upon such as a parameter in referenced cell has changed. If calculation is set to Manual, changed FXLData formulas will be refreshed when the user asks Excel to perform a recalculation using the ''Calculate Now'' or ''Calculate Sheet'' buttons on the ribbon bar. A complete recalculation can be forced using CTRL+ALT+SHIFT+F9. 

====Office 2010-2019 Excel====
For these older versions of Excel which do not support dynamic array formulas, the FXLData functions work in [[#Legacy_Mode|legacy mode]] which emulates to some extent the dynamic array formula behaviour. On execution, the result set still populates into neighbouring cells. However, there are some important differences between legacy mode and proper dynamic arrays:
* In legacy mode, the result set is written directly into the worksheet which Excel treats as a collection of largely disconnected cells. By contrast, Excel treats a dynamic array as a single entity.
* Excel automatically protects against dyanamic arrays overwriting other content in the worksheet resulting in a [https://support.microsoft.com/en-us/office/how-to-correct-a-spill-error-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023 #Spill!] error if that would otherwise occur. Legacy mode will overwrite any existing content meaning that care is required to avoid worksheet corruption if the result set from an FXLData function is larger than expected.
* Legacy mode FXLData functions will be automatically refreshed if the formula itself is changed, but it not if a dependency of the formula changes. This is because the legacy mode refresh only emulates the proper dynamic array formuala spill behaviour.

====FXLData Wizard====
The [[FXLData Wizard]] is an Excel ribbon bar tool for interactively building and editing FXLData functions and comes as part of the add in.

=Prerequisites=
# Fusion Registry 10.7.0 or later
# Microsoft 365 Excel 2018 or later - supports dynamic arrays which automatically 'spill' the result set into the worksheet
# Office 2010-2019 - FXLData functions operate in [[#Legacy_Mode|legacy mode]] which provides dynamic array emulation

Refer to Microsoft's [https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 documentation] on dyanmic arrays and legacy 'ctrl-shift-enter' behavior.

Excel on Apple Mac is not supported because FXLData uses operating system services only available on Windows.

=Installation=
[[File:Excel addinmanager.png|thumb|Excel add-in manager]]
The FXLData add-in is distributed as a single Excel file:

''FXLData.xlam''

There are two installation options.

====Option 1: Excel Add-ins manager====
# From the Excel File menu, choose Options
# On the Options popup, choose Add-ins from the list
# At the bottom, choose Excel Add-ins from the 'Manage' list and click Go
# On the Add-ins popup, choose Browse and select the FXLData.xlam distribution file

====Option 2: Excel XLSTART directory====
# Copy the FXLData.xlam to the Excel XLSTART directory which is usually in the following location C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART

=Function Reference=
====FXLData()===
By default ƒ''x'' =FXLData() returns a horizontal time-series dataset, one series per row with time spanning the columns i.e. time is the dimension at observation. A different dimension at observation can be chosen using the RestAuguments parameter - refer to the guidance [[#Dimension at Observation|below]].

'''Use Case''' 
This parameterised form is suitable for hand coding in a spreadsheet. Like all Excel functions, the arguments can be literal values, cell references or Excel expressions allowing the creation of complex data models. In its simplest form, only three arguments are required:
* The 'base URL' of the Fusion Registry to connect to
* The ID of the Dataflow
* A series key specifying which series to retrieve, or simply 'all' to retrieve all series
=FXLData("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","all")
By default, the number of series returned is capped at 100, but can be altered using the ''MaxSeries'' argument

'''Syntax''' 
=FXLData( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes]'' )

{| class="wikitable" style="width:70%;"
|-
! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?
|-
| 1||RegUrl|| The base URL of the Fusion Registry service || <nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki> || style="background-color:pink;" |Mandatory
|-
| 2||Dataflow || Options: 1. The dataflow to query conforming to the SDMX REST specification for data queries 2. A blank string - in this case the Dataflow(s) to query must be specified as part of the Expression. || EXR ECB,EXR,1.0 || style="background-color:pink;" | Mandatory
|-
| 3||Expression || Options: 1. A [[Series Key|series key]] 2. A dataflow and series key expression <dataflow>/<series key> 3. A [[Anatomy_of_the_Data_Browser#Series_List_From_Series_Basket|JSON series basket expression]] which must be a cell reference and cannot be a literal value 4. The keyword 'all' to select all series in the dataset|| A A.CAD+HUF+INR...E all ECB,EXR,1.0/A.HUF...E C4 (cell reference)||style="background-color:pink;" | Mandatory
|-
| 4||MaxSeries || The maximum number of series to return || 1000 || Optional Defaults to 100 series
|-
| 5||RestArguments || Additional [[Data_Query_Web_Service#Extended_HTTP_Query_Parameters|data REST query arguments]] intended for special use cases like adding calculated measures or changing the dimension at observation || round=3&calculatemeasure=THE_MEASURE;pch;mvavg,3&dimensionAtObservation=CURRENCY_DENOM || Optional
|-
| 6||StartPeriod || The start period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12 2010 || Optional Defaults to the start of the available data
|-
| 7||EndPeriod || The end period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12 2010 || Optional Defaults to the end of the available data
|-
| 8||Labels || Component labels <code>id</code> - IDs only <code>name</code> - names only <code>both</code> - both IDs and names || both || Optional Defaults to <code>id</code>
|-
| 9||IncludeHeader || Boolean <code>TRUE</code> includes component labels at the head of each column and shows metrics <code>FALSE</code> component labels and metrics are ommitted|| FALSE || Optional Defaults to <code>TRUE</code>
|-
| 10||IncludeSeriesKey || Boolean <code>TRUE</code> adds a calculated dimension 'series' with the series key as the value <code>FALSE</code> series keys are not included in the dataset || FALSE || Optional Defaults to <code>TRUE</code>
|-
| 11||IncludeBreakdown || Boolean Controls whether a breakdown of the dataset's components is shown. <code>TRUE</code> includes dimensions. Attributes are also shown if the IncludeAttributes argument is TRUE. <code>FALSE</code> no components are included || TRUE || Optional Defaults to <code>TRUE</code>
|-
| 12||IncludeAttributes || Boolean <code>TRUE</code> includes series attributes in the component breakdown <code>FALSE</code> excludes attributes|| FALSE || Optional Defaults to <code>TRUE</code>
|}

====FXLDataUrl()===
By default ƒ''x'' =FXLDataUrl() returns a horizontal time-series dataset. Time is the dimension at observation by default resulting in one series per row with time periods spanning the columns. A different dimension at observation can be chosen by adding a REST argument to the URL - refer to the guidance [[#Dimension at Observation|below]].

'''Use Case''' 
[[File:Fdb query syntax.PNG|thumb|Fusion Data Browser Query Syntax function]]
This version of the function is designed primarily for use cases where a data query URL is generated by a data discovery tool, for example:
* The Fusion Registry [https://demo.metadatatechnology.com/FusionRegistry/webservice/data.html REST Web Service query builder]
* The Fusion Data Browser Export Data ''Query Syntax'' function

'''Syntax''' 
=FXLDataUrl( ''Url , [MaxSeries] '' )

{| class="wikitable" style="width:70%;"
|-
! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?
|-
| 1||Url|| A complete and fully-formed SDMX data query URL || <nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki> || style="background-color:pink;" |Mandatory
|-
| 2||MaxSeries || The maximum number of series to return || 1000 || Optional Defaults to 100 series
|}

====FXLDataV()===
By default ƒ''x'' =FXLDataV() returns a vertical dataset, one series per column with time spanning the rows. The dimension at observation cannot be changed, the REST argument, if set is ignored.

'''Use Case''' 
This parameterised form is suitable for hand coding in a spreadsheet. Like all Excel functions, the arguments can be literal values, cell references or Excel expressions allowing the creation of complex data models. In its simplest form, only three arguments are required:
* The 'base URL' of the Fusion Registry to connect to
* The ID of the Dataflow
* A series key specifying which series to retrieve, or simply 'all' to retrieve all series
=FXLDataV("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","all")
By default, the number of series returned is capped at 100, but can be altered using the ''MaxSeries'' argument

'''Syntax''' 
=FXLDataV( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes]'' )

{| class="wikitable" style="width:70%;"
|-
! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?
|-
| 1||RegUrl|| The base URL of the Fusion Registry service || <nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki> || style="background-color:pink;" |Mandatory
|-
| 2||Dataflow || Options: 1. The dataflow to query conforming to the SDMX REST specification for data queries 2. A blank string - in this case the Dataflow(s) to query must be specified as part of the Expression. || EXR ECB,EXR,1.0 || style="background-color:pink;" | Mandatory
|-
| 3||Expression || Options: 1. A [[Series Key|series key]] 2. A dataflow and series key expression <dataflow>/<series key> 3. A [[Anatomy_of_the_Data_Browser#Series_List_From_Series_Basket|JSON series basket expression]] which must be a cell reference and cannot be a literal value 4. The keyword 'all' to select all series in the dataset|| A A.CAD+HUF+INR...E all ECB,EXR,1.0/A.HUF...E C4 (cell reference)||style="background-color:pink;" | Mandatory
|-
| 4||MaxSeries || The maximum number of series to return || 1000 || Optional Defaults to 100 series
|-
| 5||RestArguments || Additional [[Data_Query_Web_Service#Extended_HTTP_Query_Parameters|data REST query arguments]] intended for special use cases like adding calculated measures or changing the dimension at observation || round=3&calculatemeasure=THE_MEASURE;pch;mvavg,3&dimensionAtObservation=CURRENCY_DENOM || Optional
|-
| 6||StartPeriod || The start period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12 2010 || Optional Defaults to the start of the available data
|-
| 7||EndPeriod || The end period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12 2010 || Optional Defaults to the end of the available data
|-
| 8||Labels || Component labels <code>id</code> - IDs only <code>both</code> - both IDs and names || both || Optional Defaults to <code>id</code>
|-
| 9||IncludeHeader || Boolean <code>TRUE</code> shows resultset metrics (e.g. 108/1256 series 2.15 seconds) <code>FALSE</code> metrics are omitted|| FALSE || Optional Defaults to <code>TRUE</code>
|-
| 10||IncludeSeriesKey || Ignored|| || Optional Ignored</code>
|-
| 11||IncludeBreakdown || Ignored || || Optional Ignored</code>
|-
| 12||IncludeAttributes || Boolean <code>TRUE</code> includes series attributes if labels=both <code>FALSE</code> excludes attributes|| FALSE || Optional Defaults to <code>TRUE</code>
|}

====FXLDataUrlV()===
ƒ''x'' =FXLDataUrlV() returns a vertical dataset, one series per column with time spanning the rows. The dimension at observation cannot be changed, the REST argument, if set is ignored.

'''Use Case''' 
[[File:Fdb query syntax.PNG|thumb|Fusion Data Browser Query Syntax function]]
This version of the function is designed primarily for use cases where a data query URL is generated by a data discovery tool, for example:
* The Fusion Registry [https://demo.metadatatechnology.com/FusionRegistry/webservice/data.html REST Web Service query builder]
* The Fusion Data Browser Export Data ''Query Syntax'' function

'''Syntax''' 
=FXLDataUrlV( ''Url , [MaxSeries] '' )

{| class="wikitable" style="width:70%;"
|-
! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?
|-
| 1||Url|| A complete and fully-formed SDMX data query URL || <nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki> || style="background-color:pink;" |Mandatory
|-
| 2||MaxSeries || The maximum number of series to return || 1000 || Optional Defaults to 100 series
|}

====FXLDataUrlFact()===
ƒ''x'' =FXLDataUrlFact() returns the selected resultset as a [https://en.wikipedia.org/wiki/Fact_table data warehouse style fact table] with one row per observation including all series and observation attributes. As this is conceptually just the fact table of a [https://en.wikipedia.org/wiki/Star_schema star schema], only code IDs are returned - code labels are not available.

'''Use Case''' 
This version of the function is designed for data analytics involving observation attributes. The fact table model also works well with Excel's standard Pivot Table features.

'''Syntax''' 
=FXLDataUrlFact( ''Url , [MaxSeries] '' )

{| class="wikitable" style="width:70%;"
|-
! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?
|-
| 1||Url|| A complete and fully-formed SDMX data query URL || <nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki> || style="background-color:pink;" |Mandatory
|-
| 2||MaxSeries || The maximum number of series to return || 1000 || Optional Defaults to 100 series
|}

====FXLDataVersion()===
ƒ''x'' =FXLDataUrlV() returns the version number of the FXLData addin.

'''Syntax''' 
=FXLDataVersion()

'''Example''' 
=FXLDataVersion()
Returns <code>1.3.2 18/12/2021</code>

=Client-side REST Arguments=
The =FXLData() and =FXLDataV() functions support additional REST arguments to modify behaviour in Excel. These arguments are not passed to the Fusion Registry or Edge Server.

'''nocc''' - suppress completion of the resultset cube when startPeriod and / or endPeriod are specified. Under normal behaviour, the resultset will always span the time periods specified irrespective of whether there are observation values available. If nocc is specified in the REST Arguments, the resultset will only span time periods for which observations are available. 
Examples:
nocc
round=2&nocc
nocc&dimensionAtObservation=REF_AREA

=Legacy Mode=
Legacy Mode emulates the latest Microsoft 365 dynamic array behaviour in older Office Excel 2010-2019 versions by populating the cells neighbouring that holding an FXLData formula with the data result set.

In versions of Microsoft 365 Excel that do support dynamic arrays, legacy mode can also be used to write the result set directly into the worksheet. That's useful in situations where discrete data points are needed rather than an Excel array which has certain limitations.

====Office Excel 2010-2019====
FXLData always works in legacy mode when using Office Excel 2010-2019.

When an FXLData function is executed, the first cell of the result set will appear as the formula result. If automatic calculation is enabled, FXLData's ''dynamic array emulation'' will immediately populate the neighbouring cells with the remainder of the result set. If automatic calculation is disabled, [[#Refresh|Refresh]] must be used.

====Forcing Microsoft 365 Excel to use Legacy Mode====
FXLData functions in Microsoft 365 Excel operate as dynamic array formulas by default where the result set appears in the worksheet as an Excel array.

Using the [https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34 Implicit intersection operator: @] however forces the FXLData function to operate in legacy mode causing the result set to be written directly into the worksheet.
=@FXLData("<nowiki>https://registry.domain.org/FusionRegistry</nowiki>","ECB,EXR,1.0","A..BGN+BEF..",100,"round=2","2010","2020","id",TRUE,FALSE,TRUE,FALSE)
In the above example the '''@''' preceeding the FXLData function name instructs Excel to return the ''implicit intersection'' of the result set array which is just the first cell. If legacy mode ''automatic calculation'' is enabled, the FXLData's dynamic array emulation will populate the neighbouring cells. If not, [[#Refresh|Refresh]] must be used.

=Refresh=
===Microsoft 365 Excel and dynamic array formulas===
====Interactive====
FXLData functions by default work as ''dynamic array formulas'' in Microsoft 365 so are refreshed using the normal Excel calculation rules:

Refresh occurs when:
* a change is made to the formula
* a change is made to something the formula depends upon such as an argument value held in another cell
* Excel is forced to recalculate open workbooks using <code>CTRL+ALT+F9</code> or <code>CTRL+ALT+SHIFT+F9</code>
* a saved workbook is opened

[[File:Sign.png|30px]] The Refresh options on the FXLData ribbon do not refresh dynamic array formulas.

====From Code====
Refreshing FXLData from VBA, C# or C code uses the standard [https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation Excel API calculation functions], for instance:
* VBA: ActiveSheet.Calculate
* C API: xlcCalculateDocument()

===Office 2010-2019 Excel and Microsoft 365 Excel working in legacy Mode===
====Interactive====
[[File:FXLData Wizard Ribbon 058.JPG|thumb|FXLData Excel ribbon|200px]]
In older versions of Office Excel and when forcing legacy mode in Microsoft 365 using the @ implicit intersection operator, FXLData functions can be manually refreshed using the Refresh options on the ribbon bar.
* Refresh Selected: refreshes the FXLData function at the current active cell
* Refresh Sheet: refreshes all FXLData functions on the current work sheet
* Refresh Book: refreshes all FXLData functions in the work book

[[File:Using Refresh in Legacy mode.PNG|thumb|Using Refresh in Legacy mode|200px]]

====From Code====
The FXLData add-in exposes three public sub-routines to trigger a refresh from VBA code.

[[File:Sign.png|30px]] A reference to FXLDataAddin must be added to the VBA project.

Macro Example 1 - Refresh the FXLData function in the current active cell
Public Sub myMacro()
FXLDataRefreshActiveCell
End Sub

Macro Example 2 - Refresh all FXLData functions in the workbook
Public Sub myMacro()
FXLDataRefreshWorkbook
End Sub

Macro Example 3 - Refresh all FXLData functions in the current sheet
Public Sub myMacro()
FXLDataRefreshSheet
End Sub

Macro Example 4 - Refresh the FXLData function in the specified cell
Public Sub myMacro()
FXLDataRefreshCell Range("C4")
End Sub

=Dimension at Observation=
For horizontal presentation functions, the ''dimension at observation'' defaults to time but can be changed using the <code>dimensionAtObservation</code> REST argument.

'''REST Argument Syntax''' 
dimensionAtObservation=<DIMENSION_ID> | AllDimensions

'''Behaviour''' 
{| class="wikitable" style="width:60%;"
|-
! Value !! Behaviour !! Example
|-
| <DIMENSION_ID> || Using the ID of any other dimension in the dataset will cause the observations to be broken down by that dimension which will appear across the columns || dimensionAtObservation=REF_AREA
|-
| TIME_PERIOD || The same as the default behaviour where time is the dimension at observation || (dimensionAtObservation argument is ommitted)
|-
| AllDimensions || Setting the dimension at observation to the special value ''AllDimensions'' generates a result set with a single row per observation || dimensionAtObservation=AllDimensions
|}

'''Examples''' 
Setting REF_AREA as the dimension at observation
=FXLDataUrl("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GBR?labels=id&round=1&dimensionAtObservation=REF_AREA</nowiki>")

AllDimensions - the result is precisely one observation per row
=FXLDataUrl("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GBR?labels=id&round=1&dimensionAtObservation=AllDimensions</nowiki>")

Default behaviour with time as the dimension at observation
=FXLDataUrl("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GBR?labels=id&round=1</nowiki>")

=Choosing Attributes to Include in the Resultset=
A breakdown of some or all series attributes can be returned as part of the resultset if required. Note that dataset and observation-level attributes are not available.
====Include all attributes====
* =FXLData() horizontal presentation: set ''includeBreakdown=TRUE'' and ''includeAttributes=TRUE'' 
* =FXLDataV() vertical presentation: set ''labels=both'' and ''includeAttributes=TRUE''

====Include selected attributes====
* Set ''includeBreakdown=TRUE'' for =FXLData() or ''labels=both'' for =FXLDataV() 
* Add to the ''REST Args'': ''attributes='' followed by a comma-separated list of the attribute IDs to include. e.g. <code>attributes=UNIT,TITLE</code>

====Worked examples====
The following are samples from the ECB's EXR (Exchange Rates) dataset: 

Include all attributes using horizontal presentation.
=FXLData("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","A",100,,"1990","1995","id",True,True,True,True)

Include all attributes using vertical presentation.
=FXLDataV("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","A",100,,"1990","1995","both",True,True,True,True)

Include just the UNIT and TITLE attributes using horizontal presentation.
=FXLData("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","A",100,"attributes=UNIT,TITLE","1990","1995","id",True,True,True,True)

Include just the UNIT and TITLE attributes using vertical presentation.
=FXLDataV("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","A",100,"attributes=UNIT,TITLE","1990","1995","both",True,True,True,True)

=Fusion Registry Authentication=
Excel will automatically request the user to authenticate when executing and FXLData function where the Fusion Registry data source is operating in ''[[Server_Security#Server_Security|private mode]]''.

Points to note:
* Excel will remember authentication details provided for the remainder of the session. Close and reopen Excel if you need to re-authenticate as a different user.
* A spread sheet can contain multiple FXLData functions using different Fusion Registries. Excel will ask for separate authentication details for each Fusion Registry, thus it is possible to connect to Fusion Registry A as user X, and Fusion Registry B as user Y.
* No authentication will be requested if a Fusion Registry is operating in ''public mode''.

[[File:Sign.png|30px]] There is no way to force FXLData to authenticate as a particular user if the Fusion Registry is operating in ''[[Server_Security#Server_Security|public mode]]'' but also allows authenticated users with additional data access privileges. Fusion Registry administrators should consider switching to ''[[Server_Security#Server_Security|private mode]]'' as a work-around.

[[File:FXLData Excel Authentication.PNG|300px]]

===[username]@ URL prefix to force authentication as a specific user===
From Fusion Registry 11.1.1 (release 10 June 2022) and FXLData 1.1.1, [username]@ can be prefixed to the URL to force authentication as the user identified by [username].

Example:
=FXLData("johnwright@https://stats.mydomain.org/FusionRegistry","ABC,CPI,1.0","A..X00.M+Q._Z")

The Fusion Registry will ensure the user logs in with the given username. To force authentication without specifying a username, user the @ prefix with no username.

=FXLData("@https://stats.mydomain.org/FusionRegistry","ABC,CPI,1.0","A..X00.M+Q._Z")

=Resultset Metrics=
Metrics are shown in the first row of the result set if headers are enabled. 
[[File:Example Metrics 0 7 0.JPG|thumb|Example Metrics]]

===Series Count===
100/3608 series

<base series retrieved>/<base series available> ''series''

* <base series retrieved> is the number of base series retrieved ''before any calculated measures are added or aggregations performed''. 
* <base series available> is the total number of base series available for the query.

====The effect of calculated measures====
If the query includes calculated measures such as percentage change or moving average, the actual number of series retrieved will be '''higher''' than the reported base series. The actual number of series in the result set can generally be calculated as:

''<base series retrieved> * (1 + <number of calculated measures>)''

Note that this formula does not hold true when combining aggregations with calculated measures.

====The effect of aggregations====
Aggregations reduce the number of series retrieved due to the removal of one or more dimensions. Thus, the series retrieved will be '''lower''' than the reported base series.

===Function Execution Time===
0.29 seconds

<execution time> ''seconds''

The actual time the function took to retrieve the data from the Fusion Registry and return the result set to Excel.

===Resultset freshness timestamp===
18/11/2021 17:10:07

<date time>

The date and time the resultset was last refreshed from the Fusion Registry data source.

====Controlling when metrics are displayed====
For parameterised functions (=FXLData() and =FXLDataV()), metrics are displayed when the ''IncludeHeader'' argument is <code>TRUE</code>. Set to <code>FALSE</code> to omit metrics. 
For URL functions (=FXLDataUrl() and =FXLDataUrlV()), metrics are displayed if the REST API parameter <code>excludeHeader=false</code>. Set the parameter to <code>excludeHeader=true</code> to omit metrics.

=Troubleshooting=
====The FXLData addin disappears or fails to load====
A possible cause is Windows file protection security settings blocking the FXLData.xlam file as it has originated from another computer. The following YouTube video explains how to solve the problem by disabling Windows protection on the file: https://www.youtube.com/watch?v=AhnOU-ulqNg

This article from microsoft also explains the process to unblock a macro:

https://support.microsoft.com/en-us/topic/a-potentially-dangerous-macro-has-been-blocked-0952faa0-37e7-4316-b61d-5b5ed6024216

====Excel slow or hangs when changing or deleting a large number of cells in a worksheet====
Disable Legacy Mode automatic recalculation using the checkbox on the ribbon bar:

[[File:FXLData Wizard Ribbon 058.JPG|300px]]

==== Unable to Retrieve Dataflows ====
If you are using FREE version 11 or higher you may need to review your settings in Fusion Security Manager. If you are unsure how to do this, please create a support ticket or email us : info@metadatatechnology.com

File:FXLData 1-2-6.zip

2023-02-24T10:39:54Z

Glenn:

'''Checksum'''

FXLData.xlam SHA256: a6b691b16e422db74a845f2f4fc6acfd62975f3ed6c11de6cc7ecab11d9e3717

File:FXLData 1-2-6.zip

2023-02-24T10:38:43Z

Glenn:

FXLData Change Log

2023-02-23T11:40:21Z

Glenn:

__NOTOC__
'''Excel Command''' 
=FXLDataVersion("changelog")

== Version 1.2.5 (23 February 2023) ==
* FXLData: fixed bug in [user]@ syntax handler that mangled the URL if an @ symbol is used in the Dataflow ID for instance
* Legacy Mode: modified application event handling to make control over automatic calculation more robust
* Fusion Registry refreshable Excel workbooks: fixed bug preventing workbooks from being refreshed on open

== Version 1.2.4 (18 November 2022) ==
* FXLData: show the REST data API url in the header
* FXLData: add 'nocc' client-side REST Argument to restrict the resultset time periods to only those with observation values when startPeriod and / or endPeriod are specified

== Version 1.2.3 (14 November 2022) ==
* Legacy Mode: bug fix displaying N/A under certain circumstances

== Version 1.2.2 (11 November 2022) ==
* Legacy Mode: bug fix preventing display of datasets with only a single row

== Version 1.2.1 (08 November 2022) ==
* Legacy Mode: change automatic calculation option to 'off' by default to address general worksheet performance issues

== Version 1.2.0 (05 July 2022) ==
* Legacy Mode: add Ribbon function to disable automatic calculation to speed bulk changes to worksheets such as deleting large numbers of cells

== Version 1.1.2 (27 June 2022) ==
* Wizard: fix dataflow search

== Version 1.1.1 (09 June 2022) ==
* Refreshable worksheets: modified automatic refresh on open to improve reliability of refreshable worksheets

== Version 1.1.0 (30 May 2022) ==
* FXLData: Support 'un' parameter to force login on refreshable worksheets

== Version 1.0.1 (16 May 2022) ==
* FXLData: Fixed invalid availability query syntax when working with Fusion Registry 11 and SDMX 3.0 

== Version 1.0.0 (13 May 2022) ==
First Public Release
1.0.1 (16 May 2022)
* FXLData: Fixed invalid availability query syntax when working with Fusion Registry 11 and SDMX 3.0

------------------------------------------------------------------------------------------------
== Beta Versions ==
Beta 0.3.3
* Authentication issues fixed
Beta 0.3.4
* Modified parameterised variants to allow no dataflow and a dataflow/series key to be specified in the expression
Beta 0.3.5
* JSON / POST data query support for series baskets
Beta 0.3.6
* Vertical JSON / POST support
Beta 0.3.7
* Fixed bug in URL validator which rejected URLs where the host contains hyphens
* Fixed bug where URL's of the form crash the API call http://@hostname
Beta 0.3.8
* Add workaround for 'aggregate' queries where the availability query does not return the correct series_count
* =FXLDataV() now takes the same arguments as =FXLData() to ease switching between horizontal and vertical presentations - some arguments are ignored though
Beta 0.4.0
* Add ribbon bar Wizard
Beta 0.4.1
* Wizard: Removed redundant ribbon bar components
* Wizard: Added dataflow dropdown refresh
Beta 0.4.2
* Wizard: Added defence against JSON being supplied as a literal Expression value - Excel doesn't support that
* Wizard: Added tool tips
* Wizard: Corrected tab order
Beta 0.4.3
* Wizard: Improved handling of FXLData function execution errors when inserting the function into a sheet
Beta 0.4.4
* Wizard: Added checks to verify the correct version of Excel is being used (Microsoft 365 Excel Sept 2018 or later)
Beta 0.5.0
* FXLData / Wizard / Refresh: add support for pre-Excel 2018 'legacy mode' where the result array is iterated and inserting into the cells on ribbon button click
Beta 0.5.1
* FXLData: Added data query caching to improve performance particularly on legacy refresh where the same query may be executed multiple times
Beta 0.5.2
* Purge Cache: Added 'Purge Cache' function to the ribbon bar
Beta 0.5.3
* Legacy Mode: legacy functions now automatically refresh on change without using the Ribbon 'Refresh' emulating the behaviour of normal Excel functions to some extent
* Legacy Mode: added error handling for stack overflows on legacy 'evaulate' where the formula length exceeds the 255 character limit
Beta 0.5.4
* Ribbon: Added control over automatic refresh in legacy mode
* FXLData: Improved caching to avoid caching error results
* Legacy Mode: Modified application event handling to avoid triggering refresh events where not needed
Beta 0.5.5
* Wizard: check each Dataflow for access rights before adding to the dropdown to ensure the list only contains Dataflows for which the user has permissions
Beta 0.5.6
* FXLData: Fixed issues with FXLDaatV returning the incorrect number of series (removed paging which is not relevant for vertical and corrected series calcs when using calculated measures)
* FXLData: Removed API paging due to limited benefit
* FXLData: Fixed bug resulting in 'Query Error - 0 series retrieved of 0' due to paging errors
* Wizard: Query Builder - added clear selection button
* Wizard: Query Builder - show series count for entire dataflow on initialisation
* Wizard: Query Builder - removed irrelevant TIME_PERIOD tab
* Wizard: Added REST arguments syntax help
* Legacy Mode: Stopped screen updating during refresh to improve performance
Beta 0.5.7
* Wizard: Query Builder enhanced to grey-out invalid codes as selections are made
* Wizard: Improved query builder refresh performance
* Wizard: Improved 'unapplied changes' indicator
* Wizard: Improved REST API help popup
Beta 0.5.8
* FXLData: Trap and gracefully handled Registry data query errors that return no data, but result in HTTP 500 and 'Index:0, Size:0' responses
* Refresh: Added Refresh Sheet option
* Ribbon: Moved Refresh and Calculations to Ribbon menus
Beta 0.6.0
* Legacy Mode: Modified legacy mode dynamic array emulation to improve performance to that comparable with actual dynamic arrays (legacyRefreshCell refactor - assign resultset arrary directly to range.value instead of iterating one cell at a time)
* Legacy Mode: Application events initialised in the FXLData Workbook Open event
* Legacy Mode: Automatic calculation defaulted on true in the FXLData Workbook Open event which improves reliability by avoiding reliance on ribbon initialisation to perform that task
* FXLData: Corrected duplication of double quotes where they appear in component labels
Beta 0.6.1
* Addin: Forces Excel to execute the FXLData formula in cell B2 on workbook open ensuring that refreshable workbooks generated by Fusion Registry refresh as expected on open
Beta 0.7.0
* FXLData: Fusion Registry 11 support
* FXLData: Authentication exception handling bug fixed in all four of the fxldata query execution functions - URL h and v, POST h and v
* FXLData: Added freshness timestamp to the resultset metrics
* Wizard: Do not show the wizard for FXLDataURL or FXLDataURLV functions as it is designed for use only with the parameterised function variants
Beta 0.7.2
* FXLData: SDMX 3.0 availability API support
Beta 0.7.3
* FXLData: Added FXLDataUrlFact() function
Beta 0.7.4
* FXLDataUrlFact: Added defence against errors caused where the availability query incorrectly indicates data is available, and the data query returns none
* FXLDataUrlFact: Added gzip accept encoding to XML dataset GET
* FXLData: Weakened the URL syntax check to avoid FXLDataURL refreshable sheet calls being rejected as bad URL because of spaces in the 'saveAs' parameter
Beta 0.7.5
* FXLData: Fixed issue preventing refreshable worksheets refreshing on open in legacy mode
* FXLData: Fixed FXLDataUrlFact bug where the wrong number of output columns was estimated under certain circumstances
Beta 0.7.6
* Wizard: Added 'Purge Cache on Apply' option
* Legacy Mode: Fixed bug in legacy mode refresh where resultsets in the same cell on different sheets are deleted on refresh
Beta 0.7.7
* Legacy Mode: Changed refresh to clear the contents of the range rather than clear all thus preserving user-applied formatting
* Legacy Mode: Fixed refresh bug which left redundant ranges where a FXLdata formula was moved, deleted or over written
* Legacy Mode: Changed refresh behaviour to not delete the range content when an FXLData formula is moved, deleted or over written
Beta 0.7.8
* Legacy Mode: Modified sub legacyRefreshCell() to exit immediately if the cell does not contain an FXLData function
* FXLData: FXLDATA-2 Vertical presentation fails with an 'subscript out of range' inline error when the resultset has one series
Beta 0.7.9
* Wizard: Added dataflow quick filter
* Legacy Mode: Removed the Ribbon Bar option to control automatic refresh on change because setting to 'manual' prevented workbook and sheet refresh because named ranges were not being created
* Legacy Mode: Fixed 'refreshselectedcell' bug whereby a named range was not being created if the function returns an error thus preventing subsequent workbook and sheet refresh operations
Beta 0.7.10
* Wizard: Date format syntax help added
* Wizard: Corrected date format validation bug that reported dates of the form '01/01/2010' as valid
* Wizard: Corrected bug that incorrectly evaluated date values in existing formulas on wizard startup
Beta 0.7.11
* Legacy Mode: Changed legacy mode to use 'worksheet' scoped named ranges rather the 'workbook' to avoid confusion when worksheets are duplicated
* Legacy Mode: Modified legacy refresh to use worksheet.evaluate rather than application.evaluation to ensure formulas run within the correct worksheet context - fixes unexpected workbook refresh results due to the formulas on other worksheets being evaluated in the current worksheet context
Beta 0.7.11
* FXLData: Bug fix to availability query to correctly set the format=sdmx-2.1 - resolves problem in Fusion Registry 11.0.0 where the query was being rejected with HTTP 406 due to invalid format specifier

FXLData

2023-02-23T11:39:12Z

Glenn: /* Download */

__NOTOC__
An Excel add-in providing a collection of functions for retrieving statistical data from a Fusion Registry directly into a worksheet

[[File:Fxldata animation.gif]]

=Download=
Download the latest version [[File:FXLData 1-2-5.zip]] 23 February 2023

See the [[FXLData Changelog]] for recent changes, updates and bug fixes.

=Quick start=
# Download the latest version and unzip the add-in which is a single file: FXLData.xlam
# [[#Installation|Install]] the add in
# Open a blank Excel worksheet
# Paste the following formula into a cell
=fxldata("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","A")
A sample 100 series of annual ECB exchange rates data will be retrieved from Metadata Technology's public demonstration Fusion Registry service and inserted directly into the sheet.

The [[FXLData Wizard]] provides tools to help with the task of building FXLData formulas.

[[File:FXLData Wizard Ribbon 058.JPG|FXLData Excel ribbon|200px]]

__TOC__

=Overview=
The FXLData family of functions are Excel ''array formulas''.

These work in the same way normal as normal single-cell funcions such as =SUM() and =STDEV() with the exception that the result is an ''array'' of series and observation values spanning a range of rows and columns.

Microsoft's [https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 documentation] provides general information on how to work with array formulas in Excel.

====Parameterised functions====
These functions take a number of separate arguments making them suitable for building sophisticated data models where Start Period, End Period and other parameters use cell references or Excel formulas.
[[File:Fxldata example1.PNG|thumb|Horizontal time-series example]]
Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations.

'''=FXLData'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes]'' ) 
Returns a selected dataset in 'horizontal' time-series format with one series per row 
* Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return
* A further nine optional arguments can be specified including: maximum number of series to retrieve, start / end period, labels, headers and whether dimensions and attributes are shown

'''=FXLDataV'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] - ignored, [IncludeBreakdown] - ignored, [IncludeAttributes]'' ) 
Returns a selected dataset in 'vertical' format with one series per column
* Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return
* A further nine optional arguments can be specified including: maximum number of series to retrieve, start / end period, and whether dimensions, attributes and metrics are shown
* IncludeSeriesKey is ignored
* IncludeBreakdown is ignored
* Set Labels=both to show IDs and dimensions / attributes
* If Labels=both, setting IncludeAttributes=TRUE will show the breakdown of series / dataset attributes in the header
* IncludeHeader controls whether the resultset metrics are shown at the top (e.g. 205/1276 series 1.78 seconds)

Examples
=FXLData("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","ECB,EXR,1.0","A...")
=FXLDataV("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","ECB,EXR,1.0","A...",1000,"round=2",2010,2020,"both",true,true,true,false)
=FXLData(C14,C4,C7,C15,C13,C5,C6,C12,C9,C8,C10,C11)
=FXLDataV(A9,B24,B25,1000,,min(D55,2010),max(D56,2020))
Like all Excel functions, arguments can either be literal values, cell references, or other formulas.

====Functions taking a Fusion Registry data query URL====
These functions take a single fully-formed Fusion Registry data query URL making them suitable for use cases where another tool such as the Fusion Data Browser has been used for data discovery, with data retrieval being performed directly in Excel.

Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations.

'''=FXLDataUrl'''( ''Url , [MaxSeries] '' ) 
Returns the selected resultset in 'horizontal' time-series format with one series per row 
* Behaves in the same way as =FXLData() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL
* A single optional argument allows the maximum number of series to be set

'''=FXLDataUrlV'''( ''Url , [MaxSeries] '' ) 
Returns the selected resultset in 'vertical' format with one series per column 
* Behaves in the same way as =FXLDataV() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL
* A single optional argument allows the maximum number of series to be set

'''=FXLDataUrlFact'''( ''Url , [MaxSeries] '' ) 
Returns the selected resultset as a [https://en.wikipedia.org/wiki/Fact_table data warehouse style fact table] 
* Takes a single mandatory argument: a fully-formed Fusion Registry data query URL
* A single optional argument allows the maximum number of series to be set
* Returns one row per observation including all series and observation attributes
* Following the traditional fact table model, only dimension and attributes codes are returned - there is not option to return code labels

Examples
=FXLDataUrl("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki>")
=FXLDataUrlV("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki>",1000)
=FXLDataUrlFact("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/.CAD+MXN+MYR...</nowiki>",20)

====Administrative functions====
'''=FXLDataVersion()'''
* Returns the software version number and build date

====Microsoft 365 Excel 2018 and later====
In Microsoft 365 versions of Excel, the FXLData functions act as [https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531 dynamic array formulas] which populate or 'spill' the result set into neighbouring cells on execution. There is no need for an explicit refresh because Excel will 'recalculate' any FXLData functions that have changed using its normal worksheet calculation behaviour. Settings Excel's calculation options to Automatic (the default) will automatically refresh an FXLData formula if the formula or something it depends upon such as a parameter in referenced cell has changed. If calculation is set to Manual, changed FXLData formulas will be refreshed when the user asks Excel to perform a recalculation using the ''Calculate Now'' or ''Calculate Sheet'' buttons on the ribbon bar. A complete recalculation can be forced using CTRL+ALT+SHIFT+F9. 

====Office 2010-2019 Excel====
For these older versions of Excel which do not support dynamic array formulas, the FXLData functions work in [[#Legacy_Mode|legacy mode]] which emulates to some extent the dynamic array formula behaviour. On execution, the result set still populates into neighbouring cells. However, there are some important differences between legacy mode and proper dynamic arrays:
* In legacy mode, the result set is written directly into the worksheet which Excel treats as a collection of largely disconnected cells. By contrast, Excel treats a dynamic array as a single entity.
* Excel automatically protects against dyanamic arrays overwriting other content in the worksheet resulting in a [https://support.microsoft.com/en-us/office/how-to-correct-a-spill-error-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023 #Spill!] error if that would otherwise occur. Legacy mode will overwrite any existing content meaning that care is required to avoid worksheet corruption if the result set from an FXLData function is larger than expected.
* Legacy mode FXLData functions will be automatically refreshed if the formula itself is changed, but it not if a dependency of the formula changes. This is because the legacy mode refresh only emulates the proper dynamic array formuala spill behaviour.

====FXLData Wizard====
The [[FXLData Wizard]] is an Excel ribbon bar tool for interactively building and editing FXLData functions and comes as part of the add in.

=Prerequisites=
# Fusion Registry 10.7.0 or later
# Microsoft 365 Excel 2018 or later - supports dynamic arrays which automatically 'spill' the result set into the worksheet
# Office 2010-2019 - FXLData functions operate in [[#Legacy_Mode|legacy mode]] which provides dynamic array emulation

Refer to Microsoft's [https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 documentation] on dyanmic arrays and legacy 'ctrl-shift-enter' behavior.

Excel on Apple Mac is not supported because FXLData uses operating system services only available on Windows.

=Installation=
[[File:Excel addinmanager.png|thumb|Excel add-in manager]]
The FXLData add-in is distributed as a single Excel file:

''FXLData.xlam''

There are two installation options.

====Option 1: Excel Add-ins manager====
# From the Excel File menu, choose Options
# On the Options popup, choose Add-ins from the list
# At the bottom, choose Excel Add-ins from the 'Manage' list and click Go
# On the Add-ins popup, choose Browse and select the FXLData.xlam distribution file

====Option 2: Excel XLSTART directory====
# Copy the FXLData.xlam to the Excel XLSTART directory which is usually in the following location C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART

=Function Reference=
====FXLData()===
By default ƒ''x'' =FXLData() returns a horizontal time-series dataset, one series per row with time spanning the columns i.e. time is the dimension at observation. A different dimension at observation can be chosen using the RestAuguments parameter - refer to the guidance [[#Dimension at Observation|below]].

'''Use Case''' 
This parameterised form is suitable for hand coding in a spreadsheet. Like all Excel functions, the arguments can be literal values, cell references or Excel expressions allowing the creation of complex data models. In its simplest form, only three arguments are required:
* The 'base URL' of the Fusion Registry to connect to
* The ID of the Dataflow
* A series key specifying which series to retrieve, or simply 'all' to retrieve all series
=FXLData("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","all")
By default, the number of series returned is capped at 100, but can be altered using the ''MaxSeries'' argument

'''Syntax''' 
=FXLData( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes]'' )

{| class="wikitable" style="width:70%;"
|-
! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?
|-
| 1||RegUrl|| The base URL of the Fusion Registry service || <nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki> || style="background-color:pink;" |Mandatory
|-
| 2||Dataflow || Options: 1. The dataflow to query conforming to the SDMX REST specification for data queries 2. A blank string - in this case the Dataflow(s) to query must be specified as part of the Expression. || EXR ECB,EXR,1.0 || style="background-color:pink;" | Mandatory
|-
| 3||Expression || Options: 1. A [[Series Key|series key]] 2. A dataflow and series key expression <dataflow>/<series key> 3. A [[Anatomy_of_the_Data_Browser#Series_List_From_Series_Basket|JSON series basket expression]] which must be a cell reference and cannot be a literal value 4. The keyword 'all' to select all series in the dataset|| A A.CAD+HUF+INR...E all ECB,EXR,1.0/A.HUF...E C4 (cell reference)||style="background-color:pink;" | Mandatory
|-
| 4||MaxSeries || The maximum number of series to return || 1000 || Optional Defaults to 100 series
|-
| 5||RestArguments || Additional [[Data_Query_Web_Service#Extended_HTTP_Query_Parameters|data REST query arguments]] intended for special use cases like adding calculated measures or changing the dimension at observation || round=3&calculatemeasure=THE_MEASURE;pch;mvavg,3&dimensionAtObservation=CURRENCY_DENOM || Optional
|-
| 6||StartPeriod || The start period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12 2010 || Optional Defaults to the start of the available data
|-
| 7||EndPeriod || The end period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12 2010 || Optional Defaults to the end of the available data
|-
| 8||Labels || Component labels <code>id</code> - IDs only <code>name</code> - names only <code>both</code> - both IDs and names || both || Optional Defaults to <code>id</code>
|-
| 9||IncludeHeader || Boolean <code>TRUE</code> includes component labels at the head of each column and shows metrics <code>FALSE</code> component labels and metrics are ommitted|| FALSE || Optional Defaults to <code>TRUE</code>
|-
| 10||IncludeSeriesKey || Boolean <code>TRUE</code> adds a calculated dimension 'series' with the series key as the value <code>FALSE</code> series keys are not included in the dataset || FALSE || Optional Defaults to <code>TRUE</code>
|-
| 11||IncludeBreakdown || Boolean Controls whether a breakdown of the dataset's components is shown. <code>TRUE</code> includes dimensions. Attributes are also shown if the IncludeAttributes argument is TRUE. <code>FALSE</code> no components are included || TRUE || Optional Defaults to <code>TRUE</code>
|-
| 12||IncludeAttributes || Boolean <code>TRUE</code> includes series attributes in the component breakdown <code>FALSE</code> excludes attributes|| FALSE || Optional Defaults to <code>TRUE</code>
|}

====FXLDataUrl()===
By default ƒ''x'' =FXLDataUrl() returns a horizontal time-series dataset. Time is the dimension at observation by default resulting in one series per row with time periods spanning the columns. A different dimension at observation can be chosen by adding a REST argument to the URL - refer to the guidance [[#Dimension at Observation|below]].

'''Use Case''' 
[[File:Fdb query syntax.PNG|thumb|Fusion Data Browser Query Syntax function]]
This version of the function is designed primarily for use cases where a data query URL is generated by a data discovery tool, for example:
* The Fusion Registry [https://demo.metadatatechnology.com/FusionRegistry/webservice/data.html REST Web Service query builder]
* The Fusion Data Browser Export Data ''Query Syntax'' function

'''Syntax''' 
=FXLDataUrl( ''Url , [MaxSeries] '' )

{| class="wikitable" style="width:70%;"
|-
! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?
|-
| 1||Url|| A complete and fully-formed SDMX data query URL || <nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki> || style="background-color:pink;" |Mandatory
|-
| 2||MaxSeries || The maximum number of series to return || 1000 || Optional Defaults to 100 series
|}

====FXLDataV()===
By default ƒ''x'' =FXLDataV() returns a vertical dataset, one series per column with time spanning the rows. The dimension at observation cannot be changed, the REST argument, if set is ignored.

'''Use Case''' 
This parameterised form is suitable for hand coding in a spreadsheet. Like all Excel functions, the arguments can be literal values, cell references or Excel expressions allowing the creation of complex data models. In its simplest form, only three arguments are required:
* The 'base URL' of the Fusion Registry to connect to
* The ID of the Dataflow
* A series key specifying which series to retrieve, or simply 'all' to retrieve all series
=FXLDataV("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","all")
By default, the number of series returned is capped at 100, but can be altered using the ''MaxSeries'' argument

'''Syntax''' 
=FXLDataV( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes]'' )

{| class="wikitable" style="width:70%;"
|-
! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?
|-
| 1||RegUrl|| The base URL of the Fusion Registry service || <nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki> || style="background-color:pink;" |Mandatory
|-
| 2||Dataflow || Options: 1. The dataflow to query conforming to the SDMX REST specification for data queries 2. A blank string - in this case the Dataflow(s) to query must be specified as part of the Expression. || EXR ECB,EXR,1.0 || style="background-color:pink;" | Mandatory
|-
| 3||Expression || Options: 1. A [[Series Key|series key]] 2. A dataflow and series key expression <dataflow>/<series key> 3. A [[Anatomy_of_the_Data_Browser#Series_List_From_Series_Basket|JSON series basket expression]] which must be a cell reference and cannot be a literal value 4. The keyword 'all' to select all series in the dataset|| A A.CAD+HUF+INR...E all ECB,EXR,1.0/A.HUF...E C4 (cell reference)||style="background-color:pink;" | Mandatory
|-
| 4||MaxSeries || The maximum number of series to return || 1000 || Optional Defaults to 100 series
|-
| 5||RestArguments || Additional [[Data_Query_Web_Service#Extended_HTTP_Query_Parameters|data REST query arguments]] intended for special use cases like adding calculated measures or changing the dimension at observation || round=3&calculatemeasure=THE_MEASURE;pch;mvavg,3&dimensionAtObservation=CURRENCY_DENOM || Optional
|-
| 6||StartPeriod || The start period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12 2010 || Optional Defaults to the start of the available data
|-
| 7||EndPeriod || The end period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12 2010 || Optional Defaults to the end of the available data
|-
| 8||Labels || Component labels <code>id</code> - IDs only <code>both</code> - both IDs and names || both || Optional Defaults to <code>id</code>
|-
| 9||IncludeHeader || Boolean <code>TRUE</code> shows resultset metrics (e.g. 108/1256 series 2.15 seconds) <code>FALSE</code> metrics are omitted|| FALSE || Optional Defaults to <code>TRUE</code>
|-
| 10||IncludeSeriesKey || Ignored|| || Optional Ignored</code>
|-
| 11||IncludeBreakdown || Ignored || || Optional Ignored</code>
|-
| 12||IncludeAttributes || Boolean <code>TRUE</code> includes series attributes if labels=both <code>FALSE</code> excludes attributes|| FALSE || Optional Defaults to <code>TRUE</code>
|}

====FXLDataUrlV()===
ƒ''x'' =FXLDataUrlV() returns a vertical dataset, one series per column with time spanning the rows. The dimension at observation cannot be changed, the REST argument, if set is ignored.

'''Use Case''' 
[[File:Fdb query syntax.PNG|thumb|Fusion Data Browser Query Syntax function]]
This version of the function is designed primarily for use cases where a data query URL is generated by a data discovery tool, for example:
* The Fusion Registry [https://demo.metadatatechnology.com/FusionRegistry/webservice/data.html REST Web Service query builder]
* The Fusion Data Browser Export Data ''Query Syntax'' function

'''Syntax''' 
=FXLDataUrlV( ''Url , [MaxSeries] '' )

{| class="wikitable" style="width:70%;"
|-
! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?
|-
| 1||Url|| A complete and fully-formed SDMX data query URL || <nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki> || style="background-color:pink;" |Mandatory
|-
| 2||MaxSeries || The maximum number of series to return || 1000 || Optional Defaults to 100 series
|}

====FXLDataUrlFact()===
ƒ''x'' =FXLDataUrlFact() returns the selected resultset as a [https://en.wikipedia.org/wiki/Fact_table data warehouse style fact table] with one row per observation including all series and observation attributes. As this is conceptually just the fact table of a [https://en.wikipedia.org/wiki/Star_schema star schema], only code IDs are returned - code labels are not available.

'''Use Case''' 
This version of the function is designed for data analytics involving observation attributes. The fact table model also works well with Excel's standard Pivot Table features.

'''Syntax''' 
=FXLDataUrlFact( ''Url , [MaxSeries] '' )

{| class="wikitable" style="width:70%;"
|-
! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?
|-
| 1||Url|| A complete and fully-formed SDMX data query URL || <nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&labels=id</nowiki> || style="background-color:pink;" |Mandatory
|-
| 2||MaxSeries || The maximum number of series to return || 1000 || Optional Defaults to 100 series
|}

====FXLDataVersion()===
ƒ''x'' =FXLDataUrlV() returns the version number of the FXLData addin.

'''Syntax''' 
=FXLDataVersion()

'''Example''' 
=FXLDataVersion()
Returns <code>1.3.2 18/12/2021</code>

=Client-side REST Arguments=
The =FXLData() and =FXLDataV() functions support additional REST arguments to modify behaviour in Excel. These arguments are not passed to the Fusion Registry or Edge Server.

'''nocc''' - suppress completion of the resultset cube when startPeriod and / or endPeriod are specified. Under normal behaviour, the resultset will always span the time periods specified irrespective of whether there are observation values available. If nocc is specified in the REST Arguments, the resultset will only span time periods for which observations are available. 
Examples:
nocc
round=2&nocc
nocc&dimensionAtObservation=REF_AREA

=Legacy Mode=
Legacy Mode emulates the latest Microsoft 365 dynamic array behaviour in older Office Excel 2010-2019 versions by populating the cells neighbouring that holding an FXLData formula with the data result set.

In versions of Microsoft 365 Excel that do support dynamic arrays, legacy mode can also be used to write the result set directly into the worksheet. That's useful in situations where discrete data points are needed rather than an Excel array which has certain limitations.

====Office Excel 2010-2019====
FXLData always works in legacy mode when using Office Excel 2010-2019.

When an FXLData function is executed, the first cell of the result set will appear as the formula result. If automatic calculation is enabled, FXLData's ''dynamic array emulation'' will immediately populate the neighbouring cells with the remainder of the result set. If automatic calculation is disabled, [[#Refresh|Refresh]] must be used.

====Forcing Microsoft 365 Excel to use Legacy Mode====
FXLData functions in Microsoft 365 Excel operate as dynamic array formulas by default where the result set appears in the worksheet as an Excel array.

Using the [https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34 Implicit intersection operator: @] however forces the FXLData function to operate in legacy mode causing the result set to be written directly into the worksheet.
=@FXLData("<nowiki>https://registry.domain.org/FusionRegistry</nowiki>","ECB,EXR,1.0","A..BGN+BEF..",100,"round=2","2010","2020","id",TRUE,FALSE,TRUE,FALSE)
In the above example the '''@''' preceeding the FXLData function name instructs Excel to return the ''implicit intersection'' of the result set array which is just the first cell. If legacy mode ''automatic calculation'' is enabled, the FXLData's dynamic array emulation will populate the neighbouring cells. If not, [[#Refresh|Refresh]] must be used.

=Refresh=
===Microsoft 365 Excel and dynamic array formulas===
====Interactive====
FXLData functions by default work as ''dynamic array formulas'' in Microsoft 365 so are refreshed using the normal Excel calculation rules:

Refresh occurs when:
* a change is made to the formula
* a change is made to something the formula depends upon such as an argument value held in another cell
* Excel is forced to recalculate open workbooks using <code>CTRL+ALT+F9</code> or <code>CTRL+ALT+SHIFT+F9</code>
* a saved workbook is opened

[[File:Sign.png|30px]] The Refresh options on the FXLData ribbon do not refresh dynamic array formulas.

====From Code====
Refreshing FXLData from VBA, C# or C code uses the standard [https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation Excel API calculation functions], for instance:
* VBA: ActiveSheet.Calculate
* C API: xlcCalculateDocument()

===Office 2010-2019 Excel and Microsoft 365 Excel working in legacy Mode===
====Interactive====
[[File:FXLData Wizard Ribbon 058.JPG|thumb|FXLData Excel ribbon|200px]]
In older versions of Office Excel and when forcing legacy mode in Microsoft 365 using the @ implicit intersection operator, FXLData functions can be manually refreshed using the Refresh options on the ribbon bar.
* Refresh Selected: refreshes the FXLData function at the current active cell
* Refresh Sheet: refreshes all FXLData functions on the current work sheet
* Refresh Book: refreshes all FXLData functions in the work book

[[File:Using Refresh in Legacy mode.PNG|thumb|Using Refresh in Legacy mode|200px]]

====From Code====
The FXLData add-in exposes three public sub-routines to trigger a refresh from VBA code.

[[File:Sign.png|30px]] A reference to FXLDataAddin must be added to the VBA project.

Macro Example 1 - Refresh the FXLData function in the current active cell
Public Sub myMacro()
FXLDataRefreshActiveCell
End Sub

Macro Example 2 - Refresh all FXLData functions in the workbook
Public Sub myMacro()
FXLDataRefreshWorkbook
End Sub

Macro Example 3 - Refresh all FXLData functions in the current sheet
Public Sub myMacro()
FXLDataRefreshSheet
End Sub

Macro Example 4 - Refresh the FXLData function in the specified cell
Public Sub myMacro()
FXLDataRefreshCell Range("C4")
End Sub

=Dimension at Observation=
For horizontal presentation functions, the ''dimension at observation'' defaults to time but can be changed using the <code>dimensionAtObservation</code> REST argument.

'''REST Argument Syntax''' 
dimensionAtObservation=<DIMENSION_ID> | AllDimensions

'''Behaviour''' 
{| class="wikitable" style="width:60%;"
|-
! Value !! Behaviour !! Example
|-
| <DIMENSION_ID> || Using the ID of any other dimension in the dataset will cause the observations to be broken down by that dimension which will appear across the columns || dimensionAtObservation=REF_AREA
|-
| TIME_PERIOD || The same as the default behaviour where time is the dimension at observation || (dimensionAtObservation argument is ommitted)
|-
| AllDimensions || Setting the dimension at observation to the special value ''AllDimensions'' generates a result set with a single row per observation || dimensionAtObservation=AllDimensions
|}

'''Examples''' 
Setting REF_AREA as the dimension at observation
=FXLDataUrl("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GBR?labels=id&round=1&dimensionAtObservation=REF_AREA</nowiki>")

AllDimensions - the result is precisely one observation per row
=FXLDataUrl("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GBR?labels=id&round=1&dimensionAtObservation=AllDimensions</nowiki>")

Default behaviour with time as the dimension at observation
=FXLDataUrl("<nowiki>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GBR?labels=id&round=1</nowiki>")

=Choosing Attributes to Include in the Resultset=
A breakdown of some or all series attributes can be returned as part of the resultset if required. Note that dataset and observation-level attributes are not available.
====Include all attributes====
* =FXLData() horizontal presentation: set ''includeBreakdown=TRUE'' and ''includeAttributes=TRUE'' 
* =FXLDataV() vertical presentation: set ''labels=both'' and ''includeAttributes=TRUE''

====Include selected attributes====
* Set ''includeBreakdown=TRUE'' for =FXLData() or ''labels=both'' for =FXLDataV() 
* Add to the ''REST Args'': ''attributes='' followed by a comma-separated list of the attribute IDs to include. e.g. <code>attributes=UNIT,TITLE</code>

====Worked examples====
The following are samples from the ECB's EXR (Exchange Rates) dataset: 

Include all attributes using horizontal presentation.
=FXLData("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","A",100,,"1990","1995","id",True,True,True,True)

Include all attributes using vertical presentation.
=FXLDataV("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","A",100,,"1990","1995","both",True,True,True,True)

Include just the UNIT and TITLE attributes using horizontal presentation.
=FXLData("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","A",100,"attributes=UNIT,TITLE","1990","1995","id",True,True,True,True)

Include just the UNIT and TITLE attributes using vertical presentation.
=FXLDataV("<nowiki>https://demo.metadatatechnology.com/FusionRegistry</nowiki>","EXR","A",100,"attributes=UNIT,TITLE","1990","1995","both",True,True,True,True)

=Fusion Registry Authentication=
Excel will automatically request the user to authenticate when executing and FXLData function where the Fusion Registry data source is operating in ''[[Server_Security#Server_Security|private mode]]''.

Points to note:
* Excel will remember authentication details provided for the remainder of the session. Close and reopen Excel if you need to re-authenticate as a different user.
* A spread sheet can contain multiple FXLData functions using different Fusion Registries. Excel will ask for separate authentication details for each Fusion Registry, thus it is possible to connect to Fusion Registry A as user X, and Fusion Registry B as user Y.
* No authentication will be requested if a Fusion Registry is operating in ''public mode''.

[[File:Sign.png|30px]] There is no way to force FXLData to authenticate as a particular user if the Fusion Registry is operating in ''[[Server_Security#Server_Security|public mode]]'' but also allows authenticated users with additional data access privileges. Fusion Registry administrators should consider switching to ''[[Server_Security#Server_Security|private mode]]'' as a work-around.

[[File:FXLData Excel Authentication.PNG|300px]]

===[username]@ URL prefix to force authentication as a specific user===
From Fusion Registry 11.1.1 (release 10 June 2022) and FXLData 1.1.1, [username]@ can be prefixed to the URL to force authentication as the user identified by [username].

Example:
=FXLData("johnwright@https://stats.mydomain.org/FusionRegistry","ABC,CPI,1.0","A..X00.M+Q._Z")

The Fusion Registry will ensure the user logs in with the given username. To force authentication without specifying a username, user the @ prefix with no username.

=FXLData("@https://stats.mydomain.org/FusionRegistry","ABC,CPI,1.0","A..X00.M+Q._Z")

=Resultset Metrics=
Metrics are shown in the first row of the result set if headers are enabled. 
[[File:Example Metrics 0 7 0.JPG|thumb|Example Metrics]]

===Series Count===
100/3608 series

<base series retrieved>/<base series available> ''series''

* <base series retrieved> is the number of base series retrieved ''before any calculated measures are added or aggregations performed''. 
* <base series available> is the total number of base series available for the query.

====The effect of calculated measures====
If the query includes calculated measures such as percentage change or moving average, the actual number of series retrieved will be '''higher''' than the reported base series. The actual number of series in the result set can generally be calculated as:

''<base series retrieved> * (1 + <number of calculated measures>)''

Note that this formula does not hold true when combining aggregations with calculated measures.

====The effect of aggregations====
Aggregations reduce the number of series retrieved due to the removal of one or more dimensions. Thus, the series retrieved will be '''lower''' than the reported base series.

===Function Execution Time===
0.29 seconds

<execution time> ''seconds''

The actual time the function took to retrieve the data from the Fusion Registry and return the result set to Excel.

===Resultset freshness timestamp===
18/11/2021 17:10:07

<date time>

The date and time the resultset was last refreshed from the Fusion Registry data source.

====Controlling when metrics are displayed====
For parameterised functions (=FXLData() and =FXLDataV()), metrics are displayed when the ''IncludeHeader'' argument is <code>TRUE</code>. Set to <code>FALSE</code> to omit metrics. 
For URL functions (=FXLDataUrl() and =FXLDataUrlV()), metrics are displayed if the REST API parameter <code>excludeHeader=false</code>. Set the parameter to <code>excludeHeader=true</code> to omit metrics.

=Troubleshooting=
====The FXLData addin disappears or fails to load====
A possible cause is Windows file protection security settings blocking the FXLData.xlam file as it has originated from another computer. The following YouTube video explains how to solve the problem by disabling Windows protection on the file: https://www.youtube.com/watch?v=AhnOU-ulqNg

This article from microsoft also explains the process to unblock a macro:

https://support.microsoft.com/en-us/topic/a-potentially-dangerous-macro-has-been-blocked-0952faa0-37e7-4316-b61d-5b5ed6024216

====Excel slow or hangs when changing or deleting a large number of cells in a worksheet====
Disable Legacy Mode automatic recalculation using the checkbox on the ribbon bar:

[[File:FXLData Wizard Ribbon 058.JPG|300px]]

==== Unable to Retrieve Dataflows ====
If you are using FREE version 11 or higher you may need to review your settings in Fusion Security Manager. If you are unsure how to do this, please create a support ticket or email us : info@metadatatechnology.com

File:FXLData 1-2-5.zip

2023-02-23T11:38:37Z

Glenn:

'''Checksum'''

FXLData.xlam
SHA256: eb3da60632f64362313192da60e650886ecc67bd9fbba1b2317918f9cd979fb9

SDMX 3.0 data query URL syntax support in Tomcat

2022-08-04T15:24:18Z

Glenn:

SDMX 3.0 data query URL syntax support in Tomcat

Glenn:

[[Category:FMR_REST_API_Reference]]
[[Category:Reference Metadata]]
See [[Reference_Metadata|Reference Metadata]]

= Overview =
This API supports GET (retrieve reference metadata), POST (submit new or modified reports), and DELETE (remove existing reports) methods to maintain Reference Metadata Reports.

The GET methods conform to the [https://github.com/sdmx-twg/sdmx-rest/blob/master/doc/metadata.md SDMX REST Specification for reference metadata retrieval].

== GET Reference Metadata ==
{| class="wikitable"
|-
|style="background-color:#eaecf0"|Entry Point|| /sdmx/v2/metadata
|-
|style="background-color:#eaecf0"|Access|| Public
|-
|style="background-color:#eaecf0"|Http Method|| GET
|-
|style="background-color:#eaecf0"|Response Format|| JSON
|-
|style="background-color:#eaecf0"|Response Statuses|| 200 - Success
401 - Unauthorized (if access has been restricted)
500 - Server Error
|}

[https://github.com/sdmx-twg/sdmx-rest/blob/master/doc/metadata.md See Specification].

==Examples==
Get specific report
https://example.registry.com/sdmx/v2/metadata/metadataset/MT/REPORT1/1.0

Get all metadata sets for a specific Metadata Provider
https://example.registry.com/sdmx/v2/metadata/metadataset/MT.PROVIDER/*/*

Get all reports against Metadataflow as Stubs (only include basic details, not the actual reported content)
https://example.registry.com/sdmx/v2/metadata/metadataflow/MT/MDF1/1.0?detail=allStubs

Get all reports against Metadataflow for a specific Metadata Provider
https://example.registry.com/sdmx/v2/metadata/metadataflow/MT/MDF1/1.0/PROVIDER1

Get all reports against any BIS Codelist
https://example.registry.com/sdmx/v2/metadata/strucure/codelist/BIS

== POST Reference Metadata ==
{| class="wikitable"
|-
|style="background-color:#eaecf0"|Entry Point|| /sdmx/v2/metadata
|-
|style="background-color:#eaecf0"|Access|| Public
|-
|style="background-color:#eaecf0"|Http Method|| POST
|-
|style="background-color:#eaecf0"|Accepts|| [https://github.com/sdmx-twg/sdmx-json/tree/master/metadata-message SDMX-JSON]
|-
|style="background-color:#eaecf0"|Content-Type|| 1. multipart/form-data (if attaching file) – the attached file must be in field name of uploadFile
2. application/text or application/json (if submitting data in the body of the POST)
|-
|style="background-color:#eaecf0"|Response Format|| N/A
|-
|style="background-color:#eaecf0"|Response Statuses|| 200 - Success
401 - Unauthorized (if access has been restricted)
500 - Server Error
|}

Submit a Reference Metadata report, on success an HTTP 200 status is returned. On failure an SDMX Error message is returned with the HTTP status code set to the appropriate code.

Example Error:

<?xml version="1.0" ?>
<message:Error xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:message="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/message" xmlns:com="http://www.sdmx.org/resources/sdmxml/schemas/v2_1/common">
<message:ErrorMessage code="100">
<com:Text>Could not resolve reference from structure Metadata Set 'MT:EXAMPLE1.0.0)' to structure Metadata Provision Agreement 'MT:MUK1_EXAMPLE(1.0)'</com:Text>
</message:ErrorMessage>
</message:Error>

== DELETE Reference Metadata ==
{| class="wikitable"
|-
|style="background-color:#eaecf0"|Entry Point|| /sdmx/v2/metadata
|-
|style="background-color:#eaecf0"|Access|| Public
|-
|style="background-color:#eaecf0"|Http Method|| DELETE
|-
|style="background-color:#eaecf0"|Response Format||
|-
|style="background-color:#eaecf0"|Response Statuses|| 200 - Success
401 - Unauthorized (if access has been restricted)
500 - Server Error
|}

Deletes all reference metadata reports which match the query URL, the path parameters following the /metadata/ are used to create a filter on the Metadata Reports. The path uses the same syntax as the
[https://github.com/sdmx-twg/sdmx-rest/blob/master/doc/metadata.md GET parameters], it is therefore possible to delete by specific report identifiers, by metadataflow, or by structure query.

==Examples==
Delete specific report
https://example.registry.com/sdmx/v2/metadata/metadataset/MT/REPORT1/1.0

Delete all versions of report
https://example.registry.com/sdmx/v2/metadata/metadataset/MT/REPORT1/*

Delete all reports against Metadataflow
https://example.registry.com/sdmx/v2/metadata/metadataflow/MT/MDF1/1.0

Delete all reports against Metadataflow for a specific Metadata Provider
https://example.registry.com/sdmx/v2/metadata/metadataflow/MT/MDF1/1.0/PROVIDER1

Delete all reports against all BIS Codelists
https://example.registry.com/sdmx/v2/metadata/strucure/codelist/BIS