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 HCM Reporting SAP HCM reporting SAP HCM SAP Reporting HCM reporting EPI-USE Labs Payroll Document Builder SAP Analytics Cloud SAP Query SAP SuccessFactors Employee Central Payroll SAP Payroll SAP SuccessFactors People Analytics SAP HCM Data SAP SuccessFactors Reporting Human Capital Management (HCM) Microsoft PowerBI Payroll Data Query Manager Analytics Connector Variance Monitor people analytics sap query hr Data Sync Manager Payroll reporting SAP SAP Payroll data Tableau COVID-19 Employee Central Payroll HCM Productivity Suite HR Journey to SAP SuccessFactors SAP HCM journey reporting solution successfactors ABAP Cloud-based SAP HCM solutions GeoClock HXM Move PRISM Pay Recon Query Manager with Document Builder SAP SuccessFactors HCM Journey data validation payroll control center DSM for HCM Data Sync Manager for HCM Employee data Let's Talk HCM OData SAP ERP HCM SAP HCM Analysis SAP HCM On-Premise Solutions SAP HCM On-premise SAP HR Reporting SAP HXM 2021 SAP On-Premise customers SAP S/4HANA Private Cloud Edition (S/4 PCE) SAP SuccessFactors Roadmaps SAP and SuccessFactors HXM Reporting SAP customers Success Factors SuccessConnect 2019 Successconnect Tax Reporting Transformation without re-implementation certification custom infotype data source DSM Object Sync for SuccessFactors Hybrid Employee Central Employee Central Payroll Reporting Employee Letters Employee payroll Free HCM Assessment GDPR HCM, HR HR Journey HR Service Delivery Human Resources Human Resources data Hybrid Reporting SAP and SuccessFactors Hybrid reporting Hybrid reporting solution Intelligent HR and Payroll Microsoft Excel OData integration OM Object Sync On-Premise Payroll On-Premise Payroll S/HANA Sidecar On-premise reporting PA People Analytics Workforce Planning Personalized documents Robotic Process Automation framework SAP Data Security SAP Data Warehouse Cloud SAP HCM Payroll SAP HCM/HXM SAP HR SAP Mentors SAP Payroll to the Cloud SAP S/4HANA SAP SuccessFactors Hybrid SAP SuccessFactors Time Management SAP SuccessFactors Time Tracking SAP certified solution SAP data privacy and compliance SAP migration SAPPHIRE 2018 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 solution Analytics solutions At-risk employees Australian Payroll Australian Tax Office (ATO) Automated analysis and pay run reconciliations Automated reports Automation 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 Cloud migrations 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 Sources Data Sync Manager (DSM) Data Types Data analysis Data production support issue solution 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 communication Employee payment summaries Employer NICs Encrypt data FAQ Index Font Guidelines Gender Pay Gap General Data Protection Regulation Government forms Grid control Grid layout HANA HCM Client Sync™ HCM/HXM/HR Blogs HIPPA HMRC HR Innovations 2019 HR and Payroll Integration HR and Payroll data HR conference HR documents HR employee reports Historical payroll results Hourly time tracking Human Capital Management Human Resources reports HumanRevolution Hybrid SAP SuccessFactors environment Hybrid SAP and SuccessFactors IRS Tax Withholding Estimator Infotype Instance Refresh Instance Sync Instance Syncing Integrated reporting SuccessFactors SAP Intelligent Enterprise Intelligent slicing of payroll data JHUCSSE Knowledge Base (KB) Articles Kronos LabsScript Learning and growth Leave Liability Reporting Legislative compliance Leveraging Fiori Technology Location Managed Payroll Merging and splitting of cells Modified timestamp Move to SuccessFactors Employee Central New York Times News OCHA Organization of the data Owner PRISM for HCM (Private Cloud Edition) Pandemic Parent template Password protection Pay As You Go (PAYG) Pay reconciliation Payroll data in a dashboard Payroll support and reconciliation
+ See More

Get Instant Updates


Leave a Comment: