The Power of Query Manager Formula Builder

July 27, 2018
Written by Paul Lamonica

Paul is a Senior Solutions Architect, responsible for training of all EPI-USE Labs HCM products. He also works on new customer demos and national webinars hosted by EPI-USE Labs.

 

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 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: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: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: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: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:
Screenshot6

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: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:  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 1: Strip off the salutation

Formula 2, 3, and 4: Isolate the first name, last name, and middle initial:The power of Query Manager Formula Builder: find out more 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). Screenshot1  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:Screenshot2  Sample report output:Screenshot3  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:Screenshot4  Use the IFS statement when the decision is multiple levels deep:Screenshot5  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:Screenshot6  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:Screenshot7  Here’s the final report: Screenshot8  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:Screenshot9  Formula 2, 3, and 4: Isolate the first name, last name, and middle initial:Screenshot10  Formula 5: With all the parts of the name now created, bring it all together using a shortcut for the CONCATENATE statement: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. Formatting in field name  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’s 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.

Formula 5: With all the parts of the name now created, bring it all together using a shortcut for the CONCATENATE statement: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. Formatting in field name

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.
 

 

 

Explore Popular Tags

Query Manager SAP SuccessFactors SAP HCM reporting HCM Reporting SAP HCM SAP Reporting HCM reporting EPI-USE Labs Payroll Document Builder SAP Analytics Cloud SAP SuccessFactors Employee Central Payroll Query Manager Analytics Connector SAP HCM Data SAP Payroll SAP Query Microsoft PowerBI SAP SuccessFactors People Analytics SAP SuccessFactors Reporting Payroll reporting Tableau Human Capital Management (HCM) PRISM Payroll Data SAP Payroll data Variance Monitor people analytics sap query hr Data Sync Manager Employee Central Payroll HR and Payroll data HXM Move Journey to SAP SuccessFactors SAP HCM journey COVID-19 HCM Productivity Suite HR SAP SAP ERP HCM SAP HCM Payroll SAP S/4HANA Private Cloud Edition (S/4 PCE) reporting solution ABAP Cloud-based SAP HCM solutions DSM for HCM Employee data GeoClock Let's Talk HCM Pay Recon Query Manager with Document Builder SAP Data Warehouse Cloud SAP HCM Analysis SAP HCM On-Premise Solutions SAP HR Reporting SAP S/4HANA SAP SuccessFactors HCM Journey SAP SuccessFactors Roadmaps SAP and SuccessFactors HXM Reporting SuccessConnect data validation payroll control center successfactors Data Sync Manager for HCM Employee Central GDPR Intelligent HR and Payroll OData Real-time reporting and document creation SAP Analytics Cloud (SAC) SAP HCM On-premise SAP HXM SAP HXM 2021 SAP On-Premise customers SAP Payroll to the Cloud SAP customers Success Factors SuccessConnect 2019 Tax Reporting Transformation without re-implementation certification custom infotype data source Analytics solutions Automated reports Automation Cloud migrations DSM Object Sync for SuccessFactors Hybrid Data Types Data analysis Employee Central Payroll Reporting Employee Letters Employee communication Employee payroll Free HCM Assessment HCM, HR HR Journey HR employee reports Human Resources Human Resources data Hybrid Reporting SAP and SuccessFactors Hybrid SAP and SuccessFactors Hybrid reporting Hybrid reporting solution Microsoft Excel OData integration OM Object Sync On-Premise Payroll On-Premise Payroll S/HANA Sidecar On-premise reporting Organization of the data PA PRISM for HCM (Private Cloud Edition) People Analytics Workforce Planning Personalized documents Protect personal employee data Report Stories Reporting and analysis Robotic Process Automation (RPA) Robotic Process Automation framework SAP Data Security SAP ERP Payroll customers SAP HCM Roadmap SAP HCM and Payroll customers SAP HCM/HXM SAP HR SAP Mentors SAP Road maps SAP SuccessFactors Hybrid SAP SuccessFactors Release updates SAP SuccessFactors Time Management SAP SuccessFactors Time Tracking SAP certified solution SAP data SAP data privacy and compliance SAP migration SAPPHIRE 2018 SuccessFactors and the Intelligence Enterprise SuccessFactors' Employee Central Payroll The Report Center The Road to People Analytics Time management Workforce Planning ad hoc easy reporter ebook high-speed, low-risk on-premise SAP HCM on-premise SAP data partner roadmap s/4HANA single reporting solution sq01 technology third party ALE STP report ASUG Accessing COVID-19 data Ad Hoc Query American Payroll Association (APA) Analytics Connector Analytics reports Analytics solution At-risk employees Australian Payroll Australian Tax Office (ATO) Automated analysis and pay run reconciliations Automatic HR reports Best practice in BI Bots Business Analytics Business Intelligence COVID-19 statistics COVID-19 vaccinations Certified solutions Check for data replication errors Client Sync Cloud hosting SAP PCE Company Branding Compare legacy HR and Payroll data Comparing data Configuration Center Copy and mask test data Coronavirus Created timestamp Customer-specific infotypes DSAG Data Privacy Data Replication Data Secure Data Sources Data Sync Manager (DSM) Data access Data privacy regulations Data production support issue solution Democratize data Description Diversity & Inclusion reporting DocuSign Document Building Dynamic data ECC EPI-USE Labs’ solutions ERP Education sector Electronic Signatures Embedded Analytics Edition Employee Central time Employee Central timesheets Employee NICs Employee payment summaries Employee right to privacy Employer NICs Encrypt data FAQ Index Font Guidelines Gender Pay Gap General Data Protection Regulation Governance, Risk Management and Compliance (GRC) Government forms Grid control Grid layout H4S4 HANA HCM Client Sync™ HCM/HXM/HR Blogs HIPPA HMRC HR Innovations 2019 HR Service Delivery HR and Payroll Integration HR conference HR documents Historical payroll results Hourly time tracking Human Capital Management Human Resources reports HumanRevolution Hybrid SAP SuccessFactors environment IRS Tax Withholding Estimator Infotype Instance Refresh Instance Sync Instance Syncing Integrated reporting SuccessFactors SAP Intelligent Enterprise Intelligent slicing of payroll data JHUCSSE
+ See More

Get Instant Updates


Leave a Comment: