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 SAP HCM HCM Reporting SAP Reporting SAP Payroll HCM SAP SuccessFactors Employee Central Payroll SAP SuccessFactors Reporting EPI-USE Labs reporting PRISM Payroll Document Builder Payroll reporting Query Manager Analytics Connector SAP Analytics Cloud SAP HCM Data SAP Query Human Capital Management (HCM) Intelligent HR and Payroll Microsoft PowerBI SAP SuccessFactors People Analytics SAP Payroll data Variance Monitor HR and Payroll data Tableau HXM Move Payroll Data SAP HCM Payroll SAP S/4HANA SAP S/4HANA Private Cloud Edition (S/4 PCE) people analytics sap query hr Data Sync Manager Employee Central Payroll Journey to SAP SuccessFactors SAP SAP ERP HCM SAP HCM On-Premise Solutions SAP HCM journey SAP HR Reporting SAP HXM SAP and SuccessFactors HXM Reporting COVID-19 Cloud-based SAP HCM solutions Employee payroll HCM Productivity Suite HR PRISM for HCM (Private Cloud Edition) PRISM free assessment SAP HCM/HXM SuccessConnect reporting solution ABAP DSM for HCM Employee Central Payroll Reporting Employee data GeoClock H4S4 Let's Talk HCM Pay Recon SAP Data Warehouse Cloud SAP HCM Analysis SAP SuccessFactors HCM Journey SAP SuccessFactors Roadmaps SAP data privacy and compliance SuccessFactors Ultimate Guide: SAP HCM & Payroll Options data validation payroll control center Data Sync Manager for HCM Employee Central GDPR HCM, HR OData On-Premise Payroll Query Manager with Document Builder Real-time reporting and document creation SAP Analytics Cloud (SAC) SAP HCM On-premise SAP HCM for SAP S/4HANA On-Premise SAP HR SAP On-Premise customers SAP Payroll to the Cloud SAP Road maps SAP customers SAP data SAP data privacy & security Success Factors SuccessConnect 2019 Tax Reporting Transformation without re-implementation accurate payroll data certification custom infotype data source ebook on-premise SAP HCM s/4HANA Analytics solutions Automated reports Automation Cloud migrations DSM Object Sync for SuccessFactors Hybrid Data Secure Data Types Data analysis Digital tranformation EPI-USE Labs’ solutions Employee Letters Employee communication Free HCM Assessment HR Journey HR employee reports Human Experience Management (HXM) Human Resources Human Resources data Hybrid Reporting SAP and SuccessFactors Hybrid SAP and SuccessFactors Hybrid reporting Hybrid reporting solution Integrated reporting SuccessFactors SAP Intelligent Enterprise Microsoft Excel News OData integration OM Object Sync On-Premise Payroll S/HANA Sidecar On-premise reporting Organization of the data PA PRISM for ECP PRISM for H4S4 People Analytics Workforce Planning Personalized documents Protect personal employee data Report Stories Reporting and analysis Robotic Process Automation (RPA) Robotic Process Automation framework S/4HANA Private Cloud Edition (PCE) SAP Data Privacy Suite SAP Data Security SAP ERP Payroll customers SAP HCM 2023 SAP HCM Roadmap SAP HCM and Payroll customers SAP HCM for S/4HANA SAP HXM 2021 SAP Mentors SAP SuccessFactors Hybrid SAP SuccessFactors Next-Gen Payroll SAP SuccessFactors Release updates SAP SuccessFactors Time Management SAP SuccessFactors Time Tracking SAP Wage Type Reporter SAP certified solution SAP migration SAPPHIRE 2018 SuccessFactors and the Intelligence Enterprise SuccessFactors' Employee Central Payroll TCO Calculator The Report Center The Road to People Analytics Time management Workforce Planning ad hoc data variances easy reporter high-speed, low-risk on-premise SAP data partner roadmap single reporting solution sq01 stories in SAP SuccessFactors People Analytics technology third party ALE STP report ASUG Accessing COVID-19 data Ad Hoc Query American Payroll Association (APA) Analytics Connector Analytics reports Analytics solution Artificial Intelligence (AI) 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 ChatGPT 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 Custom store Customer-specific infotypes DSAG Data Privacy Data Replication 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 ERP Education sector Electronic Signatures Embedded Analytics Edition Employee Central time Employee Central timesheets Employee NICs Employee Retention Analytics (ERA) 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
+ See More

Get Instant Updates


Leave a Comment: