Analysis Mode killed half the Excel exports on your client's shared drive

 

Open any client's finance folder and you will find it. Six versions of the same spreadsheet. Customer Aging - Oct.xlsx, Customer Aging - Oct (2).xlsx, Customer Aging FINAL.xlsx, Customer Aging FINAL copy.xlsx. All exported from Business Central.

Nobody built that folder on purpose. It exists because someone once needed to group customer entries by salesperson and month, could not do it on the list page, and hit Open in Excel. Then they needed it again next month.

What actually happens

Hit “Enter analysis mode” and the page changes shape. The normal action bar is replaced by an analysis bar, and the screen splits into two halves.

On the left is the data area, with a summary bar along the bottom and the analysis views bar across the top. On the right are two panes: Columns and Analysis filters.

Nothing you do here touches the underlying data, and nothing you do changes the page for anyone else. That is worth saying to a nervous client before you let them click anything.

Analysis views are the point. The bar at the top starts with one view called Analysis 1. Each view holds its own columns, its own filters, its own pivot arrangement. You might have one for aged balances, one for your top twenty customers, one filtered to overdue items only. They persist between sessions; they are yours alone, and you can rename, duplicate, move, or delete them. Analysis 1 cannot be deleted, only renamed, and Delete All leaves it standing.

The Columns pane has rules. Row Groups accept non-numeric fields only: text, date, time. Values accept only fields that can be summed. Drag something into the wrong area and the client refuses.

One subtlety worth knowing: if you have personalized the page to add a field, it shows up in the Columns pane with its checkbox cleared. It is there; it is just not switched on.

Date hierarchies are generated for you. For each date field in the dataset, Business Central creates three additional fields named after it: Posting Date Year, Posting Date Quarter, and Posting Date Month. Analysing Customer Ledger Entries, you also get the same trio for Document Date, Due Date, and every other date on the page.

The hierarchy is not the field you expand into. It is three fields you stack in Row Groups, and stacking them is what produces the expandable years, quarters, and months with subtotals at each level.

Pivot mode does what you expect. Toggle it on and a Column Labels area appears alongside Row Groups and Values. Rows down the side, labels across the top, sums in the middle. Same model as Excel PivotTable, deliberately.

Note: Columns that only have a few possible values are the best candidates for use in column Values.

It reaches into related tables. “Add columns from” option on the Analysis context menu lets you pull fields in from tables related to the page's source table, and group by them.

 

And you can export the definition as JSON. Not the data, the analysis itself: columns, filters, arrangement. Which means an analysis view can be packaged into an extension and shipped to a client rather than described in a training document.

Analysis mode needs execute permission on system object 9640, Allow Data Analysis mode, normally granted through the DATA ANALYSIS - EXEC permission set. Most full users have it. Team Member and other limited roles often do not, and permission sets built years ago certainly do not. When a client says the button is not there, check this first. Five minutes, fixes it for a whole department.

Two other reasons can be missing. Developers can switch it off per page with the AnalysisModeEnabled property, so if it is absent on exactly one page, go looking there. And analysis mode is not supported on lists that use indentation, which means Chart of Accounts and the G/L Account List do not have it at all. That is unfortunate, because the Chart of Accounts is the first place a finance person will try. Have the answer ready. 

Date hierarchies use the calendar year. Not your client's fiscal year.

The generated Year, Quarter, and Month fields are built on the normal calendar. They do not know about any fiscal calendar defined in Business Central.

If your client's fiscal year starts in April, their Q1 in analysis mode is January to March and their Q1 in every financial report they have ever run is from April to June. No errors. The numbers are correct for the calendar periods they describe. They just do not match the numbers in the meeting.

Also worth knowing: the hierarchy only generates for fields of type Date. Datetime fields do not get one.

Calculated fields are the ones computed on the page rather than read from the database: running totals, percentages, conditional counts. They stop displaying in two situations. When the list goes over 100,000 rows, and whenever you add fields from a related table.

Try it yourself

Ten minutes, and you will have something worth showing a client. This is roughly Microsoft's own aged receivables example with the sharp edges labelled.

1.      Open Customer Ledger Entries and choose Enter analysis mode.

2.      In the Columns pane, clear every column at once using the checkbox beside the Search field. Start from nothing.

3.      Turn on Pivot Mode.

