PowerBI Reporting with OData

OrderEazi Central provides a built-in OData endpoint that lets you connect business intelligence tools like Microsoft PowerBI directly to your data. This gives you the flexibility to build custom repo

🧾 What is OData?

OData (Open Data Protocol) is an industry-standard way for applications to share data over the web. It works like a structured, queryable data feed that tools like PowerBI can consume natively.

OrderEazi Central exposes a read-only OData v4 endpoint at /odata/ on your instance. This endpoint provides access to 32 core data sets covering projects, products, clients, suppliers, documents, stock levels, and more.

The OData endpoint is read-only. It cannot be used to create, update, or delete data in Central.

🔒 Permissions and Access

Access to the OData endpoint requires an API key with the Read OData (PowerBI) permission enabled.

Key points:

• The OData permission is not enabled by default on existing API keys

• An administrator must explicitly grant this permission per API key via the API Keys management screen

• New API keys created for installed apps automatically receive this permission

• All OData requests are rate-limited to 500 requests per minute per API key

If you don't yet have an API key, ask your OrderEazi administrator to create one and enable the Read OData (PowerBI) permission.

⚙️ Connecting PowerBI Desktop

To connect PowerBI Desktop to your OrderEazi data:

1. Open PowerBI Desktop

2. Select Get Data, then choose Blank Query

3. Click Advanced Editor in the ribbon

4. Enter the following Power Query M code:

let
    Source = OData.Feed(
        "https://your-instance.ordereazi.com/odata",
        null,
        [
            Implementation = "2.0",
            Headers = [
                #"X-OrderEazi-Key" = "your-api-key-here"
            ]
        ]
    )
in
    Source

1. Replace your-instance.ordereazi.com with your actual OrderEazi hostname

2. Replace your-api-key-here with your API key

3. Click Done

PowerBI will connect and display all available data sets as a navigation table. Select the tables you need for your report.

Setting Implementation = "2.0" tells PowerBI to use the OData v4 client. This enables server-side query folding, meaning PowerBI pushes filters and column selections to OrderEazi rather than downloading all data first.

📋 Available Data Sets

The OData endpoint exposes the following data sets. Names shown here are the OData names you'll see in PowerBI.

Core Business Data

Data Set	Description
Clients	Customer records with contact details, credit limits, and balances
Suppliers	Supplier records with contact details and payment terms
Products	Product catalogue with codes, descriptions, pricing, and status
Projects	Orders and quotes with status, deadlines, and sales channel
Documents	Financial documents such as invoices, purchase orders, and credit notes
ProjectLines	Individual line items on projects with quantities, costs, and selling prices
DocumentProducts	Line items on financial documents with quantities and pricing
ProjectValues	Aggregated totals per project including cost, sales, GP, and VAT

Reference Data

Data Set	Description
Currencies	Currency definitions with codes and symbols
Organizations	Your company details including addresses and settings
TaxTypes	Tax/VAT type definitions with percentages
Brands	Product brand names
Industries	Client industry classifications
Categories	Product category hierarchy

Detail and Relationship Data

Data Set	Description
DocumentProductSerials	Serial numbers tracked against document line items
ProductCategories	Product-to-category assignments
ProductDimensions	Physical dimensions and weight per product
ProductKeyValues	Custom extended attributes on products
ProductMarkupMargins	Minimum, maximum, and suggested markup/margin per product
ProductSuppliers	Product-to-supplier relationships with cost prices and stock levels
SupplierCategories	Supplier category groupings
ParcelDefinitions	Shipping parcel/box size templates
ProjectHistories	Audit trail of events on projects
ProjectLineKeyValues	Custom extended attributes on project lines
ProjectKeyValues	Custom extended attributes on projects
ProjectTags	Project-to-tag assignments
Tags	Tag definitions with names and colours
UserDetails	Sales rep and user profiles with commission settings
SimpleStockLevels	Stock quantities by product, location, and warehouse
ProductMOQs	Minimum, maximum, and default order quantities per product
DocumentVoids	Records of voided documents with action type and reason
ProjectRequests	Approval requests with status, assignee, and response

🔍 Querying Specific Data

To connect to a single data set rather than the full navigation table, specify the data set name in the URL:

let
    Source = OData.Feed(
        "https://your-instance.ordereazi.com/odata/Products",
        null,
        [
            Implementation = "2.0",
            Headers = [
                #"X-OrderEazi-Key" = "your-api-key-here"
            ]
        ]
    )
in
    Source

Replace Products with any data set name from the table above.

🔗 Navigation Properties

The OData endpoint supports navigation properties, which let you traverse relationships between data sets. For example, you can retrieve all project lines for a specific project by navigating from the project to its related data:

