PPDS on S/4HANA

Introduction to PPDS (Production Planning and Detailed Scheduling)

Production Planning and Detailed Scheduling(PPDS) on SAP S/4HANA is the product running on SAP S/4HANA.  Production planning and detailed scheduling is a tool in APO(Advanced Planning and Optimization) which takes care of planning and scheduling considering the capacities of various resources.

You can use SAP PPDS on SAP S/4HANA to:

1.Create planned orders for in-house production for all the requirements.

2.Optimize the result so that the resources are well consumed.

3. Plan with various variants and yet produce the optimized results.

SAP PPDS for S/4HANA 

SAP PPDS for S/4HANA  allows the user to use PPDS in a more simplified way where he doesn’t have to worry much about CIF.

Let’s have a look on what is the advanced feature in material master for S/4HANA with PPDS.

In the transaction MM01, there is a new view “Advanced planning” which would tell whether the material should be considered for planning immediately in PPDS.

It includes the data like planning procedure, horizon, plan explosion etc…

Similar way, work center also has a check box for ‘Advanced Planning’.

S/4HANA has made it simple for the master data CIF . PPDS for S/4HANA still uses only one system but the CIF of data to PPDS is much easier.

The user has the liberty to create the product in ERP completely and then CIF it to PPDS, ‘completely’ I mean all the details like planning procedure, plan explosion, lot size etc…

I use the term PPDS interchangeably with APO as for S/4HANA , PPDS means APO.

Pre-requisite 

Set the Advanced planning and Scheduling active.

This is done in SPRO.

Master data changes 

The material master in ERP system has one more additional view as said which is ‘Advanced Planning’ as shown below.

The ‘Advanced Planning’ view in the material has a checkbox ‘Advanced Planning’ , PPDS details like plan explosion, planning procedure, Demand details, Lot size details , GR /GI processing times, and Shelf life times.

All these details can be taken from ERP system and planned in PPDS.

The flag for ‘Advanced planning’ indicates that the material should be transferred to APO system for PPDS planning and this will be handled by the system itself. No additional effort to CIF the data. It means that there is no need to create an integration model for the material.

The screen below shows the Advanced Planning view in detail.

Along with the material, work centers are another set of master data which the PPDS would be using.

Work centers are the actual resources using which the production takes place in the shop floor.

There is a check box for ‘Advanced Planning’ in the Basic data tab of the work center in transaction CR01.

The flag for this check box indicates that the work center is immediately available for planning in SAP PPDS. Again, integration model need not be created for work centers.

The below screenshot shows the ‘Advanced Planning’ checkbox for a work center.

So, overall when you have your plants ready in PPDS system, you can create materials and work centers which would immediately be available in PPDS for planning.

CIF – PDS

Production data structure or PDS is the blue print of the material with all the details like BOM (Bill of Material), routing/recipe in APO. PDS is generated in the APO system using the production version of the material. Production version in turn has the details of BOM and Routing/recipe.

Now, the Production Data Structure needs to be integrated to APO system. This is not done in the traditional way i.e., using the transaction CFM1, rather we use the transaction /CURTOADV_CREATE.

The screenshot below shows /CURTOADV_CREATE screen.

Overall the introduction of PPDS on S/4HANA  would allow the user to modify, rebuild the master data structure and integrate it with the PPDS system easily with very minute changes.

Continue reading

S/4 HANA F4 Help values, Key & Text display in ABAP CDS Views based reports

Overview:

F4 help values, Key & Text display is standard feature in SAP however in Embedded Analytics reports (Custom ABAP CDS views Development) these two functionalities will not come by default. This blog will help how to leverage these functionalities in custom developed reports in Embedded Analytics.

Version: S/4 HANA 1610

Problem Statement:

For custom developed ABAP CDS views based reports in Embedded Analytics F4 help values, Key & Text display was not appearing in reports by default as in ABAP reports or in BW reports.

Solution:

Follow the below steps to leverage these two standard functionalities in Embedded Analytics reports.

