SAP BPC Logic

SAP BPC Logic

Types of Logic

Sheet Based Logic

􀂄 Exists in worksheet only

Dimension Logic (use sparingly!)

􀂄 Within the definition of a dimension using Microsoft MDX language

􀂄 Calculations defined for a specific member ID

􀂄 Calculations can be defined for specific intersections within one application

only (cube)

Script Logic

􀂄 A script using a proprietary language or MDX

􀂄 Calculations defined for a specific member ID

􀂄 Creates base level data in the application

􀂄 Calculations can cross applications

Rules Based (table driven) Logic

􀂄 Using a rules based engine for specific pre-defined business requirements

􀂋 Legal consolidation

􀂋 Currency

􀂋 Elimination

􀂄 Rules not specific to a single member ID

􀂄 Calculations will cross applications

Script Logic

To create files containing instructions for performing calculations with special requirements on SAP Business Planning and Consolidation data.

Two files for each piece of script logic

· An .LGF file, which is an ASCII file you create and edit when setting up logic calculations. You access the .LGF file through the Script Logic editor in the Admin Console.

· An .LGX file, which is the compiled logic file created by the system when you validate and save your logic. This is an executable version of the .LGF file that is not stored in BPC.

Default logic is a special logic statement that is executed on every write back(WB) to an application.

The system constants file is the logic file that maps your dimension names for an application to the standard BPC logic.

Advanced logic can be run from Data Manager for batch processing of formulas. Using Data Manager to execute Logic module formulas is useful for calculations that do not need to be executed immediately.

For example, you may decide to wait until all data has been entered in the local currency before generating the translated amounts in reporting currencies.

Logic library

BPC has a library of standard logic functions available for your use. The file has the extension .LGF, which can be called at validation by using the INCLUDE function in your logic file. The logic module scans the library file for the appropriate formulas to use based on the information in the .LGF file.

Examples of logic delivered with BPC:

· Allocation.lgf — Runs an allocation

· Calcaccount.lgf — Used to prepare Cash Flow; runs a Account Calculation Business Rule

· Consolidation.lgf — Used to run a Legal Consolidation Business Rule

· Copy_Opening.lgf — Used to run a Balance Carry Forward business rule

· FX_Trans.lgf — Used to run currency conversion

· ICBooking.lgf — Used for running inter-company reconciliation and difference posting

· ICData.lgf — Used to run inter-company reconciliation

· ICElim.lgf — Used for running inter-company reconciliation

· MDXlib.lgf — Library of MDX financial functions

· System_Constants.lgf — Stores constant values for use within script logic. See the section that follows for more information.

· System_Library.lgf — Includes basic examples of a set of keywords

· Validation.lgf — Used for running a Validation rule.

MDX Functions

Business Planning and Consolidation (BPC) provides several BW MDX functions you can use in your dimension rule formulas.

A majority of the MDX functions define industry-standard financial ratios.

To evaluate the performance of your business and identify potential problems. Ratios expose factors such as the earning power, solvency, efficiency, and debt load of your business.

Common MDX functions is Ancestor, FirstChild, Count, Sum, Max, Median, Min, Count,IIf…etc.

Simple Rule Formula Examples

Calculating a YTD value

Syntax: [dimension].[account1],[Measures].[YTD]

How to develop the script

Current Year Net Income: ([Account].[Net Income], [Measures].[YTD])

Custom Logic

This instruction to call any custom ABAP programming

CALL_CUSTOM_LOGIC <program_name>

where program_name is the name of the ABAP program (uppercase, limited to 50 characters)

*CALL_CUSTOM_LOGIC COMPLEX_ALLOCATION

User-Defined Functions

User-defined functions are MDX functions that users create and register with the system.

User-defined functions supplied by BPC include:

MON — month

Returns the MONTH member from the Time dimension

PRO — property value

Returns the value of a property of the current member of a dimension.

MOVEMENT — month to month movement

Returns the movement on an account from one month to the next month.

MOVEMENT2 — month to month movement on two accounts

Returns the movement on two accounts combined from one month to the next month.

