DVW Analytics
  • PRODUCTS
    • ALTERYX CONNECTORS >
      • ALTERYX CONNECTOR FOR SAP
      • ALTERYX CONNECTOR FOR BUSINESSOBJECTS
      • ALTERYX CONNECTOR FOR SUCCESSFACTORS
      • SAP METADATA CONNECTOR FOR ALTERYX CONNECT
    • KNIME CONNECTORS >
      • KNIME CONNECTOR FOR SAP
  • SERVICES
    • PROFESSIONAL SERVICES
    • TRAINING
  • DEMOS
  • FREE TRIAL
  • PRICING
  • RESOURCES
    • WEBINARS >
      • ON-DEMAND WEBINAR: SAP IN ALTERYX DESIGNER AND SERVER 101 - LEARN HOW TO ACCESS YOUR DATA LIKE A PRO
      • ON-DEMAND WEBINAR: ANALYSE + AUTOMATE YOUR SAP DATA WITH COCA-COLA EUROPEAN PARTNERS
      • ON-DEMAND WEBINAR: ANALYSE + AUTOMATE YOUR SAP DATA FULL DEMO
      • ON-DEMAND WEBINAR: DETAILED WALKTHROUGH OF THE ALTERYX CONNECTOR FOR SAP
      • ON-DEMAND WEBINAR: WHAT'S NEW WITH THE ALTERYX CONNECTOR FOR SAP
      • ON-DEMAND WEBINAR: AN INTRODUCTION TO SAP FOR ALTERYX USERS
      • ON-DEMAND WEBINAR: AN INTRODUCTION TO SAP DATA PREP & ANALYTICS WITH ALTERYX
      • ON-DEMAND WEBINAR: ALTERYX AND THE ALTERYX CONNECTOR FOR SAP
    • EVENTS >
      • BEER AND ANALYTICS 2020
      • BI INNOVATION & TECH FEST 2020
      • ALTERYX INSPIRE 2019
      • ALTERYX INSPIRE EUROPE 2019
    • FAQ
    • LICENSE
    • RESELLER RESOURCES
    • GETTING STARTED
    • INSTALLATION GUIDES
    • SOFTWARE DOWNLOADS
    • TUTORIALS
    • SAMPLE WORKFLOWS
  • BLOG
  • ABOUT
    • ABOUT US
    • CONTACT US
    • CUSTOMERS
    • PARTNERS
    • RESELLERS

BLOG

Tax, Audit & Finance - Extract Sales & Purchasing Tax Data from SAP ECC & SAP S/4HANA using Alteryx & the DVW Alteryx Connector for SAP

20/2/2020

 
Picture
​The use of Alteryx within the Tax, Audit and Finance functions is a particularly hot topic at the moment and with good reason. Alteryx can help users across the Tax, Audit, and Finance offices to significantly reduce the time and effort spent manually gathering data and reconciling business and legal entities, along with generating their internal and regulatory reporting.  
All of this is true, but if the majority of the data you need to access resides in an SAP system you will need the DVW Alteryx Connector for SAP to streamline the import of that data into Alteryx. 
In this blog we will demonstrate how you can easily extract Sales and Purchasing Tax data (Output Tax and Input Tax) from an SAP ECC or SAP S/4HANA system directly into Alteryx.
If you would like to follow the steps described in this blog, you will need:
  • Alteryx Designer - Request a trial here.
  • DVW ACS for Alteryx Designer - Request a trial here.
  • Access to an SAP ECC or SAP S/4HANA system
If you have access to Alteryx Gallery and would like to download the workflow presented in the blog, click here.
If you are more interested in using your SAP G/L accounting data in Alteryx, take a look at, "Learn how you can easily extract SAP General Ledger accounting data from SAP ECC & SAP S/4HANA using Alteryx & the DVW Alteryx Connector for SAP"

Identifying the required SAP Tables

There are thousands of data tables in an SAP system. So how do you identify which tables you need to access? Unless you have developer level access, it can be difficult, but we have already done the heavy lifting for you with our blog on, the most important SAP Finance and Controlling (FI/CO) tables for Alteryx users. Barring this, Google is a great source for SAP table data information.
Our blog, Learn how you can easily extract SAP General Ledger accounting data from SAP ECC & SAP S/4HANA using Alteryx & the DVW Alteryx Connector for SAP covers how SAP uses a document concept for business transactions in which there is Header data and Item or Segment data. ​
For our purposes there are two tables that hold the data we need:
  • Accounting Document Header – BKPF
  • Tax Data Document Segment - BSET
Both tables share common fields:
  • Company Code - BUKRS
  • Accounting Document Number - BELNR
  • Fiscal Year – GJAHR
In our workflow we will use these fields in the BKPF table to find the corresponding entries in the BSET table.

