Focusing on Formulas

Almost all ad hoc reports do much more than display raw data. Formulas take data as input for calculations, security and formatting and give reports real business value. These formulas can be as simple as displaying the month of a date element or as complicated as finding the year-over-year rate of change for a particular field or metric. Exago BI provides an intuitive formula editor as well as the ability to create custom formulas. This post, meant for report designers and power users, will describe and illustrate examples of five commonly utilized functions.

5 Functions

 

If 

The If function allows reports to build in some logic. The If function takes three parameters: a condition followed by two values. If the condition evaluates to True, then the first value is used; if not, the second value is used. 

In the example below, the If function checks to make sure the report does not write a blank line if the address does not have a second street: “if({Addresses.Street 2}=”,newline(), newline() & {Addresses.Street 2}& newline())”.

 

Today

Returns the current date when the report is run. Does not take any parameters.

 

NewLine

Jumps to the beginning of the next line in text. Does not take any parameters.

 

DateDiff

Returns the amount of time between two dates. The DateDiff function takes three parameters: a string and two dates. The string tells the eWebReports what unit to measure the difference in. “yyyy” returns years, “d” days, “w” weeks, “m” months, “h” hours, “n” minutes, “s” seconds, “q” quarters.

 

LoadImage

Loads an image from the connection string parameter. This allows you to keep images on a server and load them dynamically instead of keeping them in the report.

 

Example

The report designer below shows how these five functions can be used to draft a reminder letter. The NewLine and If functions help format the address. Today and DateDiff calculate the number of days left to apply. Finally, LoadImage inserts a picture into our letter.

Formulas in Advanced Report Designer

To see what these formulas produce, we run the report as a PDF.

Report Output

Summary

This post highlighted just five out of the many formulas offered by Exago BI. Leveraging these formulas and those that you define will bring analytics and real business value to your data.

Happy Reporting.

Authored by
Travis McGrath
Senior Manager of Client Services
comments powered by Disqus
`