Skip to Content
Technical Articles
Author's profile photo Vadim Kalinin

BPC NW 7.5 Work Status setting from Excel VBA

In BPC 7.5 in order to set work status you have to use Web page launched with the menu item in the Excel. This interface is limited to one cube at a time and single member for each dimension. If you have to set work status for the list of months and for number of cubes it can take some time doing the repetitive operations.

Looking on the code executed on this page I was able to write VBA function that will set work status without going to Web interface. The code is here:

Public Function SetWS(strD1Name As String, strD2Name As String, strD3Name As String, _
    strD1Mem As String, strD2Mem As String, strD3Mem As String, _
    strWSStr As String, strWSNum As String, strIncludeChild As String, _
    strServer As String, strAppSet As String, strApp As String) As String

    Const strURLPrefix As String = "/OSoft/Proxy/WorkStatusProxy/WorkstatusProxy.asmx/SetWorkStatus?"

    Dim http As New MSXML2.XMLHTTP
    Dim strURL As String

    strURL = "http://" & strServer & strURLPrefix & "strAppSet=" & strAppSet & "&strApp=" & strApp & "&strDims=" & strD1Name & "%2C" & strD2Name & "%2C" & strD3Name & _
        "&strMems=" & strD1Mem & "%2C" & strD2Mem & "%2C" & strD3Mem & _
        "&strStatus=" & strWSNum & "&strPrimary=" & strD1Name & "&strOldStatus=None&strNewStatus=" & strWSStr & "&strInclude=" & strIncludeChild

    http.Open "GET", strURL, False

    If http.Status <> "200" Then
        SetWS = http.responseText
        SetWS = "Ok"
    End If
End Function

In VBA editor Tools -> References you have to check reference to “Microsoft XML, v(some version)” for the definition: Dim http As New MSXML2.XMLHTTP

strD1Name, strD2Name, strD3Name – Names of work status dimensions.

strD1Mem, strD2Mem, strD2Mem – Members (single member per dimension) of work status dimensions

strWSStr – work status name to be set, ex.: DEFAULT%20WORK%20STATE (blanks are replaced with %20)

strWSNum – work status ID, ex.: 0000 for DEFAULT WORK STATE (can be investigated on the WEB page)

strIncludeChild – “1” – set status on chilren; “0” – not set status on children

strServer, strAppSet, strApp – BPC server name, AppSet and Application

The function will return “Ok” if success and BPC Error text if some error happened.

You can execute this function in a loop (by Application, by TIME period etc.)

B.R. Vadim

P.S. I don’t find the way to do the same things in BPC NW 10

P.P.S. The only solution for BPC NW 10+ is to use DM package with custom logic badi: How To: Change Work Status from Script Logic using BAdI

Parameters for this DM package can be passed using: Simple VBA procedure to pass parameters to DM packages


Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Vadim, in the Railways, we know how to solve this problem in 10. come and work with us, we'll tell you 🙂

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      Hi Daria,

      If you have some good idea about BPC 10 please share it here. The only way I know how to do it in 10 is to use some custom logic badi...


      Author's profile photo Former Member
      Former Member

      Good solution, however there are still some limitations with BPC work status in my opinion.

      What has been good are steps forward to integrate workflow (BPF's) with data protection, however I'm yet to find solutions for the following:


      1. Owners/Users to assign work status to multiple Profit Centres at one time, without being the owner of the parent (i.e. Manager) providing them with too many statuses, e.g. Approve/Lock
      2. Ability to pass multiple years in to the selection
      3. Option for Managers to select 'Member and Descendants' for faster setting of entire company or top levels
      4. Ability to restrict an Owner to move the Work State back to the Default / Unlocked once Submitted - Requirements to have 'one it is submitted, manager must re-open' is useful to ensure stability in numbers (although setting Locked for all activities on Default Work State is workaround)

      I have seen BADI's implemented a few times to solve these problems but would be good for standard solutions to meet the requirements.

      Let me know if there are any thoughts on the above


      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      Simple badi to set work status can do the job. No issues!