Prerequisite: In this blog we are using standard Basic Views to bring F4 help values, Key & Text usage in reports. By default highlighted annotations should be there in the standard ABAP CDS views if not add these two annotations to leverage F4 help values, Key & Text display in reports.

Scenario 1:

When dimension (Material) is inherited from custom/standard CDS view in the basic/composite CDS view. Report should show F4 help values, Key & Text display.

Step 1: In the basic CDS view Material should come from I_PurchasingDocumentItem hence association is created in the standard CDS view.

I_PurchasingDocumentItem CDS view

In I_PurchasingDocumentItem CDS view I_Material is already associated hence it is not required to associate separately with I_Material in our CDS view, directly we can consume those associations in our views as mentioned below.

This is required because we are directly bringing the text in composite view hence the below code is mandatory.

_PurchaseItem._Material

Step 2: In the composite view mention the below highlighted annotations so that text can derive from the Basic view (Step 1).

In the above code we have to mention the Text associated view name next to the Key hence _Material is mentioned after _Purchase.Material

Step 3: In Consumption view mention the query display as Key and Text as mentioned below.

Step 4: Now run the report in RSRT or Analysis Office, highlighted (Yellow) is text for Material which wasn’t appearing earlier.

Scenario 2:

When dimension (GL Account) is coming from table, report should have F4 help values, Key & Text display.

Step 1: Here GL Account number is directly coming from EKKN table.

Mention the foreign key association as mentioned below.

Step 2: GL Account number is coming from table hence we need to separately associate GL Account to bring the F4 help value hence the standard CDS view I_GLAccount is used in our composite view.

Mention the foreign key association as mentioned below.

GL Account F4 help value need to display Text as well hence in the composite view we have mentioned the association name 

No need to mention the association name next to GL Account Number as mentioned in Scenario 1 (Step2) because here we are explicitly associating GL Account basic view in the composite view hence it is not required.

Step 4: In Consumption view mention the query display as Key and Text.

Step 5: Run the report through RSRT or Analysis office, earlier F4 help for GL Account wasn’t appearing earlier.

As GL account standard ABAP CDS view will bring text, we have enabled the text in our CDS view so report will display the text as well. Highlighted (Yellow) columns are text fields in report which wasn’t appearing earlier.

Scenario 3:

When dimension (Document Year) is coming from table, report should have F4 help values no need of Key & Text display.

Step 1: Here Material Document Year is directly coming from EKBE table in our basic view.

Step 2: Material Document Year is coming from table hence we need to separately associate Document Year in composite view to bring the F4 help value hence the standard ABAP CDS view I_CalendarYear is used.

Mention the foreign key association as mentioned below.

Step 3: In Consumption view mention your selection prompt annotations.

Step 4: Run the report through RSRT or Analysis office, earlier F4 help for Document Year wasn’t appearing earlier.

Continue reading

Central Finance: Challenges in Finance Transformation

There are many business cases for S/4HANA Adoption for existing SAP Customers. However, the journey to S/4HANA Business Suite is fraught with its own challenges which are inevitable – the costs and risks of migrating and upgrading to a new system, especially for those customers, who have built very complex and distributed landscapes spanning across geographies and businesses.

One element of S/4HANA, which has caught the imagination of many of the Global Customers is Central Finance, which has been projected as the “Smartest”, “Fastest”, “Simplest” way to latch on to S/4HANA bandwagon with a quick win of simplified and unified financial reporting structure. The approach of Central Finance, instead of forcing customers to migrate their SAP instances to S/4HANA Application, allows financial documents to be replicated into a new Central Finance instance running on S/4HANA Business Suite. This is particularly true for Customers who have multiple old SAP ERP Instances and non SAP Systems as well. This gives an organization wide financial reporting view, with minimal intervention to the Transaction Processing Source SAP and non SAP Systems. It has been claimed to be a quick win solution for all the Enterprise Customers of SAP, with extremely short turnaround time for adaption.

However, there are series of multiple challenges, which need to be considered in implementation of Central Finance. Some of them can dearly cost the S/4HANA Journey and cost thousands of dollars in terms of effort and missed objectives. Some of the business related challenges and technical product related challenges that I have encountered in my journey on Central Finance Project are below:

1. Organizational Structure: One of the biggest use cases for central finance is to look at harmonized reporting for multiple markets. A logical extension of central finance on S/4HANA is to use the same CFIN System as a future Business System with integrated Finance and logistics transaction system. Therefore, it makes the organizational structure design extremely critical. In one of my projects, the customer in the legacy ERP (Source SAP ERP Systems) was using Business Area, Segment, Profit center etc as key considerations for reporting. Additionally, the Logistics Org Structures like Sales Org were not having in synch in various Source ERP System, as well as non SAP Source system did not have the org object which were relevant from SAP standpoint. As an IT Strategy roadmap, the customer wanted all the markets, where it operated to be on S/4HANA Business Suite and CFIN was the starting point. Additionally, many of the org structures were used in Account Based COPA in CFIN. So the Organizational structure design is a very crucial factor for long term journey and it is critical that design of org structure is thought through in CFIN. This should take into consideration the future logistics org structure as well

2. New GL and Document Splitting in S/4HANA Central Finance: Document splitting and parallel ledgers functionality is one of the key asks from Finance Transformation for a large scale Enterprise level customer. Many of the customers might be using a source system without functionalities like new GL or Document splitting, but would want to use the S/4HANA CFIN with these functionalities. However a source system without Document splitting for eg and a target CFIN System with Document splitting is NOT a functionality which is supported out of box. It takes a real challenge to make sure that all the documents replicated from the source SAP ERP and non SAP Systems have the right level of details to support Document splitting. There are options available like BADI_FINS_CFIN_AC_INTERFACE, which can be used to enable the right rules for populating the account assignments for Document splitting. Some of the examples for Document splitting and parallel ledger usage:

◈ The account assignments for Document splitting can be populated only in some of the line items and let standard Splitting set up take care of offsetting line item (eg, Profit Center and segment update in line items and let vendor line item derive the same from offsetting line item in case of Vendor Invoice Posting).

◈ Some of the FI Document line items can derive account assignment objects for document splitting from areas like material master, cost center etc. The customer specific changes, should not be triggered in case, where standard derivation logic can work

◈ Secondary CO Postings from Source systems, where the profit center is not available in one of the line items, the customer specification needs to be updated

◈ Data from Non SAP systems needs to be enriched with account assignment details for postings in the target central finance system

◈ There are transfer postings between subledgers which fail in the target system if it has document splitting active.

3. Master Data Harmonization: Master data replication of objects like Cost Center, Profit center, GL Accounts, customers, Vendors etc is a challenge. Data harmonization is a key challenge in Enterprise customers with multiple SAP Systems. Some of the challenges in master data set up in Central Finance encountered in my experience are:

◈ Customer Vendor Integration - The central finance function was used in S/4HANA 1610 EM instance, where the customers and vendors are created as Business Partners and the number ranges were overlapping in the source system for Customer and vendor account groups. To tide over the issue, the number ranges for Vendors were changed in the target CFIN System

◈ GL Master Data Replication - The SAP ERP System were substantially old systems, where transactional data existed for more than 15 years. As part of CFIN replication, only few years of historical data was being replicated into the target CFIN System. So the GL Master replication was designed based on the no of years in past where transactional data existed for a GL, that only was replicated into the target system

◈ Profit Center and Segments – One of the outcome of org structure redesign in CFIN was the whole profit center and segment usage in Central finance. The profit center in target CFIN system was tagged to Brand for reporting, while a segment was concentrated around line of business. However, the brands in the source system were limited to profit center with Business area being used for line of business reporting. The usage of different objects for reporting in source and target system entailed usage of complex mapping framework for replication of master data

◈ Material master – The material master usage was also a challenge due to disparate systems. A governance structure was required to be put in place to synch up the master data in the source systems and than have the same replicated in the target system.

◈ Data Services or SLT for Master Data – Harmonization and Business Objectives for Master data usage greatly determined the usage of the technology layer for replication between Source SAP and non SAP Systems to S/4HANA Central Finance Environment. Based on specific requirements like Data Filters for GL Account, Numbering changes etc, it was decided to introduce Data Services as a technology layer between Source and Target system for master data replication in S/4HANA Master Data replication.