4.      Drag Customer Name into Row Groups and Remaining Amount into Values.

5.      Drag Due Date Month into Column labels. Twelve columns, safely inside the cardinality limit.

6.      Use Analysis filters to narrow to one year. Note that this filter lives on the view, not the page, so your other views are untouched.

7.      Rename the view to Aged Accounts by Month.

Then, to see the hierarchy properly:

8.      Add a second view. Put Posting Date Year, Posting Date Quarter, and Posting Date Month into Row Groups in that order, and Remaining Amount into Values.

9.      Expand a year, then a quarter. Watch the subtotals appear at each level and the record count beside each group.

10.                             Use Copy link from the view's dropdown. In the dialog, look at the Company field: you can link to your current company or deliberately not link to any company at all. Recipients get prompted to name their own copy of the view.

How to See When Your SQL Server Backup or Restore Will Finish

 

If you've ever started a database backup or restore and then sat there wondering how much longer it'll take, you already know the problem. A large database can leave you staring at a spinning cursor with no idea whether you're waiting one more minute or another hour.

The good news is that when a backup or restore is running, SQL Server already knows roughly when it expects to finish. You just have to ask for it. This query does exactly that, and the column that matters most is estimated_completion_time.

Run this in a new query window while your backup or restore is going, and you'll get a row back for each active operation, along with a real clock time for when SQL Server expects to be done.

The script

SELECT
   session_id as SPID, command, a.text AS Query, start_time, percent_complete,
   dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

The star of the show: estimated_completion_time

This is the column that turns a nerve-wracking wait into something you can actually plan around.

Inside sys.dm_exec_requests, SQL Server exposes an estimated_completion_time value, but the raw number is the estimated milliseconds remaining. On its own, that's not very useful. Nobody wants to do mental math on "4,380,000 milliseconds" to figure out when they can go to lunch.

That's what this part of the query fixes:

dateadd(second, estimated_completion_time/1000, getdate())

It divides the milliseconds by 1000 to get seconds, then adds those seconds to the current time with getdate(). The result is an actual finish time, like 2026-07-23 14:52:10, instead of a raw count you have to interpret. You look at it and immediately know when the operation should wrap up.

The key thing is that this value is live. SQL Server recalculates it as the operation runs, so each time you execute the query you get a fresh estimate based on the work done so far. Run it, wait a bit, run it again, and you'll watch the projected finish time settle down as the operation progresses. That's your progress, right there in SSMS, without any external tool.

The percent_complete column tells you how far along you are; the estimated completion time tells you the one thing people actually care about, which is when this will be done.

You'll also need VIEW SERVER STATE permission to see this, since it reads from sys.dm_exec_requests and sys.dm_exec_sql_text. Most DBAs already have it, but a restricted login might not.

And if you want a running view, just keep hitting execute every few seconds. Watching that projected finish time creep closer is a lot less stressful than staring at a blank screen wondering if anything is happening at all.

Wrapping up

This is one of those small scripts worth keeping in your snippets folder. When you're running a backup or restore, it lets you see the progress directly in SQL Server and, more importantly, tells you when the operation will actually finish, so you can plan around it instead of guessing.

Approval Workflows for Item Journals and Requisition Worksheets

The Gap That Existed Until Now

If you've worked with Business Central approval workflows, you know the story. General journal batches had approval support baked in. But item journals? Requisition worksheets? Planning worksheets? Nothing. You either built custom workflows from scratch (not fun) or relied on trust and process discipline to make sure someone reviewed those entries before posting.

That meant inventory adjustments, consumption postings, and purchase document creation from requisition lines could happen without a second pair of eyes. And for organizations with audit requirements or segregation of duties policies, that was a real problem.

With BC28, Microsoft has closed this gap. Let's walk through what's new.

What's New in BC28:

Approval Workflows for Item Journals

Item journals now support the same batch-level approval workflow that general journal batches have had for a while. You can send a batch for approval before anyone can post it. This applies to four journal types:

 

Once a batch has an active approval request, the system locks it down. No edits, no deletes, no posting until the approval is completed or canceled. The standard approval actions (Approve, Reject, Delegate, Comments) show up right on the journal pages, along with the workflow status.

Approval Workflows for Requisition and Planning Worksheets

