Excel Reporting Template - Revision history

Glenn: Remove "Report Templates as a mechanism to describe an output data format" which is not supported by FMR

2025-02-14T17:59:11Z

Remove "Report Templates as a mechanism to describe an output data format" which is not supported by FMR

Glenn: Remove paragraph on populating a workbook from a dataset feature which is not fully supported in FMR

2025-02-14T17:58:28Z

Remove paragraph on populating a workbook from a dataset feature which is not fully supported in FMR

Vmurrell: /* Data Worksheets */

2024-03-30T10:55:43Z

‎Data Worksheets

Vmurrell: /* Open Office */

2023-04-24T10:26:29Z

‎Open Office

Vmurrell: /* Web Service */

2023-04-24T10:26:02Z

‎Web Service

Vmurrell at 11:30, 22 August 2022

2022-08-22T11:30:03Z

Vmurrell: /* Instruction Worksheet */

2021-05-10T13:46:42Z

‎Instruction Worksheet

Vmurrell at 09:20, 5 May 2021

2021-05-05T09:20:48Z

Vmurrell at 09:13, 5 May 2021

2021-05-05T09:13:04Z

Show changes

Vmurrell at 09:08, 5 May 2021

2021-05-05T09:08:11Z

Show changes

@@ Line 265: / Line 265: @@
 The Registry is able to read any excel workbook that it has created from a Report Template. The data in the workbook can be read in for data validation, transformation, or import into one of the Fusion Registries data stores using the usual mechanisms.
 The workbook contains the required metadata to read it back in, even if the report template definition has been subsequently changed or deleted since the workbook’s initial creation.  As long as the Registry contains the same Provision Agreement and Dataflow that the worksheet references, and the Data Structure Definition has not had any changes to the number of, or order of Dimensions, then the worksheet will be read back in.
 = Web Service =
 As an alternative to the Registry web User Interface, the [[Data_Reporting_Template_Web_Services|data report template web service]] can be used to build a Reporting Template.

@@ Line 265: / Line 265: @@
 The Registry is able to read any excel workbook that it has created from a Report Template. The data in the workbook can be read in for data validation, transformation, or import into one of the Fusion Registries data stores using the usual mechanisms.
 The workbook contains the required metadata to read it back in, even if the report template definition has been subsequently changed or deleted since the workbook’s initial creation.  As long as the Registry contains the same Provision Agreement and Dataflow that the worksheet references, and the Data Structure Definition has not had any changes to the number of, or order of Dimensions, then the worksheet will be read back in.
 = Report Templates as a mechanism to describe an output data format =
 A Reporting Template can be used as a mechanism to describe an output format for data export / dissemination.

@@ Line 56: / Line 56: @@
 === Data Worksheets ===
-The Data worksheets contain tables of cells for data entry.  Each Data Worksheet is linked to a single [[Dataflow_-_Structural_Metadata_Management|Dataflow]].  The table layout is based on the Dimensions of the [[Data_Structure_Definition_(DSD)_-_Structural_Metadata_Management|Data Structure Definition]] for which the data is for.  The Dimensions in the Headers and Rows are defined at design time by the owner of the Reporting Template.  The values in the headers and rows (the Dimension members) are derived by determining the reporting universe for the data reporter.  The reporting universe may change based on Data Provider, as [[Reporting_Constraints_-_Structural_Metadata_Management|reporting rules]] for each Data Provider may differ, and as such the reporting template data worksheets may differ in content and size depending on who the template was created for.
+The Data worksheets contain tables of cells for data entry.  Each Data Worksheet is linked to a single Dataflow.  The table layout is based on the Dimensions of the Data Structure Definition for which the data is for.  The Dimensions in the Headers and Rows are defined at design time by the owner of the Reporting Template.  The values in the headers and rows (the Dimension members) are derived by determining the reporting universe for the data reporter.  The reporting universe may change based on Data Provider, as reporting rules for each Data Provider may differ, and as such the reporting template data worksheets may differ in content and size depending on who the template was created for.
 == Floating Point Errors ==

@@ Line 297: / Line 297: @@
 = Open Office =
-It is possible to use Apache Open Office to complete a data set using the same XLSD spreadsheet generated by FMR.
+It is possible to use Apache Open Office to complete a data set using the same XLSX spreadsheet generated by FMR.
 = Google Sheets =
 To use Google Sheets, simply import the generated XLSX spreadsheet into Google Sheets, complete as required and then download a copy in XLSX format. This can then be uploaded into FMR and converted as normal.

← Older revision		Revision as of 10:26, 24 April 2023
Line 295:		Line 295:

	To transform data into a Reporting Template Excel workbook, refer to the [[Data_Formats]] table to lookup the correct Accept Header to use.		To transform data into a Reporting Template Excel workbook, refer to the [[Data_Formats]] table to lookup the correct Accept Header to use.
		+
		+	= Open Office =
		+	It is possible to use Apache Open Office to complete a data set using the same XLSD spreadsheet generated by FMR.
		+
		+	= Google Sheets =
		+	To use Google Sheets, simply import the generated XLSX spreadsheet into Google Sheets, complete as required and then download a copy in XLSX format. This can then be uploaded into FMR and converted as normal.

