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.
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
- Download Report Builder free of charge from Microsoft and install it on the PC of whoever builds the reports.
- Ask your system administrator to create a separate SQL Server login with read-only access to the imos database (the
db_datareaderdatabase role). - Use that login for all your reports, never the admin account imos itself runs on.
- Test queries in SQL Server Management Studio first; you see straight away which rows they return.
Never write to the imos database. An UPDATE or DELETE takes effect immediately for every designer and every order, and cannot be undone without a backup. A report only reads. Change master data in imos itself.
Step 2: Create the data source and dataset
- In Report Builder, create a Microsoft SQL Server data source with your server and database, for example
SRV-SQLandIMOS_DEMO, and the read-only login. - Create a dataset with a query. Start small: one table, a few columns, one order.
- 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 viaIDBGPL.MATID = MAT.NAME.IDBPRF: edge banding per panel, with edge code (PRFID), banding name (PRFNAME) and height (PRFHGT).IDBGRPSandIDBINFO: the link from part to cabinet, including the position number (POSSTR).IDBPURCH: purchased parts and hardware per order.IDBCAMPROGRAMandIDBCAMATTRVALUE: CNC programs per panel, including program name and machine.
Step 3: Add an order parameter
- Add a parameter to the report. We call it
ORDERLIST: one or more internal order IDs, separated by commas. - In the query, look up the order name for that ID in
PROADMIN, for exampleSELECT NAME FROM PROADMIN WHERE ID = @ORDERLIST. - Filter every table you use on that order name, for example
WHERE IDBGPL.ORDERID = @ORDERNAME. - Test the report with the ID of a test order. You find that ID in
PROADMINnext to the order name.
Step 4: Layout and export
- Place the fields in a table or matrix and group them, for example by material, edge code or position number.
- 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"), withINCLUDEas the filter value. - Extending an existing report? Work on a copy and take over only the group you need.
- 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.
Common mistakes
- A report account with write access. One wrong statement is enough to damage every order.
- Joining without the order name. IDs recur across orders, so the report shows parts from other orders.
- No order filter on large tables. CAM tables can hold millions of rows. An unfiltered query takes a long time and loads the server your designers work on.
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
- Back up and restore the imos iX database
- A test database alongside production in imos iX
- Order and drawing numbers for unique CNC programs
- imos iX consultancy
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.