Jump to content

BIM Report

From FreeCAD Documentation

BIM Report

Menu location
Manage → Report Tools → BIM Report
Workbenches
BIM
Default shortcut
None
Introduced in version
1.2
See also
None

Description[edit | edit source]

The BIM Report is a tool for generating schedules and data reports from a FreeCAD model. It uses an SQL-like query language to select, filter, and aggregate data, offering a declarative alternative to other scheduling tools. Ultimately, it enables users to create and automatically populate a spreadsheet with data gathered from the model.

You might choose the BIM Report tool when you need to perform:

  • Complex filtering: Select objects based on a combination of properties, including nested data (e.g., an object's placement coordinates).
  • Data aggregation: Calculate totals, counts, minimums, or maximums across many objects (e.g., find the total area of all "Space" objects).
  • Hierarchical queries: Select objects based on their relationship to other objects (e.g., find all windows hosted in walls on a specific floor).

Usage[edit | edit source]

  1. Open or create a FreeCAD document which contains some objects.
  2. There are several ways to invoke the tool:
    • Press the BIM Report button.
    • Select the Manage → Report Tools → BIM Report option from the menu.
  3. The new Report will open its Task Panel automatically. Create one or more statements for the report. You can optionally use the provided statement editor to see a preview of executing those statements.
  4. Press OK to save the report and refresh the associated spreadsheet with the query results.

User interface[edit | edit source]

The BIM Report system consists of three main components: the Report object in the Tree View, the Target Spreadsheet for the results, and the Task Panel where you configure the report.

When you create a new report, you will see two new objects in the Tree View: the Report object, which stores all your settings, and its associated ReportResult spreadsheet.

Double-clicking the DataReport object opens the Task Panel, which is the primary workspace for editing your report. The panel is divided into two main areas: the Report Statements and the Statement Editor.

The BIM Report Task Panel, showing the Statements Overview section at the top.

Report Statements[edit | edit source]

This section is always visible at the top of the Task Panel and gives you a summary of all the queries (or "statements") that make up your report.

The main feature is a table listing each statement. From here, you can see and directly edit the Description, and toggle key options for each query:

  • Pipe: Enables pipeline mode for that statement, using the output of the previous one as its input.
  • Header: Uses the statement's description as a merged section header in the spreadsheet.
  • Cols: Includes the column names (e.g., Label, Area) as a header row for that statement's results.

Double-clicking on a statement row will open the statement for editing. Pressing F2 on a statement row enables in-place editing of the statement description without loading the full editor.

Below the table are buttons to Add, Remove, Duplicate, or Edit the selected statement.

Finally, the Report Templates dropdown menu allows you to load complete, pre-configured reports, such as a "Room and Area Schedule".

Statement Editor[edit | edit source]

This section appears at the bottom of the Task Panel when you select a statement and click Edit Selected. This is where you write and configure an individual SQL query.

The Statement Editor, showing the SQL editor, preview pane, and display options.
  • The SQL Query Editor is a text box with syntax highlighting. Autocompletion for SQL keywords and property names from your model is automatically enabled. Use the and keys as needed, and press the Tab to apply the chosen suggestion.
  • Below the editor, a Live Validation status label gives you immediate feedback, indicating if your syntax is valid and showing a live count of matching objects.
  • The Show Preview button will run the current query on-demand and display the full data results in a temporary table, allowing you to test your query without closing the editor.
  • The Help button opens a dialog with a "cheatsheet" of all supported SQL clauses and functions.
  • The Display Options group contains checkboxes for controlling the final formatting in the spreadsheet:
    • Use as Pipeline Step: When checked, this statement will use the results of the previous statement as its data source instead of querying the entire document. This is the key control for enabling pipeline workflows.
    • Use Description as Section Header: When checked, the statement's description (e.g., "Room List") will be written as a merged, centered header row in the spreadsheet before its data results.
    • Include Column Names: This option, enabled by default, includes the column headers (e.g., Label, Area) in the spreadsheet output for this statement.
    • Add Empty Row After: When checked, a blank row will be inserted after this statement's results, providing visual separation in a multi-section report.
    • Print Results in Bold: Renders the data cells for this statement's results in a bold font for emphasis.
  • The Query Presets dropdown allows you to load and save individual, commonly used SQL queries.

The Save button saves the modifications to the loaded statement. When the Save and Next checkbox is ticked and the Save button is pressed, in addition to the save operation a new statement will be created for a more streamlined edition experience. The ❌ Discard button closes the statement editor without saving any changes made.

Workflow[edit | edit source]

This small tutorial guides you through creating a complete room schedule, from a simple list to a multi-part report with calculated totals.

Step 1: New report[edit | edit source]

  1. Click the BIM Report button in the BIM workbench toolbar.
  2. FreeCAD will create a Report object and a ReportResult spreadsheet in the Tree View and automatically open the Task Panel.

Step 2: Simple statement[edit | edit source]

First, let's create a simple list of all spaces and their areas.

  1. In the Report Statements, click the Add Statement button. A new, empty statement will appear in the list.
  2. With the new statement selected, click the Edit Selected button. The Statement Editor will appear at the bottom of the Task Panel.
  3. In the Description field, type Room List.
  4. In the SQL Query editor, enter the following query. This selects the name and area of all objects that are Spaces and sorts them alphabetically.
    SELECT Label, Area FROM document WHERE IfcType = 'Space' ORDER BY Label ASC
    
  5. Click the Save button in the editor to commit your changes to the statement.

Step 3: Multi-section report[edit | edit source]

Now, let's add a second statement to calculate the total area.

  1. In the overview, click Add Statement again to create a second statement.
  2. With this new statement selected, click Edit Selected.
  3. Set the Description to Total Area.
  4. In the SQL Query editor, enter this aggregation query:
    SELECT 'Total Usable Area', SUM(Area) FROM document WHERE IfcType = 'Space'
    
  5. Under Display Options, uncheck Include Column Names (since our first column is a custom label) and check Print Results in Bold for emphasis.
  6. Click Save. You now have a two-statement report. Click the main OK button at the bottom of the Task Panel to execute it. The associated spreadsheet will open automatically with the formatted results.

Step 4: Pipelining for advanced queries[edit | edit source]

Pipelining allows you to chain queries together, where the output of one becomes the input for the next. This is essential for complex hierarchical selections, such as when using the CHILDREN() function. Since it is not recursive, it only finds direct descendants.

For example, to find all doors located on the "Ground Floor," you must perform a multi-step query, because doors are children of walls, which are in turn children of the floor.

  1. Create a new report.
  2. Statement 1: Find the 'Ground Floor' Object
    • Add a statement with the description Find Ground Floor.
    • This query selects the starting point of our search.
      SELECT * FROM document WHERE Label = 'Ground Floor'
      
  3. Statement 2: Find the Walls on that Floor
    • Add a second statement with the description Find Walls on Floor.
    • In the editor, check the Use as Pipeline Step checkbox. This tells the statement to run on the results of the previous one (the floor object).
    • Use this query to find all direct children of the floor and keep only the walls:
      SELECT * FROM CHILDREN(SELECT * FROM document) WHERE IfcType = 'Wall'
      
  4. Statement 3: Find the Doors in those Walls
    • Add a third statement with the description Find Doors in Walls.
    • Check the Use as Pipeline Step checkbox again. This now links it to the output of Statement 2 (the list of walls).
    • Use this query to find the children of the walls and keep only the doors:
      SELECT Label, Width, Height FROM CHILDREN(SELECT * FROM document) WHERE IfcType = 'Door'
      
  5. Click OK. The report will execute the full three-step pipeline.

Understanding the Result: The final spreadsheet will only show the results of the last statement in the pipeline (the list of doors). The first two statements act as intermediate filters whose results are passed in memory but not printed to the spreadsheet, giving you a clean and precise final report.

Step 5: Presets and templates[edit | edit source]

  • Report Templates load a full, multi-statement report. In the Report Statements, select a template like "Room and Area Schedule" from the dropdown to instantly load the configuration from Steps 2 & 3. You can save your own reports as new templates for reuse.
  • Query Presets load a single query into the editor. In the Statement Editor, select a preset like "All Walls" from the dropdown to populate the SQL editor. You can save your own frequently used queries by clicking the Save… button.

BIM SQL dialect reference[edit | edit source]

This section provides a detailed reference for the SQL dialect used by the BIM Report tool.

Note: The most up-to-date reference for all clauses and functions can always be found in the UI by clicking the Help button within the report's Task Panel editor.

Clauses[edit | edit source]

  • SELECT: Specifies the columns of data to return. Can use * to select a default identifier for all objects.
  • FROM: Specifies the data source. In a standalone query, this is always document. In a pipeline, document is a placeholder for the data from the previous step. Can also use the CHILDREN() function.
  • WHERE: Filters objects based on conditions.
  • GROUP BY: Groups rows that have the same values into summary rows, typically used with aggregate functions.
  • ORDER BY: Sorts the final results. Can be followed by ASC (ascending, default) or DESC (descending). Supports sorting by multiple columns.
  • AS: Creates an alias (a custom name) for a column in the SELECT clause, e.g., SELECT Area AS "My Area".

Usage notes for GROUP BY and ORDER BY[edit | edit source]

The dialect has specific rules for how to use expressions (like functions or arithmetic) in the GROUP BY and ORDER BY clauses.

  • For GROUP BY: When grouping by an expression, you must repeat the entire expression in the GROUP BY clause. Using an alias from the SELECT list is not supported.
    • Correct:
      SELECT TYPE(*)  GROUP BY TYPE(*)
      
    • Incorrect:
      SELECT TYPE(*) AS BimType  GROUP BY BimType
      
  • For ORDER BY: Conversely, sorting by a raw expression is not supported. To sort by the result of an expression, you must first include it in the SELECT list with an AS alias, and then refer to that alias in the ORDER BY clause.
    • Correct:
      SELECT Label, LOWER(Label) AS sort_key  ORDER BY sort_key
      
    • Incorrect:
      SELECT Label  ORDER BY LOWER(Label)
      

Functions[edit | edit source]

The dialect includes a library of built-in functions for data manipulation and analysis.

Category Function Description
Aggregate COUNT(property|*) Counts the number of objects. COUNT(*) counts all rows; COUNT(property) counts rows where the specified property is not NULL.
SUM(property) Calculates the sum of a numerical property.
MIN(property) Finds the minimum value of a numerical property.
MAX(property) Finds the maximum value of a numerical property.
Hierarchical PARENT(*) Returns the architecturally significant parent of an object (e.g., the Wall a Window is in), transparently skipping over generic groups.
CHILDREN(subquery) A function used in the FROM clause. Returns all children of objects found by the subquery, resolving both containment (.Group) and hosting (.Hosts) relationships. This function is not recursive. To find grandchildren or deeper descendants, chain multiple CHILDREN() steps together using a pipeline.
CHILDREN_RECURSIVE(subquery, max_depth=15) A function used in the FROM clause. Returns all descendant objects of the subquery results by recursively traversing the hierarchy. It transparently skips generic groups. An optional max_depth can be specified (default: 15, 0 for unlimited). This replaces the need for complex pipelines to find nested objects.
Utility TYPE(*) Returns the object's user-facing type, as determined by its proxy (e.g., Wall, Space, Part::Box).
CONVERT(quantity, 'unit') Converts a FreeCAD Quantity object to a new unit and returns it as a raw number. Example: CONVERT(Area, 'm^2').
String LOWER(property) Converts a text property to lowercase.
UPPER(property) Converts a text property to uppercase.
CONCAT(val1, val2, …) Joins multiple text values and properties together into a single string.

Property access[edit | edit source]

You can access any property of an object, including nested data structures.

  • Direct Access: Access a simple property directly by its name.
    • Example: Label, IfcType
  • Nested Access: Use dot notation to access sub-properties.
    • Example: Shape.Volume, Placement.Base.z
  • Chained Access: Chain property access onto the result of a function, most commonly PARENT().
    • Example: PARENT(*).Label, PARENT(*).PARENT(*).Height

Operators[edit | edit source]

Type Operators Description
Comparison =, !=, >, <, >=, <= Standard comparison operators.
LIKE Case-insensitive pattern matching. Use % for multiple characters and _ for a single character.
IS NULL, IS NOT NULL Checks if a property has a value.
IN (value1, value2, …) Checks if a property's value is in a given list of literals.
Logical AND, OR Used in the WHERE clause to combine multiple conditions.
Arithmetic +, -, *, / Perform calculations on numerical properties. Obeys standard operator precedence; use () to override.

General syntax[edit | edit source]

  • Literals and Quotes: Both single quotes (') and double quotes (") can be used for text literals (strings). For example, WHERE Label = 'Wall' and WHERE Label = "Wall" are equivalent.
  • Comments: The parser ignores both single-line comments, which start with --, and multi-line comments, which are enclosed in /* … */.
  • Internationalization: The parser correctly handles non-ASCII (Unicode) characters in property names (e.g., Fläche).

Sample queries[edit | edit source]

This section provides a collection of practical queries that you can use directly or modify for your reports.

Basic selections[edit | edit source]

Select all structural elements (Columns and Beams):

SELECT Label, IfcType 
FROM document 
WHERE IfcType IN ('Column', 'Beam') 
ORDER BY IfcType, Label

Select all walls with "Exterior" in their name:

SELECT Label, Length, Height 
FROM document 
WHERE IfcType = 'Wall' AND Label LIKE '%Exterior%'

Data aggregation & summaries[edit | edit source]

Count all BIM objects, grouped by their type:

SELECT IfcType, COUNT(*) 
FROM document 
WHERE IfcType IS NOT NULL 
GROUP BY IfcType 
ORDER BY IfcType ASC

Get a full quantity takeoff for all walls:

This query calculates the total number of walls, their combined length, and the minimum and maximum wall heights.

SELECT 
    COUNT(*) AS "Total Count", 
    SUM(Length) AS "Total Length (mm)", 
    MIN(Height) AS "Min Height (mm)", 
    MAX(Height) AS "Max Height (mm)" 
FROM document 
WHERE IfcType = 'Wall'

Hierarchical queries[edit | edit source]

List all objects located directly on the "Ground Floor":

This uses the CHILDREN() function to query the contents of a specific container object.

SELECT Label, IfcType 
FROM CHILDREN(SELECT * FROM document WHERE Label = 'Ground Floor') 
ORDER BY IfcType

List all doors and the specific wall they are hosted in:

This uses the PARENT() function to find the host of each door.

SELECT 
    Label AS "Door_Name", 
    PARENT(*).Label AS "Host_Wall_Name" 
FROM document 
WHERE IfcType = 'Door' 
ORDER BY "Host_Wall_Name"

Find all windows on the "Ground Floor" (by checking the grandparent):

This query finds windows, checks their parent (the wall), and then checks that wall's parent (the floor).

SELECT Label 
FROM document 
WHERE IfcType = 'Window' AND PARENT(*).PARENT(*).Label = 'Ground Floor'

Advanced queries[edit | edit source]

Generate a complete room and area schedule:

This query produces a formatted list of all spaces, grouped by the floor they belong to, with their area converted to square meters.

SELECT 
    PARENT(*).Label AS "Floor", 
    Label AS "Room Name", 
    CONVERT(Area, 'm^2') AS "Area (m²)" 
FROM document 
WHERE IfcType = 'Space' 
ORDER BY "Floor", "Room Name"

Usage notes[edit | edit source]

Units[edit | edit source]

When you select a property that has units (a `Quantity`), such as `Length` or `Area`, the BIM Report engine returns the raw numerical value of that property as it is stored internally in the FreeCAD document. FreeCAD's internal units are always based on millimeters (mm).

This means the default output will be in mm for length, mm² for area, and mm³ for volume, regardless of your current unit schema or user preferences. The automatically generated column header will correctly reflect this internal unit.

Example: Default Behavior

If you run this simple query on a space that is 1 meter by 1 meter:

SELECT Label, Area FROM document WHERE IfcType = 'Space'

The resulting spreadsheet will show:

  • Header: Area (mm²)
  • Value: 1000000.0

Using CONVERT()[edit | edit source]

To get the results in the units you desire, you can use the CONVERT() function, which provides explicit control over the output. To obtain the area in square meters, modify the query as follows:

SELECT Label, CONVERT(Area, 'm^2') AS "Area (m²)" FROM document WHERE IfcType = 'Space'

This query now produces the result in the desired unit:

  • Header: Area (m²)
  • Value: 1.0

Using CONVERT() guarantees that your report's output is unambiguous and that any calculations you perform within the query (e.g., SUM(CONVERT(Area, 'm^2'))) are mathematically correct.

Properties[edit | edit source]

An Arch Report object exposes the following properties to the user.

Data[edit | edit source]

Report

  • DataAutoUpdate: This property, True by default, controls whether the report automatically re-runs every time the document is recomputed. When enabled, the report spreadsheet will always be in sync with the model. For example, if a wall's length is changed, a SUM(Length) value in the report will update automatically after the recompute. On very large models, it may be desirable to disable this for performance reasons while making many changes. If AutoUpdate is set to False, the report can be updated manually at any time by right-clicking the Report object in the Tree View and selecting Recompute, or by pressing the global Recompute button.
  • DataTarget: holds a link to the Spreadsheet object where the report's results will be written. When creating a new report, a new spreadsheet is automatically created and linked here. This property can be changed to target a different, existing spreadsheet in the current document if so desired.

Scripting[edit | edit source]

The BIM Report engine is fully accessible through Python, allowing you to integrate its advanced query capabilities into your own scripts and automations.

Arch.selectObjects(query_string)[edit | edit source]

This is the primary function for executing a single, straightforward SQL query. It takes a query string as input and returns a list of the FreeCAD DocumentObjects that match the query. If the query is invalid or finds no objects, it returns an empty list and prints an error to the console.

Example: Get all Wall objects with "Exterior" in their name

import FreeCAD
import Arch

# This query selects all Wall objects whose Label contains the substring "Exterior"
query = "SELECT * FROM document WHERE IfcType = 'Wall' AND Label LIKE '%Exterior%'"

exterior_walls = Arch.selectObjects(query)

# The result is a list of FreeCAD objects
FreeCAD.Console.PrintMessage(f"Found {len(exterior_walls)} exterior walls.\n")
for wall in exterior_walls:
    FreeCAD.Console.PrintMessage(f"- {wall.Label} (Length: {wall.Length})\n")

Arch.selectObjectsFromPipeline(statements)[edit | edit source]

For more complex, multi-step selections, you can use this function to execute a pipeline. It takes a list of ArchSql.ReportStatement objects and returns the final list of FreeCAD DocumentObjects that result from the full pipeline.

This is the programmatic equivalent of using the "Use as Pipeline Step" checkbox in the UI.

Example: Find all doors on the "Ground Floor"

This is a classic hierarchical problem that requires multiple steps. The goal is to find the "grandchildren" (doors) of a "grandparent" (the floor). Since the CHILDREN() function is not recursive, we must chain multiple steps in a pipeline.

  1. First, we find the 'Ground Floor' object.
  2. Second, we find its direct children (the walls, spaces, etc.).
  3. Third, we find the children of those objects (which will include the doors and windows hosted by the walls).
  4. Finally, we filter that last group to keep only the doors.
import FreeCAD
import Arch
import ArchSql

# Step 1: Define the pipeline statements.
# The ReportStatement class must be imported from the ArchSql module.

# Statement 1: Find the parent 'Ground Floor' object.
# Output of this step: [Ground_Floor_Object]
stmt1 = ArchSql.ReportStatement(
    query_string="SELECT * FROM document WHERE Label = 'Ground Floor'"
)

# Statement 2: Find the direct children of the floor (walls, spaces, etc.).
# is_pipelined=True tells this to run on the output of stmt1.
# Output of this step: [Wall_Object_1, Wall_Object_2, Space_Object_1, …]
stmt2 = ArchSql.ReportStatement(
    query_string="SELECT * FROM CHILDREN(SELECT * FROM document)",
    is_pipelined=True
)

# Statement 3: Find the children of the objects from the previous step.
# This finds the doors and windows hosted by the walls.
# Output of this step: [Door_Object, Window_Object_1, …]
stmt3 = ArchSql.ReportStatement(
    query_string="SELECT * FROM CHILDREN(SELECT * FROM document)",
    is_pipelined=True
)

# Statement 4: Filter the final list to keep only the doors.
# This runs on the output of stmt3.
# Final Output: [Door_Object]
stmt4 = ArchSql.ReportStatement(
    query_string="SELECT * FROM document WHERE IfcType = 'Door'",
    is_pipelined=True
)

# Step 2: Execute the full pipeline.
pipeline_statements = [stmt1, stmt2, stmt3, stmt4]
doors_on_floor = Arch.selectObjectsFromPipeline(pipeline_statements)

# The result is the final list of door objects.
FreeCAD.Console.PrintMessage(f"Found {len(doors_on_floor)} doors on the ground floor.\n")
for door in doors_on_floor:
    FreeCAD.Console.PrintMessage(f"- {door.Label}\n")

Inspecting and sharing a report via the Python Console[edit | edit source]

For debugging, scripting, or sharing your work with others, you can get a complete, human-readable summary of a Report's configuration directly from the Python Console.

The custom logic and detailed content of a BIM Report are stored in its proxy object. Accessing the .Proxy attribute provides a full printout of all statements and their SQL queries. This is a convenient way to copy your entire report definition to the clipboard to share in a forum or a bug report.

How to use it:

  1. Select your Report object in the Tree View.
  2. Press Ctrl+Shift+P. This will send the selected object to the Python Console as the variable obj.
  3. In the console, type the following and press Enter:
print(obj.Proxy)

Example Output:

>>> print(obj.Proxy)
BIM Report: 'My Door Schedule' (2 statements)

--- Statement [1]: Find All Walls on Ground Floor ---
    SELECT * 
    FROM CHILDREN(SELECT * FROM document WHERE Label = 'Ground Floor')
    WHERE IfcType = 'Wall'

--- Statement [2]: Final Door Schedule (Pipelined, Header) ---
    SELECT
        Tag,
        COUNT(*) AS "Count"
    FROM
        document
    GROUP BY
        Tag