1. MDG Layer and Value / Key Mappings: Central Finance offers an integrated MDG layer which can be used to recreate the accounting document in the target system, based on the data replication from the source. Some of the key learnings from my association with the central finance project were:

◈ Mapping Action - The mapping action determines if a transformation is required for any data element. In one of my project, business area was very widely used for financial reporting in the source SAP system. However, the complete reimagining of financial reporting meant that business area was not a key reporting factor in Central Finance. Hence a mapping action was set to clear the business area information from the source data. Essentially, the Mapping action should be designed taking into consideration of what are the key financial reporting parameters and if those parameters will hold true for the business even in case of a completely integrated system of records with finance and logistics transactions happening in the same system some time into future.

◈ Key Mapping - Typically the identifiers for a business object can be different in source and target system, given that Central Finance has the ability to connect to a heterogeneous set of SAP and non SAP systems. Eg, a customer 2212 in a source system may be customer (BP) 500000 in CFIN to harmonize master data. In one of the projectw, the key ask was to manage millions of customer master, vendor, gl etc to name and arrive at a common structure. The current design of central finance does not support automatic mapping of the source and target object number, though there is a standard upload tool to do an excel upload using the transaction code FINS_CFIN_MAP_MANAGE. However, to manage the real time replication of master data in the mapping layer, a custom program was created for each of the object to ensure, that there are no failures in transactions

◈ Value Mapping Structure - The value mapping is used in Central Finance to ensure if any transaction specific value has to be transformed. In some of the projects, the source systems had custom values for objects like Document types, Tax Codes etc. The whole objective of the transformation program was to ensure a standardized approach to finance structure across multiple markets. Eg, the markets used Document type R1 and RX for billing in two different geographies, but the intention in the target central finance system was to ensure that all the geographies, where the business operated should have the same document type for billing transactions. Hence the value mapping functionality was used to ensure that the new system has a standardized approach

2. COPA Transformation from Costing based COPA to Account based COPA: Most of the customers in SAP World use Costing based COPA. However, the preferred approach for reporting in S/4HANA Is Account based COPA. The data replication from the Costing based COPA to Account based COPA is not a standard and out of box functionality in S/4HANA. Some of the challenges encountered have been:

◈ There are scenarios in CB COPA where COPA data and GL postings happen at different time, whereas the expectation on AB COPA is to have the data based in COPA and GL at the same time. This presented it’s own challenges since AB COPA was supposed to be used for all kind of profitability analysis. Some of the design considerations for this transformation included moving key data like Sales Area data, material product hierarchy etc from material master, customer sales area data into central finance. These attributes were used in the derivation rule to ensure that the right attributes are populated. In some of the scenario like post goods issue, the data which is relevant for COPA was pulled through custom enhancements into Central finance.

◈ There are condition types on the billing cycle, which post data into COPA but not into GL. This functionality is not yet supported in AB COPA in S/4HANA. This means that the business had to look at the way, in which some of the discounts and pricing could be extended to Billing cycle

3. Transactional Data Posting: Central finance is supposed to reflect all of transactional financial data into a single place from multiple systems. However, the fine print is not all straight forward. There are not all scenarios which can be replicated into Central Finance. For eg, the transactional postings on Asset Sub ledger and WBS Elements through direct replication is not yet supported in central finance. There are quite a few challenges for customers, who already have SAP Source system which have parallel ledgers implemented. In such cases, the fiscal year differe nce in multiple ledgers presents its own issues. Some of the challenges assigned in Transactional data postings are:

◈ Asset Subledger postings are not supported in S/4HANA Central Finance. The posting on asset sub ledger in the source system have to be replicated on a normal GL Accounts in the target central finance system. We utilized the Key Mapping functionality in MDG Layer in Central Finance to ensure that the postings happened correctly

◈ The Transactional postings on WBS System in the source are not replicated into Central finance. This is mapped as a functionality, which will be addressed in the new releases of Central Finance. A custom enhancement was done in one of our projects to create the postings on other supported cost objects like internal order.

