Step 6: Setup the Pivot Table and Slicer. The final step is to make the data labels interactive. We do this with a pivot table and slicer. The source data for the pivot table is the Table on the left side in the image below. This table contains the three options for the different data labels.

1. Learn Excel from MrExcel Podcast, Episode 2198: A Slicer for Two Data Sets. Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. I was in New Jersey doing a seminar there, and Rick asked a question, he said, 'Hey, look, I have pivot tables built on two different data sets and I'd like one slicer to be able to control those.'
2. Version 4.0 revision 29402 built 2020-10-05 Preview Release Unstable! Version 4.13.0 revision 29505 built 2020-12-06: version 4.13.0 revision 29505 built 2020-12-06: version 4.
3. A Clear Filter button removes the filter by selecting all items in the slicer. A scroll bar enables scrolling when there are more items than are currently visible in the slicer. Border moving and resizing controls allow you to change the size and location of the slicer.

This document will describe step by step procedure to implement Data Slice lock using BPC embedded Technology. This concept has been described using BPC 11 Embedded and BW4HANA technology.

• Administrator can Lock / Unlock Data via Analysis Office Template by clicking one button.
• Once Data Selection is being locked by administrator, no one can plan or post any data for that locked selection.
• Once Data is being unlocked by administrator, posting / planning can be possible within that unlocked data selection.

We assumed a generic architecture, which can be used by any planning scenario.

Normally for any Embedded Planning scenario, we have two distinct data flows. One is normally coming from BW Actual Data which is denoted in below diagram as ACTUAL – Data aDSO. The PLAN – Data aDSO denotes the Planning related data to be posted in this aDSO. The composite provider joins both Actual and Plan data. The Aggregation Level – Plan has been created on the top of Composite Provider and the Planning Query has been created on the top of Aggregation level. The Planning Sequence has been created on the top of Aggregation Level.

We introduced another aDSO, named LOCK aDSO which will store the Lock unlock information. The lock and Unlock will be two planning functions one will make an entry with some Flag value as Locked and another planning function will change the Flag value as unlock. The Below figure shows the diagram.

Sometimes the Lock / Unlock information can be stored in within same Plan aDSO. The Composite provider can be created on the top of all 3 aDSO’s: Actual aDSO, Plan aDSO and Lock aDSO.

The below figure shows the diagram.

In our example we have a transaction data for Actual and Planning as below.

We want the below mentioned selection is relevant for locking mechanism. For any entry with a flag value ‘1’ is locked and the same selection with flag value is ‘0’ for unlock.

• Direct Update Planning aDSO to be created.

For storing the Lock / Unlock information we created a separate aDSO which is planning enable and it is also direct update in settings.

In our example, the below characteristic are the lock relevant.

We maintain the primary key as it is direct update aDSO.

4 Level Data Slicer As Seen On Tv

• Aggregation Level for lock to be created.

We need to create the Filter, Planning Function and Planning Sequence for Lock / Unlock Functionality. We added all lock relevant characteristic in Lock / Unlock Aggregation level.

• Planning Filter for Lock / Unlock to be created.

We are going to use the same Planning filter for Lock operation and same planning filter for unlock operation. Normally administrator will first lock and then unlock for a particular selection.

In our example, Administrator will lock or Unlock based on Account and Posting Period Value.

• Planning Function for Lock to be created.

We are going to create a planning function which will be created by FOX code. Dynamically we will read the planning filter value via FOX code and add the lock entry in direct update aDSO.

The FOX code shown below will generate an entry for lock. We are adding one entry with Flag value as ‘1’ which represent the Lock entry.

• Planning Sequence for Lock to be created.

We will call this Lock function from Analysis Office Template, so we are going to create a planning Sequence.

• Planning Function for Unlock to be created.

To unlock, we are going to modify the Flag value to ‘0’. We are going to create a Planning Function for unlock functionality.

See the settings.

The FOX cod would be as below.

• Planning Sequence for Unlock to be created.

To call the planning sequence from Analysis Office Template, we need a planning sequence.

• Analysis Office Work book to be created.

We are going to create Two Buttons in our example, one is Lock and one is Unlock. We need a have user selection where user wants to see whether the particular selection is locked or unlocked. The Template Looks like below.

If you click, lock button and entry for lock will be generated. If you unlock the button then same entry flag will be modified to ‘0’. Let’s press lock button and see the changes.

See the lock entry.

Let’s press the unlock button and see the changes.

• Exit to be created.

This Exit class will read the value dynamically during planning posting in planning cube and stop posting data if the value is locked. Need to create a class and write the code to read the value from aDSO.

See the class and method code implemented.

To create the class and method, please follow the bellow link.

• Data Slice to be activated.

Now the exit class to be linked with data slice where planning can be done.

Go to RSPLAN and select the PLAN aDSO name.

You can refer the below link for step by step creation.

• Test the Data Slice.

We have a planning Query which will input data in our system. Our intention is to check the data slice working principle.

a) First step, we will push the data from input enable query.

We open the input enable query and we have seen it is input enable so we can change the data.

b) Second Step, we will lock the selection from administrative template.

Now we are going to lock the data via administrative template.

c) Third step, Try to push data in system via same input enable query. The posting of data is not allowed.

To lock the mentioned selection, we run the lock button providing the same selection.

We do not have any values as we have not pressed the button. Press the Lock button so the lock entry will be generated.

See the locked entry is grayed out, that means the input is not possible. Planning is not available for this selection.

d) Last step, unlock the data selection and try to post the data again via query.

Excel Slicer Data Model

Unlock the data via Administrative template.

4 Level Data Slicer Attachment

The unlock functionality worked, which means that the posting from input enable query is now possible.