Requirements to run and extract a report in a specific format can vary from customer to customer and time to time. A very common scenario to point out is a field available in Fiori UI is not available as a reportable field via custom CDS View. In S4HANA Cloud, you may not necessarily find all the fields available for reporting and may also not find to be extracted in a desired format. Sounds familiar?
Recently, I came across a requirement where we need to extract a Supplier Master Record at the company code level to be extracted in an excel format and most of all, the fields required as part of reporting are not whitelisted, meaning they cannot be consumed in the reports using the app custom CDS view.
The good thing is, you always have a way to get things around by looking for alternate options in S4HANA Cloud.
This is when I came across an app called Advanced Compliance Report.
So, in this blog, I will be describing a scenario on how we can fetch the Supplier Master Records and extract them into an excel(csv format) that includes non-whitelisted fields using Advanced Compliance Report.
Now, you might wonder, having to extract into excel is easily available using some of the app like Custom Analytical queries, but again with more than 10k plus, this could run into performance issues and eventually run into system crash/issues while performing data dump.
The requirement is to fetch the following fields:
INDUSTRY,LEGALENTITYOFORGANIZATION, INDUSTRYSTANDARD,TAXNUMBER,WITHHOLDINGTAXTYPE,WITHHOLDINGTAXCODE, ISWITHHOLDINGTAXSUBJECT,WITHHOLDINGTAXCERTIFICATE,WITHHOLDINGTAXEXMPTPERCENT,EXEMPTIONDATEBEGIN, EXEMPTIONDATEEND,EXEMPTIONREASON.
You’ll notice that the following fields: LegalFormDescription, Industry Description and probably, Industry Standard are not yet a whitelisted reportable fields and hence are not available fields in CDS View app. And getting them into a reporting format is definitely going to be a game changer and that is the prime focus of this blog, which we will explain as we will drill down further.
Now, let’s get to the bottom of this without any further due.
You have administrative access to SAP S/4HANA Cloud, and implementation experience on the system. You are also familiar with the implementation of Advanced Compliance Reporting on SAP S/4HANA Cloud
|Business Role||Business Role ID|
|Administrator||SAP_ BR _ ADMINISTRATOR|
|Configuration Expert – Business Process Configuration||SAP_BR_BPC_EXPERT|
Creation of the new report, swift through the following Fiori Applications and nothing gets better than a visual representation running through each of them.
Now, let’s get to the practical side of the story:
Step 1: Create the custom CDS view:
- In that list of required fields, you will notice, fields reportable are spread across I_SupplierCompanyCode, I_Supplier,I_BusinessPartner and I_SupplierWithHoldingTax.
So I created a custom CDS view: YY1_Supplier_CClevel, with a ‘standard’ scenario with the primary data source being I_SupplierCompanyCode as I want to pull suppliers at the company code level.
The join condition set up to join are all at Supplier level and Company code(where necessary) while joining I_SuppierCompanyCode and I_ SupplierWithHoldingTax along with I_Supplier and I_BusinessPartner.
Using the custom CDS view: YY1_Supplier_CClevel, I was able to gather all the fields except: Legal Form Description, Industry Description and Industry Standard.
1.2 Please note that in addition to the listed field, I will add three additional fields that matches the CHAR Type and Field length for Legal Form Description, Industry Description and Industry Standard. So, in my example, I chose OrganizationBPName1, OrganizationBPName2 and OrganizationBPName3 and will rename the Alias to LEGALFORMDESCRIPTION, INDUSTRYDESCRIPTION and INDUSTRYSTANDARD respectively. Accordingly, during the Document Definition, I will feed the values for LEGALFORMDESCRIPTION, INDUSTRYDESCRIPTION and INDUSTRYSTANDARD.
Step 2: Create Report Definition
While creating a Report Definition, you need to assign Properties, Parameters, queries, schema, document, correspondence definitions, and so on. Please refer the help document for guidelines on Creating a Report Definition
In the properties section, I will provide the report name: YY1_SUPPLIERMASTERRECORD and an appropriate Description.
2. Parameter Definition
As we are focussed to fetch Supplier Master Record at the company code level, our Parameter Definition should be Company Code. The Data Element for Company Code is BUKRS.(Take advantage of your ECC knowledge, as we rely on traditional field names and DB tables)
3. Query Definition
3.1 In Query Definition, we create our own query that we created using the Custom CDS View and assign this as the master view.
I created a custom query by the name: YY1_SUPPLIER_CC and picked the datasource: YY1_Supplier_CClevel(custom CDS View that I created from Step 1)
In the filter criteria, I provide CompanyCode as the Report Parameter as our intention is to pull all supplier Records at the Company Code level.
3.2 Now, I will need to find my way to identify the fields for LegalFormDescription, IndustryDescription and Industry Standard.
For this, I created a query: YY1_LEGALFORMDESCRIPTION to fetch Legal Form Description and I use the Database Table TB020. In the filter criteria, I will feed the Legal Form from YY1_SUPPLIER_CC, this will help me ensure I get the Legal Form Description for the respective Legal form from the query that I created from step 3.1
From the figure above, I set the Language Key( Literal) equal ‘E’ i.e. English
3.3 Similarly, I created a query:YY1_INDUSTRYDESCRIPTION to fetch Industry Description, for this, I use the CDS View IBUPAIS. In the filter criteria, I will feed the BusinessPartner from YY1_SUPPLIER_CC, this will help me ensure I get the Industry Description for the respective BusinessPartner from the query that I created from step 3.1. Please, refer the figure below:
3.4 Same goes true by creating a query: YY1_INDUSTRY for fetching other information specific to Industry such as Industry Standard and this field is available in the Database Table: BUT0IS
Save the Query and we are moving to next stage Report Definition.
4. Document Definition:
In the Document Definition, we create a Document ID by adding the Query we created in the previous step. Since the Query: YY1_SUPPLIER_CC has most of fields we require, this will serve as the main query. I named the Document ID as YY1_SUPPLIER_CC(named same as the Query name). You can name it YY1_DOCID as an example.
On point (6), It is important, we provide, extension csv, since we require, the output to be in CSV format.
On point (4) We chose the query that we created from Query Definition.
Once we pick the Query, it is important, we identify, the fields that we need in our final output.
Obviously, the query: YY1_SUPPLIER_CC do not have the following fields: LEGALFORMDESCRIPTION,INDUSTRYDESCRIPTION and INDUSTRYSTANDARD which is why we created queries outlined in the section 3.2, 3.3 and 3.4
As mentioned in the section 1.2, we will feed the values for LEGALFORMDESCRIPTION, INDUSTRYDESCRIPTION and INDUSTRYSTANDARD by fetching the query and the respective field: YY1_LEGALFORMDESCRIPTION-TEXTSHORT, YY1_INDUSTRYDECRIPTION-INDUSTRYKEYDESCRIPTION and YY1_INDUSTRY-IND_SECTOR respectively. This is applied at the ‘Mapping’ section.
And on the De-limiter section, we need the pipe ‘|’ symbol that helps to differentiate the field while extracting them in a CSV format.
STEP 3: Activate the Report and then link the report to Report Category
Step 4: Create Report Category
You can now create report categories using the Define Compliance Reports app. You must assign country, phases, organizational units, and activities to a report category. While assigning activities, you assign the phase to which it belongs. You can monitor and run the reporting activities in the Run Compliance Reports app. Please find the steps here for creating a report category.
The report category I named is YY1_Supplier_Category
Step 5: Perform the Configuration steps in Manage your solution App.
Depending on your configuration environment for SAP S/4HANA Cloud, choose one of the following options:
Configure Your Solution:
- Log on to the SAP Fiori launchpad as a Configuration Expert – Business Process Configuration.
- Open Manage Your Solution (F1241).
- If necessary, change the country or region by choosing Set Local Version.
- Choose Configure Your Solution.
- Make the following entries:
Application Area: Finance
Sub Application Area: Statutory Reporting
- For the row with Item Name of Basic Settings, choose Details (>).
- For the Setting Up Your Compliance Reporting row, choose Configure.
SAP Central Business Configuration:
- Log on to the project experience in SAP Central Business Configuration.
- In the Realize phase, navigate to the Configuration Activities
Search for the Setting Up Your Compliance Reporting activity and choose Go to Activity to navigate directly to the SAP S/4HANA Cloud system. Log on to the SAP Fiori launchpad as a Configuration Expert – Business Process Configuration
Refer the scope Item: 1J2 to get complete guidelines.
So here are snippets on how I have set it up using Managing your solution app:
Save and Exit.
Step 6: Run Advance Compliance Report
Go to the app: Run Compliance Report and provide the Report ID/Report Name you have created, in my case, it is YY1_Supplier_Category and select the Report ID from the list (if you have more than 1) and click Go.
Select the Activity Name from the Activities Tab and perform a New Run. Once the New Run is completed, select the Document Name and Download report.
Conclusion: We have several methods within our S4HANA Cloud system to extract a Supplier Master Record, what probably stands out using Advance Compliance Report(ACR) is, you can consume fields directly from the database table or CDS View that are not whitelisted Views. If you’re looking to extract a specific field that are not whitelisted and need direct consumption from database Table, I suggest, you familiarize with ECC tables and field names. Alternatively, you can you also raise incident with SAP using the component: FI-AP-AP-Q1
I hope you enjoyed reading.
Thank you! Amith Nair
References and Credits:
I want to thank Prashant Kumar from the SAP ACR team for offering his idea and expertise in building this report.