A Guide to Expressions Used in .NET Report Applications

Expressions are an excellent resource for both technical and non-technical users of ActiveReports. For users familiar with Microsoft Visual Basic® .NET or Microsoft Excel functions, many of these functions will be familiar. Whenever designing reports, there may be times when you would like to perform calculations that vary from basic to advanced or change the formatting of the control.

These situations are where expressions shine and can allow for the creation of a more visually appealing report. Expressions allow the user to create a dynamic report using Run-Time calculations that will be made using data from the Data Source.

To start creating an expression, you can select a control such as a textbox, right-click it and select Expression to use the Expression Editor. This will show you a list of available functions and fields.

NOTE: When using the Visual Studio or Stand-Alone Designer, the expression must lead with an equal sign ‘='. For the Web Designer, the expression must be surrounded by curly brackets ‘{}’; otherwise, anything in the expression will turn into plain text.

Syntax at Design Time

Rendered Result at Run Time

Want to Build Your Own Expression? Download ActiveReports Today!

Basic Calculations

The building blocks of an expression will be the basic calculations. These include multiplication (*), division (/), addition (+), subtraction (-), and concatenation (&). The expression will follow PEMDAS logic with any concatenation being done after the calculations are complete.

Advanced Calculations

Through the Expression Editor, we can also see some more advanced calculations that notably include the following:

• Pow(number1, number2), which will allow for the use of exponents
• Sqrt(number) to find the square root of a number
• Sum(value) to find the total of the field
• Min(value) and Max(value) to find the smallest and largest values in a field
• Other advanced functions

Below we will cover a few specific functions that can be useful across various industries.

RunningValue(value, aggregateFunction)

If you have a value where you would like to find the current total of a field or control from the start of the report to the current page, RunningValue will allow for finding this value. The example below will show the RunningValue of orders from the beginning of the report.

Page One with No RunningValue

Page Two with a RunningValue (Fields!Cost.Value, Sum)

Example: =RunningValue(Fields!Cost.Value, Sum)

Lookup(sourceExpression, destinationExpression, resultExpression, lookupDataset)

The Lookup Function will be familiar to those with experience with SQL Join statements or the VLOOKUP Function in Excel. This function will allow for joining multiple tables to create a more user-friendly report. Our tables will need to have a shared key so that we can link them together.

Our example below will be for a Claims Management System where our Claims and Patient tables have a shared key (PatientID) that we will use to link them together. This will allow us to have a table where we can see the patient by name rather than an ID.

Claims Table

Patients Table

Lookup Expression in the Expression Editor

Joined Claims and Patients Tables that allow for a more user-friendly viewing of the tables

Example: =Lookup(Fields!PatientID.Value, Fields!PatientID.Value, Fields!PatientName.Value, “Patients”)

Formatting and Style

When designing a report, the expressions are not limited to performing calculations shown in the report. Another use for the Expression Editor is to format and style controls, enabling the user to bring attention to key details in the report. Using expressions, we can change the font (size, color, family, and weight), background color, border (width, color, and style), and text alignment.

Two primary functions will be used when using expressions to change formatting: IIF and Switch. The IIF statement will enable the user to alter the formatting based on if a value is TRUE or FALSE. In contrast, the Switch statement will allow the user to set multiple cases to change the formatting based on the value. Below we have an example of using a Switch statement to change the FontSize, and an IIF Statement to change the BackgroundColor.

IIF Statement for BackgroundColor

Switch Case for FontSize

Report Example

Syntax:

IIF(condition, true, false)

Switch(condition1, value1, condition2, value2,..., conditionN, valueN)

Examples:

=IIF(Fields!AnnualSales.Value >= 80000, "Above Average", "Below Average")

=Switch(Fields!FirstName.Value = "Abraham", "Adria", Fields!FirstName.Value = "Charelotte", "Cherrie")

The following pages in our documentation will detail the available functions in ActiveReports. If you have any specific questions regarding expressions and functions, feel free to contact our support team and create a ticket.

Want to Build Your Own Expression? Download ActiveReports Today!