Requisition worksheets and planning worksheets now have full approval workflow support at the batch level. Before you carry out planning actions or convert requisition lines into purchase documents, the batch can go through an approval cycle.

When an approval request is active on a worksheet batch, the system prevents inserting, modifying, or deleting any requisition or planning lines. The workflow status is visible on the worksheet pages, and the same approval actions are available.

This is a big deal for procurement teams. Think about it: someone generates a planning run, the lines get reviewed and approved, and only then do they become purchase orders. No one accidentally carries out action messages on lines that haven't been vetted.

How It Behaves at Runtime

Here's what happens when a batch is pending approval:

If you try to modify a record that has an active approval request, BC will stop you with a message asking whether you want to cancel the approval first. It's the same pattern you already know from general journal approvals, just extended to new areas.

Setting It Up

Configuration follows the standard workflow setup pattern. No surprises here.

1.    Open the Workflows page (search for it using Tell Me).

2.    Select the Item Journal Batch Approval Workflow or Requisition Worksheet Batch Approval Workflow template.

3.    Specify the template and batch name in the event conditions.

4.    Configure your approvers and enable the workflow.

5.    Open the relevant journal or worksheet and choose Send for Approval.

6.    Track the status on the Approval Entries page.

These are standard workflow templates, which means they come ready to use. You're not building anything from scratch. Pick a template, point it at your batch, set up approvers, and go.

Power Automate Support

Microsoft has also updated the workflow events and responses to work with Power Automate. So if your organization uses Power Automate for approval routing or notifications, you can plug these new workflows into your existing flows. The integration follows the same pattern as other BC workflow events.

Allow Posting From/To DateFormula: No More Monthly Date Updates from Business Central v28

 Business Central 28 introduces DateFormula-based posting period fields that automatically calculate your allowed posting range. Here's what changed, how it works alongside the existing fields, and what the code actually does.

The Problem Everyone Knows

If you've worked with Business Central for any length of time, you know the drill. Every month (or every period close), someone has to go into General Ledger Setup and manually update the Allow Posting From and Allow Posting To date fields. Same thing on the User Setup page for individual users who need different posting windows.

 

Miss it, and users can't post. Update it late, and someone might accidentally post into a closed period. It's a small administrative task, but it adds up, and it's surprisingly easy to forget.

What's New in BC28

With Business Central 2026 Release Wave 1 (BC28), Microsoft has added two new fields to both the General Ledger Setup table (Table 98) and the User Setup table:

New Field	Field No.	Type	What It Does
Allow Posting From DateFormula	205	DateFormula	Calculates the start of the allowed posting range dynamically
Allow Posting To DateFormula	206	DateFormula	Calculates the end of the allowed posting range dynamically

These fields use the standard DateFormula data type, the same one you see in payment terms, lead time calculations, and other places across Business Central. The system takes the work date, applies your formula, and determines the allowed posting window automatically.

 

No more monthly manual updates. Set the formula once, and the posting window moves forward on its own.

The Key Rule: DateFormula and Date Fields Are Mutually Exclusive

This is the most important thing to understand about this feature. You can use either the static Date fields or the new DateFormula fields, but not both at the same time. The code enforces this automatically.

How the mutual exclusivity works

If you set a DateFormula value, the system automatically clears the corresponding static Date field (sets it to 0D). And if you set a static Date value, the system automatically clears the corresponding DateFormula field (evaluates it to an empty string).

When you enter a DateFormula (say <-CM>), the trigger checks if the formula is not empty. If it has a value that clears the static date field to blank. Then it validates the date range.

Same thing in reverse. When you enter a specific date (say 03/01/2026), the trigger checks if the new date is not blank. If it has a value, it clears the DateFormula field by evaluating it to an empty string. Clean and simple.

The same pattern applies to the "Allow Posting To" (Field 3) and "Allow Posting To DateFormula" (Field 206) pair. The logic is identical.

 

Bottom line: You don't need to worry about conflicts between the old and new fields. The system handles it. Fill in one, and the other gets cleared automatically. They can coexist on the same page, but only one of each pair will hold a value at any given time.

Practical Example: Setting It Up

Scenario: Monthly Posting Window

Your company wants users to only post within the current month. Previously, you'd set Allow Posting From = 03/01/2026 and Allow Posting To = 03/31/2026 in General Ledger Setup, then remember to change both fields when April starts.

 