@@ Line 1: / Line 1: @@
 [[Category:Structural Metadata]]
 = Overview =
 A Data Reporting Template is a metadata driven Excel Form to enable Data Providers to easily report data.  The form is Metadata Driven as the definition of each Worksheet takes into account:

@@ Line 46: / Line 46: @@
 **bold**
 ***bold and italic***
-\n New Line (or alternatively enter a carriage return when typing the text using the report template wizard)
+\n New Line (or alternatively enter a carriage return when typing the text using the report template wizard) *
 </pre>
 === Checking Table Summary Worksheet ===

@@ Line 9: / Line 9: @@
 * How to report Attribute values such as Observation Confidentiality, Status or Pre-Break values
 * Constraints are used to determine the universe of data, each form can contain a different universe of data depending on which Data Provider the form is generated for
-* Mathematical Validation Rules can be used to inject formuala and checking tables into the Worksheet
+* Mathematical Validation Rules can be used to inject formula and checking tables into the Worksheet
 A Reporting Template definition is created in the Fusion Metadata Registry and describes the contents and layout of one Excel Workbook. Each workbook contains at least one worksheet which is backed by the Dataflow that the data collection is for.  A workbook may contain multiple worksheets, enabling one excel file to be used to submit data for multiple datasets.  The Registry provides both a web User Interface and web services to generate the Excel Workbook for a Reporting Template for a specific Data Provider.  The contents of the Excel workbook are dynamically obtained based on who the Data Provider is.  This enables a single data collection template to be set up for multiple data providers, where each provider's workbook contains only the worksheets, and data cells they need to report data for.
@@ Line 46: / Line 46: @@
 **bold**
 ***bold and italic***
-\n New Line (or alternativley enter a carrige return when typing the text using the report template wizard)
+\n New Line (or alternatively enter a carriage return when typing the text using the report template wizard)
 </pre>
@@ Line 53: / Line 53: @@
 === Data Worksheets ===
-The Data worksheets contain tables of cells for data entry.  Each Data Worksheet is linked to a single [[Dataflow_-_Structural_Metadata_Management|Dataflow]].  The table layout is based on the Dimensions of the [[Data_Structure_Definition_(DSD)_-_Structural_Metadata_Management|Data Structure Definition]] for which the data is for.  The Dimensions in the Headers and Rows are defined at design time by the owner of the Reporting Template.  The values in the headers and rows (the Dimension members) are derived by dertermining the reporting universe for the data reporter.  The reporting universe may change based on Data Provider, as [[Reporting_Constraints_-_Structural_Metadata_Management|reporting rules]] for each Data Provider may differ, and as such the reporting template data worksheets may differ in content and size depending on who the template was created for.
+The Data worksheets contain tables of cells for data entry.  Each Data Worksheet is linked to a single [[Dataflow_-_Structural_Metadata_Management|Dataflow]].  The table layout is based on the Dimensions of the [[Data_Structure_Definition_(DSD)_-_Structural_Metadata_Management|Data Structure Definition]] for which the data is for.  The Dimensions in the Headers and Rows are defined at design time by the owner of the Reporting Template.  The values in the headers and rows (the Dimension members) are derived by determining the reporting universe for the data reporter.  The reporting universe may change based on Data Provider, as [[Reporting_Constraints_-_Structural_Metadata_Management|reporting rules]] for each Data Provider may differ, and as such the reporting template data worksheets may differ in content and size depending on who the template was created for.
 == Floating Point Errors ==
@@ Line 263: / Line 263: @@
 The workbook contains the required metadata to read it back in, even if the report template definition has been subsequently changed or deleted since the workbook’s initial creation.  As long as the Registry contains the same Provision Agreement and Dataflow that the worksheet references, and the Data Structure Definition has not had any changes to the number of, or order of Dimensions, then the worksheet will be read back in.
 = Populating a Workbook from a Dataset =
-A Data Provider can to export a Workbook for a Report Template and have it prepopulated with data.  This can be achieved either by using the Registry Data Transformation services in order to 'inject' the data from a dataset in a different format into their workbook.  The Data Transformation Service needs to know what the desired output data format is. Refer to the [[Data_Formats]] table to lookup the correct Accept Header to use.
+A Data Provider can to export a Workbook for a Report Template and have it pre-populated with data.  This can be achieved either by using the Registry Data Transformation services in order to 'inject' the data from a dataset in a different format into their workbook.  The Data Transformation Service needs to know what the desired output data format is. Refer to the [[Data_Formats]] table to lookup the correct Accept Header to use.
 Alternatively, if the Registry is being used as a Data Store (or virtual store) on the Data Provider’s data, then the Data Provider can simply ask for a Reporting Template for a given time period with existing data already included.