How to extract Sales and Purchasing Tax data using Alteryx and the DVW ACS for Alteryx Designer

So, we’ve established that we need to extract data from two SAP tables:
  • Accounting Document Header – BKPF
  • Tax Data Document Segment - BSET
To achieve this, we are going to use 2 workflows, a “Caller” workflow and a “Callee” workflow. ​
Picture
The “Caller” workflow will be used to extract data from the Header table, BKPF. ​
Key fields from BKPF will be passed to the “Callee” workflow to support the extract of data from the Tax Segment table, BSET. 
The “Callee” workflow will pass the data extracted from BSET back to the “Caller” workflow, where it will be combined with the data extracted from BKPF to provide us with a complete picture of a financial transaction. ​
Let’s take a look at how the two workflows are defined.

Step 1

Picture
The SAP Logon tool passes your SAP system details & user credentials to the ACS for Alteryx Designer tools that connect to it. ​
In this workflow, the details are passed to the SAP Table Data tool.

Step 2

Picture
SAP table, BKPF (Accounting Document Header) is selected in the SAP Table Data tool along with the fields that will (a) be used to select BSET (Tax Data Document Segment) data and (b) included in the workflow output
Picture

Step 3

Picture
This branch of the workflow passes the data extracted from the BKPF table to the Join tool. This step ensures that the data extracted from the BKPF table will be incorporated into the final workflow output.

Step 4

Picture
The Filter tool controls which fields from table BKPF will be used to select data from table BSET. In this workflow the following BKPF fields are used to select the required data from BSET: 
  • Company Code - BUKRS
  • Accounting Document Number - BELNR
  • Fiscal Year – GJAHR
The Filter tool also controls the number of BKPF records that will be passed to the “Callee” workflow to select BSET data, at a time. In our example we will pass records in batches of 8,000 records.
Picture

Step 5

Picture
The outputs from the Filter tool are passed to a macro which handles the extract of data from table BSET. The macro calls the “Callee” workflow.

Step 6

Picture
This workflow uses “GroupId” as the Control Parameter.

Step 7

Picture
The Macro Input tool macro receives data from SAP table BKPF as inputs from the “Caller” workflow.
In order to pass the BKPF table data correctly to the SAP Table Data tool a Text Input Template is defined. ​
Picture
BKPF table data is passed as a dynamic input selection into the SAP Table Data tool.

Step 8

Picture
SAP table, BSET (Tax Data Document Segment) is selected in the SAP Table Data tool along with the fields that will be included in the workflow output.
Picture

Step 9

Picture
The Macro Output tools enables the data output from the SAP Table Data tool to be passed back to the "Caller" workflow.

Step 10

Picture
​The results of the “Callee” workflow can be viewed by clicking the output port of the Macro.
Picture

Step 11

Picture
The data extracted from table BKPF and BSEG are joined using the Join tool.
The join is created on common fields:
  • Company Code - BUKRS
  • Accounting Document Number - BELNR
  • Fiscal Year – GJAHR
Picture

Step 12

Picture
The Select tool is used to define which fields will be displayed in the output. It is also used to control the sequence in which the fields appear in the output.

Steps 13 & 14

Picture
These steps allow us to display Field Descriptions rather than Technical Field Names in the final output.
The Field Info tool provides the Field Descriptions for the selected fields.
Picture
The Dynamic Rename tool is used to ensure that the Description of the field rather than the Name of the field is used in the column headers.
Picture

Step 15

Picture
The Sort tool is used to arrange the order of the output. 
Picture

Steps 16 & 17

Picture
A pair of Filter tools are used to prepare the final output.
Output Tax Lines Items have a Transaction Key value of "MWS" while Input Tax Line Items have a Transaction Key value of "VST".
The first Filter tool filters all the output records where the Transaction Key is "MWS". ​
The True out port provides the output for the Output Tax Line Items.
Picture
The second Filter tool filters all the output records where the Transaction Key is "VST". ​
The True out port provides the output for the Input Tax Line Items.
Picture

Steps 18 & 19

Picture
Finally, Browse tools are used so that we can display the output after the workflow is run.
A sample of the Output Tax Line Items output is shown below
Picture
And finally, a sample of the Input Tax Line Items output is shown below
Picture
GET A FREE TRIAL TODAY