◈ Some of the legal entities had multiple ledgers in the source system with both ledgers having different fiscal year variants (one corresponding to 5-5-4 set up and other being April – March). The specification of the cutover month in source system for balance upload and document replication start due to different fiscal years in leading and non leading ledger meant duplicate data being posted in the non leading ledger, since the balance upload happens based on the fiscal year set up for leading ledger.

◈ A big challenge faced was the replication of the open items on the sub ledger and GL. The original document in source might be open at the time of initial load, but cleared in the period replication started. Such practical issues led to document failures in replication when subsequent actions on open items are not replicated immediately in central finance. These have been resolved through implementation of correction notes and custom code involvement

Continue reading

Production Planning Innovations in SAP S/4HANA for Faster and More Efficient Manufacturing

Manufacturing is under increasing pressure to deliver faster, better, and smarter. From the rise of additive manufacturing and robotics, to e-commerce and same day delivery, the list of technology and market trends reshaping the manufacturing function is only growing. These disruptions create new challenges for classical manufacturing especially around production planning processes.

But as these new technologies present new challenges, they also present new opportunities. 3D printing may be disrupting centralized manufacturing, but it also gives manufactures more freedom to bring new more complex geometrical designs, that have been difficult to create with subtractive manufacturing techniques, all while reducing inventory levels, time to market and waste. By simplifying the underlying data model of their manufacturing systems, companies can better tap into their data supply and capitalize on new trends. All of this is possible now, with the application support that allows for streamlined, agile manufacturing.

SAP S/4HANA has come a long way since its first iteration as a purely financial module. Starting with the 1511 release, we began focusing on simplified supply chain and manufacturing solutions, architectures, and data models. Now, with the release of 1610, we are taking these enhancements and innovations to another level. In this post, I want to focus on material requirements planning (MRP), the process by which a manufacturing company analyzes its sales orders, demand forecasts and inventory to determine which materials and components it needs to purchase, produce in house or transfer from a neighboring plant to deliver on its order commitments.

Classical ERP vs. S/4HANA

In the classical ERP solution, MRP has been extremely effective in offering manufacturing customers with basic production planning capabilities that were quick to implement and easy to use. The downside is that MRP assumes infinite manufacturing resources. MRP’s lack of acknowledgement of production environment constraints such as skilled labor pool or manufacturing capacity sets up an obviously unrealistic scenario that must then be addressed by a secondary application such as Production Planning and Detailed Scheduling (PP/DS) in SAP Advanced Planning and Optimization (SAP APO). This is one of the manufacturing inefficiencies we are addressing with the 1610 release.

To better illustrate the difference between old and new, let us look at a hypothetical bicycle manufacturer called Speedy Bikes. Let us say, Speedy Bikes gets an order to build a bicycle and deliver it by noon the same day. Let’s also assume for simplicity that the Bill of Material (BOM) of Speedy Bikes consists of only 3 components as in figure 1.

Figure 1. Bicycle Simplified Bill of Material

In the classical ERP solution, the sales order is passed on to MRP which explodes it into its BOM, compares that against inventory levels and then determines what components need to be built in house, transferred from another plant or ordered externally to deliver the bicycle. Taking into account the dependability between the different BOM levels, MRP works its way backward from the delivery date and builds a production plan like the one in Figure 2.

Figure 2. Infinite MRP Planning in Classical ERP

That’s all fine, but now let us assume that one of the production lines used to build the wheels is unavailable or occupied between 9:30 to 10:30, and the chain cannot be installed until the wheels are ready because of the BOM dependency. The classical ERP solution in MRP would not be able to deal with such a constraint, because of its assumption of infinite resources. So, the MRP controller now has to rely on an advanced production solution, such as SAP Advanced Planning and Optimization (APO), to build a constraint based plan for the wheels and then go back into MRP to consolidate both plans. It is a cumbersome, time-consuming process that slows down production (Figure 3 and Figure 4).

Figure 3. Current Functional Model in Classical ERP

Figure 4. Finite Planning in APO PP/DS

If we look at the same scenario using SAP S/4HANA, that now has both constraint and unconstraint based planning embedded in one system, we see a much simpler and more efficient process (Figure 5). Now an MRP controller can build the entire bicycle and factor in the constraints on a particular material in a single place. In addition to being obviously simpler for the controller in terms of time, effort and user-experience, there are fewer errors in terms of transferring and consolidating data between two systems.

Figure 5. Unified Production functionality in SAP S/4HANA

If all this seems a little too confusing then watch this 2 min explanation video.

Innovation beyond integration

We have done more than take advanced functionalities from other areas of the SAP portfolio and embed them into S/4HANA. We are also delivering new innovations and functionality. One example of an innovation that we are bringing with MRP is the Truckload Optimized Purchase Orders Fiori app (Figure 6.).

Figure 6. Screenshot of new Truckload Optimization Fiori App

If your company places multiple orders for different materials with the same vendor, the truckload optimization app can bundle these orders in a way that optimizes the shipments of these materials. The app allows the MRP controller to enter in the load capacity of the trucks that the vendor uses to transfer the materials to your site, and the app considering the priority of the materials – optimizes them so that they consume fewer trucks or fewer shipments from the vendor.

Of course, the vendors bill for every truck transaction, so by minimizing the number of transactions, you can save on supply chain and transportation costs and, at the same time, ensure that you are fulfilling the orders of the highest priority among your sales orders.

The next level of efficiency

Essentially, when you look at the changes that S/4HANA has brought to production planning, it has simplified things by integrating constraint and unconstraint planning and it has offered users new capabilities and innovations that will make a big difference in manufacturing efficiency and effectiveness and the overall ability to respond to new technology and market trends.

Continue reading

MRP on SAP S/4 HANA: Insight to Action

Material Requirement planning key process in production to maintain optimized level of inventory while producing the product at expected timeline and cost. SAP has versatile Material requirement planning tool which has served as most useful for years. Increasing inventory cost improved computing capability brings the expectation of further improvement of MRP.

These are few challenges with current MRP tool:

◈ Long and resource intensive MRP run means a periodic and batch process.
◈ New Demand and Supply changes make MRP plan immediately outdated.
◈ No real time single view for material information and shortages.
◈ Navigation to multiple ERP transaction to evaluate solution alternatives.

Improving Manufacturing to next level will need below goal to be achieved:

SAP S/4 HANA as digital core is capable to execute data intensive MRP process with unprecedented speed. MRP Live (MD01N) optimizes process to reduce MRP run time up to 10 fold and makes MRP Fast, Simple and Cross Plant.

S/4 HANA 1610 is not just about the speed of MRP execution but also provide Smart solution to manage material shortages and other requirement around MRP.  In scope of this blog we will discuss FIORI Apps available in S/4 HANA 1610. Below is snapshot of All MRP related FIORI app available with 1610.

Material availability can be influenced by several factors like delayed material status, Sales Order fulfillment, change in customer order and several others. MRP controller should be aware of real time material shortage situation.

Manage Material Shortages

With S4 HANA FIORI apps MRP Controller will see all relevant information side by side allowing him to have quick look and meaningful insight to take immediate action.

Below is snapshot of Material shortages for the MRP Planner.

This app highlight issue across thousands of material stock situation. MRP Planner has option to check stock requirement list from the same app.

Solution doesn’t only show the material shortage situation but also recommend solution with option to simulate the possible solution and check viability.

Insight to action capability of solution help to resolve the issue before it happens.

Manage Change Requests - MRP

Change request can be raised when using app for managing material and uncovered requirement. App for new, requested and answered change request are available MRP Planner.

Proposed changes can be requested from vendor and status can be updated from Answered to Closed upon response of vendor.

Email can be sent to vendor and on their response changes to Purchase order or stock transfer order can be applied.

Manage Production Order

Production order can also be managed from MRP cockpit. Manage Production order app provide list of your order with uncovered items.

Individual production order at app has Stock Requirement list, Component info, Milestone, Material overview and order detail. User can also navigate to fact sheets and back end transaction.

