https://wiki.sdmxcloud.org/api.php?action=feedcontributions&feedformat=atom&user=Grahamcassidy Fusion Registry Wiki - User contributions [en] 2026-05-27T20:58:02Z User contributions MediaWiki 1.32.0 https://wiki.sdmxcloud.org/index.php?title=Troubleshooting_-_FXLData&diff=7690 Troubleshooting - FXLData 2025-07-30T13:22:13Z <p>Grahamcassidy: </p> <hr /> <div>==The FXLData addin disappears or fails to load==<br /> 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<br /> <br /> This article from microsoft also explains the process to unblock a macro: <br /> <br /> https://support.microsoft.com/en-us/topic/a-potentially-dangerous-macro-has-been-blocked-0952faa0-37e7-4316-b61d-5b5ed6024216<br /> <br /> == Unable to Retrieve Dataflows ==<br /> If you are using version 11, R.1.0.00 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 : <br /> <br /> ce-val.murrell@regnology.net</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=FXLDataV()&diff=7689 FXLDataV() 2025-07-30T13:19:22Z <p>Grahamcassidy: </p> <hr /> <div>[[Category:Functions Reference FXLData]] <br /> By default ƒ&lt;sub&gt;''x''&lt;/sub&gt; =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. <br /> <br /> '''Use Case'''&lt;br&gt;<br /> 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:<br /> * The 'base URL' of the Fusion Registry to connect to<br /> * The ID of the Dataflow<br /> * A series key specifying which series to retrieve, or simply 'all' to retrieve all series<br /> =FXLDataV(&quot;https://demo.metadatatechnology.com/FusionRegistry&quot;,&quot;WB,WDI_POVERTY&quot;,&quot;A&quot;)<br /> By default, the number of series returned is capped at 100, but can be altered using the ''MaxSeries'' argument<br /> <br /> '''Syntax'''&lt;br&gt;<br /> &lt;small&gt;=&lt;strong&gt;FXLDataV&lt;/strong&gt;( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName] '' )&lt;/small&gt;<br /> <br /> {| class=&quot;wikitable&quot; style=&quot;width:70%;&quot;<br /> |-<br /> ! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?<br /> |-<br /> | 1||RegUrl|| The base URL of the Fusion Registry service || &lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt; || style=&quot;background-color:pink;&quot; |Mandatory<br /> |-<br /> | 2||Dataflow || Options:&lt;br&gt;1. The dataflow to query conforming to the SDMX REST specification for data queries&lt;br&gt;2. A blank string - in this case the Dataflow(s) to query must be specified as part of the Expression. || WB&lt;br&gt;WB,WDI_POVERTY,1.0 || style=&quot;background-color:pink;&quot; | Mandatory<br /> |-<br /> | 3||Expression || Options:&lt;br&gt;1. A [[Series Key|series key]] &lt;br&gt;2. A dataflow and series key expression &lt;dataflow&gt;/&lt;series key&gt;&lt;br&gt;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&lt;br&gt;4. The keyword 'all' to select all series in the dataset|| A&lt;br&gt;A.CAD+HUF+INR...E&lt;br&gt;all&lt;br&gt;ECB,EXR,1.0/A.HUF...E&lt;br&gt;C4 (cell reference)||style=&quot;background-color:pink;&quot; | Mandatory<br /> |-<br /> | 4||MaxSeries || The maximum number of series to return || 1000 || Optional&lt;br&gt;Defaults to 100 series<br /> |-<br /> | 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&amp;calculatemeasure=THE_MEASURE;pch;mvavg,3&amp;dimensionAtObservation=CURRENCY_DENOM || Optional<br /> |-<br /> | 6||StartPeriod || The start period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12&lt;br&gt;2010 || Optional&lt;br&gt;Defaults to the start of the available data<br /> |-<br /> | 7||EndPeriod || The end period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12&lt;br&gt;2010 || Optional&lt;br&gt;Defaults to the end of the available data<br /> |-<br /> | 8||Labels || Component labels&lt;br&gt;&lt;code&gt;id&lt;/code&gt; - IDs only&lt;br&gt;&lt;code&gt;both&lt;/code&gt; - both IDs and names || both || Optional&lt;br&gt;Defaults to &lt;code&gt;id&lt;/code&gt;<br /> |-<br /> | 9||IncludeHeader || Boolean&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; shows resultset metrics (e.g. 108/1256 series 2.15 seconds)&lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; metrics are omitted|| FALSE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |-<br /> | 10||IncludeSeriesKey || Ignored|| || Optional&lt;br&gt;Ignored&lt;/code&gt;<br /> |-<br /> | 11||IncludeBreakdown || Ignored || || Optional&lt;br&gt;Ignored&lt;/code&gt;<br /> |-<br /> | 12||IncludeAttributes || Boolean&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; includes series attributes if labels=both&lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; excludes attributes|| FALSE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |-<br /> | 13||TimeFormatFrequencyId || Text&lt;br&gt;If the optional TimeFormatFrequencyId argument is set to &lt;code&gt;'D'&lt;/code&gt;, the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily|| D || Optional&lt;br&gt;Defaults to the underlying time format if not used&lt;/code&gt;<br /> |-<br /> | 14||DataflowName|| DataflowName is an optional setting that isn’t intended to be changed by the user. When present, it helps improve the user experience on the wizard's edit screen|| n/a || Optional<br /> |}</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=FXLDataUrlV()&diff=7688 FXLDataUrlV() 2025-07-30T13:15:02Z <p>Grahamcassidy: </p> <hr /> <div>[[Category:Functions Reference FXLData]] <br /> ƒ&lt;sub&gt;''x''&lt;/sub&gt; =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. <br /> <br /> '''Use Case'''&lt;br&gt;<br /> [[File:Fdb query syntax.PNG|thumb|Fusion Data Browser Query Syntax function]]<br /> 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:<br /> * The Fusion Registry [https://demo.metadatatechnology.com/FusionRegistry/webservice/data.html REST Web Service query builder]<br /> * The Fusion Data Browser Export Data ''Query Syntax'' function<br /> <br /> '''Syntax'''&lt;br&gt;<br /> &lt;small&gt;=&lt;strong&gt;FXLDataUrlV&lt;/strong&gt;( ''Url , [MaxSeries], [TimeFormatFrequencyId] '' )&lt;/small&gt;<br /> <br /> {| class=&quot;wikitable&quot; style=&quot;width:70%;&quot;<br /> |-<br /> ! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?<br /> |-<br /> | 1||Url|| A complete and fully-formed SDMX data query URL || &lt;nowiki&gt;=FXLDataUrlV(&quot;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&quot;,20)<br /> &lt;/nowiki&gt; || style=&quot;background-color:pink;&quot; |Mandatory<br /> |-<br /> | 2||MaxSeries || The maximum number of series to return || 20 || Optional&lt;br&gt;Defaults to 100 series<br /> |-<br /> | 3||TimeFormatFrequencyId || Text If the optional TimeFormatFrequencyId argument is set to 'D', the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily || D|| Optional Defaults to the underlying time format if not used<br /> |}</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=FXLData()&diff=7687 FXLData() 2025-07-30T13:09:04Z <p>Grahamcassidy: </p> <hr /> <div>[[Category:Functions Reference FXLData]] <br /> By default ƒ&lt;sub&gt;''x''&lt;/sub&gt; =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]]. <br /> <br /> '''Use Case'''&lt;br&gt;<br /> This parameterised form is suitable for hard 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:<br /> * The 'base URL' of the Fusion Registry to connect to<br /> * The ID of the Dataflow<br /> * A series key specifying which series to retrieve, or simply 'all' to retrieve all series<br /> =fxldata(&quot;https://demo.metadatatechnology.com/FusionRegistry&quot;,&quot;WB,WDI_POVERTY&quot;,&quot;A&quot;)<br /> By default, the number of series returned is capped at 100, but can be altered using the ''MaxSeries'' argument<br /> <br /> '''Syntax'''&lt;br&gt;<br /> &lt;small&gt;=&lt;strong&gt;FXLData&lt;/strong&gt;( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/small&gt;<br /> <br /> {| class=&quot;wikitable&quot; style=&quot;width:70%;&quot;<br /> |-<br /> ! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?<br /> |-<br /> | 1||RegUrl|| The base URL of the Fusion Registry service || &lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt; || style=&quot;background-color:pink;&quot; |Mandatory<br /> |-<br /> | 2||Dataflow || Options:&lt;br&gt;1. The dataflow to query conforming to the SDMX REST specification for data queries&lt;br&gt;2. A blank string - in this case the Dataflow(s) to query must be specified as part of the Expression. || EXR&lt;br&gt;ECB,EXR,1.0 || style=&quot;background-color:pink;&quot; | Mandatory<br /> |-<br /> | 3||Expression || Options:&lt;br&gt;1. A [[Series Key|series key]] &lt;br&gt;2. A dataflow and series key expression &lt;dataflow&gt;/&lt;series key&gt;&lt;br&gt;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&lt;br&gt;4. The keyword 'all' to select all series in the dataset|| A&lt;br&gt;A.CAD+HUF+INR...E&lt;br&gt;all&lt;br&gt;ECB,EXR,1.0/A.HUF...E&lt;br&gt;C4 (cell reference)||style=&quot;background-color:pink;&quot; | Mandatory<br /> |-<br /> | 4||MaxSeries || The maximum number of series to return || 1000 || Optional&lt;br&gt;Defaults to 100 series<br /> |-<br /> | 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&amp;calculatemeasure=THE_MEASURE;pch;mvavg,3&amp;dimensionAtObservation=CURRENCY_DENOM || Optional<br /> |-<br /> | 6||StartPeriod || The start period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12&lt;br&gt;2010 || Optional&lt;br&gt;Defaults to the start of the available data<br /> |-<br /> | 7||EndPeriod || The end period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12&lt;br&gt;2010 || Optional&lt;br&gt;Defaults to the end of the available data<br /> |-<br /> | 8||Labels || Component labels&lt;br&gt;&lt;code&gt;id&lt;/code&gt; - IDs only&lt;br&gt;&lt;code&gt;name&lt;/code&gt; - names only&lt;br&gt;&lt;code&gt;both&lt;/code&gt; - both IDs and names || both || Optional&lt;br&gt;Defaults to &lt;code&gt;id&lt;/code&gt;<br /> |-<br /> | 9||IncludeHeader || Boolean&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; includes component labels at the head of each column and shows metrics&lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; component labels and metrics are ommitted|| FALSE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |-<br /> | 10||IncludeSeriesKey || Boolean&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; adds a calculated dimension 'series' with the series key as the value&lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; series keys are not included in the dataset || FALSE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |-<br /> | 11||IncludeBreakdown || Boolean&lt;br&gt;Controls whether a breakdown of the dataset's components is shown.&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; includes dimensions. Attributes are also shown if the IncludeAttributes argument is TRUE. &lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; no components are included || TRUE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |-<br /> | 12||IncludeAttributes || Boolean&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; includes series attributes in the component breakdown&lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; excludes attributes|| FALSE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |-<br /> | 13||TimeFormatFrequencyId || Text&lt;br&gt;If the optional TimeFormatFrequencyId argument is set to &lt;code&gt;'D'&lt;/code&gt;, the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily|| D || Optional&lt;br&gt;Defaults to the underlying time format if not used&lt;/code&gt;<br /> |-<br /> | 14||DataflowName|| DataflowName is an optional setting that isn’t intended to be changed by the user. When present, it helps improve the user experience on the wizard's edit screen|| n/a || Optional<br /> |}<br /> <br /> '''''Example:'''''<br /> =fxldata(&quot;https://demo11.metadatatechnology.com/FusionRegistry&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;..AUS+CHN+FJI&quot;,200,,,,&quot;id&quot;,TRUE,FALSE,TRUE,FALSE)</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=FXLDataUrl()&diff=7686 FXLDataUrl() 2025-07-30T13:08:05Z <p>Grahamcassidy: </p> <hr /> <div>[[Category:Functions Reference FXLData]] <br /> By default ƒ&lt;sub&gt;''x''&lt;/sub&gt; =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]]. <br /> <br /> '''Use Case'''&lt;br&gt;<br /> [[File:Fdb query syntax.PNG|thumb|Fusion Data Browser Query Syntax function]]<br /> 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:<br /> * The Fusion Registry [https://demo.metadatatechnology.com/FusionRegistry/webservice/data.html REST Web Service query builder]<br /> * The Fusion Data Browser Export Data ''Query Syntax'' function<br /> <br /> '''Syntax'''&lt;br&gt;<br /> &lt;small&gt;=&lt;strong&gt;FXLDataUrl&lt;/strong&gt;( ''Url , [MaxSeries], [TimeFormatFrequencyId] '' )&lt;/small&gt;<br /> <br /> {| class=&quot;wikitable&quot; style=&quot;width:70%;&quot;<br /> |-<br /> ! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?<br /> |-<br /> | 1||Url|| A complete and fully-formed SDMX data query URL || &lt;nowiki&gt;=FXLDataUrl(&quot;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/CD2030,CONSOLIDATED,1.0/all?format=sdmx-compact-2.1&quot;,50)&lt;/nowiki&gt; || style=&quot;background-color:pink;&quot; |Mandatory<br /> |-<br /> | 2||MaxSeries || The maximum number of series to return || 1000 || Optional&lt;br&gt;Defaults to 100 series<br /> |-<br /> | 3||TimeFormatFrequencyId || Text If the optional TimeFormatFrequencyId argument is set to 'D', the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily || D|| Optional Defaults to the underlying time format if not used<br /> |}</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=FXLDataUrlFact()&diff=7685 FXLDataUrlFact() 2025-07-30T13:07:53Z <p>Grahamcassidy: </p> <hr /> <div>[[Category:Functions Reference FXLData]] <br /> ƒ&lt;sub&gt;''x''&lt;/sub&gt; =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. <br /> <br /> '''Use Case'''&lt;br&gt;<br /> 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.<br /> <br /> '''Syntax'''&lt;br&gt;<br /> &lt;small&gt;=&lt;strong&gt;FXLDataUrlFact&lt;/strong&gt;( ''Url , [MaxSeries], [TimeFormatFrequencyId] '' )&lt;/small&gt;<br /> <br /> {| class=&quot;wikitable&quot; style=&quot;width:70%;&quot;<br /> |-<br /> ! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?<br /> |-<br /> | 1||Url|| A complete and fully-formed SDMX data query URL || &lt;nowiki&gt;=FXLDataUrlFact(&quot;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&quot;,100)&lt;/nowiki&gt; || style=&quot;background-color:pink;&quot; |Mandatory<br /> |-<br /> | 2||MaxSeries || The maximum number of series to return || 1000 || Optional&lt;br&gt;Defaults to 100 series<br /> |-<br /> | 3||TimeFormatFrequencyId || Text If the optional TimeFormatFrequencyId argument is set to 'D', the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily || D|| Optional Defaults to the underlying time format if not used<br /> |}</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=FXLDataUrlFact()&diff=7684 FXLDataUrlFact() 2025-07-30T13:07:31Z <p>Grahamcassidy: </p> <hr /> <div>[[Category:Functions Reference FXLData]] <br /> ƒ&lt;sub&gt;''x''&lt;/sub&gt; =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. <br /> <br /> '''Use Case'''&lt;br&gt;<br /> 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.<br /> <br /> '''Syntax'''&lt;br&gt;<br /> &lt;small&gt;=&lt;strong&gt;FXLDataUrlFact&lt;/strong&gt;( ''Url , [MaxSeries], [TimeFormatFrequencyId] '' )&lt;/small&gt;<br /> <br /> {| class=&quot;wikitable&quot; style=&quot;width:70%;&quot;<br /> |-<br /> ! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?<br /> |-<br /> | 1||Url|| A complete and fully-formed SDMX data query URL || &lt;nowiki&gt;=FXLDataUrlFact(&quot;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&quot;,100)&lt;/nowiki&gt; || style=&quot;background-color:pink;&quot; |Mandatory<br /> |-<br /> | 2||MaxSeries || The maximum number of series to return || 1000 || Optional&lt;br&gt;Defaults to 100 series<br /> |-<br /> | 3||TimeFormatFrequencyId || Text If the optional TimeFormatFrequencyId argument is set to 'D', the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily || D|| Optional Defaults to the underlying time format if not used.<br /> |}</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=FXLDataUrl()&diff=7683 FXLDataUrl() 2025-07-30T12:53:39Z <p>Grahamcassidy: </p> <hr /> <div>[[Category:Functions Reference FXLData]] <br /> By default ƒ&lt;sub&gt;''x''&lt;/sub&gt; =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]]. <br /> <br /> '''Use Case'''&lt;br&gt;<br /> [[File:Fdb query syntax.PNG|thumb|Fusion Data Browser Query Syntax function]]<br /> 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:<br /> * The Fusion Registry [https://demo.metadatatechnology.com/FusionRegistry/webservice/data.html REST Web Service query builder]<br /> * The Fusion Data Browser Export Data ''Query Syntax'' function<br /> <br /> '''Syntax'''&lt;br&gt;<br /> &lt;small&gt;=&lt;strong&gt;FXLDataUrl&lt;/strong&gt;( ''Url , [MaxSeries], [TimeFormatFrequencyId] '' )&lt;/small&gt;<br /> <br /> {| class=&quot;wikitable&quot; style=&quot;width:70%;&quot;<br /> |-<br /> ! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?<br /> |-<br /> | 1||Url|| A complete and fully-formed SDMX data query URL || &lt;nowiki&gt;=FXLDataUrl(&quot;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/CD2030,CONSOLIDATED,1.0/all?format=sdmx-compact-2.1&quot;,50)&lt;/nowiki&gt; || style=&quot;background-color:pink;&quot; |Mandatory<br /> |-<br /> | 2||MaxSeries || The maximum number of series to return || 1000 || Optional&lt;br&gt;Defaults to 100 series<br /> |-<br /> | 3||TimeFormatFrequencyId || Text If the optional TimeFormatFrequencyId argument is set to 'D', the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily || D|| Optional Defaults to the underlying time format if not used.<br /> |}</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=FXLData()&diff=7682 FXLData() 2025-07-30T12:33:03Z <p>Grahamcassidy: </p> <hr /> <div>[[Category:Functions Reference FXLData]] <br /> By default ƒ&lt;sub&gt;''x''&lt;/sub&gt; =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]]. <br /> <br /> '''Use Case'''&lt;br&gt;<br /> This parameterised form is suitable for hard 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:<br /> * The 'base URL' of the Fusion Registry to connect to<br /> * The ID of the Dataflow<br /> * A series key specifying which series to retrieve, or simply 'all' to retrieve all series<br /> =fxldata(&quot;https://demo.metadatatechnology.com/FusionRegistry&quot;,&quot;WB,WDI_POVERTY&quot;,&quot;A&quot;)<br /> By default, the number of series returned is capped at 100, but can be altered using the ''MaxSeries'' argument<br /> <br /> '''Syntax'''&lt;br&gt;<br /> &lt;small&gt;=&lt;strong&gt;FXLData&lt;/strong&gt;( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/small&gt;<br /> <br /> {| class=&quot;wikitable&quot; style=&quot;width:70%;&quot;<br /> |-<br /> ! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?<br /> |-<br /> | 1||RegUrl|| The base URL of the Fusion Registry service || &lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt; || style=&quot;background-color:pink;&quot; |Mandatory<br /> |-<br /> | 2||Dataflow || Options:&lt;br&gt;1. The dataflow to query conforming to the SDMX REST specification for data queries&lt;br&gt;2. A blank string - in this case the Dataflow(s) to query must be specified as part of the Expression. || EXR&lt;br&gt;ECB,EXR,1.0 || style=&quot;background-color:pink;&quot; | Mandatory<br /> |-<br /> | 3||Expression || Options:&lt;br&gt;1. A [[Series Key|series key]] &lt;br&gt;2. A dataflow and series key expression &lt;dataflow&gt;/&lt;series key&gt;&lt;br&gt;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&lt;br&gt;4. The keyword 'all' to select all series in the dataset|| A&lt;br&gt;A.CAD+HUF+INR...E&lt;br&gt;all&lt;br&gt;ECB,EXR,1.0/A.HUF...E&lt;br&gt;C4 (cell reference)||style=&quot;background-color:pink;&quot; | Mandatory<br /> |-<br /> | 4||MaxSeries || The maximum number of series to return || 1000 || Optional&lt;br&gt;Defaults to 100 series<br /> |-<br /> | 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&amp;calculatemeasure=THE_MEASURE;pch;mvavg,3&amp;dimensionAtObservation=CURRENCY_DENOM || Optional<br /> |-<br /> | 6||StartPeriod || The start period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12&lt;br&gt;2010 || Optional&lt;br&gt;Defaults to the start of the available data<br /> |-<br /> | 7||EndPeriod || The end period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12&lt;br&gt;2010 || Optional&lt;br&gt;Defaults to the end of the available data<br /> |-<br /> | 8||Labels || Component labels&lt;br&gt;&lt;code&gt;id&lt;/code&gt; - IDs only&lt;br&gt;&lt;code&gt;name&lt;/code&gt; - names only&lt;br&gt;&lt;code&gt;both&lt;/code&gt; - both IDs and names || both || Optional&lt;br&gt;Defaults to &lt;code&gt;id&lt;/code&gt;<br /> |-<br /> | 9||IncludeHeader || Boolean&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; includes component labels at the head of each column and shows metrics&lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; component labels and metrics are ommitted|| FALSE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |-<br /> | 10||IncludeSeriesKey || Boolean&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; adds a calculated dimension 'series' with the series key as the value&lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; series keys are not included in the dataset || FALSE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |-<br /> | 11||IncludeBreakdown || Boolean&lt;br&gt;Controls whether a breakdown of the dataset's components is shown.&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; includes dimensions. Attributes are also shown if the IncludeAttributes argument is TRUE. &lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; no components are included || TRUE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |-<br /> | 12||IncludeAttributes || Boolean&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; includes series attributes in the component breakdown&lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; excludes attributes|| FALSE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |-<br /> | 13||TimeFormatFrequencyId || Text&lt;br&gt;If the optional TimeFormatFrequencyId argument is set to &lt;code&gt;'D'&lt;/code&gt;, the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily|| D || Optional&lt;br&gt;Defaults to the underlying time format if not used.&lt;/code&gt;<br /> |-<br /> | 14||DataflowName|| DataflowName is an optional setting that isn’t intended to be changed by the user. When present, it helps improve the user experience on the wizard's edit screen|| n/a || Optional<br /> |}<br /> <br /> '''''Example:'''''<br /> =fxldata(&quot;https://demo11.metadatatechnology.com/FusionRegistry&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;..AUS+CHN+FJI&quot;,200,,,,&quot;id&quot;,TRUE,FALSE,TRUE,FALSE)</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=FXLData()&diff=7681 FXLData() 2025-07-30T09:59:12Z <p>Grahamcassidy: </p> <hr /> <div>[[Category:Functions Reference FXLData]] <br /> By default ƒ&lt;sub&gt;''x''&lt;/sub&gt; =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]]. <br /> <br /> '''Use Case'''&lt;br&gt;<br /> This parameterised form is suitable for hard 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:<br /> * The 'base URL' of the Fusion Registry to connect to<br /> * The ID of the Dataflow<br /> * A series key specifying which series to retrieve, or simply 'all' to retrieve all series<br /> =fxldata(&quot;https://demo.metadatatechnology.com/FusionRegistry&quot;,&quot;WB,WDI_POVERTY&quot;,&quot;A&quot;)<br /> By default, the number of series returned is capped at 100, but can be altered using the ''MaxSeries'' argument<br /> <br /> '''Syntax'''&lt;br&gt;<br /> &lt;small&gt;=&lt;strong&gt;FXLData&lt;/strong&gt;( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/small&gt;<br /> <br /> {| class=&quot;wikitable&quot; style=&quot;width:70%;&quot;<br /> |-<br /> ! !!style=max-width:4em | Argument!! style=max-width:4em | Description !! style=max-width:4em | Examples !! style=max-width:4em | Required?<br /> |-<br /> | 1||RegUrl|| The base URL of the Fusion Registry service || &lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt; || style=&quot;background-color:pink;&quot; |Mandatory<br /> |-<br /> | 2||Dataflow || Options:&lt;br&gt;1. The dataflow to query conforming to the SDMX REST specification for data queries&lt;br&gt;2. A blank string - in this case the Dataflow(s) to query must be specified as part of the Expression. || EXR&lt;br&gt;ECB,EXR,1.0 || style=&quot;background-color:pink;&quot; | Mandatory<br /> |-<br /> | 3||Expression || Options:&lt;br&gt;1. A [[Series Key|series key]] &lt;br&gt;2. A dataflow and series key expression &lt;dataflow&gt;/&lt;series key&gt;&lt;br&gt;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&lt;br&gt;4. The keyword 'all' to select all series in the dataset|| A&lt;br&gt;A.CAD+HUF+INR...E&lt;br&gt;all&lt;br&gt;ECB,EXR,1.0/A.HUF...E&lt;br&gt;C4 (cell reference)||style=&quot;background-color:pink;&quot; | Mandatory<br /> |-<br /> | 4||MaxSeries || The maximum number of series to return || 1000 || Optional&lt;br&gt;Defaults to 100 series<br /> |-<br /> | 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&amp;calculatemeasure=THE_MEASURE;pch;mvavg,3&amp;dimensionAtObservation=CURRENCY_DENOM || Optional<br /> |-<br /> | 6||StartPeriod || The start period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12&lt;br&gt;2010 || Optional&lt;br&gt;Defaults to the start of the available data<br /> |-<br /> | 7||EndPeriod || The end period in [[SDMX Time Formats|SDMX standard time format]] || 2010-06-12&lt;br&gt;2010 || Optional&lt;br&gt;Defaults to the end of the available data<br /> |-<br /> | 8||Labels || Component labels&lt;br&gt;&lt;code&gt;id&lt;/code&gt; - IDs only&lt;br&gt;&lt;code&gt;name&lt;/code&gt; - names only&lt;br&gt;&lt;code&gt;both&lt;/code&gt; - both IDs and names || both || Optional&lt;br&gt;Defaults to &lt;code&gt;id&lt;/code&gt;<br /> |-<br /> | 9||IncludeHeader || Boolean&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; includes component labels at the head of each column and shows metrics&lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; component labels and metrics are ommitted|| FALSE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |-<br /> | 10||IncludeSeriesKey || Boolean&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; adds a calculated dimension 'series' with the series key as the value&lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; series keys are not included in the dataset || FALSE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |-<br /> | 11||IncludeBreakdown || Boolean&lt;br&gt;Controls whether a breakdown of the dataset's components is shown.&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; includes dimensions. Attributes are also shown if the IncludeAttributes argument is TRUE. &lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; no components are included || TRUE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |-<br /> | 12||IncludeAttributes || Boolean&lt;br&gt;&lt;code&gt;TRUE&lt;/code&gt; includes series attributes in the component breakdown&lt;br&gt;&lt;code&gt;FALSE&lt;/code&gt; excludes attributes|| FALSE || Optional&lt;br&gt;Defaults to &lt;code&gt;TRUE&lt;/code&gt;<br /> |}<br /> <br /> '''''Example:'''''<br /> =fxldata(&quot;https://demo11.metadatatechnology.com/FusionRegistry&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;..AUS+CHN+FJI&quot;,200,,,,&quot;id&quot;,TRUE,FALSE,TRUE,FALSE)</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=FXLData_Refreshable_Worksheet&diff=7680 FXLData Refreshable Worksheet 2025-07-29T14:55:05Z <p>Grahamcassidy: </p> <hr /> <div>[[Category:SdmxDataFormat]]<br /> [[Category:How to and other features in FXLData]]<br /> =Overview=<br /> [[File:Refreshable worksheet.png|thumb|Refreshable Excel Worksheet]]<br /> The FXLData Refreshable Worksheet is an Excel file which, when opened, loads the data from the web service. The data is not saved in the worksheet, instead it saves the data query and executes it on demand. This enables the data to be dynamically refreshed on request.<br /> <br /> This data format requires the [[FXLData]] plugin for Microsoft Excel to be installed.<br /> <br /> To export a dataset in this format, simply create an [[Data_Query_Web_Service|SDMX Data Query]] and add the Query Parameer: <br /> <br /> format=excel-refresh<br /> <br /> Example: https://demo.metadatatechnology.com/FusionRegistry/sdmx/v2/data/dataflow/BIS/WS_SPP/1.0/?c%5BREF_AREA%5D=5R&amp;format=excel-refresh&amp;labels=both<br /> <br /> The underlying format passed from server to client is [[Fusion-CSV-TS]], and as such any parameters available to [[Fusion-CSV-TS]] are available in this format.<br /> <br /> As of FXLData R1.3, refreshable workbooks no longer render automatically upon opening. This change is part of a performance optimization that deliberately inhibits FXLData functions from executing on initial load. To view data, users must now manually initiate a refresh sequence by first clicking Refresh, followed by Refresh Workbook. This ensures that large or complex queries do not slow down workbook opening, while still allowing users to retrieve up-to-date data on demand.</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=Overview_FXLData&diff=7679 Overview FXLData 2025-07-28T14:17:57Z <p>Grahamcassidy: </p> <hr /> <div>__NOTOC__<br /> FXLData is an Excel add-in that enables users to retrieve statistical data from a Fusion Registry directly into their worksheets using custom formulas.<br /> <br /> Although these formulas resemble Excel’s built-in formulas , they are handled differently behind the scenes. When a user enters an FXLData formula, the plugin calls the Fusion Registry API, processes the response, and populates the worksheet with a structured grid of series and observation values. This design promotes performance and gives the plugin more control over how data is displayed, while still offering a familiar formula-based interface.<br /> <br /> [[File:Fxldata animation.gif]]<br /> <br /> <br /> ==Parameterised functions ==<br /> 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.<br /> [[File:Fxldata example1.PNG|thumb|Horizontal time-series example]]<br /> Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLData'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/span&gt; &lt;br/&gt;<br /> Returns a selected dataset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * A further eleven optional arguments can be specified including: maximum number of series to retrieve, start / end period, labels, headers and whether dimensions and attributes are shown<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataV'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] - ignored, [IncludeBreakdown] - ignored, [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/span&gt; &lt;br&gt;<br /> Returns a selected dataset in 'vertical' format with one series per column<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * A further eleven optional arguments can be specified including: maximum number of series to retrieve, start / end period, and whether dimensions, attributes and metrics are shown<br /> * IncludeSeriesKey is ignored<br /> * IncludeBreakdown is ignored<br /> * Set Labels=both to show IDs and dimensions / attributes<br /> * If Labels=both, setting IncludeAttributes=TRUE will show the breakdown of series / dataset attributes in the header<br /> * IncludeHeader controls whether the resultset metrics are shown at the top (e.g. 205/1276 series 1.78 seconds)<br /> * If TimeFormatFrequencyId is set to 'D', the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily<br /> * DataflowName is an optional setting that isn’t intended to be changed by the user. When present, it helps improve the user experience on the wizard's edit screen.<br /> <br /> '''''Examples'''''<br /> =FXLData(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;)<br /> =FXLDataV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;,1000,&quot;round=2&quot;,2010,2020,&quot;both&quot;,true,true,true,false)<br /> =FXLData(C14,C4,C7,C15,C13,C5,C6,C12,C9,C8,C10,C11)<br /> =FXLDataV(A9,B24,B25,1000,,min(D55,2010),max(D56,2020))<br /> Like all Excel functions, arguments can either be literal values, cell references, or other formulas.<br /> <br /> ==Functions taking a Fusion Registry data query URL==<br /> 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.<br /> <br /> Both the horizontal and 'V' vertical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrl'''( ''Url , [MaxSeries] , [TimeFormatFrequencyId] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Behaves in the same way as =FXLData() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * The optional MaxSeries argument allows the maximum number of series to be set<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlV'''( ''Url , [MaxSeries] , [TimeFormatFrequencyId] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'vertical' format with one series per column&lt;br/&gt;<br /> * Behaves in the same way as =FXLDataV() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * The optional MaxSeries argument allows the maximum number of series to be set<br /> * If the optional TimeFormatFrequencyId argument is set to 'D', the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlFact'''( ''Url , [MaxSeries] , [TimeFormatFrequencyId] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset as a [https://en.wikipedia.org/wiki/Fact_table data warehouse style fact table]&lt;br/&gt;<br /> * Takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * The optional MaxSeries argument allows the maximum number of series to be set<br /> * Returns one row per observation including all series and observation attributes<br /> * If the optional TimeFormatFrequencyId argument is set to 'D', the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily<br /> * Following the traditional fact table model, only dimension and attributes codes are returned - there is not option to return code labels<br /> <br /> '''''Examples'''''<br /> =FXLDataUrl(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;)<br /> =FXLDataUrlV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;,1000)<br /> =FXLDataUrlFact(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/.CAD+MXN+MYR...&lt;/nowiki&gt;&quot;,20)<br /> <br /> ==Administrative functions==<br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion()'''&lt;/span&gt;<br /> * Returns the software version number and build date<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion(&quot;changelog&quot;)'''&lt;/span&gt;<br /> <br /> Returns the latest version of the change log<br /> <br /> ==Data Wizard==<br /> In addition to the Parameterised functions a Data Wizard, is available as an Excel Ribbon Tool for interactively building and editing FXLData functions and comes as part of the add-in.<br /> <br /> [[File:Wizard Version R1.0.png|Wizard - July 2004|600px]]<br /> <br /> <br /> [[FXLData_Wizard |Read more about the Data Wizard here. ]]</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=Overview_FXLData&diff=7678 Overview FXLData 2025-07-28T14:14:49Z <p>Grahamcassidy: /* Functions taking a Fusion Registry data query URL */</p> <hr /> <div>__NOTOC__<br /> FXLData is an Excel add-in that enables users to retrieve statistical data from a Fusion Registry directly into their worksheets using custom formulas.<br /> <br /> Although these formulas resemble Excel’s built-in formulas , they are handled differently behind the scenes. When a user enters an FXLData formula, the plugin calls the Fusion Registry API, processes the response, and populates the worksheet with a structured grid of series and observation values. This design promotes performance and gives the plugin more control over how data is displayed, while still offering a familiar formula-based interface.<br /> <br /> [[File:Fxldata animation.gif]]<br /> <br /> <br /> ==Parameterised functions ==<br /> 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.<br /> [[File:Fxldata example1.PNG|thumb|Horizontal time-series example]]<br /> Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLData'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/span&gt; &lt;br/&gt;<br /> Returns a selected dataset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * A further eleven optional arguments can be specified including: maximum number of series to retrieve, start / end period, labels, headers and whether dimensions and attributes are shown<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataV'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] - ignored, [IncludeBreakdown] - ignored, [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/span&gt; &lt;br&gt;<br /> Returns a selected dataset in 'vertical' format with one series per column<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * A further eleven optional arguments can be specified including: maximum number of series to retrieve, start / end period, and whether dimensions, attributes and metrics are shown<br /> * IncludeSeriesKey is ignored<br /> * IncludeBreakdown is ignored<br /> * Set Labels=both to show IDs and dimensions / attributes<br /> * If Labels=both, setting IncludeAttributes=TRUE will show the breakdown of series / dataset attributes in the header<br /> * IncludeHeader controls whether the resultset metrics are shown at the top (e.g. 205/1276 series 1.78 seconds)<br /> * If TimeFormatFrequencyId is set to 'D', the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily<br /> * DataflowName is an optional setting that isn’t intended to be changed by the user. When present, it helps improve the user experience on the wizard's edit screen.<br /> <br /> '''''Examples'''''<br /> =FXLData(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;)<br /> =FXLDataV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;,1000,&quot;round=2&quot;,2010,2020,&quot;both&quot;,true,true,true,false)<br /> =FXLData(C14,C4,C7,C15,C13,C5,C6,C12,C9,C8,C10,C11)<br /> =FXLDataV(A9,B24,B25,1000,,min(D55,2010),max(D56,2020))<br /> Like all Excel functions, arguments can either be literal values, cell references, or other formulas.<br /> <br /> ==Functions taking a Fusion Registry data query URL==<br /> 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.<br /> <br /> Both the horizontal and 'V' vertical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrl'''( ''Url , [MaxSeries] , [TimeFormatFrequencyId] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Behaves in the same way as =FXLData() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlV'''( ''Url , [MaxSeries] , [TimeFormatFrequencyId] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'vertical' format with one series per column&lt;br/&gt;<br /> * Behaves in the same way as =FXLDataV() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlFact'''( ''Url , [MaxSeries] , [TimeFormatFrequencyId] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset as a [https://en.wikipedia.org/wiki/Fact_table data warehouse style fact table]&lt;br/&gt;<br /> * Takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> * Returns one row per observation including all series and observation attributes<br /> * Following the traditional fact table model, only dimension and attributes codes are returned - there is not option to return code labels<br /> <br /> '''''Examples'''''<br /> =FXLDataUrl(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;)<br /> =FXLDataUrlV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;,1000)<br /> =FXLDataUrlFact(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/.CAD+MXN+MYR...&lt;/nowiki&gt;&quot;,20)<br /> <br /> ==Administrative functions==<br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion()'''&lt;/span&gt;<br /> * Returns the software version number and build date<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion(&quot;changelog&quot;)'''&lt;/span&gt;<br /> <br /> Returns the latest version of the change log<br /> <br /> ==Data Wizard==<br /> In addition to the Parameterised functions a Data Wizard, is available as an Excel Ribbon Tool for interactively building and editing FXLData functions and comes as part of the add-in.<br /> <br /> [[File:Wizard Version R1.0.png|Wizard - July 2004|600px]]<br /> <br /> <br /> [[FXLData_Wizard |Read more about the Data Wizard here. ]]</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=Overview_FXLData&diff=7677 Overview FXLData 2025-07-28T14:10:15Z <p>Grahamcassidy: /* Parameterised functions */</p> <hr /> <div>__NOTOC__<br /> FXLData is an Excel add-in that enables users to retrieve statistical data from a Fusion Registry directly into their worksheets using custom formulas.<br /> <br /> Although these formulas resemble Excel’s built-in formulas , they are handled differently behind the scenes. When a user enters an FXLData formula, the plugin calls the Fusion Registry API, processes the response, and populates the worksheet with a structured grid of series and observation values. This design promotes performance and gives the plugin more control over how data is displayed, while still offering a familiar formula-based interface.<br /> <br /> [[File:Fxldata animation.gif]]<br /> <br /> <br /> ==Parameterised functions ==<br /> 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.<br /> [[File:Fxldata example1.PNG|thumb|Horizontal time-series example]]<br /> Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLData'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/span&gt; &lt;br/&gt;<br /> Returns a selected dataset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * A further eleven optional arguments can be specified including: maximum number of series to retrieve, start / end period, labels, headers and whether dimensions and attributes are shown<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataV'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] - ignored, [IncludeBreakdown] - ignored, [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/span&gt; &lt;br&gt;<br /> Returns a selected dataset in 'vertical' format with one series per column<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * A further eleven optional arguments can be specified including: maximum number of series to retrieve, start / end period, and whether dimensions, attributes and metrics are shown<br /> * IncludeSeriesKey is ignored<br /> * IncludeBreakdown is ignored<br /> * Set Labels=both to show IDs and dimensions / attributes<br /> * If Labels=both, setting IncludeAttributes=TRUE will show the breakdown of series / dataset attributes in the header<br /> * IncludeHeader controls whether the resultset metrics are shown at the top (e.g. 205/1276 series 1.78 seconds)<br /> * If TimeFormatFrequencyId is set to 'D', the plugin will convert TIME_PERIOD values to a daily format—provided the original frequency is less granular than daily<br /> * DataflowName is an optional setting that isn’t intended to be changed by the user. When present, it helps improve the user experience on the wizard's edit screen.<br /> <br /> '''''Examples'''''<br /> =FXLData(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;)<br /> =FXLDataV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;,1000,&quot;round=2&quot;,2010,2020,&quot;both&quot;,true,true,true,false)<br /> =FXLData(C14,C4,C7,C15,C13,C5,C6,C12,C9,C8,C10,C11)<br /> =FXLDataV(A9,B24,B25,1000,,min(D55,2010),max(D56,2020))<br /> Like all Excel functions, arguments can either be literal values, cell references, or other formulas.<br /> <br /> ==Functions taking a Fusion Registry data query URL==<br /> 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.<br /> <br /> Both the horizontal and 'V' vertical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrl'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Behaves in the same way as =FXLData() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlV'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'vertical' format with one series per column&lt;br/&gt;<br /> * Behaves in the same way as =FXLDataV() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlFact'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset as a [https://en.wikipedia.org/wiki/Fact_table data warehouse style fact table]&lt;br/&gt;<br /> * Takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> * Returns one row per observation including all series and observation attributes<br /> * Following the traditional fact table model, only dimension and attributes codes are returned - there is not option to return code labels<br /> <br /> '''''Examples'''''<br /> =FXLDataUrl(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;)<br /> =FXLDataUrlV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;,1000)<br /> =FXLDataUrlFact(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/.CAD+MXN+MYR...&lt;/nowiki&gt;&quot;,20)<br /> <br /> ==Administrative functions==<br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion()'''&lt;/span&gt;<br /> * Returns the software version number and build date<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion(&quot;changelog&quot;)'''&lt;/span&gt;<br /> <br /> Returns the latest version of the change log<br /> <br /> ==Data Wizard==<br /> In addition to the Parameterised functions a Data Wizard, is available as an Excel Ribbon Tool for interactively building and editing FXLData functions and comes as part of the add-in.<br /> <br /> [[File:Wizard Version R1.0.png|Wizard - July 2004|600px]]<br /> <br /> <br /> [[FXLData_Wizard |Read more about the Data Wizard here. ]]</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=Overview_FXLData&diff=7676 Overview FXLData 2025-07-28T14:03:03Z <p>Grahamcassidy: </p> <hr /> <div>__NOTOC__<br /> FXLData is an Excel add-in that enables users to retrieve statistical data from a Fusion Registry directly into their worksheets using custom formulas.<br /> <br /> Although these formulas resemble Excel’s built-in formulas , they are handled differently behind the scenes. When a user enters an FXLData formula, the plugin calls the Fusion Registry API, processes the response, and populates the worksheet with a structured grid of series and observation values. This design promotes performance and gives the plugin more control over how data is displayed, while still offering a familiar formula-based interface.<br /> <br /> [[File:Fxldata animation.gif]]<br /> <br /> <br /> ==Parameterised functions ==<br /> 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.<br /> [[File:Fxldata example1.PNG|thumb|Horizontal time-series example]]<br /> Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLData'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/span&gt; &lt;br/&gt;<br /> Returns a selected dataset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * A further eleven optional arguments can be specified including: maximum number of series to retrieve, start / end period, labels, headers and whether dimensions and attributes are shown<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataV'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] - ignored, [IncludeBreakdown] - ignored, [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/span&gt; &lt;br&gt;<br /> Returns a selected dataset in 'vertical' format with one series per column<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * A further eleven optional arguments can be specified including: maximum number of series to retrieve, start / end period, and whether dimensions, attributes and metrics are shown<br /> * IncludeSeriesKey is ignored<br /> * IncludeBreakdown is ignored<br /> * Set Labels=both to show IDs and dimensions / attributes<br /> * If Labels=both, setting IncludeAttributes=TRUE will show the breakdown of series / dataset attributes in the header<br /> * IncludeHeader controls whether the resultset metrics are shown at the top (e.g. 205/1276 series 1.78 seconds)<br /> <br /> '''''Examples'''''<br /> =FXLData(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;)<br /> =FXLDataV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;,1000,&quot;round=2&quot;,2010,2020,&quot;both&quot;,true,true,true,false)<br /> =FXLData(C14,C4,C7,C15,C13,C5,C6,C12,C9,C8,C10,C11)<br /> =FXLDataV(A9,B24,B25,1000,,min(D55,2010),max(D56,2020))<br /> Like all Excel functions, arguments can either be literal values, cell references, or other formulas.<br /> <br /> ==Functions taking a Fusion Registry data query URL==<br /> 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.<br /> <br /> Both the horizontal and 'V' vertical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrl'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Behaves in the same way as =FXLData() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlV'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'vertical' format with one series per column&lt;br/&gt;<br /> * Behaves in the same way as =FXLDataV() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlFact'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset as a [https://en.wikipedia.org/wiki/Fact_table data warehouse style fact table]&lt;br/&gt;<br /> * Takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> * Returns one row per observation including all series and observation attributes<br /> * Following the traditional fact table model, only dimension and attributes codes are returned - there is not option to return code labels<br /> <br /> '''''Examples'''''<br /> =FXLDataUrl(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;)<br /> =FXLDataUrlV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;,1000)<br /> =FXLDataUrlFact(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/.CAD+MXN+MYR...&lt;/nowiki&gt;&quot;,20)<br /> <br /> ==Administrative functions==<br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion()'''&lt;/span&gt;<br /> * Returns the software version number and build date<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion(&quot;changelog&quot;)'''&lt;/span&gt;<br /> <br /> Returns the latest version of the change log<br /> <br /> ==Data Wizard==<br /> In addition to the Parameterised functions a Data Wizard, is available as an Excel Ribbon Tool for interactively building and editing FXLData functions and comes as part of the add-in.<br /> <br /> [[File:Wizard Version R1.0.png|Wizard - July 2004|600px]]<br /> <br /> <br /> [[FXLData_Wizard |Read more about the Data Wizard here. ]]</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=Overview_FXLData&diff=7675 Overview FXLData 2025-07-28T14:02:40Z <p>Grahamcassidy: </p> <hr /> <div>__NOTOC__<br /> FXLData is an Excel add-in that enables users to retrieve statistical data from a Fusion Registry directly into their worksheets using custom formulas.<br /> <br /> Although these formulas resemble Excel’s built-in formulas , they are handled differently behind the scenes. When a user enters an FXLData formula, the plugin calls the Fusion Registry API, processes the response, and populates the worksheet with a structured grid of series and observation values. This design promotes performance and gives the plugin more control over how data is displayed, while still offering a familiar formula-based interface.<br /> <br /> [[File:Fxldata animation.gif]]<br /> <br /> <br /> ==Parameterised functions ==<br /> 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.<br /> [[File:Fxldata example1.PNG|thumb|Horizontal time-series example]]<br /> Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLData'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/span&gt; &lt;br/&gt;<br /> Returns a selected dataset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * A further eleven optional arguments can be specified including: maximum number of series to retrieve, start / end period, labels, headers and whether dimensions and attributes are shown<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataV'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] - ignored, [IncludeBreakdown] - ignored, [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/span&gt; &lt;br&gt;<br /> Returns a selected dataset in 'vertical' format with one series per column<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * 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<br /> * IncludeSeriesKey is ignored<br /> * IncludeBreakdown is ignored<br /> * Set Labels=both to show IDs and dimensions / attributes<br /> * If Labels=both, setting IncludeAttributes=TRUE will show the breakdown of series / dataset attributes in the header<br /> * IncludeHeader controls whether the resultset metrics are shown at the top (e.g. 205/1276 series 1.78 seconds)<br /> <br /> '''''Examples'''''<br /> =FXLData(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;)<br /> =FXLDataV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;,1000,&quot;round=2&quot;,2010,2020,&quot;both&quot;,true,true,true,false)<br /> =FXLData(C14,C4,C7,C15,C13,C5,C6,C12,C9,C8,C10,C11)<br /> =FXLDataV(A9,B24,B25,1000,,min(D55,2010),max(D56,2020))<br /> Like all Excel functions, arguments can either be literal values, cell references, or other formulas.<br /> <br /> ==Functions taking a Fusion Registry data query URL==<br /> 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.<br /> <br /> Both the horizontal and 'V' vertical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrl'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Behaves in the same way as =FXLData() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlV'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'vertical' format with one series per column&lt;br/&gt;<br /> * Behaves in the same way as =FXLDataV() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlFact'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset as a [https://en.wikipedia.org/wiki/Fact_table data warehouse style fact table]&lt;br/&gt;<br /> * Takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> * Returns one row per observation including all series and observation attributes<br /> * Following the traditional fact table model, only dimension and attributes codes are returned - there is not option to return code labels<br /> <br /> '''''Examples'''''<br /> =FXLDataUrl(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;)<br /> =FXLDataUrlV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;,1000)<br /> =FXLDataUrlFact(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/.CAD+MXN+MYR...&lt;/nowiki&gt;&quot;,20)<br /> <br /> ==Administrative functions==<br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion()'''&lt;/span&gt;<br /> * Returns the software version number and build date<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion(&quot;changelog&quot;)'''&lt;/span&gt;<br /> <br /> Returns the latest version of the change log<br /> <br /> ==Data Wizard==<br /> In addition to the Parameterised functions a Data Wizard, is available as an Excel Ribbon Tool for interactively building and editing FXLData functions and comes as part of the add-in.<br /> <br /> [[File:Wizard Version R1.0.png|Wizard - July 2004|600px]]<br /> <br /> <br /> [[FXLData_Wizard |Read more about the Data Wizard here. ]]</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=Overview_FXLData&diff=7674 Overview FXLData 2025-07-28T14:01:39Z <p>Grahamcassidy: </p> <hr /> <div>__NOTOC__<br /> FXLData is an Excel add-in that enables users to retrieve statistical data from a Fusion Registry directly into their worksheets using custom formulas.<br /> <br /> Although these formulas resemble Excel’s built-in formulas , they are handled differently behind the scenes. When a user enters an FXLData formula, the plugin calls the Fusion Registry API, processes the response, and populates the worksheet with a structured grid of series and observation values. This design promotes performance and gives the plugin more control over how data is displayed, while still offering a familiar formula-based interface.<br /> <br /> [[File:Fxldata animation.gif]]<br /> <br /> <br /> ==Parameterised functions ==<br /> 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.<br /> [[File:Fxldata example1.PNG|thumb|Horizontal time-series example]]<br /> Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLData'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/span&gt; &lt;br/&gt;<br /> Returns a selected dataset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * 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<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataV'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] - ignored, [IncludeBreakdown] - ignored, [IncludeAttributes], [TimeFormatFrequencyId], [DataflowName]'' )&lt;/span&gt; &lt;br&gt;<br /> Returns a selected dataset in 'vertical' format with one series per column<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * 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<br /> * IncludeSeriesKey is ignored<br /> * IncludeBreakdown is ignored<br /> * Set Labels=both to show IDs and dimensions / attributes<br /> * If Labels=both, setting IncludeAttributes=TRUE will show the breakdown of series / dataset attributes in the header<br /> * IncludeHeader controls whether the resultset metrics are shown at the top (e.g. 205/1276 series 1.78 seconds)<br /> <br /> '''''Examples'''''<br /> =FXLData(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;)<br /> =FXLDataV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;,1000,&quot;round=2&quot;,2010,2020,&quot;both&quot;,true,true,true,false)<br /> =FXLData(C14,C4,C7,C15,C13,C5,C6,C12,C9,C8,C10,C11)<br /> =FXLDataV(A9,B24,B25,1000,,min(D55,2010),max(D56,2020))<br /> Like all Excel functions, arguments can either be literal values, cell references, or other formulas.<br /> <br /> ==Functions taking a Fusion Registry data query URL==<br /> 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.<br /> <br /> Both the horizontal and 'V' vertical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrl'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Behaves in the same way as =FXLData() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlV'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'vertical' format with one series per column&lt;br/&gt;<br /> * Behaves in the same way as =FXLDataV() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlFact'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset as a [https://en.wikipedia.org/wiki/Fact_table data warehouse style fact table]&lt;br/&gt;<br /> * Takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> * Returns one row per observation including all series and observation attributes<br /> * Following the traditional fact table model, only dimension and attributes codes are returned - there is not option to return code labels<br /> <br /> '''''Examples'''''<br /> =FXLDataUrl(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;)<br /> =FXLDataUrlV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;,1000)<br /> =FXLDataUrlFact(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/.CAD+MXN+MYR...&lt;/nowiki&gt;&quot;,20)<br /> <br /> ==Administrative functions==<br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion()'''&lt;/span&gt;<br /> * Returns the software version number and build date<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion(&quot;changelog&quot;)'''&lt;/span&gt;<br /> <br /> Returns the latest version of the change log<br /> <br /> ==Data Wizard==<br /> In addition to the Parameterised functions a Data Wizard, is available as an Excel Ribbon Tool for interactively building and editing FXLData functions and comes as part of the add-in.<br /> <br /> [[File:Wizard Version R1.0.png|Wizard - July 2004|600px]]<br /> <br /> <br /> [[FXLData_Wizard |Read more about the Data Wizard here. ]]</div> Grahamcassidy https://wiki.sdmxcloud.org/index.php?title=Overview_FXLData&diff=7673 Overview FXLData 2025-07-28T13:58:17Z <p>Grahamcassidy: </p> <hr /> <div>__NOTOC__<br /> FXLData is an Excel add-in that enables users to retrieve statistical data from a Fusion Registry directly into their worksheets using custom formulas.<br /> <br /> Although these formulas resemble Excel’s built-in formulas , they are handled differently behind the scenes. When a user enters an FXLData formula, the plugin calls the Fusion Registry API, processes the response, and populates the worksheet with a structured grid of series and observation values. This design promotes performance and gives the plugin more control over how data is displayed, while still offering a familiar formula-based interface.<br /> <br /> [[File:Fxldata animation.gif]]<br /> <br /> <br /> ==Parameterised functions ==<br /> 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.<br /> [[File:Fxldata example1.PNG|thumb|Horizontal time-series example]]<br /> Both the horizontal and 'V' verical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLData'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] , [IncludeBreakdown] , [IncludeAttributes]'' )&lt;/span&gt; &lt;br/&gt;<br /> Returns a selected dataset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * 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<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataV'''( ''RegUrl , Dataflow , Expression , [MaxSeries] , [RestArguments] , [StartPeriod] , [EndPeriod] , [Labels] , [IncludeHeader] , [IncludeSeriesKey] - ignored, [IncludeBreakdown] - ignored, [IncludeAttributes]'' )&lt;/span&gt; &lt;br&gt;<br /> Returns a selected dataset in 'vertical' format with one series per column<br /> * Three mandatory arguments: the base URL of the Fusion Registry, the Dataflow, a Series Key pattern specifying what series to return<br /> * 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<br /> * IncludeSeriesKey is ignored<br /> * IncludeBreakdown is ignored<br /> * Set Labels=both to show IDs and dimensions / attributes<br /> * If Labels=both, setting IncludeAttributes=TRUE will show the breakdown of series / dataset attributes in the header<br /> * IncludeHeader controls whether the resultset metrics are shown at the top (e.g. 205/1276 series 1.78 seconds)<br /> <br /> '''''Examples'''''<br /> =FXLData(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;)<br /> =FXLDataV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry&lt;/nowiki&gt;&quot;,&quot;WB,WDI_POVERTY,1.0&quot;,&quot;A...&quot;,1000,&quot;round=2&quot;,2010,2020,&quot;both&quot;,true,true,true,false)<br /> =FXLData(C14,C4,C7,C15,C13,C5,C6,C12,C9,C8,C10,C11)<br /> =FXLDataV(A9,B24,B25,1000,,min(D55,2010),max(D56,2020))<br /> Like all Excel functions, arguments can either be literal values, cell references, or other formulas.<br /> <br /> ==Functions taking a Fusion Registry data query URL==<br /> 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.<br /> <br /> Both the horizontal and 'V' vertical variants take the same arguments making it easy to switch between presentations. <br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrl'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'horizontal' time-series format with one series per row&lt;br/&gt;<br /> * Behaves in the same way as =FXLData() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlV'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset in 'vertical' format with one series per column&lt;br/&gt;<br /> * Behaves in the same way as =FXLDataV() but takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataUrlFact'''( ''Url , [MaxSeries] '' )&lt;/span&gt;&lt;br/&gt;<br /> Returns the selected resultset as a [https://en.wikipedia.org/wiki/Fact_table data warehouse style fact table]&lt;br/&gt;<br /> * Takes a single mandatory argument: a fully-formed Fusion Registry data query URL<br /> * A single optional argument allows the maximum number of series to be set<br /> * Returns one row per observation including all series and observation attributes<br /> * Following the traditional fact table model, only dimension and attributes codes are returned - there is not option to return code labels<br /> <br /> '''''Examples'''''<br /> =FXLDataUrl(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;)<br /> =FXLDataUrlV(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.CAD+HUF+INR...E?round=2&amp;labels=id&lt;/nowiki&gt;&quot;,1000)<br /> =FXLDataUrlFact(&quot;&lt;nowiki&gt;https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/.CAD+MXN+MYR...&lt;/nowiki&gt;&quot;,20)<br /> <br /> ==Administrative functions==<br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion()'''&lt;/span&gt;<br /> * Returns the software version number and build date<br /> <br /> &lt;span style=&quot;color:red;&gt;'''=FXLDataVersion(&quot;changelog&quot;)'''&lt;/span&gt;<br /> <br /> Returns the latest version of the change log<br /> <br /> ==Data Wizard==<br /> In addition to the Parameterised functions a Data Wizard, is available as an Excel Ribbon Tool for interactively building and editing FXLData functions and comes as part of the add-in.<br /> <br /> [[File:Wizard Version R1.0.png|Wizard - July 2004|600px]]<br /> <br /> <br /> [[FXLData_Wizard |Read more about the Data Wizard here. ]]</div> Grahamcassidy