FIRSTPREV — opening balance, first month

Returns the opening balance, but only in first month of year.

Advanced Rule Formula Examples

Advanced rule formulas include movement calculations and allocating expenses based on entity type.

Features

Movement calculations define the copying or movement of data from one time period to another. In the formula examples below, [AccRec] is the accounts receivable account, and [AccPay] is the accounts payable account.

Account :-Mvmt Acc Rec

Movement in Accounts Receivable

Iif([Time].CurrentMember.Children.Count=0, [Account].[AccRec] - ([Account].[AccRec], [Time].PrevMember), SUM(Descendants([Time].CurrentMember, [Time].[Month], LEAVES))).

Rules Keyword Reference

The reference contains descriptions of all the rule statements you can use, the Rules Assistant to help create rules statements.

The syntax is:

Example

*ADD %ACC%=A,B,C,D[#MYSUM] = [%ACC%]*ENDADD

This expands into:

#MYSUM = [A]+[B]+[C]+[D]

The expression to the right of the equals sign ( = ) currently does not support more complex expressions. Also, only one formula can be written inside the ADD/ENDADD structure.

Types Business Rule

There are six types of business rules available at the application level which can be enabled

• Account Transformation: Performs a straight summarization of a group of accounts. Account transformation reads and aggregates the values posted to specific combinations of accounts, flow types and data sources in order to post the aggregated amount under an alternate destination account, flow and data source combination.
• Carry – Forward: Summarizes a group of activity accounts and copies the value into an opening balance account. Facilitates populating the opening balances in the new year with the closing balances as of the last period of the prior year and transfer current period net income to the Balance Sheet in order to create a fully balanced Balance sheet for each period.
• Currency Translation: Performs standard currency translation. The currency conversion function is designed to convert local currency values into one or more reporting and/or group currencies.
• Intercompany Eliminations: Performs intercompany eliminations and allows us to perform the intercompany matching process.
• US Eliminations: Controls where in an Entity dimension member the results of eliminations are stored
• Validation: Allows user to check integrity and correctness of entered data. This facilitates validating the data at different stages of the data submission and enrichment process to help ensure the completeness and accuracy of the existing data. In other words this business rule ensures that the data is in consistent state as per the business logic.

SAP BPC Introduction

SAP Business Objects Planning and Consolidation (SAP BPC) Product support to meet organization full array of bottom-up and top-down financial and operational planning requirements, as well as complete consolidation and reporting through a single application and user interface to empower every user Business level-to easily gain understand financial flow to, interact with, and leverage financial and operational data for timely and effective decision-making. The SAP BPC Product works smoothest, most timely financial close possible - all through a single application, which provides easy of User interface showing a speed of operation and flexibility.

BPC7.5 Microsoft platform(MS Version) includes modeling and maintaining dimensions and applications, performing data imports and transformations, creating your own script logic, setting up a security model, how to set up business process flows(BPF) and work status.

SAP BPC leverages the power of Microsoft Excel on an enterprise-scale, transforming Excel from a personal desktop tool into a collaborative, enterprise-scale EPM solution that provides users with a familiar, intuitive, readily accepted environment. Microsoft Excel as the easy-to-use interface for all administrative tasks. Due that SAP BPC customer obtains full ownership of the solution. Provide Security of the high level applications.

BPC7.5 Microsoft platform (MS Version) is an existing spreadsheet-based budgeting and planning system that is out of control, or it is the need for a complete, unified CPM solution, for the mid-market customer getting the first application up and running quickly and cost effectively is vital.
BPC MS Version is a FastStart package is designed to deliver a working solution quickly, efficiently and at an affordable price, without the need for a large development team.

The SAP Business Objects Planning and Consolidation application (SAP BPC), Microsoft platform (MS Version), introduces new interfaces to the application, including Microsoft Word and Microsoft PowerPoint as well as many enhancements to the Web and Microsoft Excel interfaces. SAP BPC (MS Version) providing the best user experience, intelligent action panes now guides users through the application, accelerating the learning curve. Business process flow (BPF) guide users through standard business processes, ensuring compliance with corporate standards.

SAP BPC enhances the most popular user interface: Microsoft Excel. While maintaining support for the existing Microsoft Excel functionality, several enhancements, such as a drag-and-drop report builder, are introduced. The intelligent action pane lets users navigate with less trouble to common tasks, such as report building and schedule submission.

SAP BPC is an unified Enterprise Performance Management (EPM) application that leverages a centralized performance database, common user interface (Excel), and single sign-on across the whole of the EPM processes it supports (including statutory consolidation, budgeting, forecasting, planning, reporting, analysis, predictive analytics, score carding, and dashboards).

SAP BPC delivers robust financial and operational applications spanning the full spectrum of performance management-including planning, consolidation, reporting, analysis, and compliance. New to Outlook Soft SAP BPC is its revolutionary business process flow (BPF) technology-delivering contextual, user-driven workflow definition and process enablement. BPF represents an industry breakthrough in how users create and manage workflow and shared processes throughout the whole of the performance process.

SAP BPC enables developers build, manage, and control applications faster and in a more unified fashion than ever before-delivering solutions that leverage shared metadata, approval flows, process and workflow tracking, and application interfaces.

SAP BPC includes applications for planning, reporting and analysis, complete process management (with contextual business process flows), accelerated analysis, robust compliance capabilities, and more.

Business planning and budgeting - Streamline and automate by using a collaborative top-down and bottom-up approach

Forecasting - Produce more accurate plans and budgets, create rolling forecasts, and incorporate real-time actuals with historical data analysis

Predictive analysis - Receive automatic alerts about potentially at-risk key performance indicators and recommended actions, plus one-click access to explanations for variances and root causes

Reporting and analysis - Gain one-click access to up-to-date production and management reporting, financial, operational, and multidimensional analysis

Consolidation - Centralize all performance-related data, shave weeks off consolidation processes, and improve compliance

Streamlined Collaboration - Effective use of prepackaged business process flows and self-service flows reduces time modifying common business processes.

SAP BPC MS Comments

SAP BPC MS Comments:
Business users are comfortable with flexibility and features available with excel to work on their different business plans. BPC is a tool which provides best features of Excel in an integrated planning tool with enhanced planning features like trends, spread, drilldown, commenting etc.

Business users in general add comments for various needs like, to share their view points on the plan data, to specify any reasons for data fluctuations etc.

The comment functions
Comment Manager Interface makes it easy to search and report on comments Create ad-hoc reports and extract to Excel through Reporting Services.
Can be assigned priority and keywords for setting search criteria Flexible for either partial or fully – qualified metadata Reports contain full history of comment Secured by member access
Define cell-based formulas in Excel to retrieve and send (EvCOM)

Posting any Comment in BPC is possible via three interfaces,
1. Via Action Pane
2. Cells of Excel – EVCOM
3. Excel Pop Up – EVCOM

The procedure for adding comments from Action Pane and via cells of excel sheet using EVCOM function

Steps to add a comment:

1. Within BPC for Excel, open the report or input schedule to which a comment needs to be added.

2. After the report or input schedule has opened, click on the “Add new comment” task from the Report State Action Pane.

3. This will bring the user to the “Enter a New Comment” Action Pane, where the user is required to select the context of the comment from a choice of the three following options:
a. The active report or schedule – This option is only available when a live report or input schedule is open. The comment will get applied to the data region associated cell.
b. The active current view – This option applies the comment to the current view shown in the action pane.
c. Custom current view – Option allows the user to specify specific current view associated with the comment. If this option is selected the “Comment for these CV Values:” area of the action pane becomes available for use. The user selects the Application, as well as each dimension and member id for each dimension selected, to which they need the comment applied to. If a dimension is left unselected, the member ids for that dimension will be ignored for this comment.

4. Click the button which takes the user to Step 2 of “Enter a New Comment”.

5. Select the priority of the comment from the drop down menu, in the “What is the
priority of the comment?” section of the action pane. The priority choices are:
Critical, High, Medium, Low, and No Priority. Note: When reporting on comments, they are shown in priority level order.

6. The next section is “What keyword do you want to associate with this comment?”
This should be a meaningful word (up to 30 characters) that helps the user organize and search the comments as the database grows.

7. The final step is to add the comment’s content which can be up to 256 characters long.

8. Click the button, a dialog box will appear stating the comment has been stored in the database.




How to configure comment by Excel Pop Up Follow the Steps,
1. Create any Input Schedule or Report in BPC Excel Client.

2. Set the value for Workbook Options a Read Option for Comment to “As Excel Popup”.

3. Set EVCOM function for various fields in the report and refresh the sheet.
a. Select the cell where the function is to be written.
b. Fill it with “=evcom()”
c. Select insert function and define the cell value for different parameters of the function.
4. Select the cell for which the comment needs to be added then go to Review Tab and select New comment,

5. Enter the required comment for the cell selected in the pop up screen and move back to Add Ins Tab,

6. Click on the button to save comment to the system,

7. The comment is saved successfully to the system and you have the comment in the Popup Screen as and when you move the mouse over the cell.


Advantages of having comment as Excel Popup are,
• Business have the feel of using the same Excel functionality for adding comments with data saved in system
• Can be used to comment any data cell in a Report or Input Sheet having dynamic expansion.
• No need for allocating any area for commenting in the IS/Reports.
• User Interface looks good having only the required set of data without explicitly displaying the comment entered for it.
Note: The Comments included via Excel Pop Up can also be viewed using EVCOM function in cells of different BPC reports or Input Sheet and can also be reported via standard system reports for Comment.

The Table below clearly states what type of comment can be used in different business scenarios,

Business Need

CASE1:
1.Report or Input sheet dynamic
2.Huge volume of data and commenting across any individual cell of the data.

Best Option : Excel Pop Up

CASE2:

1.Report or Input sheet is static
2.All Relevant comments to be displayed simultaneously.
3.Sheet where comments are entered without any data

Best Option : Within Cell


Business Need

case3:
1.For any business user who maintains only comments and doesn’t need any access to see any plan or consolidation data.


Best Option :

Action Pane(Provide Access only to Commenting in Task Profile of that business user).


Thus comments can be added using Excel Popup function and the business users can be provided with the feel of their excel functionality for indenting Business.

The Comments for the Selected Data Region table has 5 different options in its table Header. They are:


Option Description

1.Comment Displays the entire comment. If you are the originator of
the comment, or have administrator rights, you have ability
to modify the comment directly in the cell. Note: Be sure to
select “Update Comments” in the action pane when completed.

2.Priority Displays priority of the comment. If you are the originator of
the comment, or have administrator rights, you have the
ability to change priority from the drop down list. Note: Be
sure to select “Update Comments” in the action pane when completed.

3.Keyword Displays keyword associated with the comment. If you are
the originator of the comment, or have administrator rights,
you can modify the key word by typing directly into this cell.
Note: Be sure to select “Update comments” in the action pane when
completed.

4.Originator Displays the comments originator.

5.Date Displays the date the comment was saved to the database.
If comment has been updated, then updated date appears.

Other Options on the Comments Summary Page
• View Comment Options action pane
• Add a new comment
• Report on comments
• Delete selected comments
• Update comments
• Changing the Display criteria(Severity , Priority )

/Development /Production Support Issues SAP BPC

SAP BPC Production Support issues:
1. How to transfer the data SAP BPC 5.1 TO SAP BPC 7.5 ?
Ans) Use Data manager package to expoert data in ASCII format (flatfiles) and then apply conversion/transformation then import flatfile into bpc 7.5 using data manager package to using flat file.