Field	Value	Meaning
Allow Posting From DateFormula	<-CM>	Beginning of the current month
Allow Posting To DateFormula	<CM>	End of the current month

With today's date being March 13, 2026, the system calculates:

Allow Posting From = March 1, 2026 (CalcDate of <-CM> from today)

Allow Posting To = March 31, 2026 (CalcDate of <CM> from today)

When April starts, the window automatically becomes April 1 through April 30. No one needs to touch it.

What Happens When You Post Outside the Range?

The error behavior is exactly the same as before. Whether the posting range comes from a static date or a calculated DateFormula, if the posting date on your document falls outside that range, you get the familiar error.

Error

"Posting Date is not within your range of allowed posting dates ...."

Note: Same fields available on User Setup.

Who Keeps Moving My Business Central Work Date?

 Let me paint you a picture.

It's Monday morning. You fire up Business Central. You've set your Work Date to March 31st because you're doing month-end closing. Life is good. Then your session times out, or you refresh the browser, or you log back in after lunch and... your Work Date has changed to a completely different date. Not today. Not the date you set. Some other date entirely.

You set it again. Next login? Gone. Again.

"I could've sworn I set that to March 31st." Yeah, you did. BC just decided you were wrong.

The Crime Scene

After some digging (and a fair amount of yelling at my monitor), I tracked the problem to Codeunit 40 – LogInManagement, specifically a little procedure called GetDefaultWorkDate().

Here's what it looks like out of the box:

Codeunit 40 - LogInManagement.al

procedure GetDefaultWorkDate(): Date

See those highlighted lines? That's the culprit. For demo companies (Cronus, I'm looking at you), this procedure grabs the last G/L Entry's Posting Date and uses it as your default Work Date. Every single login. Every session refresh.

So whatever the Posting Date is on that last G/L Entry, that's your new Work Date now. Could be a date from three months ago. Could be a date in the future. Doesn't matter what you had it set to before. BC will keep resetting it to that G/L Entry date every time you log in or your session restarts. Like a toddler who keeps unbuckling their seatbelt.

But Wait, I'm Using a Demo Company for Real Work

And here's where it actually starts to hurt. A lot of consultants, partners, and even small businesses run on demo/evaluation environments that still have the IsDemoCompany() flag set to true. Maybe they started with Cronus and built on top of it. Maybe they're testing in a sandbox. Whatever the reason, they end up in this exact trap.

You're trying to do month-end, quarter-end, year-end... and every time your session refreshes or you log back in, BC pulls a "new phone, who dis?" on your Work Date.

The Fix

The solution is to subscribe to the OnAfterLogin event from Codeunit 150 "System Initialization". This event fires right after login, so we can step in and force the Work Date to today's date before the user even sees a page. Like civilized software should.

WorkDateFix.Codeunit.al

codeunit 50100 WorkDateHandler

{

    [EventSubscriber(ObjectType::Codeunit,

        Codeunit::"System Initialization",

        'OnAfterLogin',

        '', false, false)]

    local procedure SetWorkDateOnLogin()

    begin

        // Override the G/L Entry-based Work Date

        // and just use today's date like a normal person.

        WorkDate(Today);

    end;

}

That's it. That's the whole fix.

Wrapping Up

If you've ever logged into a Cronus or demo company and wondered why your Work Date keeps changing to a date you didn't set, now you know. It's not you. It's GetDefaultWorkDate() pulling the Posting Date from the last G/L Entry every time your session initializes, with all the subtlety of a sledgehammer.

Drop that event subscriber into your extension, and you'll never have to fight this particular battle again. 

Create Purchase Orders from Drop Shipments

If you have worked with drop shipments in Business Central, you know the process has always been a bit... indirect. You would create a sales order, mark lines for drop shipment, and then separately create a purchase order and use the Get Sales Orders action to pull those lines in. It worked, but it was not exactly smooth, especially when you were juggling multiple orders.

With Business Central 2026 Release Wave 1 (BC28), Microsoft has simplified this workflow. You can now create purchase orders directly from the sales order itself using the Create Purchase Orders action. On top of that, drop shipment lines are now included in the Order Planning page when you calculate plans, and the Planning Worksheet page gets a dedicated Drop Shipment action group.

What Exactly Changed?

1. Create Purchase Orders Directly from Sales Orders

