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 http.send If http.Status <> "200" Then SetWS = http.responseText Else 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.)
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