Data Manager Package
SAP BPC provides standard out of the box functionality to execute common tasks
Required for planning and consolidation such as currency translation, intercompany
Elimination and so on. These tasks are executed via a data manager package.

Transformation File Definition
• HEADER: Indicates if the data file has a header (Required)
• AMOUNTDECIMALPOINT: Allows to enter non-period decimal point
• SKIP: No. of lines to skip at the top (Required)
• SKIPIF: Skips a line in the data file if it begins with a given text string required)
• VALIDATERECORDS: Validates mapping and maps data (Required)
• CREDITPOSITIVE: Reverses sign in case of LEQ and INC accounts (Required)
• MAXREJECTCOUNT: If validating records, the no. of rejected records at which
Stop processing (empty string means 500, some postive number, 0r -1) (Required)
• ROUNDAMOUNT: Indicates amount of decimal values to round


2) When I use BPF , I want insert a PACKAGE LINK using a Task but when I select it I can't modify APPLICATION anc CURRENT VIEW and I can't choose a specific PACKAGE LINK.?
Ans) When you add a step for executing a package link, this package link will be executed for the controlling application. So, you are not able to change the application. Moreover, a package link doesnt work for a particular Current View. It works based on the members prompted during the execution. So, you are not able to change the Current View.

How I can choose a specific PACKAGE LINK like a PACKAGE?
Ans) When assign the action of run a package link, running this step will open the window with the list of all the package links. In the BPF, you cannot specify the name of the link.

