sheldon.edelstein

Ownership Manager System Generated Proposals from SAP Business Objects Planning and Consolidation 10.0, version for Netweaver

Sheldon Edelstein — Thu, 18 Aug 2011 09:07:55 GMT

Similar in function to the Dynamic Hierarchy Editor available in BPC 7.x versions, the SAP Business Objects Planning and Consolidation 10.0, version for Netweaver (BPC10NW), Ownership Manager allows the definition of complex holding/subsidiary relationships that can be time, category, and scope specific. The organizational structures defined by Ownership Manager are also used by the Consolidation Monitor and the Controls Monitor to display and status the defined organizational levels.

New to the Ownership Manager functionality is the ability for the BPC system to propose key consolidation parameters.

There are three main prerequisites for activating system calculations for ownership manager:

1. Model and Master Data requirements

Ownership manager functionality requires a configured ownership manager model as defined by :

Ownership-based Hierarchy Requirements .

Summary requirements:

Required member ids in the O_ACCOUNT (ownership model account) dimension:

METHOD

METHOD_SYS is populated by the system to store the generated proposal for the consolidation method

PCON

PCON_SYS is populated by the system to store the generated proposal for the consolidation rate

POWN

POWN is also known as the ultimate percent ownership of an entity by the holding company. It is the calculated share percentage based upon a specified calculation method (Direct Share or Group Share). For the Direct Share calculation, the percent ownership is assumed to be 100% of the subsidiary ownership. For Group Share calculations, the percent ownership is the product between the various subsidiaries percentages.

Example:

Holding Company (H1) owns 70% of subsidiary (S1) and S1 owns 50% of subsidiary S2.

Using the Direct Share method, the ultimate percent ownership of S2 is 50%.

Using the Group Share method, the ultimate percent ownership of S2 is 35% (=70%x50%).

directly or indirectly

PCTRL

PCTRL identifies the ultimate percent of control for each entity in a model.

Member ids used internally during Ownership Manager Calculations

METHOD_SYS

METHOD_SYS is populated by the system to store the generated proposal for the consolidation method

PCON_SYS

PCON_SYS is populated by the system to store the generated proposal for the consolidation rate

POWN_SYS

POWN_SYS is populated by the system to store the generated proposal for the financial interest rate, or percent shares owned. Shares Owned contains the number of shares and voting shares that an entity owns of another entity.

PCTRL_SYS

PCTRL_SYS is populated by the system to store the generated proposal for the percent of subsidiary control

Required O_ACCOUNT Dimension Properties

The IS_INPUT property for the O_ACCOUNT dimension identifies the dimension members which can be displayed in the Ownership Manager hierarchy display.

2. A Defined Method Business Rule:

The Method Business Rule table supports the Ownership Manager calculations by identifying a relationship between a range of shares allowing a system derived consolidation method (METHOD) as well as a proposed Financial Interest Rate (PCON):

indentifying source accounts
Indentifying target accounts
Indentifying source flows
Indentifying target flows

Note the unique syntax for identifying the share ranges.

0.20<,<=0.49 is interpreted by the system as a range definition: “any value greater than 20%, through any value less than or equal to 49%”.

0.50<,<=1.00 is interpreted by the system as a range definition: “any value greater than 50%, through any value less than or equal to 100%”.

3. Implementing a Cross Ownership Matrix

Automatic ownership calculations require additional information to allow the ultimate ownership share ownership values. An input schedule providing details on Percent Control and Percent Ownership data must be provided by entity and intercompany partner. Data is saved to the OWNERSHIP model.

In the following example:

- Germany controls 80% of a UK subsidiary, and the UK subsidiary controls 60% of a French subsidiary

- Germany owns a 70% share of a UK subsidiary, and the UK subsidiary owns a 50% share of a French subsidiary

Note: Share and control data must be entered without any scope (group) specification (i.e.: S_NONE).

Once all prerequisites have been completed, the user can request system generated ownership manager calculation through the CALCULATE option available in the edit mode of the ownership definition:

User can select the accounting principle for calculating the ultimate ownership and control (Direct or Group share methods).

Checkbox options allow the user to update the “Current” sub column values for Method, Percent Consolidation, and calculated ownership percentages.

A graphical illustration of the data flow between the three prerequisites:

Installation Options for SAP Business Objects Planning and Consolidation, version for Netweaver

Sheldon Edelstein — Mon, 15 Nov 2010 06:50:19 GMT

Updated October 10, 2011

There are two main installation paths that can be chosen for your SAP Business Objects Planning and Consolidation, version for Netweaver (BPC) implementation: you can install BPC within your current BW system (the "Add On" method) or you can install BPC on a separate BW system (the "Stand Alone" method). Each installation method has pros/cons that should be considered prior to deciding upon an implementation path.

Prerequisites:

SAP Business Objects Planning and Consolidation 7.5, version for Netweaver (BPC75NW), must be installed on a suitable Netweaver platform. For BPC75NW Support Pack 08 or earlier, the Netweaver platform version must be Netweaver 7.01. Based upon customer demand (SAP listens, and SAP delivers), BPC75NW will be supported by Netweaver 7.3 in the near future. The current anticipated timing for NW 7.3 support is SP09 (end October 2011), please review SAP Note 1639341: "BW 7.3 support on BPC 7.5".

Which is better: Add-On vs. Stand Alone? The answer varies for each customer since it depends on a number of factors that need to be evaluated for each implementation:

Hardware Investment
Performance
Administration Costs
Integration Development and Maintenance
Upgrade Considerations
Support Pack Considerations
BWA Investment
Data Movements

Hardware Investment

There is typically some increase in hardware investment when implementing any BPC project. The Stand Alone scenario will lead to some additional cost for servers required for creating the new instance of BW that will be used to support the BPC application. However following an "Add-On" implementation scenario will also require additional application servers to accommodate the anticipated incremental BPC user load on the existing BW system. The net hardware cost differences between "Stand Alone" and "Add-On" may be negligible.

Performance

On initial reflection, the Stand Alone scenario may be presumed to inherently have superior performance; however this is not necessarily to case if the Add-On scenario is properly sized. In addition, load balancing is also an important consideration to ensure overall system responsiveness (for example, BPC processes can be isolated from the BW processes, from a load balancing perspective, if desired).

Administration Costs

System Admin costs may be greater with a Stand Alone implementation since an additional host system will need to be maintained, tuned, monitored, etc.

However if the administration support is provided using in-house resources, the additional cost may be negligible by being easily absorbed into the existing in-house administration support model.

If system administration is out-sourced, then additional cost should be anticipated (as most external support contracts charge by system instance).

Additional consideration should be anticipated to keep your out-sourced system up to date with current support packs and corrections. Failure to maintain the system can result in increased administration and operational costs through the need to research and implement corrections that have already been developed for more current support pack levels.

Integration Development and Maintenance

There are a variety of ETL tools to pull master and transactional data into BPC, but these tools typically point to BW objects (info cubes and info objects) within the hosting BW for source input. Following a Stand Alone implementation path, additional processes will need to be developed to load the source data into the BPC supporting NW environment. This additional configuration will need to developed and, of course, maintained.

Upgrade Considerations

New BW functionality is continuously being deployed and customers struggle to determine the best timing to upgrade their systems. In a Stand Alone deployment, it may be easier to upgrade your main BW system while leaving your BPC hosting BW system at an earlier revision. One of the main benefits to the stand alone path is the elimination of many of the dependencies between BW upgrades and BPC upgrades. This decoupling may be a significant consideration since currently BPC 75 NW is currently only supported by Netweaver 7.01. If you have a BPC75NW system today deployed using the Add On approach, you cannot upgrade your NW environment to NW 7.3 since BPC is not supported by this version of NW.

Recognize that system upgrades depend on a number of business environmental factors: corporate politics, the needs and schedule of the end user work streams (i.e.: Finance, Sales, Marketing, HR, etc.), and the technical capacities to perform the work. Some companies elect to upgrade their systems on regular basis, others are more conservative and will upgrade infrequently if the system performance is acceptable.

Support Pack Considerations

Certain highly regulated industries require extensive regression testing of all BW processes after implementation of any support packs. Even companies that are not specifically bound by industry regulations should always consider some planned regression testing after any system change is introduced (i.e.: support pack, individual correction, SAP kernel updates, OS kernel updates, DB patch updates, etc...). Similar to the discussion surrounding "Upgrade Considerations", a decoupled system approach allows timely support pack implementation for BPC without the need for extensive regression testing of the main BW system.

BWA Investment

