Business Central dimensions and analysis views to compare stores

Analytics · 8 min read

Two stores, same chain, same month — which one actually made money? Business Central can answer that from posted data, but only if two things were set up before the sales were posted: a dimension that identifies the store, and an analysis view that turns that dimension into a side-by-side matrix. This guide builds both, in order.

Note: store comparisons feed real decisions about staffing, rent, and closures. Treat the totals here as a starting point for review with whoever owns the books, not as financial statements. Where a total matters, reconcile it back to the general ledger before acting on it.

Why a store dimension, not more G/L accounts

Dimensions are values that categorize entries so they can be tracked and analyzed on documents such as sales orders. Microsoft's guidance is explicit about the alternative to avoid: instead of setting up separate general ledger accounts for each department and project, use dimensions as the basis for analysis and avoid a complicated chart of accounts. A retail version of the same rule applies — one sales account plus a STORE dimension scales to a third or tenth store; a separate sales account per store does not.

A single sales entry can also carry information from several dimensions at once: the account the sale was posted to, where the item was sold, who sold it, and which customer bought it. STORE answers the “where” part. That only works if the value is actually present on the entry — which is why setup order matters more than the matrix at the end.

Step 1: create the STORE dimension and its values

1. Open the Dimensions page. Use Search (Alt+Q), enter Dimensions, and choose the related link. Dimensions and their values are set up here, one line for each dimension.
2. Add a dimension with code STORE. Keep the code short and stable — it will appear on entries, filters, and analysis views for years.
3. Add one dimension value per physical store (for example, one value for each location that sells). Dimension values can be set up in a hierarchical structure similar to the chart of accounts, and subsets of values can be totaled — useful if stores later group into regions, with region values as totals over the stores beneath them.
4. Decide now, not later. Business Central allows as many dimensions and dimension values as needed, so there is no technical pressure to skimp — the discipline is operational: every store gets exactly one value, and closed stores keep their value so history stays readable.

What success looks like: the Dimensions page shows STORE with a value for every selling location, and no two values describe the same store.

Step 2: make STORE easy to pick and hard to forget

An analysis view cannot report a dimension that postings never carried. Three documented mechanisms get the value onto entries:

• Make it a shortcut dimension. Global and shortcut dimensions are defined on the General Ledger Setup page. Shortcut dimensions are available as fields on journals, document lines, and ledger entries, so staff can see and set STORE without opening the Dimensions page. The limits are real: only two global dimensions exist, and up to eight shortcut dimensions. Choose carefully — changing a global or shortcut dimension later updates every entry posted with it, can be time-consuming, can affect performance, and can lock tables.
• Set default dimensions. A default dimension assigned to an account is copied onto a journal or document when the account number is entered on a line, and a default for an individual account replaces the default for its account type. Account-type defaults are set from the Dimensions page with the Account Type Default Dim action. To require the dimension without pre-filling a value, leave the Dimension Value Code blank and set Value Posting to Code Mandatory.
• Resolve conflicts with priorities. When a customer, an item, and a location each propose a different default, Default Dimension Priorities (set up per source code) decides which source wins. If two tables tie on priority for the same source code, Business Central selects the table with the lowest table ID — a tie-break worth knowing before debugging a “wrong store” posting.

One warning that matters in retail: if an account is used by the Adjust Exchange Rates or Post Inventory Cost to G/L batch jobs, do not set Code Mandatory or Same Code on it — those batch jobs cannot use dimension codes. Forcing STORE onto a cost-of-sales account in that way can break the very posting that creates the cost side of a store margin. Use defaults and priorities for cost accounts instead, and test the inventory-cost posting in a sandbox company first.

Step 3: build the analysis view for sales and margin

An analysis by dimensions uses a selected combination of dimensions, stored and updated as an Analysis View card. Build one view dedicated to the store comparison:

