Reporting & Integration Capabilities | AICITSS Unit 5 Chapter 2

AICITSS · Unit 5 · Chapter 2

📊 Reporting & Integration Capabilities
in Finance & Operations Apps

SSRS Reports · Power BI · Excel · Office 365 · Power Platform · Business Events

Progress: 0%

📌

Chapter Overview

What this chapter covers — 5 learning objectives

      🎯 Learning Objectives:

Describe built-in reporting capabilities (SSRS)

Describe options for analyzing data (Power BI)

Describe options for working with data in Microsoft Office (Excel, Word)

Describe integration capabilities with Microsoft Power Platform (Dataverse, Power Apps, Power Automate)

Describe Business Events

🗺️ Chapter at a Glance — 6 Sections

2.2 — SSRS Reports: Pre-built SQL Server Reporting Services reports + custom report creation using Visual Studio and 4 data source types
2.3 — Power BI: Interactive data analysis, dashboards, embedded reports in workspaces, filtering, AppSource visuals
2.4 — Excel Data Manipulation: Export to Excel feature, Excel Data Connector, OData integration
2.5 — Office 365 Integration: Microsoft Dynamics Office Add-in, Word templates, SharePoint document management, Email integration
2.6 — Power Platform Integration: Dual-write, Virtual Entities, Power Apps canvas apps, Power Automate
2.7 — Business Events: Notifications to external systems, JSON schema, supported endpoints

🔑 Key Integration Technologies at a Glance

SSRS — Built-in, printable, compliance reports with drill-down navigation
Power BI — Interactive data visualization embedded in workspaces
Excel Data Connector — Real-time bidirectional data sync via OData/REST
Microsoft Dynamics Office Add-in — Key enabler for Excel & Word integration
Dual-write — Near real-time bidirectional sync between Dataverse and F&O
Virtual Entity — Dataverse tables backed by F&O data without replication
Power Automate — Workflow automation triggered by business events
Business Events — JSON notifications from F&O to external systems

📄

2.2 — SSRS Reports

SQL Server Reporting Services — built-in and custom reports

📋 What are SSRS Reports?

Finance and operations apps include pre-generated SQL Server Reporting Services (SSRS) reports. You can print these reports to:

Your screen, a printer, a file, or send via email
Create parameterized views that support drill-down navigation
Embed hyperlinks from a report to F&O pages
Schedule reports using a batch job to run periodically
Create precise compliance documents for local regulatory needs
Create custom reports using the SSRS tool and X++ development (via Microsoft Visual Studio)

🏗️ How to Create SSRS Reports

Requires Microsoft Visual Studio + a Finance and Operations apps development environment:

Step 1: Create a new report object in Visual Studio
Step 2: Assign a data set — defines the data the report uses (data source + data source type)
Step 3: Set the report layout — Auto Design (simple) or Precision Design (customized)
Step 4: Deploy the report — it then becomes available to run in F&O apps

🗂️

4 SSRS Data Source Types

Click each to expand — exam critical!

1️⃣ Query Fastest / Least Code

▼

Uses an existing Application Object Tree (AOT) query. Allows for faster data filtering in SQL to quickly generate reports. Requires limited X++ code — only for display methods in tables.

Best for: Simple reports where an existing AOT query can be reused.

2️⃣ Business Logic External Data Source

▼

Uses a data source other than finance and operations apps. Can only be used with a single report because the name of the class used must match the name of the report.

Best for: Reports that pull data from external or non-F&O sources.

3️⃣ Report Data Provider (RDP) Complex Logic / Dynamic Filters

▼

Used when added logic is needed to run the report — typically when using dynamic filters. An RDP class is an X++ class. It allows advanced filter criteria to access and process data. Report parameters applied in the UI can also be processed through the RDP class.

Best for: Complex reports with dynamic, user-driven filtering requirements.

4️⃣ AX Enum Provider Enumeration-Based Filtering

▼

Used to filter a report view when the report parameter is an enumeration type. Allows you to add a dataset that binds to a specific enumeration and forces the report to display a specific enumeration value.

Best for: Reports where filtering is based on a fixed set of values (enumerations).

Query

AOT Query

Fast SQL filtering. Limited X++ (display methods only).

Business Logic

External Source

One report per class. Class name must match report name.

RDP (Report Data Provider)

X++ Class

Dynamic filters. Advanced filter criteria. UI parameters processed.

AX Enum Provider

Enumeration Type

Binds dataset to a specific enumeration. Forces specific enum value display.

📊

2.3 — Power BI for Data Analysis

Interactive data visualization and reporting in Dynamics 365

📈 What is Power BI?

A business analytics service that delivers insights to enable fast, informed decisions. It:

Connects to hundreds of data sources using standard connectors (unlimited with custom connectors)
Simplifies deriving insights from transactional and observational data
Displays data as visually immersive, interactive insights — drill-down to financial and operational data
Supports data from multiple sources: Excel, SharePoint, OneDrive, Salesforce, SQL databases, Exchange, Dynamics 365, and more
Can embed reports within a workspace in finance and operations apps
Especially useful for dashboards and non-document reports (reports that don’t need printing)

📦 Ready-Made Power BI Reports — in Lifecycle Services

Available in the shared asset library of Microsoft Dynamics Lifecycle Services — download and deploy in your environment:

Actual vs. Budget | Cash Overview | Compensation and Benefits
Cost Accounting Analysis | Credit and Collections Management
Employee Competencies and Development | Financial Performance
Fixed Asset Management | Organizational Training | Practice Manager
Production Performance | Purchase Spend Analysis | Recruiting
Sales and Profitability Performance | Vendor Payments | Warehouse Performance | Workforce Metrics

Also available on PowerBI.com marketplace: Cost Management, Financial Performance, Retail Channel Performance

🔎 Power BI Filter Types (4 Types)

Note: Filtering applies to reports only — not to dashboards. Filtering the view does NOT change the source data.

📋 Report Filter

Applies to all pages in the report.

📄 Page Filter

Applies to all visuals on the current page of the report.

📊 Visual Filter

Applies to a single visual on a page. Visible only when a visual is selected on the canvas.

🔍 Drill Through

Explore successively more detailed views within a single visual.

🛠️ Power BI Tools & Features

Power BI Desktop — Windows app for authoring reports and custom visuals
Power BI Service — Online SaaS for sharing dashboards and refreshing data
Power BI Report Builder — Creates paginated reports shared in Power BI Service
Power BI Report Server — On-premises server to publish reports created in Power BI Desktop
Power Query Editor — Built into Power BI Desktop; shapes and transforms data before visualization
AppSource Power BI Visuals — Community and Microsoft-contributed visuals; must be certified to confirm no access to external services
Buttons in Power BI — Add interactive buttons on Insert ribbon → Buttons menu; creates app-like report experience

📗

2.4–2.5 — Excel & Office 365 Integration

Excel Data Connector, Word templates, SharePoint & Email

      Key Tool: The Microsoft Dynamics Office Add-in is the single most important integration enabler for both Excel and Word in Dynamics 365. It uses the OAuth security protocol and retrieves application security roles for the current user.
    

📗 Excel Integration — Export & Data Connector Section 2.4

▼

Export to Excel — Available in the upper-right corner of most F&O pages; exports only the columns visible in the current grid (including any Personalize-added columns)
Custom Export Button — Create a command button with the command “Export to Microsoft Excel” to trigger export
Excel Data Connector — Out-of-the-box app that views, updates, and edits data within an Excel workbook in real-time
Uses REST-based integration protocol connecting data entities through OData (Open Data Protocol) endpoints
Data is uploaded and validated in real-time; records appear immediately in Dynamics 365
Requires downloading the Microsoft Dynamics Office Add-in

🧩 What is a Data Entity? Key Concept

▼

A data entity is an object in a data model — combines multiple tables into one logical view
Example: The vendor entity combines over 17 different tables into a single entity
Uses business logic during import/export to validate and transform data
Each data entity has a property named “Enable public API” — if set to Yes, the entity is automatically exposed as an OData endpoint
The Excel Data Connector consumes this OData endpoint to establish synchronous integration with F&O apps

🔓 Open in Excel — Capabilities Microsoft Dynamics Office Add-in

▼

With the Microsoft Dynamics Office Add-in, you can:

Open entity data in Excel from F&O apps or directly from Excel
View, edit, and update entity data in Excel
Add or remove columns using the designer
Filter data using the Filter button
Publish changes back to F&O apps (passes through data entity logic validation)
Copy configuration data between environments using “Copy Environment Data” functionality

Columns in Excel are defined by the Auto Report field group on the entity. The Design button in the app lets you add/edit bindings to entity data sources and labels.

📝 Word Integration — Export to Word Lightweight Reporting

▼

Used for lightweight reporting powered by prebuilt templates. To create a Word template:

Navigate to Common → Common → Office integration → Excel workbook designer
Select the required entity and the fields to display
Click Create blank document in the Action Pane → download the Word template
Design the template according to your reporting format
Navigate to Common → Common → Office integration → Document templates and upload the template
The Export to Word menu will appear on the corresponding page

🗂️ SharePoint Integration Document Management

▼

SharePoint gives organizations a secure place to store, organize, share, and manage content. Benefits:

Empower teamwork, quickly find information, seamlessly collaborate
Accessible from any device with a web browser (Edge, Chrome, Firefox)
Associate files/images with any record using the Attachments icon (upper-left corner of any page)
Document types configured under: Organization administration → Document management → Document types
SharePoint address set in the Location field of the Document types page

Prerequisites: Must be enabled by a system administrator. Requires a Microsoft 365 subscription or SharePoint online service subscription.

📧 Email Integration Admin & User Settings

▼

Administrator-set behaviors:

Batch email provider — which provider sends batch/non-interactive emails
Attachment size limit — max size of a single email
SMTP settings: Outgoing mail server, SMTP port (typically 587 for secure transport), Username/Password, SSL requirement

How to send email — 4 options:

Use an email app like Outlook — generates an .eml file
Use Exchange email server — uses Exchange Online (on-premises Exchange not supported for Exchange mail provider)
Use the system email client — opens Send email dialog, sends via SMTP
Do not ask again — remembers last selected option

⚡

2.6 — Microsoft Power Platform Integration

Dataverse, Dual-write, Virtual Entities, Power Apps, Power Automate

⚡ Microsoft Power Platform

Provides a suite of capabilities for finance and operations apps. Made up of:

Canvas Apps (Power Apps) — No-code app development
Power Automate — Workflow automation service
Power BI — Data visualization and analytics

F&O apps are hosted on Azure, which increases integration scope. Can connect to on-premises apps using a gateway.

🗄️

Dataverse Integration

Default data storage for Power Platform. Stores business data securely in tabular format with key and table relationships. F&O apps store data in Azure SQL, integrated with Dataverse via data entity framework.

🔄

Dual-Write

Standard integration platform providing near-real-time, bidirectional interaction between Dataverse and F&O apps. No-code/low-code principle. Supports both online and offline modes. For documents, master, and reference data.

🌐

Virtual Entity

Represents external (F&O) data as tables in Dataverse without replication and without custom coding. Enables full CRUD operations from Dataverse on F&O virtual entities. All OData entities in F&O are available as virtual entities.

📱

Power Apps Canvas App

No-code app development platform. Connect to F&O in two ways: Embed a canvas app inside F&O pages OR connect directly from a canvas app using the Fin & Ops Apps (Dynamics 365) data connector.

⚙️ Embed Canvas App in F&O — Required Fields

Name — Name of the canvas app to embed
App ID — Get from the Power Apps maker portal → Details button
Input context for the app — A column from the F&O page’s data source, passed as a parameter to the canvas app
Application size — Thin or Wide (controls how it appears in the UI)

🤖 Power Automate Integration — Actions Available

Power Automate has a connector for F&O apps that connects all OData entities and performs CRUD operations. Two components: Trigger (business event) and Actions.

✅ Create Record — Create a new record in F&O apps
📋 List items present in the table — Get list of records; supports cross-company reading
🗑️ Delete Record — Delete an existing data record for a data entity
⚡ Execute Action — Invoke methods on a data entity to perform a business action
🔍 Get a Record — Fetch a specific record for a data entity
📁 Get the list of entities — Get list of entities for further use in the app
✏️ Update a Record — Update an existing data record for a data entity

Can also connect to: Dataverse, SharePoint, Outlook alongside F&O connector.

🔔

2.7 — Business Events

How F&O sends notifications to external systems

🔔 What are Business Events?

A mechanism that lets external systems receive notifications from Finance and Operations apps. Based on the notification, the external system can perform business actions in response.

Triggered by: Workflow trigger, Purchase Order confirmation, Journal posting, and Create/Update/Delete operations on a table.

Output: A JSON file is generated and sent through the selected endpoint. Each business event provides a JSON schema downloadable via the “Download schema” option.

Found at: System administration → Setup

Custom Business Events — Can be created through code development and appear in the same list.

🔌

6 Supported Endpoints

Where F&O sends Business Event notifications

🚌

Azure Service Bus Queue

📢

Azure Service Bus Topic

🌐

Azure Event Grid

📡

Azure Event Hubs

🔗

HTTPS

📦

Azure Blob Storage

⚡ Power Automate as a Business Event Endpoint

Power Automate is a widely used endpoint for business events. To activate:

Open the Power Apps maker portal
Create a Power Automate flow using the Fin & Ops Apps (Dynamics 365) data connector
Select the trigger: “When a Business Event occurs”
Enter: Instance (environment URL), Category, Business Event, Legal Entity
The trigger consumes the JSON file from F&O → parses it → processes for further business actions

🔐 Business Events Security

The Business Events page has a Security tab where you can define role-based security for each business event — controlling which roles can trigger and consume each event.

🃏

Flashcards — Revise Key Terms

Click the card to flip and reveal the definition

Term / Concept

Loading…

Definition / Explanation