Manage Internal and External requirement:

There are separate apps available for Internal and external requirements. MRP controller can check the coverage of component required for in-house Production Order, Process order, Maintenance order and network order. Solution recommendation are available for handling shortage situation similar to material Shortages app.

Manage external requirement app provide view of uncovered and delayed items required for sales order and STO. App recommend solution to overcome the situation with preview capability.

Display MRP Material Issues:

This app provides view of material for which issues occurred in MRP execution. Planner can get the information to analyzes the issue with material for which problem occurred in the planning run.

MRP scheduling and Key figures:

Planner can schedule MRP from Fiori app and review the status of the MRP execution.

MRP Key Figures:

Exception handling based on real time data and early identification of issues with different materials improves material flow and reduce lead time of product development.

Transformed user experience for the material planner has great potential to boost Planner productivity. Simplified and Insightful FIORI Application gives delightful user experience to the Material Planners.

Continue reading

How to create ABAP CDS View with enabling derivation of the value for parameters

There have been dozens of blogs online for walking through developers on how to create ABAP CDS view with parameters in ABAP for HANA, however, one major issue still exists is that although input parameters can restrict or filter the data from CDS views, the limitation of the value input for a parameter to filter out more specific data range is significant.

Here the author presents you a way to enable derivation of the value for parameters to tackle similar problems in your future development using the example shown below.

Taking a financial case as an example, if we want to trace back the last three months data instead of user selected month period using the input parameter as the filter, creating parameters traditionally without any derivation would be insufficient.

This is one example in Analysis for Office 2.4 SP3 (latest version), where the prompt for user input is shown as below:

In this simple case, our objective is to get the last three months of data tracing back from the end of April, which should include February, March and April data, and the user inputs have been shown above.

Here are the steps on how to realize this functionality by enabling derivation of the value for user input parameters.

Step 1: Create a private basic view for the parameters we want to make derivation with in the bottom layer:

Rationale: This basic view is to make sure it only returns one record all the time, so we can create a dummy table with only one row.

Step 2: Create another similar basic view on top of the previous view, but here we include all the necessary calculations to the parameter:

Rationale: CDS view parameters cannot be used directly in the same view. That is why we need to have these two layers of views as shown above. FYI, the red block represents the logic of this input parameter derivation.

Step 3: In a consumption view where we want to use this derivation along with our defined parameters, we can create a parameter where user can fill in their initial values, say April, in this case. This is more like a starting point of the user period selection:

Step 4: Add the derivation annotation on top of the second parameter, where we plan to integrate the derivation, and we hide this parameter to make it compatible with the prompt you saw before Step 1:

Rationale:

@Consumption.derivation.loopupEntity

Reads the result to fill the parameter, in this case, it is the view we created in Step 2 (P_QUARTERSTARTPERIOD).

@Consumption.derivation.resultElement

Element of the entity/scalar export parameter of the procedure providing the result, where in this case is the end parameter name we defined after calculation and derivation (QuarterStartPeriod)

@Consumption.derivation.binding.targetElement

This enables provision of a parametrization for the (scalar) input parameters of the lookupEntity procedure. This sub-annotation specifies a parameter name of lookupEntity or procedure, where in this case is the parameter we created in Step 1 and Step 2, i.e. P_Period.

@Consumption.derivation.binding.type

Specifies how the target is filled (by a constant, a parameter or a (filtered) element), where in this case is the #Parameter.

@Consumption.derivation.binding.value

It contains, depending on the type, the constant value, the parameter name or the element name, where in this case is the parameter we defined in Step 3 of this consumption view, in other words, is the parameter we want to derive from, i.e. P_CurrentToPeriod.

After these steps, the user should be able to get the last three months data with only one user input, which in this case is February, March and April, according to the logic we made in Step 2. The following is an example, showing part of the report after clicking OK in prompt with user input April as a requesting period:

The applications of CDS view parameter derivation are enormous, developers can easily utilize this feature and thus make the future CDS view development more smoothly and flexible, no matter what frontend tools we implement.

Continue reading