1. Create the view. Search for Analysis Views, choose New on the Analysis View List page, and give the view a clear code and name (for example, a code that says store comparison, not a person's initials).
2. Set the account source and filter. The Account Source on an analysis view can be G/L Account or Cash Flow Account; choose G/L Account. Use the Account Filter to include the sales accounts and the cost-of-sales accounts that together make up store margin — a single account, several accounts, or a range.
3. Add STORE on the Dimensions FastTab. The card carries four dimension slots; put STORE in one of them. (Additional dimension codes beyond the four can be added with the Filter action.) Add a second dimension only if there is a question for it — for example a department dimension, if departments exist inside stores.
4. Set date compression deliberately. Date Compression sets the level of detail, such as Month instead of Day. Month matches how store performance is normally reviewed, and the matrix cannot later show a period shorter than the compression chosen on the card.
5. Choose an update strategy. Leave Update on Posting off for a first build and update manually, or select it so the view updates automatically when an associated transaction posts. Several views can also be refreshed together with the Update Analysis Views batch job. Budget figures are optional: an analysis view can include budgets so budget entries update alongside the view, which works with G/L Account sources.
6. Run Update. Choose the Update action to populate the view from posted entries. For large data sets, community guidance is to schedule that first population outside busy hours.

What success looks like: the view exists, names STORE as a dimension, covers exactly the sales and cost accounts intended, and has been updated at least once.

Step 4: run Analysis by Dimensions and read the matrix

1. Search for Analysis Views, select the store view, and choose the Analysis by Dimensions action.
2. Set Show as Lines to G/L Account and Show as Columns to the STORE dimension, then choose Show Matrix. In the matrix, the left columns follow Show as Lines and the right columns follow Show as Columns — accounts down the side, one column per store.
3. To flip the view (stores down the side, accounts across), use Reverse Lines and Columns instead of rebuilding anything.
4. Choose any amount in the matrix to see the details behind it. A store total that looks wrong should always be drilled into before it is discussed.

Margin comes from reading the two account groups together for the same store column: sales accounts total, cost-of-sales accounts total, and the difference. The matrix shows G/L amounts by dimension; it does not invent a margin row, so keep the account filter tight enough that “sales minus cost” is an honest reading of what is on screen.

Worked example: reading margin store by store (hypothetical figures)

The figures below are a hypothetical worked example, invented to show the arithmetic — they are not real store results or benchmarks.

The decision this view supports is narrow and useful: Store South sold less and kept less of each sale. That points the follow-up at cost and mix in one store — not at a chain-wide problem, and not at the store with the lower sales alone. Percentages here are simple arithmetic on the two totals (difference divided by sales); confirm the underlying totals against the ledger before using them in any lease, staffing, or closure discussion.

When the numbers look wrong: update, then check the values

Work through these in order before doubting the setup:

1. Update the view. The amounts on the Analysis by Dimensions page are a picture of the company at the time of the last update. If nobody updated since yesterday's posting run, the matrix is honestly showing yesterday. Choose Update and read it again.
2. Check the dimension values on the posted entries. Microsoft documents that an incorrect dimension value used on posted G/L entries can be corrected, after which the analysis views are updated. A sale posted to the wrong store is a data correction, not a report redesign.
3. Check the compression. A matrix that refuses a weekly or daily view is usually obeying the Date Compression on the card; it cannot show a period shorter than that setting.
4. Spot-check with a quick filter. The Set Dimension Filter action filters totals by dimensions in the chart of accounts and on entries pages — a fast way to confirm one store's total for one account before trusting the full matrix.

For a paper trail instead of a screen, the Dimensions - Total report shows only total amounts by dimension, while Dimensions - Detail shows the detailed classification of how dimensions were used on entries over a selected period. Both work from an analysis view, so they inherit its account filter and dimensions.

Analysis view, quick filter, or financial report: which tool for which question

The practical sequence most stores end up with: quick filter for a sanity check, one analysis view for the monthly store comparison, and a financial report only when the same numbers need a formal, repeatable layout. Building the analysis view first is what makes the other two cheap. (For the close process these totals feed into, see the end-of-day routine.)

FAQ

Can the matrix show a period shorter than a month? Only if the analysis view's date compression allows it — a period length shorter than the compression on the Analysis View card cannot be selected.

Why is a store column empty? Work the checklist above: update the view, then verify that postings for that store actually carry the STORE value — defaults and priorities only help entries posted after they were set up.

How many dimensions can the view use? The card shows four dimension slots on its Dimensions FastTab; more dimension codes can be added through the Filter action.

Does the view update itself? Only if Update on Posting is selected on the card. Otherwise the displayed amounts are as of the last manual Update or the last run of the Update Analysis Views batch job.