/odata/Projects({project-id})/ProjectLines
/odata/Projects({project-id})/ProjectValues
/odata/Documents({document-id})/DocumentProducts

The following navigation paths are available:

From Core Business Data

Parent Data Set	Navigation Path	Returns
Projects	/ProjectValues	Aggregated totals for the project
Projects	/ProjectLines	Line items on the project
Projects	/ProjectHistories	Audit trail of events on the project
Projects	/ProjectTags	Tags assigned to the project
Projects	/Documents	Financial documents linked to the project
Projects	/DocumentProductSerials	Serial numbers tracked against the project
Documents	/DocumentProducts	Line items on the document
Documents	/DocumentVoids	Void records for the document
Documents	/DocumentProductSerials	Serial numbers tracked against the document
Documents	/ChildDocuments	Documents created from this document
Products	/DocumentProducts	Document line items for the product
Products	/ProductCategories	Category assignments for the product
Products	/ProductSuppliers	Supplier relationships for the product
Products	/ProductKeyValues	Custom extended attributes on the product
Products	/ProductDimensions	Physical dimensions and weight for the product
Products	/ProductMarkupMargins	Markup/margin settings for the product
Products	/ProductMOQ	Minimum order quantities for the product
Products	/ProjectLines	Project line items for the product
Products	/SimpleStocklevels	Stock levels for the product
Suppliers	/DocumentProducts	Document line items from the supplier
Suppliers	/ProductSuppliers	Product relationships for the supplier
Suppliers	/ProjectLines	Project line items from the supplier
ProjectLines	/DocumentProducts	Document line items linked to the project line
ProjectLines	/DocumentProductSerials	Serial numbers tracked against the project line
DocumentProducts	/DocumentProductSerials	Serial numbers tracked against the document line item

From Reference Data

Parent Data Set	Navigation Path	Returns
Currencies	/CurrencyDocuments	Documents using this currency
Currencies	/CurrencyProjects	Projects using this currency
Currencies	/Clients	Clients using this currency
Currencies	/Suppliers	Suppliers using this currency
TaxTypes	/Clients	Clients assigned this tax type
TaxTypes	/Suppliers	Suppliers assigned this tax type
TaxTypes	/Projects	Projects assigned this tax type
Brands	/Products	Products under this brand
Industries	/Clients	Clients in this industry
Tags	/ProjectTags	Project-tag assignments for this tag
UserDetails	/Clients	Clients managed by this sales rep
UserDetails	/Documents	Documents created by this user
UserDetails	/DocumentVoids	Document voids performed by this user
UserDetails	/ProjectHistories	Project history entries by this user
UserDetails	/Projects	Projects managed by this sales rep

You can also use $expand as an alternative to navigation paths. For example, /odata/Projects({id})?$expand=ProjectValues returns the project with its values inline in a single request.

📐 Query Options

The OData endpoint supports standard query options that PowerBI uses automatically when you apply filters and transformations:

• $select - Choose specific columns to reduce data transfer

• $filter - Apply conditions to retrieve only matching records

• $orderby - Sort results by one or more columns

• $top - Limit the number of records returned (maximum 1,000 per request)

• $skip - Skip a number of records for pagination

• $count - Include a total record count in the response

• $expand - Include related data in a single request (one level deep)

PowerBI applies these automatically through query folding. When you add filters or remove columns in PowerBI, those operations are pushed to the server for efficiency.

The default page size is 100 records, and the maximum you can request via $top is 1,000. PowerBI handles pagination automatically, so you don't need to manage this yourself.

💡 Why Use OData with PowerBI?

Connecting PowerBI to OrderEazi via OData is particularly useful for:

• Building custom dashboards tailored to your business KPIs

• Combining OrderEazi data with data from other sources in a single report

• Creating scheduled reports that refresh automatically

• Performing ad-hoc analysis that goes beyond Central's built-in reports

• Sharing interactive reports with stakeholders who don't need Central access

• Tracking trends over time with historical snapshots in PowerBI datasets

✅ Summary

The OData endpoint provides a powerful, read-only connection between OrderEazi Central and PowerBI:

• Access 32 data sets covering projects, products, clients, suppliers, documents, and stock

• Navigate relationships between data sets using navigation properties (e.g. Projects/ProjectLines)

• Authenticate using your existing API key with the Read OData (PowerBI) permission

• Connect via PowerBI's Advanced Editor using the OData.Feed function

• Server-side query folding ensures efficient data transfer

• Standard OData query options give you full control over filtering, sorting, and pagination

This feature lets you build the exact reports your business needs, using the full power of PowerBI's visualisation and analysis tools.