Comments are closed.

    RSS Feed

    Categories

    All
    ACS
    ACS BusinessObjects
    ACS BW Write
    ACS Cloud
    ACS Metadata
    Alteryx
    Alteryx Gallery
    Alteryx Server
    Analytic Process Automation
    APA
    Audit
    BusinessObjects
    Consulting
    DVW Integration Platform
    DVW IP
    Inspire
    Jobs
    KCS
    KNIME
    Microsoft Azure
    Microsoft Power BI
    Office Of Finance
    Predictive Analytics
    Qlik
    Salesforce
    Salesforce Wave Analytics
    SAP BAPI Read Tool
    SAP BAPI Write Tool
    SAP BusinessObjects
    SAP Business Suite
    SAP BW
    SAP Data Migration
    SAP Document Tool
    SAP ECC
    SAP ERP
    SAP HANA
    SAP IDoc Tool
    SAP Query Tool
    SAP Read Tools
    SAP Report Tool
    SAP S/4HANA
    SAP Table Data Tool
    SAP Tables
    SAP Transaction Codes
    SAP Write Tools
    Services
    Tableau
    Tableau Prep
    Tax
    TCS
    ThoughtSpot
    Top 10 Blog
    Training
    Webinar

    Archives

    December 2020
    November 2020
    October 2020
    July 2020
    June 2020
    May 2020
    February 2020
    November 2019
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    May 2019
    March 2019
    September 2018
    August 2018
    June 2018
    May 2018
    April 2018
    February 2018
    July 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    May 2016
    November 2015

PRODUCTS

ALTERYX CONNECTOR FOR SAP​
​
ALTERYX CONNECTOR FOR BUSINESSOBJECTS
​ALTERYX CONNECTOR FOR SUCCESSFACTORS
SAP METADATA CONNECTOR FOR ALTERYX CONNECT​
​KNIME CONNECTOR FOR SAP

SERVICES

PROFESSIONAL SERVICES
TRAINING

DEMOS

FREE TRIAL

FREE TRIAL OF ALTERYX CONNECTOR FOR SAP
FREE TRIAL OF ALTERYX CONNECTOR FOR BUSINESSOBJECTS
FREE TRIAL OF ALTERYX CONNECTOR FOR SUCCESSFACTORS
​FREE TRIAL OF KNIME CONNECTOR FOR SAP

PRICING

BLOG

ABOUT US

CONTACT US

DVW Analytics, from De Villiers Walton, is dedicated to bringing the power of SAP to users of Alteryx, KNIME, Tableau & other global leaders in BI and analytics.

© COPYRIGHT 2003 - 2020 DE VILLIERS WALTON LIMITED. ALL RIGHTS RESERVED.

SAP® Business Suite, SAP® BusinessObjects™, SAP® BW, SAP® SuccessFactors are the trademarks or registered trademarks of SAP SE in Germany and in several other countries.
PRIVACY POLICY
  • PRODUCTS
    • ALTERYX CONNECTORS >
      • ALTERYX CONNECTOR FOR SAP
      • ALTERYX CONNECTOR FOR BUSINESSOBJECTS
      • ALTERYX CONNECTOR FOR SUCCESSFACTORS
      • SAP METADATA CONNECTOR FOR ALTERYX CONNECT
    • KNIME CONNECTORS >
      • KNIME CONNECTOR FOR SAP
  • SERVICES
    • PROFESSIONAL SERVICES
    • TRAINING
  • DEMOS
  • FREE TRIAL
  • PRICING
  • RESOURCES
    • WEBINARS >
      • ON-DEMAND WEBINAR: SAP IN ALTERYX DESIGNER AND SERVER 101 - LEARN HOW TO ACCESS YOUR DATA LIKE A PRO
      • ON-DEMAND WEBINAR: ANALYSE + AUTOMATE YOUR SAP DATA WITH COCA-COLA EUROPEAN PARTNERS
      • ON-DEMAND WEBINAR: ANALYSE + AUTOMATE YOUR SAP DATA FULL DEMO
      • ON-DEMAND WEBINAR: DETAILED WALKTHROUGH OF THE ALTERYX CONNECTOR FOR SAP
      • ON-DEMAND WEBINAR: WHAT'S NEW WITH THE ALTERYX CONNECTOR FOR SAP
      • ON-DEMAND WEBINAR: AN INTRODUCTION TO SAP FOR ALTERYX USERS
      • ON-DEMAND WEBINAR: AN INTRODUCTION TO SAP DATA PREP & ANALYTICS WITH ALTERYX
      • ON-DEMAND WEBINAR: ALTERYX AND THE ALTERYX CONNECTOR FOR SAP
    • EVENTS >
      • BEER AND ANALYTICS 2020
      • BI INNOVATION & TECH FEST 2020
      • ALTERYX INSPIRE 2019
      • ALTERYX INSPIRE EUROPE 2019
    • FAQ
    • LICENSE
    • RESELLER RESOURCES
    • GETTING STARTED
    • INSTALLATION GUIDES
    • SOFTWARE DOWNLOADS
    • TUTORIALS
    • SAMPLE WORKFLOWS
  • BLOG
  • ABOUT
    • ABOUT US
    • CONTACT US
    • CUSTOMERS
    • PARTNERS
    • RESELLERS