Acumatica Report Designer Variables and Expressions

Acumatica report designer is a Chameleon. It is both a powerful mature and robust multi band report creation tool and at the same time a fiendishly difficult to understand Windows only software tool that confounds even the most experienced user and dare I say it is in need of a complete rewrite and rethink. It is, however what it is. There is no alternative at the moment than to roll up your sleeves and master it. It is, as someone on the Acumatica forums said, 'quite the journey'. 

TL:DR – This article is all about the Acumatica Report Designer expressions I have found most useful and how you can use them to create effective exciting reports. More Acumatica Report Designer Variables and Expressions can be found too, but these are the main ones that you need to master.

Getting started

If you are reading this you've already understood that you need to customise or write a report for Acumatica, and installed the software.

It may not have dawned on you yet that the relationship between generic inquiries in Acumatica and Acumatica Report Designer reports is one way. You can and should get your report working first as a GI. Do the hard work of making the GI to do what you need to report on first and then port your work to a report in Acumatica Report Designer. They are different tools, and generic inquiry conditions dont exactly equate to Acumatica Report Designer expressions, but you can re-implement them and you will save time with this approach because you will know the schema design is correct for what you are trying to accomplish.

Adding an expression to an Acumatica Report Designer report

This is hidden away and if you are just starting out with Acumatica Report Designer it is hard to find.

• Add a field to your report by choosing a field type and clicking it. I chose TextBox and added textBox1.
• That value name textBox1 is just text. But you can turn it into an expression.
• Click on Value in the Field Properties and you'll see a little ellipsis button to the right of the value. You can see it in the screenshot. You'll need to click it to open the Expression Editor.

The Expression Editor in Acumatica Report Designer

It might be over-egging it a little to called this dialog box an editor, but here it is. The middle column shows all the allowed constructions. Clicking on them reveals the syntax in the rightmost column. Double clicking on the syntax inserts it at the cursor insertion point in the bottom pane. Once you define a database scheme for your report its fields will be selectable in the leftmost column.

Creating your first expression

It is quite painful to use this so called editor really. Once you get beyond simple fields or dates you are better off pasting your expression in from your editor of choice. The most useful feature is the Validate button which checks that the expression is syntactically correct although this does not guarantee that your expression will work as expected.

• to try it out select the bottom pane of the editor and delete the textBox1 text. Type an equals = sign to denote that this is now an expression, and select DateTime from the Functions in the middle pane and then Today() from the right pane.
• You should see =Today() in the bootom pane now.
• Press Validate, then Ok. You don't have to validate but it is incredibly helpful as it is very easy to introduce simple errors.
• That's it, you created your first expression!

Notice that the value has changed in the report design itself, and in the Value property pane.

Digging deeper into Acumatica Report Designer expressions

Pretty soon after discovering expressions you'll run into functions and their parameters. These are the heart of custom reporting text and fields in Acumatica Report Designer reports.

If I had to hazard a guess, i would say that the Acumatica Report Designer and its features and capabilities were designed and product managed by a very technical team as some of the functions are duplicates, the syntax is very cumbersome unless you are a .net dev type and the user experience can be very unforgiving in terms of editing, saving to multiple servers and so on.

Most used Functions 

This group of functions have been the ones I have used most often to provide powerful ways to determine what text to display in an Acumatica Report Designer report.

IIf

IIf(expression, true, false)

The expression is evaluated and a value is returned depending on whether the expression is true or false. If the expression is True, the function returns the true value; otherwise, it returns the false value.

=IIF([ACUMATICAFIELDNAMEHERE]<>null, 'DISPLAY THIS TEXT', '')

In this example, if a field is not null, so not null is true, then display the content if the field is null so not null is false then dont display anything. This kind of expression whilst perhaps seemingly constructed back to front by making not null true, is used all over the place in Acumatica Report Designer reports. You can see it used in AR Invoice (AR641000) below.

You can see that these statements can be built up into a complex displayed output in the printed report. You can get a very long way in Acumatica report design just by effective masterng of IIf.

Switch

Switch( expr1, value1, expr2, value2, ...)

The expression is evaluated and a value is returned that corresponds to the first expression that evaluates to true. 

=Switch( [ARDunningLetter.DunningLetterLevel]= 1, 'Dear Customer,{br}Payment is now overdue.', [ARDunningLetter.DunningLetterLevel]= 2, 'Dear Customer,{br}Payment is now *very* overdue!')

In this example, if a the value of the field is 1, text related to this first value the text is displayed, if the value of the field is 2 the text related to this value is displayed. You can have as many expressions and values as you like. You can use it in AR Dunning Letter (AR661000) for example.

Switch is somewhat easier to read than long nested IIf statements and makes sense when you can determine the text to produce based on a single field.

Now() and NowUTC() / Today() and TodayUTC()

These functions return the current date and time according to the system date and time settings on the local computer.

=Now()

=NowUTC()

=Today()

=TodayUTC()

In this example, the default date is shown and this isn't very useful in a report, as it is in US format and makes no sense in the UK.

=Format('{0:MMMM dd yyyy}',Now())

=Format('{0:MMMM dd yyyy}',Today())

=Format('{0:MMMM dd yyyy}',NowUTC())

=Format('{0:MMMM dd yyyy}',TodayUTC())

Formatting the function inline (covered elsewhere) makes the output far more useful for more easily readbale dates in printed reports.