Excel Integration & Electronic Reporting

Two different tools, two different jobs

---

Part 1: Excel integration

Edit in Excel

The “Edit in Excel” button appears on most list pages in F&O. It opens the current entity’s data in Excel via an OData connection.

Concept	Detail
OData entity	The data entity must have IsPublic = Yes to be available
Data connector	The Excel Add-in for Dynamics 365 manages the OData connection
Bidirectional	Users can read data AND push changes back (if they have write permissions)
Filtering	Filters applied in F&O carry over to the Excel view
Batch edit	Users can modify hundreds of rows and publish all changes at once

┌──────────────┐    OData     ┌──────────────┐    Publish    ┌──────────────┐
│  F&O Entity  │◀───────────▶│  Excel        │──────────────▶│  F&O Entity  │
│  (read)      │  GET         │  (edit cells) │   POST/PATCH  │  (updated)   │
└──────────────┘              └──────────────┘               └──────────────┘

ℹ️ Scenario: Warehouse bulk update

PacificForge’s warehouse team needs to update safety stock levels for 800 items:

1. Navigate to Released products list
2. Click Edit in Excel → opens the entity in Excel
3. Filter to the relevant warehouse
4. Update the safety stock column for all 800 rows
5. Click Publish → all changes pushed back via OData

“What used to take a full day now takes 30 minutes,” Elena tells Gregor.

Excel templates

Excel templates are pre-formatted workbooks with embedded data bindings — a designed experience with headers, formatting, and specific field layouts.

Edit in Excel = ad-hoc. Templates = standardised and branded.

Feature	Edit in Excel	Excel Template
Setup	Zero — built-in button	Template must be created and uploaded
Layout	Generic tabular grid	Pre-formatted with headers, formulas, branding
Fields	All entity fields available	Only specified fields in the template
Use case	Ad-hoc bulk editing	Standardised data entry or export format
Reusable	Opens fresh each time	Template saved in Document Templates gallery

ℹ️ Creating an Excel template

To create a custom Excel template:

1. Navigate to Common → Document templates
2. Click New → select the data entity
3. Download the generated Excel file
4. In Excel, arrange fields, add formatting, add formulas
5. Upload the customised template back to F&O
6. Users can now select this template from the Edit in Excel dropdown

Templates support multiple entities in one workbook — useful for header/line scenarios like sales orders.

OData entity requirements for Excel

Requirement	Why
IsPublic = Yes	Only public entities are exposed via OData
Data management enabled	Entity must be registered in the Data Management framework
Key fields defined	OData needs a unique key to identify records for updates
Appropriate security	User must have the privilege to read/write the entity
Change tracking enabled	Recommended for publish-back — tracks which records changed

---

Part 2: Electronic Reporting (ER)

Why ER exists

Every country has its own format for tax returns, payment files, and regulatory reports. Before ER, developers wrote custom X++ for each format. ER replaces that with a configuration-driven approach — business users and consultants can modify formats without code.

ER architecture: three layers

┌─────────────────────────────────────────────────┐
│                 ER Configuration                 │
│                                                  │
│  ┌──────────────┐    ┌──────────────────────┐   │
│  │  Data Model   │    │  Model Mapping        │   │
│  │  (abstract    │◀──▶│  (maps model to       │   │
│  │   structure)  │    │   F&O tables/entities) │   │
│  └──────┬───────┘    └──────────────────────┘   │
│         │                                        │
│  ┌──────▼───────┐                                │
│  │  Format       │                                │
│  │  (output      │                                │
│  │   template)   │                                │
│  └──────────────┘                                │
└─────────────────────────────────────────────────┘

Three layers — separation enables reuse and localisation

Layer	Purpose	Who manages it	Example
Data Model	Defines abstract data structure — what fields are available	Microsoft (standard) or developer (custom)	InvoiceModel: InvoiceNumber, Date, Lines[], TaxAmount
Model Mapping	Maps abstract fields to actual F&O tables and entities	Developer or consultant	InvoiceModel.InvoiceNumber → CustInvoiceJour.InvoiceId
Format	Defines output structure and layout (XML, Excel, text)	Consultant or business user	NZ GST Return XML with specific element names

💡 Why three separate layers?

The three-layer separation is the key design principle of ER:

• One data model can serve multiple formats — same invoice model generates NZ XML, AU XML, and US text
• Format changes don’t require code — tax authority changes XML schema? Update format only
• Model mappings can be swapped — same format, different data source
• Versioning — each layer versioned independently

This is heavily tested on the exam.

ER Global Repository

Microsoft provides hundreds of pre-built ER configurations via the Global Repository.

Concept	Detail
Global Repository	Cloud-hosted library of ER configurations by Microsoft
Import	Download configurations to your F&O environment
Derive	Create a custom version based on a Microsoft config (preserves upgradeability)
Versioning	Each configuration has versions — import specific ones
Provider	Each config has a provider (Microsoft or your custom provider)

ℹ️ Scenario: Elena implements NZ e-invoicing

New Zealand is rolling out mandatory e-invoicing. Elena’s approach:

1. Opens Electronic Reporting workspace → Repository → Global
2. Searches for “New Zealand” → finds the NZ e-invoice format
3. Imports the format and its dependent data model + mapping
4. Derives a custom version (so PacificForge can add custom fields)
5. Opens the Format Designer → adds PacificForge’s “Production Batch” field
6. Configures the format to run from Accounts Receivable invoicing