The system offers to save the file to:
HTTP://////App/DataManager/DATAFILES


3) BPC for NW 7.5 SP4 system. When trying to import data from .txt files via the data manager process Steps.
Ans) 1) Login to BPC for Netweaver website
2) Go into the excel portion
3) log into the applicationset
4) click on edata at the top of the screen
5) select upload data file
6) enter source and target locations. the file is a .txt file (40KB in
size)
7) click OK to start the upload



4) Account dimension, which member list contains just "Price”. When I built an input schedule, enter prices in some currency, the total year value list the sum of the values of months. But I need an average value. How can I organise it?

SAP BPC Product does have different types of application in reporting by mean is it reporting application built or non reporting type.
Next, think is it to build planning wise or consolidation (Legal/Consolidation).

To store the supporting data required for planning and the rate type application. “Write dimension formula in the time dimension for the account price."
Syntax of formula.
IIF (ACCOUNT.Currentmember = "PRICE", ((2010.JAN + 2010.FEB + 2010.MAR) / 3), (2010.JAN + 2010.FEB + 2010.MAR))
The above is for 2010.Q1. Similarly, to maintain formulas for the other parent members also.

After that need displaying average at parent members, either you can write dimension formula in the time dimension for the account price. Otherwise, you can create a custom price.


5) SAP BPC 7.5. MS cannot open databse "apShell"(" OLE DB error: OLE DB or ODBC error)
ANS)
• Open up SQL Management Studio
• Go to Security > Logins > NT AUTHORITY\LOCAL SERVICE
• Right click > Properties
• In the User Mapping section tick ApShell
• In the bottom section of the screen, tick db_datareader.

