Skip to Content
Author's profile photo James Lim

How to create custom SSIS package for unlocking workstatus of selected base member without unlocking other workstatus of memebrs in SAP BPC (Microsoft Platform)

Note: This article was developed and tested for BPC 7.X so I am not sure about EPM10 (BPC 10) but if developer didn’t change its procedure name and table structure, it may work but I would like to let you know I didn’t test it in EPM10.

SAP BPC has Workstatus Function that user can or can’t submit their data from client according to workstatus state value but some users complain below things.

a.     If one of the child entity wants to adjust value after all entity’s workstatus set as ‘locked’,  there is no way to allow submitting data from only that entity. Admin has to unlock all entity so Admin can’t make sure others do not submit data during workstatus is unlocked.

b.     Even though there is TOPDOWN application parameter exists, it needs to unlock from top member to based member.

c.     In addition, if user wants to change status, it can’t change it from ‘Locked(Approved)’ to ‘Unlocked’. It means if user has 4 states in their workstatus, they have to change 3 times using current UI.

Tbl<app>Lock table has all workstatus information so user can solve above issues using Data Manager custom package. This blog article will help to create it.

Note: Create custom package should be done by customers and partners. This document is just helping to create package and doesn’t guarantee its result. SAP strongly recommends testing custom package after it is created by users or partners.

Package will work based on the selected base members.

For example, if user selects Actual, Budget and 2010.JAN, 2010.FEB, SalesUSNeast, SalesWest it will change below combinations.

Actual, 2010.JAN, SalesUSNeast

Actual, 2010.JAN, SalesWest

Actual, 2010.FEB, SalesUSNeast

Actual, 2010.FEB, SalesWest

Budget, 2010.JAN, SalesUSNeast

Budget, 2010.JAN, SalesWest

Budget, 2010.FEB, SalesUSNeast

Budget, 2010.FEB, SalesWest

Here is detail design of SSIS package.

a. Package Screen shot


-. Package will create SQL statement in the script task

-. ExecuteSQL task will execute that SQL statement

-. ForeachLoopContainter will execute stored procedure as many as combination of selected members.

b. SQL statement for creating all combination of selected base members

For example, if user selects below members, query and result set will be same as below.

Category Dimension: ‘Actual’,’Budget’

Time Dimension: ’2007.JAN’,’2007.FEB’

Entity Dimension: ‘SalesUSNeast’,’SalesSouth’

select a.*, b.* , c.* from ( select ‘actual’ as category union select ‘budget’ ) as a, (select ’2007.JAN’ as [time]union select ’2007.FEB’ ) as b, (select ‘salesusNeast’ as [entity]union select ‘salesSouth’ ) as c order by category,[time],entity


actual   2007.FEB    salesSouth

actual   2007.FEB    salesusNeast          

actual   2007.JAN    salesSouth

actual   2007.JAN    salesusNeast

budget   2007.FEB    salesSouth

budget   2007.FEB    salesusNeast

budget   2007.JAN    salesSouth

budget   2007.JAN    salesusNeast

c.  Stored procedures that needs to be executed in the loop container

<For ChangeBasememberWorkStatus>

EXEC up_SetWorkStatusFinance @StatusCode=2,@UpdateBy=N’<Execution user>,@Category=N’ACTUAL’,@Entity=N’Worldwide1′,@Time=N’2006.TOTAL’

<For ChangeWorkstatuswithChildren >

EXEC up_SetWorkStatusIncludeChildrenFinance @StatusCode=2,@UpdateBy=N’<Execution User’,@Category=N’ACTUAL’,@Entity=N’Worldwide1′,@Time=N’2006.TOTAL’

d. Data Manager Script for receiving selected dimension members

You can define Global variable and use this value for creating SQL statement in the First script task


  Note: Global variable is case sensitive so please define it as UPPERCASE


One of my customers created this package with SAP Partner based on this article and used it in the production environment now. Of course, this is not for usual case but might be useful for controlling complex workstaus without affecting workstatus of other entity.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Halomoan Zhou
      Halomoan Zhou
      Hi James

      It is a good idea, but do you share the code of the Script Task and the SP?


      Author's profile photo Former Member
      Former Member
      This sounds like a modification to the system.

      Will SAP continue to support your environment if you make this kind of change?

      Author's profile photo James Lim
      James Lim
      Blog Post Author
      This content will not change table structure but will change data of the table. Therefore, there is no problem to get a support from SAP about the BPC product.

      For supporting this package itself, this is a kind of custom solution so it will not get any support from SAP but if you create this pacakge based on my recommendation, there will be no problem.

      In the EPM 10, developer changed its structure of workstatus so performance was improved a lot but still this limitation exists (Lock/Unlock).

      I didn't check the code so I am not sure it will work in the EPM 10 but this post works for the BPC 7.X

      Thank you.
      James Lim.

      Author's profile photo Former Member
      Former Member

      Hi James,

      Our customer is having the same problem. Could you share us the code for implementation, please?

      Please contact me to

      Thanks in advance.

      William Guevara

      Author's profile photo James Lim
      James Lim
      Blog Post Author

      Hi Urquizu,

      I just noticed my post was broken since they migrated into new SCN post.

      I fixed it so that you can try.

      Thank you.


      James Lim

      Author's profile photo Jörg Finster
      Jörg Finster

      Nice post,

      in our case the unfortunatly we had to implement the "apply to all basmembers" options.

      This is a little bit tricky (Select Total Year and Total Entity to apply Workstatus).

      Any hints how to resolve this?



      Author's profile photo James Lim
      James Lim
      Blog Post Author

      Hi Jorg (sorry. I don't have O umlaut in my keyboard 🙂

      I think you can select all base members in the step b using mbr<dimension table>.

      As I remembered, you can select basemember only from that table so that you can get all list of combination.

      I hope this will help you.


      James Lim

      Author's profile photo Former Member
      Former Member

      Hello Experts,

      Jim, the blog is very helpful and Thanks for sharing it.

      Does any one know How to implement same thing BPC 10.0 NW?