Previously, to create a purchase order for a drop shipment, you had to leave the sales order, go to the purchase side, and manually pull in the sales lines. Now there is a Create Purchase Orders action right on the sales order. Business Central looks at your drop shipment lines, figures out the vendor from the Item Card or Stockkeeping Unit Card, and suggests the purchase order lines for you. You review, tweak if needed, and you are done. 

2. Drop Shipments Included in Order Planning

The Order Planning page now considers drop shipment lines when you run Calculate Plan. Before this update, drop shipment demand was essentially invisible to the planning engine. Now it shows up alongside your other demand, which gives you a more complete picture of what you need to order and when.

3. New Drop Shipment Actions in Planning Worksheet

The Planning Worksheet page now includes a dedicated Drop Shipment action group with Get Sales Orders and Sales Orders actions. This mirrors what the requisition worksheet already had, bringing consistency across the planning tools.

Tip: Vendor Determination Business Central picks the vendor from the Item Card's Vendor No. field first. If you use Stockkeeping Units (SKUs), the vendor configured on the SKU for that location takes priority. You can always override it on the Create Purchase Orders page before confirming.

Before vs. After: Quick Comparison

 

Area	Before (BC27 and earlier)	After (BC28)
Creating POs from Sales Orders	Create PO separately, then use Get Sales Orders from the purchase order	Create Purchase Orders action directly on the sales order
Order Planning	Drop shipment lines not included in Calculate Plan	Drop shipment lines are included when calculating plans
Planning Worksheet	No dedicated drop shipment actions; only available in requisition worksheet	New Drop Shipment action group with Get Sales Orders and Sales Orders actions

Define Item Attributes for Item Variants

The public preview for Dynamics 365 Business Central 2026 release wave 1 (BC28) is now available, and there’s a feature in this release that many of us in the BC community have been waiting for. You can now define item attributes at the item variant level. It sounds simple, but if you’ve ever worked with variants in a real-world scenario, you know how much this changes things.

Let me walk you through what this feature does, how it works, and why it matters.

The Problem This Solves

Item variants have been a great way to keep your product list manageable in Business Central. Instead of creating a separate item for every color, size, or material variation, you set up one item and define the variants underneath it. A t-shirt in five colors? One item, five variants. Much cleaner than five separate item cards.

But here’s where it used to fall short: item attributes. Until now, attributes were only defined at the item level. So if your base t-shirt had an attribute of “Color: Blue,” all your variants inherited that same value. The red variant? Also said “Blue.” Not ideal.

You either had to live with inaccurate attribute data on your variants, or you gave up on variants entirely and created separate items just so each one could have the right attribute values. Neither option was great.

What’s New in BC28

With this release, you can assign attribute values that reflect the specific variant at the item variant level. Variants still inherit attributes from the parent item, but you can now adjust or remove those inherited values when a variant needs different information.

Here’s what that looks like in practice:

Key Capabilities: •      Assign attribute values directly to individual item variants •      Variants inherit attributes from the parent item automatically •      Adjust or remove inherited values per variant as needed •      New Item Variant Attribute Values page accessible from the Variants List or Variant Card •      New Update Variant Attributes action on the Item Card to force sync from item to variants

How It Works

Attribute Inheritance

When you add a new variant to an item, the attributes defined for that item automatically transfer to the variant. This is the inheritance part. If you’ve already set up attributes like Material, Weight, or Color on the parent item, your new variant gets all of those right away.

One thing to note: if no attributes are defined on the parent item, the variants will also be empty. The inheritance only works when there’s something to inherit.

Editing Variant-Specific Attributes

Once the attributes are inherited, you can open the new Item Variant Attribute Values page to review and edit them. You can get to this page from either the Item Variants List or the Item Variant Card. From there, you update or delete the inherited values so each variant shows the correct details.

So back to our t-shirt example: your parent item might have Color set to “Blue” (the default). When you create the “Red” variant, it inherits “Blue,” but you can immediately go in and change it to “Red.” Simple, clean, accurate.

Syncing Attributes with Update Variant Attributes

There’s also a new Update Variant Attributes action available directly from the Item Card. This lets you force a sync of attributes from the item down to all its variants. It’s useful when you’ve added or changed attributes on the parent item and want to push those changes out to existing variants without going through them one by one.