The Power of Query Manager Formula Builder

Easily produce reports specifically tailored to recipients’ needs

Query Manager from EPI-USE Labs is an exceptional product to report on data from all functional areas within SAP.  In addition to reporting on data that resides within SAP tables, Query Manager users can add their own columns to their reports, and fill those columns based on “formulas” they create.  

The power of the Query Manager Formula Builder, powered by the custom language called LabsScript, incorporates thousands of commands and combinations of commands to allow reports to show SAP data exactly as required by the recipients. In its simplest form, it supports a vast majority of the syntax and function equivalents found in Excel, with some features that were changed or added for an enhanced user experience. Unlike in Excel, LabsScript is a compiled language which runs at near-native speeds, so the formulas users create don’t bog down the performance of their queries.

This blog post article illustrates just a few examples of the kinds of formulas that can be written within Query Manager.

Example 1. Writing formulas is simplified by using field labels within the Formula Builder. This very simple formula is adding D1 (Amount1) and F1 (Amount2). 

Example 2.  Another very simple formula is to concatenate one or more text fields together.  This example concatenates the Personnel Area and the Personnel Subarea into a single column:

Sample report output:

Example 3. Commonly used formulas are ones that make a decision in order to produce results. The IF and IFS statements make these types of formulas easy to create:

This formula will highlight when two fields from different parts of SAP do not equal each other:

Use the IFS statement when the decision is multiple levels deep:

Example 4. Taking formulas to the next level, we can start to create some fantastic reports. Case in point: The manager of each Organizational Unit in the company wants a report of the gross pay amounts per month for a given year to track variances in pay. This can be accomplished in Query Manager with a couple of formulas.

The first one will create a column that holds values for each month of the year. A little extra logic formats this field as
“01 – Jan”, “02 – Feb”, “03 – Mar” etc. for clarity and for sorting purposes:

The second formula creates the total gross pay, subtotaled by Org Unit and by month. Also, this formula is using the output of the earlier formula as one of its parameters. Query Manager knows to execute the first formula before the second:

Here’s the final report: 

Example 5: The final example executes several formulas to do some intricate text field manipulation. This was a real example given to me by one of our EPI-USE customers that was required for one of their reports. They had a name field that was showing by default on their report in the format of Mr. Paul J. Lamonica. The requirement was to reformat this name as Lamonica, Paul J. Now, I’m not ashamed to admit that my Excel formula skills are medium at best. But, what I am able to do quite well is internet searches when I need assistance. That’s exactly what I did to solve this issue. I simply searched on the internet how to do the actions I needed to perform to reformat the name in the required format. When I found what I was looking for, I copied and pasted the results of my search into the Formula Builder within Query Manager. And just like that, I solved the issue.

The result was the following three formulas. I broke it up into smaller sections of work for clarity, but maybe a highly skilled Excel expert could do this all in one formula.

Formula 1: Strip off the salutation:

Formula 2, 3, and 4: Isolate the first name, last name, and middle initial:

Formula 5: With all the parts of the name now created, bring it all together using a shortcut for the CONCATENATE statement:

Here is the final report. This example shows the individual parts of the name on the report, but these can optionally be removed, so only the end result of all the formulas appears on the output.

If you don’t understand all the commands used in these formulas, guess what, neither do I. This is just a great example of getting the syntax of the formulas from internet searches, and using the results within the Formula Builder in Query Manager. 

In Summary
These examples are only a very small sample of the kinds of things that can be done by creating formulas in EPI-USE Labs Query Manager. And, the best part…you don’t have to be a super technical person to do some amazing things with formulas. Formulas (and their language, LabsScript) were created to be used by all types and levels of SAP personnel. From the most experienced ABAP developer to the newly hired functional analyst, formulas can be used by everyone to produce reports specifically tailored to the needs of the recipients.