Business Central Item Attributes in Excel: 2 Ways

By Thomas Werkhoven, CEO of Exsion365, registered accountant, previously group controller and CFO.

You want a sales report by item with three columns from the item attributes: origin, packaging type, trade category. You add the Item table to your Excel query, look for the attribute fields, and they are not there. That is the moment most Business Central users discover that item attributes are a separate structure, and the moment the report either gets a second tab full of XLOOKUPs or a proper join. This post explains both, with a real build from a customer session, and an honest word on what each one costs.

The short answer

Business Central stores item attributes in four tables. Item Attribute (7500) holds the attribute definitions, Item Attribute Value (7501) holds the allowed values, and Item Attribute Value Mapping (7505) links an item to a value by item number, attribute ID and value ID. To show an attribute next to an item in Excel you have to go from the item to the mapping row for that attribute, then from the mapping row to the value.

There are two ways to do that. Pattern A downloads the mapping and the values to a lookup tab and uses XLOOKUP on the report sheet; it is quick to build and easy to break. Pattern B does both hops inside the query as an expression column, so the value arrives in the same row as the item; it is more robust and easier to hand over, and in our measurement it was not dramatically faster. Choose by who maintains the report and how many attributes you need, not by the stopwatch.

Why item attributes are awkward to report on

Microsoft designed item attributes to be flexible: any number of attributes, each with its own type and list of values, without adding fields to the Item table. Microsoft explains the setup in Work with Item Attributes. The price of that flexibility is that the value you see on the item card ("Origin: Netherlands") is assembled from three records at display time.

In the mapping table, each row says: this item, this attribute ID, this value ID. The value ID points at a row in Item Attribute Value, which carries the text. The attribute ID points at Item Attribute, which carries the name. So a report that wants "Origin" for every item has to filter the mapping on attribute ID (say, 40), pick up the value ID per item, and look up the text. For one attribute that is two lookups per item. For 35 attributes, which is what the customer in our session has, it is 70.

Pattern A: the lookup tab

The pattern most people arrive at on their own, and the one the customer had been using, works like this. One query downloads Item Attribute Value Mapping, filtered to the attribute IDs you need, into a separate tab. A second query downloads Item Attribute Value into another tab. On the report sheet, one XLOOKUP per attribute finds the value ID for the item and attribute, and a second XLOOKUP turns the value ID into text. Concatenate the item number and the attribute ID to make a key, and it works.

It is genuinely quick to build, it is transparent to anyone who reads Excel, and for a handful of attributes on a report you maintain yourself it is fine. The customer's application manager put it well: he had learned to keep those lookups on a separate tab precisely because nesting them into the main query had felt slower.

The problems come with time. The mapping tab is downloaded whole, so it grows with the item master. The XLOOKUPs live on the sheet, where any user can overwrite one, and the person who breaks it rarely knows. And when a controller opens the report and the output range has shifted by a row, every lookup on the sheet is off by one and nobody notices until a number looks wrong in a meeting.

Pattern B: one expression column in the query

Exsion Reporting lets you add an expression column to a query: a column whose value is computed per row, using Exsion's standard formulas or your own formulas against any table or field in the customer environment, custom tables included. An expression can look up a value in another Business Central table using values from the current row, and that is what makes the two hops possible inside one query, so the attribute text arrives in the same row as the item.

Nicky Oudt, Senior Reporting Consultant at Exsion365, built it live on the customer's report in a screen share. Three steps.

Step 1: lookup on the mapping. Add an expression column to the Item query. The expression looks up Item Attribute Value Mapping where Item No. equals the current row's No. and Item Attribute ID equals 40 (the attribute you want), and returns the Item Attribute Value ID. Refresh, and the value IDs appear next to the item numbers within seconds.

Step 2: lookup on the value. Add a second expression column that looks up Item Attribute Value where the Attribute ID is 40 and the ID equals the value ID from step 1, and returns the Value field. Refresh again. The attribute text now sits next to the item number, and nothing on the worksheet had to be touched.

Step 3: go one further if you need to. In the customer's case, the attribute value was a code that pointed at a trade type, and the report needed a field from that trade type. A third expression takes the code from step 2 and looks up the next table. The pattern is the same each time: the output of one expression is the input of the next.

One ordering detail is worth knowing before you build it: the expressions are processed from top to bottom, so the row that fetches the value ID has to sit above the row that turns it into text. Get that the wrong way round and the second expression has nothing to work with.

Chaining: using the output of one expression as the input of the next

This is the part the customer had actually come to ask about, because it solves a bigger problem than attributes. They run track and trace in a food business: a code comes in, the first step determines the item, and from the item the report has to go deeper into lots and receipts. They had wanted to run part one, take its output, and feed it into part two as a parameter. An expression column does exactly that inside one query, row by row, so there is no second query, no manual copy of a result into a filter cell, and no macro.

