Dotnet Report allows users to create Custom Fields for basic as well as advanced calculations. This feature allows users to easily add a calculation instead of a developer creating a stored procedure or coding it for them.
To add a Custom Field to a Report, click on the “Customize” button when choosing Data Fields in the Report Designer:
This initiates the process of creating a Custom Field. As the user selects fields, it is added to the calculation. The user can choose basic arithmetic operations to apply to the field calculation. You can only use data fields from the same table to create a custom field or combine data fields from different tables if needed.
The user also has the option to add parenthesis to the formula by clicking the “Add ( )” button. An example of this is shown below
Another option available is to add a constant value to the equation by clicking the “Add Value” button. An example of using a constant value in the calculated field can be the following to concatenate Last and First name:
Once the field design is done, enter a name for the custom field, and click on the “Save” button to add the custom field to selected fields list. Depending on the data type, you have different calculations available. Creating a custom field using Date fields works differently from other data types.
Once you “Save”, the custom field will be added to the list of selected fields as shown below:
The custom field is now displayed on the report as shown below:
Advanced Expressions
Dotnet Report’s Advanced Expression field builder allows users to create custom SQL-based calculated columns directly inside the report designer without writing full SQL queries by hand.
This feature is designed for power users who need more flexibility than basic formulas while still benefiting from Dotnet Report’s built-in SQL generation and safety checks.
What Is an Advanced Expression?
An Advanced Expression is a custom field that translates directly into a SQL column expression.
It allows you to:
- Build conditional logic (CASE / IIF)
- Apply SQL functions (COALESCE, ROUND, CONCAT, etc.)
- Reference existing fields from your data model
- Create complex calculated values that behave like native columns
The generated SQL is automatically injected into the report query at runtime.
How to Enable Advanced Expressions
When creating or editing a Custom Field:
- Set the Field Builder Type to Advanced Expression
- Choose the appropriate Data Format (Integer, Decimal, Money, Text, Date, etc.)
- Use the Select Data Operation box to build your expression
This switches the field editor into SQL-expression mode.
Smart Autocomplete While You Type
As you type inside the expression editor:
- Available tables and fields automatically appear
- SQL functions are suggested as you type
- Field names are inserted using Dotnet Report’s internal syntax
Autocomplete works across joined tables and respects the report’s schema.
Built-In Functions vs “Other”
Built-In Functions
Dotnet Report provides a list of commonly used SQL functions, including:
- IIF (Conditional) – Simple if/else logic
- CASE (Multiple Conditions) – Multi-branch conditional logic
- COALESCE – Handle null values
- Math and string functions (database-aware)
Selecting one inserts a template you can customize.
Using Other for Custom SQL
If your logic isn’t covered by a predefined function, select Other.
This allows you to:
- Write your own SQL expression manually
- Combine multiple functions
- Use advanced SQL constructs supported by your database
Example use cases:
- Custom scoring logic
- Conditional financial calculations
- Database-specific expressions
- Nested CASE statements
Dotnet Report will still safely embed your expression into the generated SQL query.
Database Compatibility
Advanced Expressions respect the selected database type (SQL Server, Oracle, PostgreSQL, MySQL, etc.).
- Function templates adapt where possible
- Raw expressions entered via Other should use syntax compatible with your database
- The expression is evaluated by the database engine, not the UI
When to Use Advanced Expressions
Use Advanced Expressions when you need:
- Complex conditional logic
- Calculations not available in basic formulas
- SQL-level control without writing full queries
- Reusable calculated fields across reports
For simple math or formatting, basic field builders may be sufficient.