The difference between Dummy Profit Center and Default Profit Center?

They basically do the same thing, which is automatically assigning a profit center to an item when none currently exists. However, the dummy profit center is populated automatically by the system, while the default profit center is manually assigned to a configuration table for the relevant GL accounts.
Note that the dummy profit center no longer needed for the new GL, while the default profit center is still used, and can be assigned using transaction FAGL3KEH.
Upload mass journal entries,
According my knowledge I know that there are two options:
1. Third-party solutions which cost extra
2. Custom programs which could have bugs.
Is there any other option/s you are aware of to meet this requirement?
Two standard tools that you can use are as follows:
(1) LSMW
(2) CATT
Both involve setting up the fields that you want to upload and entering them into a predefined template. Personally, I prefer using LSMW because I find it more user-friendly and flexible. CATTs are also good, but I believe they are no longer accessible with the newer versions of SAP (from ECC 6.0). Instead, you need to use eCATT.

Can you share any data preparation and/or management techniques to ensure a smooth conversion to the material ledger?
To consider upon conversion to material ledger are
- Ensure that all plants in the company code are activated for material ledger;
- Activate material ledger at the start of the period (to ensure that all inventory movements for the period are captured);
- Ensure that no inventory transactions are made during production startup
- All FI currencies should be enabled in material ledger.