A colleague of the application manager had tried to build this a year earlier and had not got it working, because the first expression's output has to be referenced by name in the second one, and that is not obvious from the dialog. It took Nicky a few minutes on a screen share. If you are trying it alone and get stuck on the reference, that is what a support session is for.

What it does to refresh time

Here is the honest part. The customer's original item report, with the lookup tab, refreshed in about six seconds. With both hops moved into expression columns, the report refreshed in a similar time; Nicky's own words on the call were that it was "no mega win". We did not run a stopwatch, so treat this as an observation rather than a benchmark. The reason is that each expression still asks Business Central for a row per item, so the request count per attribute is comparable to the download-and-lookup approach.

The win is not the stopwatch. It is that the logic lives in the query definition, where it is readable, where it is protected from a stray keystroke on the sheet, and where a colleague can see exactly what was pulled. If someone deletes the output range by accident, a single refresh restores the whole thing. With 35 XLOOKUPs on a sheet, that is not true. We covered why a readable definition matters in a Business Central Excel report should explain itself.

Which one to choose

The trade-off depends less on speed than on who opens the report after you.

[[TABLE_START]]

Situation | Pattern A: lookup tab | Pattern B: expression column

Three attributes, you maintain the report yourself | Fine | Fine, slightly more setup

Report handed to 20 users who start from a read-only original | Breaks when a copy drifts | Survives; refresh restores it

Report is scheduled overnight and distributed | Works, but lookup tabs bloat the file | Cleaner file, definition only

You need the output of one step as the input of the next | Two queries and a manual filter | One query, chained expressions

35 attributes on a large item master | Mapping tab downloaded whole; many lookups | Test growth first

The report is slow on a busy Monday | Splitting master data helps either way | Same, plus the read-only connection setting

[[TABLE_END]]

If the report is one you built for yourself and nobody else opens it, the lookup tab is the pragmatic choice. The moment a report is shared, scheduled or inherited, the expression column is the one you will thank yourself for.

Thirty-five attributes

The customer's question at the end of the session was the right one: if the two-step block works for one attribute, do I copy it 35 times? You can, and the query will still return one row per item with 35 attribute columns, which is a much nicer starting point for a pivot than 35 lookup pairs. Nicky's advice was to copy the block once or twice first and measure, to see whether the refresh time grows in proportion or faster. There is also a ceiling on how many functions one expression column can hold, so 35 attributes is a case for several columns rather than one long expression.

Two habits keep it manageable. First, attributes are master data; they change when someone edits an item card, not when someone posts a sale. Put the item-with-attributes query in its own block, refresh it once a day (or once a night, scheduled), and let the sales or ledger query refresh on demand. That is the same static-versus-transaction split we recommend in Business Central Excel reports slow on Monday morning.

Second, once the item block returns a clean row per item, join it to the transaction query on Item No. inside the definition rather than on the sheet; the mechanics of that join are in how to join Business Central tables in Excel.

Learn the expressions properly

Nicky does this three hundred times a day, so a three-step build took him a few minutes. The customer's team had been carrying a report a colleague of ours built for them years ago, extending it carefully and avoiding nesting because nesting had felt slow. Both are reasonable. But there was an hour's worth of tips and tricks in that session that would have saved them a year of workarounds, which is why the session ended with an agreement to spend half a day on site walking through the report library.

If you run Exsion Reporting and have a shelf of inherited reports, collect the questions and book that half day. If you are evaluating tools and item attributes are on your list of things that have to work, install the 30-day trial from AppSource, open the Item table, and try the three steps above on attribute ID 40, or whichever one your business lives on. Pricing is on the pricing page.

Frequently asked questions

[[FAQ_START]]

Where are item attributes stored in Business Central? | Not on the Item table. Item Attribute holds the definitions, Item Attribute Value holds the allowed values, and Item Attribute Value Mapping links each item to a value by Item No., Item Attribute ID and Item Attribute Value ID.

How do I get Business Central item attributes into Excel? | Either download the mapping and the values to a lookup tab and use XLOOKUP on the sheet, or add an expression column to the item query that looks up the mapping by item and attribute ID and then the value by value ID.

Is an expression column faster than XLOOKUP for item attributes? | Not dramatically. In a customer build, a six-second item report refreshed in a similar time, because each expression still requests a row per item. The advantage is robustness, not speed.

Can I use the output of one lookup as the input of the next inside one query? | Yes. Expression columns can reference each other. They are processed from top to bottom, so the one that produces a value has to sit above the one that consumes it.

How do I report on 35 item attributes without a slow refresh? | Keep the item-with-attributes query in its own block and refresh it once a day, since attributes are master data. Test the refresh time after copying the expression block once or twice before building all 35.

Why does my item attribute report break when a colleague opens a copy? | Lookup formulas on the sheet depend on the output range staying exactly where it was. A shifted row or an overwritten cell breaks every lookup silently. Keep a read-only original, or move the lookups into the query.

[[FAQ_END]]