“When Microsoft updates the base format, our derived version inherits changes automatically,” Elena explains to Kenji.

Format Designer

The visual tool for building ER output templates:

Element type	Purpose	Example
File	Root element — defines the output file	NZGSTReturn.xml
XML element	Structural grouping	<TaxReturn>, <Header>, <Lines>
Attribute	XML attributes or cell references	version="2.0"
Content	Data values from the model	InvoiceNumber, TaxAmount
Sequence	Ordered list of repeating elements	Invoice lines, payment transactions
Condition	Conditional inclusion	Only include <Discount> if discount > 0

Data Model Designer

Defines the abstract data structure:

Concept	Detail
Root	Entry point — a model can have multiple roots
Record	A data element (like a table row)
Record list	A repeating collection (like invoice lines)
Fields	Individual data points (string, integer, date, real, enum, boolean)
Calculated fields	Formulas using ER expression language
References	Links between records

ER expression language

Function	Purpose	Example
FORMAT	String formatting	FORMAT("INV-%1", model.InvoiceNumber)
NUMBERFORMAT	Number to text	NUMBERFORMAT(model.Amount, "N2")
DATEFORMAT	Date to text	DATEFORMAT(model.InvoiceDate, "yyyy-MM-dd")
IF	Conditional logic	IF(model.TaxAmount > 0, "Taxable", "Exempt")
SUMIF	Conditional sum	SUMIF(model.Lines, Lines.Category = "Tax", Lines.Amount)
FILTER	Filter a record list	FILTER(model.Lines, Lines.Amount > 100)

💡 Exam tip: ER expressions vs X++ code

The exam tests when ER expressions are sufficient vs when X++ is needed:

• ER expression — string formatting, date conversion, simple math, filtering, conditional output
• X++ code needed — complex business logic, external API calls, database operations not exposed in the data model

ER is designed so most regulatory reporting needs are met without code.

ER vs SSRS: when to use which

SSRS = printed documents with layout. ER = structured electronic documents.

Scenario	SSRS	Electronic Reporting
Customer invoice (printed PDF)	✅ Best fit — pixel-perfect layout	Possible but overkill
Tax return in government XML	❌ Not designed for structured data files	✅ Purpose-built for this
Bank payment file (ISO 20022)	❌ Wrong tool	✅ Standard config in Global Repo
Warehouse picking list	✅ Operational doc with layout control	❌ Over-engineered
E-invoice (Peppol/UBL format)	❌ Can't generate structured XML	✅ Standard configs available
Internal management report	✅ Tabular/summary report	❌ Not designed for ad-hoc reporting

---

Question

What are the three layers of an Electronic Reporting configuration?

Click or press Enter to reveal answer

Answer

1) Data Model — abstract data structure (fields, records, lists). 2) Model Mapping — maps model fields to F&O tables/entities. 3) Format — defines output template (XML, Excel, text). This separation lets one model serve multiple formats and enables format changes without code.

Click to flip back

Question

What is the ER Global Repository?

Click or press Enter to reveal answer

Answer

A cloud-hosted library of pre-built ER configurations maintained by Microsoft. Contains hundreds of formats for tax reporting, payment files, and e-invoicing. You import configurations and derive custom versions that inherit future Microsoft updates.

Click to flip back

Question

What must be true about a data entity for Edit in Excel to work?

Click or press Enter to reveal answer

Answer

IsPublic = Yes (exposed via OData), data management enabled, key fields defined (for record identification), user has security privileges to read/write, and change tracking recommended for publish-back.

Click to flip back

Question

What is the difference between Edit in Excel and Excel Templates?

Click or press Enter to reveal answer

Answer

Edit in Excel is ad-hoc — click button, get generic grid, edit and push back. Templates are pre-designed workbooks with specific field layouts, formatting, and formulas. Templates are uploaded to Document Templates for standardised, reusable experience.

Click to flip back

Question

How do you customise a Microsoft-provided ER configuration without losing upgradeability?

Click or press Enter to reveal answer

Answer

Use Derive — create a derived version inheriting the Microsoft base. Add only your customisations. When Microsoft updates the base, your derived version can rebase, preserving your changes.

Click to flip back

---

Knowledge Check

PacificForge needs to generate a bank payment file in ISO 20022 XML format. Which tool should Elena use?

ASSRS report with XML outputBEdit in Excel with OData exportCElectronic Reporting with a format from the Global RepositoryDPower BI export to XML

Check Answer

Knowledge Check

Elena needs to customise a Microsoft-provided ER format to add a PacificForge-specific field. How should she proceed?

AEdit the Microsoft configuration directlyBCreate a new configuration from scratchCDerive a custom version from the Microsoft configuration and add the fieldDExport the configuration, modify the XML, and re-import

Check Answer

Knowledge Check

A user clicks 'Edit in Excel' on Released Products but gets an error. What should Elena check first?

AWhether the user has a Power BI Pro licenseBWhether the entity has IsPublic = Yes and is registered in Data ManagementCWhether the entity store has been refreshedDWhether the user's Excel version supports OData

Check Answer

Knowledge Check

Which ER layer would Elena modify to change XML element names in a tax return output without changing the underlying data?

AData ModelBModel MappingCFormatDAll three layers must be changed together

Check Answer

Next up: Security Roles, Duties & XDS — building the security architecture for F&O with role-based access and row-level filtering.