Tag: power-bi

Optimizing Business Central Reports with the DataAccessIntent Property

When developing reports in Microsoft Dynamics 365 Business Central, performance and scalability are crucial—especially in cloud environments where system efficiency impacts everything from responsiveness to cost. One often-overlooked feature that can significantly enhance report performance is the DataAccessIntent property.

What Is DataAccessIntent?

The DataAccessIntent property is available on report objects in AL. It specifies how a report should access the database—whether it’s read-only or read-write.

The property supports two values:

  • DataAccessIntent = ReadOnly
  • DataAccessIntent = ReadWrite

ReadOnly

When you set DataAccessIntent = ReadOnly, you are explicitly telling the platform that your report only needs to read data from the database and will not perform any write operations.

Why is this important?

  • Better Performance: In Business Central SaaS, reports marked as ReadOnly can run on read-only replicas of the database. This reduces the load on the primary (read-write) database and enhances scalability.
  • Improved Report Execution Time: Since queries are routed to optimized replicas, report rendering can be faster and more efficient.

ReadWrite

If your report needs to modify data during execution—a rare scenario—you should use DataAccessIntent = ReadWrite. This forces the report to run on the primary database.

However, you should avoid using ReadWrite unless absolutely necessary because:

  • It eliminates the benefit of using read replicas.
  • It may degrade performance, especially under high concurrency.

When to Use Each

ScenarioUse ReadOnly?Use ReadWrite?
Standard data listing/reporting✅❌
Reports that update records❌✅
Diagnostic or audit reports✅❌

How to Set DataAccessIntent in AL

report 50100 "Customer Balance Report"
{
    DataAccessIntent = ReadOnly;

    dataset
    {
        dataitem(Customer; Customer)
        {
            column(Name; Name) { }
            column(Balance; "Balance (LCY)") { }
        }
    }

    layout
    {
        // Define RDLC or Word layout
    }
}

By default, if you don’t specify the property, it behaves as ReadWrite. So it’s a good practice to explicitly set it to ReadOnly when applicable.

Important Considerations

  • The DataAccessIntent property is only a hint to the Business Central server. The server may not always be able to use a read-only replica, even if the property is set to ReadOnly.
  • If a report with DataAccessIntent set to ReadOnly attempts to modify data, a runtime error will occur.
  • The DataAccessIntent property can be overridden by the user through the “Database Access Intent List” page in Business Central.

In short: if your report doesn’t write data, use DataAccessIntent = ReadOnly. It’s an easy win for performance and best practice compliance.

Stay tuned for more….

Boosting Performance in Business Central with SETLOADFIELDS

When building customizations or integrations in Microsoft Dynamics 365 Business Central, performance is often a key concern—especially when working with large datasets. One powerful tool for improving performance is the SETLOADFIELDS method on record variables.

🚀 What is SETLOADFIELDS?

In Business Central, when you retrieve a record (using FIND, GET, NEXT, etc.), all fields of the record are loaded from the database by default. This includes fields you might never use, such as FlowFields (which are calculated on the fly). This can lead to unnecessary memory usage and slower data retrieval.

SETLOADFIELDS tells the system which fields to load, so it skips the rest. This can dramatically improve performance, particularly when:

  • You’re looping through large datasets
  • You’re only using a few fields per record
  • Your table includes FlowFields or BLOBs

📘 Syntax

Rec.SETLOADFIELDS(Field1, Field2, ...);

You place this line before reading the records. This tells the AL runtime engine to load only the specified fields when the records are retrieved.

Let’s say you need to loop through all items and check their No. and Inventory:

Item.SETLOADFIELDS("No.", Inventory);
if Item.FINDFIRST then
  repeat
    if Item.Inventory > 0 then
      // do something
  until Item.NEXT = 0;

  1. Use SETLOADFIELDS only when needed – Overusing it without understanding the data flow may result in missing fields or unexpected behavior.
  2. Calling CALCFIELDS still works – You can still explicitly calculate FlowFields after using SETLOADFIELDS.
  3. Resets on modification – If you call MODIFY, INSERT, or VALIDATE, the load fields context is reset. Use SETLOADFIELDS again if necessary.
  4. BLOB and FlowFields – Avoid loading BLOBs and FlowFields unless absolutely necessary. SETLOADFIELDS helps you skip them efficiently.

👨‍💻 Summary

FeatureWithout SETLOADFIELDSWith SETLOADFIELDS
Data LoadedAll fieldsOnly specified fields
Memory UsageHighLower
SpeedSlowerFaster

By leveraging SETLOADFIELDS, developers can significantly optimize the performance of their AL code in Business Central. It’s a small addition that can make a big difference in speed and scalability.

Avoid it when doing full record operations like MODIFY, unless you’re confident about which fields are required.

Stay Tuned for more…