Skip to content
Knowledge base

How do I build my own reports on the imos iX database with SQL Server Report Builder?

Use Microsoft's free Report Builder to create your own lists on the imos database: edge banding overview, drawers or materials per order, as PDF or Excel.

Short answer: Report Builder is a free Microsoft tool for designing reports on a SQL Server database. Connect it to the imos database with an account that can only read, write a query with an order parameter for each report and place the result in a table. Then print it or export it to PDF or Excel.

Information

Applies to

  • imos iX on a Microsoft SQL Server database; applied in iX 2019 up to and including iX 2025
  • Areas: order data in the database (parts, edge banding, hardware, CNC programs), material master data, reports in the Document Manager
  • Software: Microsoft Report Builder, SQL Server Management Studio for testing queries
  • Last reviewed: October 2026

Step 1: Install Report Builder and set up a read-only account

  1. Download Report Builder free of charge from Microsoft and install it on the PC of whoever builds the reports.
  2. Ask your system administrator to create a separate SQL Server login with read-only access to the imos database (the db_datareader database role).
  3. Use that login for all your reports, never the admin account imos itself runs on.
  4. Test queries in SQL Server Management Studio first; you see straight away which rows they return.

Step 2: Create the data source and dataset

  1. In Report Builder, create a Microsoft SQL Server data source with your server and database, for example SRV-SQL and IMOS_DEMO, and the read-only login.
  2. Create a dataset with a query. Start small: one table, a few columns, one order.
  3. Always join tables on both ID and order name. Join on ID alone and you will pull in parts from other orders.

The tables we use most in practice:

  • PROADMIN: order administration, with the internal order ID (ID) and the order name (NAME).
  • IDBGPL: the parts list per order, with order name (ORDERID), description (NAME1), material (MATID), finished sizes (FLENG, FWIDTH, FTHK), cutting sizes (CLENG, CWIDTH) and quantity (CNT).
  • MAT: material master data such as thickness (THK), description and order number (BESTELLUNG). Join via IDBGPL.MATID = MAT.NAME.
  • IDBPRF: edge banding per panel, with edge code (PRFID), banding name (PRFNAME) and height (PRFHGT).
  • IDBGRPS and IDBINFO: the link from part to cabinet, including the position number (POSSTR).
  • IDBPURCH: purchased parts and hardware per order.
  • IDBCAMPROGRAM and IDBCAMATTRVALUE: CNC programs per panel, including program name and machine.

Step 3: Add an order parameter

  1. Add a parameter to the report. We call it ORDERLIST: one or more internal order IDs, separated by commas.
  2. In the query, look up the order name for that ID in PROADMIN, for example SELECT NAME FROM PROADMIN WHERE ID = @ORDERLIST.
  3. Filter every table you use on that order name, for example WHERE IDBGPL.ORDERID = @ORDERNAME.
  4. Test the report with the ID of a test order. You find that ID in PROADMIN next to the order name.

Step 4: Layout and export

  1. Place the fields in a table or matrix and group them, for example by material, edge code or position number.
  2. Use expressions for filters and calculations. A filter that excludes lines whose description starts with Extra: =IIf(NOT(Fields!DESCRIPTION1.Value LIKE "Extra*"), "INCLUDE", "EXCLUDE"), with INCLUDE as the filter value.
  3. Extending an existing report? Work on a copy and take over only the group you need.
  4. Export to PDF for the workshop or to Excel for purchasing and costing. For Excel, put the data in its own clean table without merged cells.

Examples of useful reports

  • Edge banding overview: per edge code and height, the banding name and the panel numbers it is applied to, leaving out the empty edge code. Useful for the edgebander and for purchasing.
  • Material overview: materials per group (carcass, front, plinth, worktop, drawers) with thickness, description and order number.
  • Drawer report: per cabinet position the drawer type, operation, height, width, colour and quantity, derived from the connector names.
  • Hardware and purchasing list: all purchased parts per order, optionally with images.
  • CNC and barcode list: per panel the program names of program 1 and 2.

Why this approach?

Standard imos lists don't always fit how your workshop or purchasing works. A custom report reads directly from the database, so it is always up to date, with no exporting or retyping. In practice, small targeted lists pay off most: one overview for the edgebander, one for drawers, one for purchasing.

We only read, because the imos database is the heart of your production preparation. A mistake in a report means a wrong list at worst; a mistake in a writing query affects every order.

Frequently asked questions

Do I need a Reporting Services server?

Not to get started. You can save a report as a file and run it in Report Builder. A server is only needed if you want to publish reports centrally.

Will my reports still work after an imos update?

Usually, but the database structure can change between versions. After every update, test your reports on a test database first.

Can I run a report from within imos?

Yes. Reports set up in the Document Manager are started from imos. How that is configured differs per installation; we are happy to go through it with you.

Further reading

More than one solution

Almost every question in this knowledge base has more than one solution. The best fit depends on your workflow, machinery and integrations. Not sure which applies to you, or are you using software we don't (yet) support? Feel free to email Wood Intelligence. We're happy to think along with any challenge.

imos is a trademark of imos AG. Wood Intelligence is an independent consultancy.

Download access

Sign in to check whether this download is available.