Many SAP BW customers are currently enjoying the benefits of speed enhanced processing provided by BW Accelerator (BWA). In an Add On approach, the BWA can very easily be extended provide performance enhancement for BPC. The Stand Alone approach requires additional BWA hardware to be installed since BWAs are typically setup to service a specific BW instance using a 1:1 connection between the BW instance and the BW Accelerator (reference slides 24-25 of http://www.sdn.sap.com/irj/sdn/bwa?rid=/library/uuid/3604c604-0901-0010-f0aa-b37378495537 ).

Data Movements

As discussed in the "Integration Development and Maintenance" section a Stand Alone approach will require additional data extraction configuration and maintenance. Most customers will load master/transactional data into their main BW systems for general reporting purposes. Following a Stand Alone process will require at least two sets of data to be generated (one for the main BW system, and a second set for the host BW system). When data is pulled into the BPC application a third set of data can be generated as well. All this data requires storage and has to be periodically backed up. In a high availability system, additional copies may also be generated by the mirrored backup systems.

Other Considerations

Creating and installing a "system copy" (i.e.: to establish a quality test environment) will be incrementally easier with Stand Alone approach. The copied system will be much smaller due to its segregation from the main BW processes.
Data Manger is by design constrained to one BW instance (the instance upon which BPC is installed) , so the ability to create a master process chain to update all data will require addition separate steps for a stand-alone system. Additional BW configuration, such as additional staging cubes and the potential for redundant data with both BW systems should be anticipated.

This blog does not provide a final recommendation for one implementation path over the other. There is no best implementation path that can be generalized for all customers. The choice for Add On or Stand Alone should be thoroughly evaluated by your system architecture group along with project goals and appropriate cost considerations.

Discovering the hidden tables of SAP Business Objects Planning and Consolidation, version for Netweaver

Sheldon Edelstein — Mon, 20 Sep 2010 11:36:59 GMT

Within SAP Business Objects Planning and Consolidation, version for Netweaver (from herein called "BPC") there are several control tables that are automatically generated when certain options are activated. For example, activating BPC comment capability requires the BPC system to generate tables to store the user submitted comments. These "hidden" tables are located in the 1CPMB name space. The technical id of these tables always incorporate the two character application set designation.

A delivered program, UJ0_GET_GEN_TABNAME , allows you to locate the technical names of all generated tables. To access this program, log into the BW Netweaver enviroment supporting the BPC application and execute t-code SE38 "ABAP Editor". Enter the program name "UJ0_GET_GEN_TABNAME":

The program will request a selection for the Appset and Application:

After entering the Appset and application names, the system response is to provide a report with all generated tables associated with the specific selection:

You may copy the table technical names and access the table contents via t-code SE16:

Execute the transaction and a selection screen is presented to allow you to limit the selection to specific records:

Executing from the selection screen provides a listing of all relevant table entries:

You can drill into each table entry (by double clicking the desired row) to view the specific record (this includes information on the user who entered the comment, the date the comment was created, the dimension context associated with the comment, and the comment text itself.

Note that for comments, the actual comment text is stored in the "SCOMMENT" field.

BW Objects technical name changeability for SAP BO Planning & Consolidation, version for Netweaver

Sheldon Edelstein — Tue, 30 Mar 2010 05:34:31 GMT

SAP Business Objects Planning & Consolidation, version for Netweaver (BPC_NW), transports operate by transferring metadata about the existing source BPC_NW configuration to a target system. Upon successful import into the target system, the metadata describing the new (or changed) BPC_NW objects described in the transport is used to recreate/change BPC_NW objects in the target system.

BW users that wish to connect to these objects should be aware of which objects will retain their original (source system) technical name, and which should not be counted upon to remain static.

DIEMENSIONS

SAP Note 1445688 advises that “… (the) BPC_NW transport is designed to guarantee the info object technames of BPC dimensions (will remain) identical between source and target system”. In other words , BPC_NW transports will not change the technical name of the BW info object associated with a particular BPC Dimension.

PROPERTIES

Likewise I have confirmed that the BW attribute technical names associated with BPC_NW Dimension Properties also will not be changed by transport. As with the BPC_NW Dimensions, the technical name retention is by design of the transport.

APPLICATION

The BW technical name of the BPC_NW Application is a different situation. These applications can be “fully optimized” and as a result the data model of an optimized application may change. The current solution is for the underlying BW info provider to be deleted then recreated with the new data model. BPC_NW transports of application configuration act in a similar manner. The technical name of the BPC_NW application in the target system is not guaranteed to match the technical name of the same application in the transport source system.

MULTIPROVIDER

The generated BPC_NW multi info provider does retain its technical name after transport. This is also by design of the transport process.

LIMITATIONS

If configuration is allowed to be performed directly in the target system the technical id coding the application (a two character designation that is unique for every application…”QW” in the examples below) may be used. As a result, this two character combination will not be available for generating the same technical id as in the source system upon importing the transport.

In summary, the following BW object’s technical name retention after transport (assuming the above limitations do not apply) is as follows:

Info Objects (Dimensions) - technical name is retained

Attributes (Properties) - technical name is retained

Info Provider (Applications) - technical name can be changed!

Multi Info Providers- technical name retained

SAP BusinessObjects Planning and Consolidation version for Netweaver - Deleting an Appset

Sheldon Edelstein — Wed, 20 Jan 2010 05:51:19 GMT

The application SAP BusinessObjects Planning and Consolidation version for Netweaver (SBOP PC NW) contains delivered functionality to transport configuration. In some cases transports may fail and the only way to correct the issue is to delete an appset and re-import it. This blog lists the steps that I found successful in performing this activity.

Note: this process will effectively delete the appset in the target system; all master and transactional data will be destroyed! Be sure to make backups of master and transactional data to so that they may be entered back into the restored appset!

The situation:

Configuration for an appset had been transported into the target system. At some point in the project the source system was refreshed leaving the old configuration in the target system. Attempts at reproducing the old configuration in the source system, then transporting the deletion, provided ineffective – primarily due to the difficulty of controlling the generated technical names of newly created BPC objects. The decision was made to delete the entire appset in the target system and re-transport the new configuration.

The steps that needed to be taken to delete the appset are listed below. All steps that were needed are listed below. If you attempt this, please follow all steps in sequence. Pay close attention as to which steps are executed in which systems!

1. Target System only: Delete the appset in the target system by using the delivered program (SE38): UJS_ACTVIATE_CONTENT. Enter the AppSet Id and uncheck all options except for “Delete the Appset”:

2. Source and Target systems: Delete all entries (source and target systems) in the tables ujt_trans_hdr and ujt_trans_obj by running the program (SE38) : UJT_DLT_TRANS_DATA. Select the appset to be deleted (you may need to type the Appset name directly into the prompt box):

3) Target and Source Systems: Verify that all table entries for the appset have been deleted. Run program (SE38) : UJT_CHECK_TABLE_ENTRIES. Enter the Appset name and UJ* to capture all BPC tables.

Target System: all UJ tables should indicate 0 rows

Source System: Check that tables ujt_trans_hdr and ujt_trans_obj indicate 0 rows:

4. Only after steps 1-3 are completed: create a new transport (t-code: UJBPCTR) in the source system for the appset and release to the target system.

Using JavaScript in SAP BusinessObjects Planning and Consolidation, version for Netweaver

Sheldon Edelstein — Mon, 07 Dec 2009 12:04:08 GMT

General Syntax:
The data to be converted is initially identified in the system variable %external%. JavaScript functionality can be accessed by preceding any command with the key "js:" to indicate a JavaScript command will follow next.

To use JavaScript, ensure that the CONVERT_INTERNAL parameter is set in the calling Transformation file as follows:
If the transformation option Convert_Internal = NO, JavaScript can be used in the Internal column.
Example: the function js:parseInt(%internal%) placed in the internal column of the conversion sheet will return the Integer portion of the converted data.

If the transformation option Convert_Internal = YES, JavaScript can be used in the External column.
Example: the function js:parseInt(%external%) placed in the external column of the conversion sheet will return the Integer portion of the incoming data.

JavaScript can also be used in the FORMULA column of the Conversion sheet. The system variable "Value" is defined as the initial value of the string before processing:
js:Math.round(Value) and Value =0.60 returns the quantity "1"
js:Math.round(Value) and Value =0.50 returns the quantity "1"
js:Math.round(Value) and Value =0.49 returns the quantity "0"
js:Math.round(Value) and Value =-4.43 returns the quantity "-4"

Example:

Wildcards
You can use the asterisk (*) and question mark (?) wildcards in the External or Internal columns.
An asterisk (*) stands for any character, while a question mark (?) stands for any single
character. For example, if you want to reference all members, use the asterisk (*). Example:

JavaScript function: parseInt()
This function parses an input string and returns an integer value. For example:
js:parseInt(%external%)and %external%="10 " returns the value 10
js:parseInt(%external%)and %external%="10.00" returns the value 10
js:parseInt(%external%)and %external%="50.33" returns the value 50
js:parseInt(%external%)and %external%="000000010" returns the value 10
js:parseInt(%external%)and %external%="C000010" returns the value "NaN"

Tips and Notes:
1. Only the first number in the string is returned!
2. Leading and trailing spaces are allowed.
3. If the first character cannot be converted to a number, parseInt() returns NaN ("Not a Number").

Example:

JavaScript function: if...then...else
Use the if/then/else statement to execute some code if the condition is true and another code if the condition is false.
See example above: js: if(isNaN(%external%)) then %external%.substring(0,6);else parseInt(%external%) .

JavaScript function: toUpperCase()
This function coverts all characters in the input string into upper case characters.
Example:

JavaScript function: replace
This function finds a match between a substring (or regular expression) and a string, and replaces the matched substring with a new substring.
Example:

JavaScript function: split & join
This function finds a match between a substring (or regular expression) and a string, and replaces the matched substring with a new substring.

Example:

SAP BusinessObjects Planning and Consolidations, version for Netweaver: DESTINATION_APP "How To" Guide

Sheldon Edelstein — Wed, 09 Sep 2009 13:09:36 GMT

The script logic functionality for the application SAP BusinessObjects Planning and Consolidation 7.0 version for Netweaver (SAP BPC NW) does not currently have the inherent functionality to move transaction records from one application to another. This functionality exists in previous Microsoft based versions of BPC (keyword: *DESTINATION_APP), and is desirable from a data modeling design perspective.

New coding is now available to replicate the DESTINATION_APP functionality in SAP BPC NW. The new functionality allows the transfer of records from one application to another within the same AppSet. The dimension values contained in the source and the target application can be mapped, renamed, and/or added through a series of parameters available in the new functionality.

The SAP Businessobjects EPM Region Implementation Group has recently posted onto SDN a How To Guide that provides this functionality back into SAP BPC NW through the use of a Business Add-In (BADI). You can view the How To guide and an associated file that provides the ABAP code to implement it (contained in an easy to use self contained transport) at the following links:

How To... Custom BADI for replicating "Destination_App" script logic functionality in SAP BUSINESSOBJECTS PLANNING AND CONSOLIDATION, version of SAP NetWeaver:

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/e04b5d24-085f-2c10-d5a2-c1153a9f9346&overridelayout=true

Transport File for Destination App :

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/1048e171-0a5f-2c10-42a6-852f6caa0c05&overridelayout=true

SAP BusinessObjects Planning and Consolidations (SAP BPC NW) Tips -6 [Allocations]

Sheldon Edelstein — Fri, 21 Aug 2009 05:52:17 GMT

The application SAP BusinessObjects Planning and Consolidation version for Netweaver (SAP BPC NW) contains delivered functionality to execute allocations. This functionality differs from the Microsoft based versions of BPC, mainly by the lack of some capability. Nevertheless, the functionality that is provided allows quick and easy configuration of a variety of allocation scenarios. This blog identifies the current capabilities of the allocation function for BPC version up to an including BPC 70 NW SP04.

The general format for defining an allocation is:

*RUNALLOCATION

*FACTOR={expression}

*DIM {dim name} WHAT={set}; WHERE={set};[USING ={set};] [TOTAL={set}]

*DIM ...

*ENDALLOCATION

*FACTOR

This instruction can be used to define any arithmetic expression ( written in the {expression} parameter) and may contain operands, parentheses, constants and one or both of the keywords USING and TOTAL, representing respectively the amount coming from the "USING" region (i.e. the amount of the driver) and the amount coming from the "TOTAL" region (i.e. the sum of the drivers). Another keyword supported by this parameter is COUNT, which represents the number of members into which one amount must be allocated. For example when allocating evenly a yearly value into all months of a year, the administrator may just use the COUNT keyword (defining the factor expression as 1/COUNT). In this case COUNT will automatically contain the value 12 (This keyword will obviously be more helpful in cases where the number of members is not predictable.

If omitted, the factor will always default to 1

There are two basic syntax definitions that can be used when defining the allocation function:

Format 1: this format is commonly used when an allocation is to be performed once in the script logic file.

*RUNALLOCATION

*FACTOR=USING/TOTAL

*DIM P_ACCT WHAT=CE0004010; WHERE=CE0004020; USING=CE0004030; TOTAL=<<<

*DIM ENTITY WHAT=A1000; WHERE=<<<; USING=<<<; TOTAL=<<<

*DIM TIME WHAT=2009.JAN; WHERE=>>>; USING=BAS(2009.TOTAL); TOTAL=<<<

*ENDALLOCATION

Format 2: this format is used when it is desirable to call an allocation multiple times within a script logic file.

*ALLOCATION ALLOCATE

*FACTOR=USING/TOTAL

*DIM P_ACCT WHAT=CE0004010; WHERE=CE0004020; USING=CE0004030; TOTAL=<<<

*DIM ENTITY WHAT=A1000; WHERE=<<<; USING=<<<; TOTAL=<<<

*DIM TIME WHAT=2009.JAN; WHERE=>>>; USING=BAS(2009.TOTAL); TOTAL=<<<

*ENDALLOCATION

...

*RUN_ALLOCATION ALLOCATE

...

***************************************************************

Delta functionality from the Microsoft version of BPC:

*APP

The *APP key word is not available in BPC 70 NW. This of course implies that the ability to source and/or post data into applications outside of the calling application is not currently supported by BPC 70 NW. Unfortunately, the *APP keyword is accepted by BPC70NW during script logic file validation; no error message is displayed when using *APP. Execution of an allocation involving external application references in the *APP command line will still run, however the system will only source and/or post data from/to the home application.

In the following example:

*RUNALLOCATION

*FACTOR=USING/TOTAL

*APP WHAT=PLANNING; WHERE=NEWAPP; USING<<<; TOTAL<<<

*DIM P_ACCT WHAT=CE0004010; WHERE=CE0004020; USING=CE0004030; TOTAL=<<<

*DIM ENTITY WHAT=A1000; WHERE=<<<; USING=<<<; TOTAL=<<<

*DIM TIME WHAT=2009.JAN; WHERE=>>>; USING=BAS(2009.TOTAL); TOTAL=<<<

*ENDALLOCATION

The intention of the developer was to post the allocated values into the application "NEWAPP". However during execution, the allocation function will actually post into the "PLANNING" application.

*DIM AMOUNT

In previous version of BPC, there was the ability to specify a special case of a dimension name called "AMOUNT". This AMOUNT dimension was used to set a filter for the amounts to be allocated or to be used as drivers. For example, an allocation is only performed on members where the amount of sales is greater than zero. As with the *APP command, this keyword is accepted during script logic validation, however it is also ignored during actual execution.

Business Planning and Consolidations (BPC) Tips -5 [Hidden Park-n-Go Cache]

Sheldon Edelstein — Wed, 01 Jul 2009 10:19:18 GMT

The application SAP BusinessObjects Planning and Consolidation version for Netweaver (SAP BPC_NW) provides the ability to create offline copies of input schedules and reports. These schedules and reports contain "EV" functionality that is continues to work even though the document is offline. This is facilitated by the system converting "EV" functions into a special offline version, such as "EV_". For example: EVDES is converted into EV_DES in the offline Park-n-Go version. In addition, the associated dimension data is cached into hidden sheets within the offline workbook.

These hidden sheets can be accessed using the following Visual Basic code entered into the marcro of the offline file. The full procedure is as follows:

Open Offline Park-n-Go template and note number of workbook sheets visible (in the example show there is only one sheet visible):

Access Visual Basic (Alt+F11), this is located in the "Developers" tab for Excel 2007:

Select the "ThisWorkbook" object and add the following Visual Basic (VB) code to the right screen:

Sub showAllSheets()

Dim i As Integer

For i = 1 To Sheets.Count

Sheets(i).Visible = -1

Next i

End Sub

Your screen should look similar to this:

Run the script by selecting F5 or .

The VB code exposes the cache sheet generated when the Park-n-Go offline options were executed:

In this example: EVDRE, EVGET, EVCOM, and EVPROPS data were all cached to allow for the proper display of all EV function in this workbook.

Examples of the cached data:

BOBJ Planning and Consolidations, Netweaver

Sheldon Edelstein — Thu, 05 Mar 2009 09:53:35 GMT

BusinessObjects Planning and Consolidation, version of Netweaver (formally known as Business Planning and Consolidation, version for Netweaver) has the ability to incorporate Script Logic to perform calculations and customize functionality. In this blog series, I will attempt to provide guidance surrounding the use of key words for BPCNW Script Logic.

Basics:

Script logic can be called from two locations from within BPCNW:

Default Logic –

A “default logic” file is automatically defined within every BPC application. Upon the creation of new records, such through the action of a BPC input schedule, this default logic is automatically executed. The default logic is executed specifically against all of the newly created records. In other words, the “scope” of default logic initially consists of all new records created/modified by the input schedule.

In some cases, the scope of the default logic may need to include additional member values not contained in the original scope. For example, you might enter Quantity on an input schedule, but you may need to calculate TotalCost by finding UnitCost and multiplying that value by the manually input for Quantity. In this scenario, the scope initially defined by the newly created records and need to be expanded to include TotalCost and UnitCost.

Controlling scope

There are several key words included in BPC70NW Script Logic that directly control scope:

*XDIM_MEMBER

*XDIM_MEMBERSET
*XDIM_ADDMEMBERSET

*XDIM_MEMBER {Dimension name} = {Members Set}

As of BPCNW, SP01 my observations are that this command has the same functionality as *XDIM_MEMBERSET

*XDIM_MEMBERSET {Dimension name} = {Members Set}

This command allows the definition of the specific member values to be included in the execution of the subsequent script commands. There are several potential useful formats:

Example 1

*XDIM_MEMBERSET TIME = 2007.JAN

This statement restricts the execution of any subsequent logic to only the one time member, 2007.JAN.

Example 2

*XDIM_MEMBERSET TIME = 2007.JAN, 2007.FEB, 2007.MAR

This statement restricts the execution of any subsequent logic to a predefined selection of time members: 2007.JAN, 2007.FEB, and 2007.MAR

Example 3

*XDIM_MEMBERSET TIME = 2007.JAN to 2007.MAR

This statement restricts the execution of any subsequent logic to a predefined selection of time members: 2007.JAN, 2007.FEB, and 2007.MAR using the “to” qualifier.

Example 4

*XDIM_MEMBERSET TIME = BAS(2007.TOTAL)

This statement restricts the execution of any subsequent logic to the twelve base member children associated with parent node 2007.TOTAL.

Example 5

*XDIM_MEMBERSET TIME = DEP(2007.Q3)

This statement restricts the execution of any subsequent logic to the three member children associated with parent node 2007.Q3. In this case scope would be restricted to 2007.JUL, 2007.AUG, and 2007.SEP.

Example 6

*XDIM_MEMBERSET TIME = ALL(2007.TOTAL)

This statement restricts the execution of any subsequent logic to all parent as well as base member children associated with the parent node 2007.TOTAL. In this case scope would be restricted to 2007.JAN, 2007.FEB, 2007.MAR, 2007.APR, 2007.MAY, 2007.JUN, 2007.JUL, 2007.AUG, 2007.SEP, 2007.OCT, 2007.NOV, 2007.DEC, as well as including 2007.Q1, 2007.Q2, 2007.Q3, 2007.Q4, and 2007.TOTAL.

Example 7

*XDIM_MEMBERSET TIME = %VARIABLE%

This statement restricts the execution of any subsequent logic to all member included in the variable set “%VARIABLE%. This variable set can be populated using the SELECT statement, to be discussed in more detail in a future blog.

===================================================================================

*XDIM_ADDMEMBERSET {Dimension name} = {Members Set}

With the keyword XDIM_ADDMEMBERSET, the logic can merge a specific set of members withthe members passed in the region for which the logic should be executed. This instruction issimilar to the instruction *XDIM_MEMBERSET. The difference is that, while XDIM_MEMBERSETredefines the region passed by the user, XDIM_ADDMEMBERSET adds the defined set to thepassed region.

Example 8

*XDIM_MEMBERSET TIME = 2007.JAN

*XDIM_ADDMEMBERSET TIME = 2006.JAN

The first line restricts the execution to 2007.JAN. The second line adds to the defined scope, namely 2007.JAN, the additional value 2006.JAN. Scope is now defined as 2007.JAN and 2006.JAN.

Business Planning and Consolidations (BPC) Tips -4

Sheldon Edelstein — Wed, 23 Apr 2008 09:59:16 GMT

BPC Excel has a variety of delivered macros that utilized by the BPC application builder. These macros can be incorporated into any BPC design and can be executed via the instructions provided in my previous posting [Business Planning and Consolidations (BPC) Tips -1].

Please note, although this list is substantial, I do not wish to imply it is in any way 100% complete:

Send and Refresh Schedule	MNU_ESUBMIT_REFRESH
Open My Schedule	MNU_ESUBMIT_OPENMY
Save My Schedule	MNU_ESUBMIT_SAVEMY
Open Dynamic Schedule Template	MNU_ESUBMIT_SCHEDULE
Open Schedule Library	MNU_ESUBMIT_OPENSTANDARD
Validate Submission	MNU_ESUBMIT_VALIDATE
Publish to Content Library	MNU_ESUBMIT_SUBMIT
Modify Work Status	MNU_ESUBMIT_MODIFY
Spread	MNU_eSUBMIT_ESPREAD
Trend	MNU_eSUBMIT_ETREND
Weight	MNU_eSUBMIT_EWEIGHT
Manage Dynamic Hierarchies	MNU_ESUBMIT_MANAGE_DYNAMICHIERARCHIES
Using Schedule's Help	MNU_ESUBMIT_HELP

Open My Reports	MNU_EANALYZE_OPENMY
Save My Reports	MNU_EANALYZE_SAVEMY
Open Dynamic Report Template	MNU_EANALYZE_REPORTWIZARD
Open Report Library	MNU_EANALYZE_OPENSTANDARD
Audit Reports	MNU_EANALYZE_VIEWAUDIT
Using Report Help	MNU_EANALYZE_HELP

Open Web-Ready File from BPC Web	MNU_EPUBLISH_OPENPUBLICATION
Save as Web-Ready File	MNU_EPUBLISH_PUBLISHSHEET
Book Publication Wizard	MNU_EPUBLISH_PUBLISHBOOK_WIZARD
Create New Book	MNU_EPUBLISH_PUBLISHBOOK_NEW
Edit Book	MNU_EPUBLISH_PUBLISHBOOK_EDIT
Save Book	MNU_EPUBLISH_PUBLISHBOOK_SAVE
Save As Book	MNU_EPUBLISH_PUBLISHBOOK_SAVEAS
Validate Book	MNU_EPUBLISH_PUBLISHBOOK_VALIDATE
Offline Distribution Wizard	MNU_EPUBLISH_OFFLINE_WIZARD
Manage Distribution List - New	MNU_EPUBLISH_OFFLINE_NEW
Manage Distribution List - Edit	MNU_EPUBLISH_OFFLINE_EDIT
Manage Distribution List - Save	MNU_EPUBLISH_OFFLINE_SAVE
Manage Distribution List - Save As	MNU_EPUBLISH_OFFLINE_SAVEAS
Manage Distribution List - Validate	MNU_EPUBLISH_OFFLINE_VALIDATE
View BPC Web	MNU_EPUBLISH_VIEWeDASH
Publishing Help	MNU_EPUBLISH_HELP

Park N Go	MNU_ETOOLS_PARKNGO
Expand All	MNU_ETOOLS_EXPAND
Refresh Workbook	MNU_ETOOLS_REFRESH
Drill Down	MNU_ETOOLS_DRILLDOWN
Drilldown Back	MNU_ETOOLS_DRILLDOWN_BACK
Drill Down Forward	MNU_ETOOLS_DRILLDOWN_FORWARD
Drill Through	MNU_ETOOLS_DRILLTHROUGH
Select Member	MNU_ETOOLS_MEMBERSELECTOR(xxxxxxx)
Function Wizard	MNU_ETOOLS_FUNCTIONWIZARD
Refresh Dimension Members	MNU_ETOOLS_UPDATEAPPINFO
Data Manager	MNU_ETOOLS_DATAMANAGER
Workbook Options	MNU_ETOOLS_WBOPTION
Open Dynamic Templates	MNU_ETOOLS_OPENSTANDARD
Save Dynamic Templates	MNU_ETOOLS_SAVESTANDARD
Open Custom Menu	MNU_ETOOLS_PSMANAGER_OPEN
Test Current Worksheet	MNU_ETOOLS_PSMANAGER_COMPILE
Save Custom Menu	MNU_ETOOLS_PSMANAGER_SAVE
Change Application Set	MNU_ETOOLS_CHANGEAPP
Client Options	MNU_ETOOLS_OPTION
Journal	MNU_ETOOLS_JOURNAL
View BPC Action Pane	MNU_ETOOLS_TASKPANE
BPC for Office Help	MNU_ETOOLS_HELP
About BPC	MNU_ETOOLS_ABOUT

Run Package	MNU_eData_RUNPACKAGE
Packages Schedule Status	MNU_eData_PackageSechedules
View Status	MNU_eData_ViewStatus
Organize Package List	MNU_eData_OrganizePackage
Manage Team User Package Access	MNU_eData_ManageSitePackage
Data Preview	MNU_eData_DataPreview
Data Upload	MNU_eData_DataUPLoad
Data Download	MNU_eData_DataDownLoad
New Transformation File	MNU_eData_NewTransformation
Manage Transformation Files	MNU_eData_OpenTransformation
Validate and Process Transformation File	MNU_eData_SaveTransformation
Copy Transformation File	MNU_eData_SaveAsTransformation
New Conversion File	MNU_eData_NewConversionFile
Manage Conversion File	MNU_eData_OpenConversionFile
Validate and Process Conversion File	MNU_eData_SaveConversionFile
Copy Conversion File	MNU_eData_SaveAsConversionFile
Add Conversion Sheet	MNU_eData_NewConversionSheet
Clear Save Prompt Values	MNU_eData_ClearPromptValue
Data Manager Help	MNU_eData_Help
About Data Manager	MNU_eData_About
Show Member List	MNU_eData_Showmemberlist

Enjoy!

Business Planning and Consolidations (BPC) Tips -3

Sheldon Edelstein — Mon, 21 Apr 2008 08:02:10 GMT

The REC Statement provides a convenient method to create and manipulate the values of new records using Script Logic. The basic format of the REC statement is as follows:

...

*WHEN Dim1

*IS *

*REC(FACTOR=1, Dim2="A")

*ENDWHEN

...

NOTE: The REC statement requires the selection of an existing record to process, hence the mandatory inclusion of the WHEN / ENDWHEN statements.

The new record(s) created by the REC statement inherits the same values of all dimensions from the original record. Any dimension specified within the REC expression will override the original dimension value and will be substituted by the REC definition for that dimension. In the example above, the REC statement inherits each record's dimension values except for Dim2. Regardless of the original record's Dim2 value, the new record will have Dim2 set to the value "A".

The REC can accept multiple dimension assignments, each dimension assignment must be separated with a comma. Example:

...

*REC(FACTOR=1, Dim2="A",Dim3="C",Dim4="D")

...

The keyword FACTOR allows the definition of a numeric value that is used to determine the new record's value. Factors are multiplied against the original record's values. In the example provided, a FACTOR=1 essentially keeps the original value in the new records being created. A FACTOR=2 will double the value of the new record, and a FACTOR=0.5 will reduce the new record's value by half. Example:

*WHEN ....

*IS ....

*REC(FACTOR=.5)

*ENDWHEN

A FACTOR can be positive or negative values:

...

*REC(FACTOR=-2)

...

A FACTOR can contain simple calculated values:

...

*REC(FACTOR=3.6/4.7)

...

In this case, the factor that will be applied is 3.6 divided by 4.7 or 0.765957.

The keyword EXPRESSION may also be used to modify the REC generated record values. The EXPRESSION formula can include regular arithmetic operators, fixed values and the keyword %VALUE% (representing the original retrieved value).

Examples:

...

*REC(EXPRESSION=%VALUE%+1500)

...

In this example, the value of the new record is determined by adding the value of the original record (%VALUE%) by a fixed amount (1500).

Business Planning and Consolidations (BPC) Tips -2

Sheldon Edelstein — Fri, 18 Apr 2008 03:31:20 GMT

Relevant versions: BPC5.x+

EVLST and EVSET are two powerful BPC functions that can work together to present filtered member data selections for use in BPC Planning applications. EVLST accesses the member data file and EVSET filters the returned list according to MDX filtering specifications.

A practical example in using these functions is as follows:

Return a list of dimension members, but only those members with a specific property (i.e. attribute).

In the example provided below, the scenario is to return a list of dimension member IDs that only contain the property "Group" set to "Group A".

The EVLST function requires the following configuration:

EvLST Parameters:

AppName = the name of the BPC Application

DimensionName = the name of the dimension from which you are requesting the list of members.

SetExpression = the cell location of the EVSET function. EVSET will be applied as a filter against the entire member list associated with "DimensionName".

Target = specify the cell locations you want the filtered list to appear

PropertyName = EVLST can return any property within the specified dimension, in this example the member's ID is being requested.

ExpandDown = enter TRUE to expand members by row, FALSE to expand members by column. Value if omitted is TRUE.

RepeatDuplicates = TRUE displays duplicate properties, FALSE suppresses multiple copies of the same property. Value if omitted is TRUE.

The EvSET function is referenced by the EvLST function in order to filter the listing to specific parameters. In this example, the EvSET function is limiting the selection to a MDX filter specified in the Filter parameter:

EvSET parameters:

AppName = the name of the BPC Application

Member = enter one valid member of the dimension; this is required for the MDX statement to execute properly. The selection of which member to enter is irrelevant, since the returned list will be consist of all members matching the "Filter" criteria

Include Flag = enter "EVMEMEBRS" to specify base members

LevelDown = specific number of hierarchy levels down the filter needs to expand to

ParentBefore = if returning a hierarchy, enter TRUE for the placement of the parrnt before the child, FLASE to place parent after children.

Filter = is aan optional parameter, but is required in this example to filter on member properties. Complete example expression:

"ACCOUNT.CurrentMember.PROPERTIES('GROUP')='GROUP_A' "

This expression will select only those members in the dimension "ACCOUNT" that have the property "GROUP" set equal to "GROUP_A".

Business Planning and Consolidations (BPC) Tips -1

Sheldon Edelstein — Thu, 17 Apr 2008 08:51:04 GMT

Relevant versions: BPC5.x+

Using Excel “FORMS” to provide end user initiated function selection capability:

Steps

Form the Excel menu View>Toolbars>Forms The forms toolbar will appear.
To create a user selectable “button”, select the form that looks like a grey rectangle.
Create the button by moving your cursor to the desired location and trace out the location of the button. You can always resize and reposition the button at a latter date by access the object’s Format Control.
When you create the button, you will be asked to identify a macro for the object. The macro is the excel program that will be executed when the button is clicked.
The following macros are typically used within BPC applications:

MNU_ETOOLS_REFRESH

[Same as "REFRESH WORKBOOK"

MNU_ESUBMIT_REFRESH

[Same as "SEND AND REFRESH SCHELDULE"]

Edit the text of the new button by right clicking on the button outline and selecting “Edit Text” from the context menu:
Final product: you now have a “button” that will allow the user to initiate specific actions.

Modifying Web Interface Builder - Part 4 - Hiding Cell Contents

Sheldon Edelstein — Tue, 13 Jun 2006 03:54:00 GMT

BW-BPS Layouts presented through the Web Interface Builder may attempt to present cells that are expected to contain a zero value or are not relevant. This scrip allows you to blank-out or hide any specific cells of a layouts output. Example code: This sample script "blanks-out" or hides cells in rows 12, 13, and 14, all associated with column 2 of layout "LAYOUT_LAY002".