BPC311_EN_Col91_FV_Part_A4 (第2页)

第 62 页 / 共 237 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Ratiosexposefactorssuchastheearningpower, solvency, efficiency, anddebt

load of your business.

See the Mdxlib.LGL file from ApShell for more information on supported

functions.

Figure 67: Benefits of Dimension Member Formulas

Benefitsrelatedtodimensionmemberformulasinclude:

• Speed - just like Excel formulas in worksheets, formulas defined in a

dimension are usually calculated fast.

• Consistency-thesamelogicisappliedtoagivenmemberinanyapplication

which uses the dimension.

Considerations:

• Formulasinadimensionarenotflexiblebecausetheyapplytolevelsinthe

dimensionandinallapplicationsusingthedimension.

• Ifyouwanttoapplyaformulaonlytobaselevelmembersorwantaformula

tobeusedinonlyoneapplicationyoushoulduseScriptLogic.

• Considerationmust begiven to the impact of any dimension formulasto

allapplicationssharingthisdimensionorallmembercombinationswithin

an application.

• Theseformulascanaffectretrievalperformanceifnotusedproperly.

第 63 页 / 共 237 页

BPC310 Lesson: Introduction to Logic

Figure 68: Overview of Script Logic

Base-levelcellsarecellswhereallmembershavenochildreninanydimension.

UsingDataManagertoexecuteScriptLogicformulasisusefulforcalculations

that do not need to be executed immediately.

For example, as an administrator you can decide to wait until all the data has

been entered in local currency before generating the translated amounts in the

reporting currencies.

Thesepackagescanberuninrealtimedialogmodeorscheduled.

Youwillhavetwofilesfor eachScript logicyoucreate.

• .LGF-anASCIIfilethatyouusetocreate/edityourlogiccalculationsandis

accessedthroughtheScriptLogiceditorintheAdminConsole.

• .LGX-thecompiledlogicfile. Createdbythesystemwhenyouvalidateand

saveyourlogic. Thisisanexecutableversionofthe.LGFfile. Thisisnever

storedinBPCandisgeneratedontheflywhenyouexecutea.LGFfile.

Thisexecutable.LGXversionissavedtothefilesystem.

Uponpostingajournal,thesystemrunslogicthatisspecifictojournalprocesses.

To overwrite the default journal logic, you can create a new script logic file

called Journal.lgf.

第 64 页 / 共 237 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Figure69: MaintainingScriptLogic-ExecutionbySendData

Shownonthisslideisthescriptlogiceditorwhereformulasareentered.

Afterenteringaformula, clickonthe“ValidateandSave”optionfromthetask

pane. ThiscompilestheexecutablecodeintotheLGXfile.

Andvalidatesdimensions,members,etcreferencedinthecode.

The // are comment lines.

ROLLTOBS – Role to balance sheet

CYNI – Currency year net income

CALCNI – Calculate Net Income

FINSTMT – Financial Statements

PL – Profit & Loss Statement

FXTrans – Currency Translation

第 65 页 / 共 237 页

BPC310 Lesson: Introduction to Logic

Figure 70: Structure of Script Logic Programs

Here is how modeling logic works:

BPCloadsinmemoryaspecificselectionofdata(knownasScoping),appliestoit

asetofuser-definedformulasandderivesanappropriatesetofcalculatedamounts

(Body/Code). Theseamountsarethenwrittenbackintothefacttableasifthey

were additional input data (Commit).

The IIF command is used in cases when there could be a positive or negative

outcome. (thatswhytherearetwoIs). IIFreturnsoneoftwovaluesdetermined

by a logical test.

Figure 71: Script Logic - Execution via Data Manager

Iftheadministratorexecutesthescriptlogiccentrallyadatamanagerpackagehas

tobe created and added into theData Manager.

第 66 页 / 共 237 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

With7.0MicrosoftthepackagehastobeaSSISpackage.

SSISstandsforsequelserviceintegrationserviceandisusedwithSQL2005.

Theabovelogicincludesa*-1sincerevenueisstoredasanegativevalue.

Figure 72: The System Constants File

The System (dimension) Constants File: This is a logic file which maps your

dimensionnamesfortheapplicationtothestandardBPCdynamiclogic.

Thedimensionconstantsfileislocatedin: <BPC>\Webfolders\<AppSet>\Sys-

temLibrary\LogicLibrary\System_Constants.LGT

Ifyouusedifferent dimensionnamesto thoseusedin theLibraryfileyouwill

haveto update theConstants File by setting your dimensionnameon theright

hand side. Leavethe left hand sideunchanged.

Youcanalsoincludeapplicationconstantsandmemberconstantsinthedimension

constants file.

For example, you would do this if your advanced formulas reference another

applicationwherevaluestobeusedintheformulawerestored.

第 67 页 / 共 237 页

BPC310 Lesson: Introduction to Logic

Figure 73: Benefits of Script Logic

Benefits related to Script Logic include:

• Real-time - allows for real-timecalculations.

• Option to run in batch from Data Manager.

• Flexible-enablesyoutoapplydifferentformulastodifferentapplications

within an application set (dimension member formulas are applied to all

applications using the dimension).

• Powerfulandeffectivetooltoperformcalculations,deliveringthedesired

results efficiently and accurately.

• Torunissimulationmode: SettheSSISpackagetorunthatwayandnodata

base records will be written.

GotoStart>EnterpriseManager>DataTransformationServices>Locate

yourpackage>Edit>checkoffthesimulationcheckbox.

Figure 74: Overview of Business Rules

Business rules allow you to customize large data manipulation tasks, such as

bulk data imports with currency translations, as well as smaller tasks, such as

submitting input data to the database.

第 68 页 / 共 237 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

BPC'sbusinessrulessupportbothmanagementandlegalconsolidationreporting.

• BusinessRulesareparameterbasedlogicthatresultsindatastoredwithin

an application.

• Legalconsolidationsscenarioscanusethedeliveredbusinessruletablesto

do:

• Eliminations

• Validations

• end of month currency translations

• Consolidations of Investment eliminations ie Ownership of subsidiaries,

retained earnings, minority interest etc

• Balance carry forward.

Figure 75: Logic Summary

TheBPCmodelinglogicenginehasprovenovertimetobeaveryflexibleand

effectivetooltoperformallsortsofcalculationswithinourdatabase,delivering

the desired results efficiently and accurately.

第 69 页 / 共 237 页

BPC310 Lesson: Introduction to Logic

Lesson Summary

You should now be able to:

• UnderstandthevarioustypesofBPClogicandwhenyouusethem.

第 70 页 / 共 237 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Lesson: Loading of Transaction Data

Lesson Overview

Lesson Objectives

After completingthislesson, you will beable to:

• Understand your options for loading transaction data into BPC, the

transformationmethodology, anderror handling.

Business Example

Figure 76: Data Manager

DataManagerisaBusinessPlanningandConsolidation(BPC)modulethathelps

youmanagethedatamovingintoandoutofBPC,aswellascopyormovedata

withinandacrossapplications. Inadditiontoallowingyoutomoveandcopydata,

DataManagersupportsmappingandcomplextransformationsofdata.

Data Manager can handle transactional data and master data only – it cannot

handlemetadata. Forexample,thedimensionmembersaremasterdataanddata

in the cube is the transaction data.

第 71 页 / 共 237 页

BPC310 Lesson: Loading of Transaction Data

Figure 77: Uploading Files on to the Server

In order to import a file, the file must reside in the Data Manager Data Files

folder on the server.

The Upload function allows you to move files from your local machineto the

Data Files folder on the server.

TheDownloadfunctionallowsyoutomovefilesfromtheDataFilesfolderon

the server to your local hard drive.

Figure 78: Preview Data File

第 72 页 / 共 237 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Youcanpreviewyourdatafiles. Thisisusefulwhenyouwanttoseethewayyour

dataislaidout when creating atransformation file. Youcan previewdatabase

(mdb),Excel,andASCIItextdatafiles. Youcanpreviewdatafilesthatresideon

theserver inCompanysiteor otherSitedirectories.

To preview data

1. OpenDataManager,andselecteData>DataPreview.

2. Select a site on the left side.

3. Selectadirectory,andthenadatafile,andthenclickOK.

4. Dooneofthefollowingdependingonthetypeoffileyouopened: a. Fortext

files,chooseDelimitedorFixed-widthfromtheSelectadatatypelist. Ifyou

choseDelimited,selectadelimiterfromtheSelectadelimiterlist. b. ForExcel

files,selectaworksheetfromtheSelectaworksheetlist. c. Fordatabasefiles,

select a table from the Select a table list.

5. Usethescrollbarstolookatthedata. Thepreviewwindowloadsonlythefirst

200 lines of your data.

6. Ifyouwanttoopenadifferentfile,clicktheOpenbutton,selectanotherfile,

and then click OK.

7. ClicktheCancelbuttonwhenyouaredonepreviewingyourdata.

Figure 79: Create a Conversion File

Conversionfilesdefinethemappingbydimensionsfromexternalmembernames

to internal member names.

They also provide the ability to perform arithmetic and rounding on data as it

is converted.

第 73 页 / 共 237 页

BPC310 Lesson: Loading of Transaction Data

Youcreateoneconversionfileperdimensioninatransformation. Eachconversion

filecancontainoneormoresheetsfordifferenttypesofdatatransformations.

Asabestpractice,conversionfilesshouldbenamedthesameasthedimension

for which they are being used.

DataManagersupportsVBscriptinconversionfiles. WhileVBAcanbeused

successfullyinsomeinstances, wecannot guaranteeitwillworkwithBPCfor

Excel.

• Special options in conversion files

• Youcanusespecialoptionswhenyoudefineyourconversionfiles.

• Ifyouwanttoignorecertainexternaldata,youcanplacethekeyword*skip

in the Internal column. For example:

• External ACCPAY Internal *SKIP.

Figure 80: Create a Transformation File

FORMAT = DELIMITED

Thereisnodefaultsothisoptionmustbedefined. Youmustenteraformattype.

Delimitedmeansthatthereisaspecialcharacterbetweeneachcolumnofdata,

definedbytheDelimiteroption(below). Fixedmeansthatthedataisinafixed

field format.

HEADER

Default: YESIfYES,thenyourinputfilecontainsoneheaderrowthatdefines

the fields.

第 74 页 / 共 237 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

DELIMITER can be COMMA | SPACE | TAB Default: , (comma) If the

FORMAToptionissettoDELIMITED,thisoptiondefinesthesinglecharacter

thatisthedelimiterbetweencolumns. UsethekeywordsSPACEorTABifspace-

or tab-delimited.

SKIP=Default: 0(zero)Numberoflinestoskipatthetopofadatafile. Ifyour

datafilehas aheader, set this value so that those

linesareskippedduringtransformation. SKIPIF=NULLwillskiparecordisit

null.

VALIDATERECORDS=No|YesDefault: NoIfYES,validatethemappingand

thatmembersexist,andmapdataintheproperorderforBPC.

CREDITPOSITIVE = Default: YES, If Yes, all amounts referring to an

ACCOUNTtype(LEQ,INC)willhavetheirsignsreversed.

MAXREJECTCOUNT=Thenumberofbadrecordsbeforetheentireloadfails.

ROUNDAMOUNT=<integer>Default: no rounding. Specifiestheamountof

decimalplacestoroundvaluesduringthetransformation.

Figure 81: Delivered Packages in ApShell

Clearcreatesan additional recordwith areversed signandiseliminatedupon

compressionto achievetheeffect of adeletion.

ThecopypackagecopiesdatabetweendimensionswithinBPCapplication.

TheexportpackageexportsdatavaluesfromtheBPCcube(includingcalculated

values) into an ASCII file.

第 75 页 / 共 237 页

BPC310 Lesson: Loading of Transaction Data

TheimportpackageimportsoneormoreASCIIfilesintothecurrentapplication

ThesignsoftheamountsarereversedbasedontheAccounttype.

Themovepackageletsyoumoveanyselectionofdatawithinanapplication.

TheFXTranslationisusedforcurrencytranslation. Forexample, whenLCis

loadedintoFinance,USD&EURreportingcurrenciesarealsoloaded.

ICEliminationsperformsmanagerialeliminationsforAP/AR,Sales/Cogsetc.

Appendtothefacttablesaddstoexistingvalueswiththesamekey.

Defaultformulasrunthescriptlogicintheapplicationsdefaultlogicfile.

Exportfromfacttableexportsinput-leveldata(butincludestranslatedcurrency

values)fromtheapplicationfacttableintoanASCIIfile.

Import with clear clears the fact table for all Entity/Category/Time member

combinationsfoundinthedatasourcefileandimportsthedataintothedatabase.

Figure 82: Sample Packages in ApShell

Toactivateapackage: Goto→BPCExcel→eData→Organizepackagelist→

rightclick→addpackage→filedropdown→selectpackageinExamplesfolder

→open→selectuserfolderforexample→add→save

第 76 页 / 共 237 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Figure 83: Run the Import Data ManagerPackage (1of3)

YoucanpreviewboththeImportandTransformationfilesusingthemagnifying

icon on the right.

You might have to modify DM packages to work within your company's

requirements. The packages that come with BPC are designed to be dynamic

sothat youdonotneedtomodify thepackagesinorder for them to workwith

your applicationsand dimensions. But you might want to modifypackages to

behavedifferently or toperformadditionaltasks. Youcanmakemodifications

to thepackagessuppliedwithBPCor usethosepackagesastemplatestobuild

packages as per your requirements.

第 77 页 / 共 237 页

BPC310 Lesson: Loading of Transaction Data

Figure 84: Run theImport Data ManagerPackage (2of3)

Figure85: Run(Schedule)theImportDataManagerPackage(3of3)

When you run packages, you have the option of scheduling them to run at

predetermined times. The schedule status is different from the package status

because package status shows you the results of running packages, while the

schedulestatusshowsyouwhenvariouspackagesarescheduledtorun.

Youcancheckandchangepackageschedulesfor allscheduledpackagesusing

the Package Scheduler Status window.

第 78 页 / 共 237 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Figure 86: View Package Status

ThePackagelogliststhemetadataforthepackagesuchastheappandappset.

TheFormulalogdisplaysdatarecordsandpossibleerrormessagesatthebottom

of the log.

Figure 87: Error Processing

第 79 页 / 共 237 页

BPC310 Lesson: Loading of Transaction Data

Figure 88: Error Processing

Ifyoudontuseatransformationfile,thereisadefaultlimitof500recordswhich

willrejectoutuntiltheloadfails. Ifyoudouseatransformationfile,youcanset

themaxnumberofrecordsbeforetheentireloadfails.

Figure 89: Error Processing

第 80 页 / 共 237 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Figure 90: Run a report to see the data

To run theabove report: In BPCExcel → Reporting and Analysis→Build a

reportusingadynamictemplate→Selecttrendreport→OK→setthecurrent

viewasshown. Well go into moredetail later.

Figure91: MergeandReplacefortheImportDataManagerPackage

ThenextslidesshowhowMergeandReplace&Clearworkonthedatabase.

第 81 页 / 共 237 页

BPC310 Lesson: Loading of Transaction Data

Figure 92: Example of Merge/Replace/Append (1 of 4)

Pleasenotethatthemergeoptiondoesnotdeleteanythingfromtheexistingdata

setandnewrecordsaremergedintotheexistingrecords.

Thereplaceoptionhoweverdoesnotdeleterecordseither. Itactuallygenerates

offsettingrecordswiththereversesignthusprovidingthedeletioneffectwhen

the application is compressed.

Figure 93: Example of Merge/Replace/Append (2 of 4)

第 82 页 / 共 237 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Figure 94: Example of Merge/Replace/Append (3 of 4)

Figure 95: Example of Merge/Replace/Append (4 of 4)

IfyouwanttotestthesystemreactiononMerge,Replace&Clear,Appenddoa

fulloptimizationonthecubesoastoclearthewritebackandfac2tables. Then

you will have a clean slate to start with.

Thencreateyourownflatfilestoreplicatetheexamples.

Tip: Onthereplaceandclearexample,useaSetofmembersthataretotallynew

to thecubebecausein thatscenariothesystemwillbecheckingfor previously

loadedrecordswiththesamekeyanditwilllookinallthreefacttables.

第 83 页 / 共 237 页

BPC310 Lesson: Loading of Transaction Data

Lesson Summary

You should now be able to:

• Understand your options for loading transaction data into BPC, the

transformation methodology, anderror handling.

第 84 页 / 共 237 页

UnitSummary BPC310

Unit Summary

You should now be able to:

• Understandhowtocreate,modelandoptimizeApplications. Youwillalso

haveanideaabout howtosecurea dimension.

• UnderstandthevarioustypesofBPClogicandwhenyouusethem.

• Understand your options for loading transaction data into BPC, the

transformationmethodology, anderror handling.

第 85 页 / 共 237 页

BPC310 Test Your Knowledge

Test Your Knowledge

1.

2.

第 86 页 / 共 237 页

TestYourKnowledge BPC310

Answers

1.

Answer:

2.

Answer:

第 87 页 / 共 237 页

第 88 页 / 共 237 页

第 89 页 / 共 237 页

Unit 3

Working with BPC for Excel, BPC

Web, BPC for Word and BPC for

PowerPoint

Unit Overview

Unit Objectives

After completing thisunit, you will beableto:

• Understand how to work With BPC for Excel, especially working and

navigating with Delivered Templates, creating comments and using the

OfflineDistributionWizardandthePublishBookTool.

• Understand which features BPCWebdelivers.

• Understand BPCfor Word tasks, see how to insert SAP BPC data into a

MicrosoftWordfileandunderstandtheBPCforPowerPointtasksaswellas

seehowtoinsertSAPBPCdataintoaMicrosoftPowerPointfile.

Unit Contents

Lesson: BPC for Excel..........................................................78

Lesson: BPC Web..............................................................122

Lesson: BPC for Word and BPC for PowerPoint...........................134

第 90 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Lesson: BPC for Excel

Lesson Overview

Lesson Objectives

After completingthislesson, you will beable to:

• Understand how to work With BPC for Excel, especially working and

navigating with Delivered Templates, creating comments and using the

OfflineDistributionWizardandthePublishBookTool.

Business Example

Figure 96: Microsoft Office Integration

BusinessPlanningand Consolidation(BPC) forOfficecombinesthepower of

BPC with the rich functionality

of Microsoft Excel, Word andPowerPoint. With BPC for Office, you have all

of the Microsoft functionality

youareusedto,plus,yourworksheets,documents,andslideshowscanbelinked

directly to the BPC

databasethat housesyour company'sreporting data.

第 91 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Figure 97: The Action Pane

Logon

• ThelogonsectionshowstheactiveuserIDandtheapplicationset.

• To change the application set, select the link, make your selection, then

click OK.

Current View (CV)

• Thissectioncontrolswhichapplicationwithintheselectedapplicationset

is taken and which members, parent members, or top level members are

representedintheactivereportorinputschedule.

• Itallowsyoutodynamicallychangethecurrentviewbyenteringvarious

members,orselectingthemfromtheMemberLookup. Allthedimensions

andmembersto whichyou haveaccessareavailable, whenthesectionis

expanded.

第 92 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 98: Session Information

Alldimensionsintheapplicationappearinthecurrentview.

Every dimension must have a member selected. It could be either a parent or

base level member.

Multiplesinglevaluesor rangesarenotallowed.

Figure 99: See Also (Help!)

Please note that the content of the Online Help is also available in serveral

languages via:

第 93 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Service Marketplace → SAP Support Portal → Release and Upgrade Info

→ Installation and Upgrade Guides → Select B → Business Planning and

Consolidation→7.0VersionfortheMicrosoftPlatform.

Figure 100: BPC for Excel

Being able to access and input data into a central database via Excel helps to

ensure data consistency.

ItspossibletomodifytheTemplates. WewilldothisinCPM320. InCPM310the

focusisonusingthedeliveredReportsandInputSchedules.

Figure 101: BPC for Excel - eMenus

第 94 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Whenselecting the eDatamenu for thefirst time, thereisa slight delay asthe

Data Manager toolbar loads.

The eJournal menu is only available when you load a journal entry form by

selectingeTools>Journalandjournalshavebeensetupfortheapplication.

Ifyoudonthaveataskinyourtaskprofileinsecurity,itwillbegrayedout.

Figure102: TheCurrentViewandAccesstotheMemberLookup

The Current View is user dependent, therefore you will only be able to view

informationthatyour memberaccessprofileallows.

TheMemberLookupcanbeaccessedifyouclickatthetechnicalnamesofthe

relevant dimension within the Current View.

A further possibility is via: ETools -> Select Members. Then select your

dimension.

A third possibility would be if you are in an open Report Template or Input

Scheduleandyouexecutearightmouseclickatanyplaceyouwillfindthemenu

point “Select Members”.

第 95 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Figure 103: The Member Lookup 1/2

To select a member

1. OpentheMemberLookupdialogbox. SeeAccessingtheMemberLookup.

2. SelectHierarchytoviewthemembersinhierarchicalview,orTabletosee

the members in a flat list.

3. Select how you want to view details on members:ID Only, Description

Only, Member ID and Description

4. Search for the desired member. You can use the Filters tab to search on

specificproperties,theFindMemberslinktosearchforspecificmember

names, or use the Show Property field to display members that use a

specificproperty. Ifyoudefineafilter,thememberswillalwaysbeshown

in table view (not hierarchy view).

5. Highlightthememberinthelist,thenclickOK.Youcanselectmembersby

double-clickingthemifanoptionissetintheMemberSelectionOptions.

See Setting Member Lookup options. The check mark means that the

member has been copied to the clipboard for pasting into a spreadsheet.

See Copying/pasting member lists.

第 96 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 104: The Member Lookup 2/2

Youcanfilterthememberlistbasedonmemberproperties.

To filter by properties

1. FromtheMemberLookup,makesuretheFilteringareaisdisplayed. (Ifnot,

click the Show or

hide filtering window button.)

2. Select the Use the Filter check box.

3. FromtheFilterarea, selectoneormorepropertiesontheleft, andproperty

values on the right.

4. Click Refresh.

Tostopfilteringforadimension,cleartheUsetheFiltercheckbox.

Anothermethodtoselectmembers: eTools>Selectmembers.

第 97 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Figure 105: Analysis & Reporting

Books,PDBooks,andBookReportareusedtopre-calculation,distribution,and

collection.

WizardfolderstypicallycollecttemplatesaccessedviaTheReportorSchedule

Wizard.

Inputschedulesarelayouts(ieExcelspreadsheets)usedforplanninginput.

HQareforHeadQuarterscurrencyinputandintercompanyverification.

TheManagerreportsincludeEVDREexamples,them03statusreportandothers.

Hot Analysis includes report to report jumps.

ProcessandmenureportsincludetheCommandMenutypereports.

第 98 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 106: Dynamic Report Templates

Comparative This custom report contains any dimension in rows and

Categories/TimePeriodsincolumns. ItalsofeaturesVariances

calculation(bothamountandpercentage).

Trend ThiscustomreportcontainsanydimensioninrowsandTime

Periods across in columns.

Consolidat- ThiscustomreportcontainsanydimensioninrowsEntitytype

ing members across the columns.

AnybyAny Thiscustomreportcontainsanydimensionintherowsandany

dimension in the columns.

DrillinPlace Thiscustomreportcontainsperformsdrillinplace.

Sort and Thiscustomreport performssortingand ranking. Containsa

Rank pie chart.

%ofTotal Thiscustomreportperformsa%oftotalasacolumn. Thepctis

basedonthelastmemberintherow. Dimensionsareselectable

for Row and Column.

Graph-Comp This graphical report contains a Bar chart comparing 2

Categories/Time Periods.

Graph- This graphical report contains a Pie chart. Drill Downs are

Consol activeinthegraph. Donotdrillonabaselevelentity.

Free Form- Thisreporthasuserdefinedrowsandcolumns.

Anything

3Cat-Comp This custom report contains any dimension in rows and 3

categories/Time in columns.

第 99 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Year Over ThiscustomreportcontainsYearoverYearcomparisons.

Year

NestedRow Thiscustomreportallowsanytwodimensionstobenestedin

therowsandathirddimensioninthecolumn.

Double Thiscustomreport allowsyoutodrillfromonedimensionto

Expansion another in the rows.

FX Thiscustomreportprovidestheabilitytoverifytheresultsof

Verification currency translation and check rates in the Rate application.

Doesnot apply to allapplications.

Figure 107: Analysis & Reporting

BPCreportsaresavedinonlytwoplacesingeneral: Yourharddriveandonthe

BPC server.

IntheeMenus,Myreportsorinputschedulesrefertoyourharddrive.

TherearequiteafewfoldersontheBPCServerbutyoucanaddmore.

Filesonthe Companyfolderareavailabletoeveryone.

FilesontheTeamfolderareavailabletouserswhobelongtothatteam.

Onlyteamleaderscansave,delete,andmodifydocumentsintheteamfolder.

Thewizardfolderisuniqueinthatitalwayscontainsthedynamictemplates.

Itspossibleto createadditional dynamictemplateson theserver. They can be

madeavailabletoallusersontheclients. ThiswillbeshownindetailinCPM320.

第 100 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 108: The EvGTS Function

IfyouworkwithdeliveredReportTemplatesorcreateyourowntemplatesyou

willalwayshavetouseasocalledEVGTS-Functionwhichreadsthedatafrom

theserverintothecellswhichcontaintheEVGTSfunction.

EVGTS has the following syntax:

EvGTS(“AppName”,“ScaleValue”,“[dim:]member1”,“[dim:]mem-

ber2”,“[dim:]memberN”)

You can specify as many members as you want. Any dimension that is not

specifiedintheformulawillbetakenfromtheCurrentView.

If you only supply the period and not the year, EvGTS uses the year from the

CATEGORY YEAR property.

For example, EvGTS (Jan,Budget,GlobalMotors, Revenue,AllProducts) uses

whateveryearisspecifiedintheBudgetcategoryYEARproperty.

Ifthatyearis2004,theexamplegetsdatafortheperiodJan.2004.

Thesystem helpexplainsall of the EVfunctions.

第 101 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Figure 109: EvGTS - Sample

Tolockdownbothrowsandcolumns, usetheF4key.

Ifdimensionsarenotincluded,thecurrentviewmembervaluewillbeused.

While in the function argument box a change is made which affects a data

selection, #RFRmay appear which means that arefresh isrequired. It isnt an

error,itjustmeansthatpieceofdatahasntbeencalledduringthecurrentsession.

Figure 110: The Control Panel

第 102 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Control panels, which come standard in all sample BPC templates, contain

predefined dynamic expansion formulas.

Usingthecontrolpanel,youcanselectoptionsfortheexpansionfunctionswithout

having to write the functions yourself.

YoucanprohibitaccesstothecontrolpanelviaeTools>WorkbookOptions>Set

Worksheet Password.

ThissecurityisturnedoffviathenormalExcelworksheetsecurity.

You can type in single members in the frozen area which are either parents or

children. Multiple values are not allowed.

Figure 111: Configure the layout

Youcanonlytypeinthewhitecellsnottheyellowthosehaveformulas.

Ifyouhavethesamedimensionintherowsandcolumns,itwillappearinRED.

Expand:

Allrefreshesthecurrentviewfromthedatabase,andrunsanyexpansionfunctions

in the active report or input schedule.

第 103 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Figure 112: Specify Expansion Options

Thesystemvalidatesexpansionoptionsifyoutypethemin.

Figure 113: EV Expansion Options

Here yourcan see thedifferentexpansionoptions.

第 104 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 114: MDX Expansion Options

The Level Down Value in this case is 2.

Figure 115: Freeze Dimension Settings 1/2

Theupper slideshowstheFreezeDimensionsettings.

第 105 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Figure 116: Freeze Dimension Settings 2/2

Whentheworkbookissavedthesesettingsarestoredwiththeworkbook.

Figure 117: Scaling

Thefunctionargumentsbox isaccessedviathefixbutton ontheExcel toolbar

input field.

The help link is not functional.

第 106 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 118: Utilize Expansions

Although thesearedynamic reports, sometimesyou may need to hold a set of

roworcolumnmembersconstanttoprovideastaticviewofdataforcomparison

purposesetc. sothatswhyyoumightneedtoturnoffexpansion.

Anotherreasonwouldbewhenyouwantacertaingroupofmembersintherows

andyouwanttomanuallyenterthemembers,thenexpansionshouldbeturnedoff

so your work is not overwritten.

Figure 119: Filtering with Properties

Thisisagreatwaytoselectmembersfortheroworcolumnexpansion.

第 107 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Inthecontrolpanel, youcanrestrictthemembersviaonepropertyintherows

and/oroneinthecolumnsunlessyouuseMDXlogicforcomplexfiltering. There

is no drop down for properties in this case.

Figure 120: Park N Go

ParkNGoisinvokedautomaticallywhenyousaveinputschedulesonyourlocal

machine.

InBPCforExcel,opentheinputscheduleyouwanttosave,thenselecteSubmit>

SaveMySchedule. Ifyouhavenotlockeddownthedata,amessageisdisplayed.

To lock down your data, click Yes.

TheParkNGodialogboxisdisplayed. SelectLockdowncurrentviewonlyor

Lockdowndataandcurrentview,thenclickOK.SeeUsingParkNGo.

第 108 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 121: Data Input

BPCprovidesallthetoolsyourcompanyneedstocontrolthedatainputprocess

and analyze that data.

WithBPCreports,userscanquicklyaccessaccurate,current,andrelevantdata,

andworkwith itwithinthefamiliar Excel interface.

WithBPCinputschedules,userscanenterandsenddatatoadatabase,directly

fromExcelspreadsheets. BPCprovidesseveraltemplatesthathelpyougetstarted

working with reports and input schedules.

TheeSubmitmenucanbeusedtocreate,access,modify,andsaveinputschedules,

andsubmitsupportingdocumentstotheContentLibraryonBPCWeb.

第 109 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Figure 122: Dynamic Schedule Templates

Account- This schedule contains Account type members in Rows and

Trend Timeperiodsincolumns,usingEVSNDfunctions.

Comparative This schedule contains Account type members in Rows and

Category type members in columns with a variance column,

using EVSND functions.

Entity-Trend ThisschedulecontainsEntitytypemembersinRowsandTime

periodsincolumns, usingEVSNDfunctions.

Consolidat- This schedule contains Account type members in Rows and

ing EntitytypemembersinColumns,usingEVSNDfunctions.

NestedRow This schedule provides the ability to choose the dimensions

and members on the Row, Column, and Page. Two Row

dimensions can be selected and they will be nested. It uses

EVSND functions.

AnybyAny Thisscheduleprovidestheabilitytochoosewhichdimensions

areontheRowandColumn,usingEVSNDfunctions.

FreeForm This schedule provides the ability to choose the dimensions

andmembersontheRow, Column, andPage. Itdoesnotuse

expansionsandusesEVSNDfunctions.

第 110 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 123: The EvSND Function

The EvSND (Send) function referencesanother cell and sends its valueto the

databasewhenyouselectSendandRefreshSchedules.

The parametersspecify the dimensionsthat identify where the datavalue isto

be sent in the database.

EvSNDalsoreturnsthevaluefromthedatabasetothereferencedcell.

This is useful for two reasons:

1. Youdonothaveto addany EvGET functionstothesheet inorder toreturn

values to the cell;

2. Becausethevaluesarereturnedtothereferencedcell,BPConlysendsthose

cellsthathavechangedsincethelastsendinthecurrentsession.

第 111 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Figure 124: Submitting Comments

Therearethreewaysof howto createcomments.

Thefirstiscreatingcommentsinextracommentcolumns.

Thesecondiscreatingcommentsin Excel Pop-ups.

Both variants have in common that you can see the figures and the relevant

commentsat onetimeinoneTemplateor Schedule.

Thelastmethodisthatyoucreateacommentbutyoucantseethecommentatthe

sametimeinaTemlateorSchedule,sofiguresandcommentarekeptseparately.

The upper screenshot shows the first method.

第 112 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 125: Comments Function

Theupperscreenshotshowsthesecondvariantofcreatingcomments.

Figure 126: EVCOM - In Cell Popup

You can specify as many members that you want. Any dimension that is not

specifiedintheformulaistakenfromthecurrentview.

The following example sends the text value in cell D16 to the database:

EvCOM(D16,“Finance”,“Salaries”,“Budget”,“Jan2006”).

Comments submitted this way will be fully qualified with every dimension

assigned to a specific value.

100

第 113 页 / 共 237 页

BPC310 Lesson: BPC for Excel

TheotherEVCOMoptionistohavetextsinanothercellotherthanthedata. That

optionisverysimilartothecellpopupexceptthereadoptionsforcommentwill

be Within Cell input cell will not have data.

Figure 127: Submitting Comments via theAction Pane

Theactivereportorschedulecell: Thisoptionisavailablewhenalivereportor

scheduleisactive. Thecommentgetsappliedtothedataregionassociatedwith

the selected cell.

The active current view: This option applies the comment to the current view

shown in the action pane.

Customcurrentview: Thisoptionallowsyoutospecifyaspecificcurrentview

to which to associate the comment. From the Comment for these CV values

table, select the application. For each dimension, select the check box next to

thedimensionsforwhichyouwantaspecificmemberorallmemberstoapply.

Leavingacheckboxunselectedignoresthemembersforthatdimensionforthis

comment.

Keywordsareusedtohelpusersorganizeandsearchforcommentsinthedatabase.

101

第 114 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 128: Meta Data Included with Comments

Keywordsareusedtohelpusersorganizeandsearchforcommentsinthedatabase.

WhencommentsaresubmittedviaEVCOM,thekeywordisEVCOM!

Figure 129: Viewing Comments

Theactivereportorschedulecellreturnscommentsassociatedwiththeactivecell.

102

第 115 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Theactivecurrentviewreturnscommentsassociatedwiththeactivecurrentview.

The active current view is the combination of members displayed in Session

Information: CurrentViewandanylockedmembers.

Customcurrentviewreturnscommentsassociatedwithadifferentcurrentview

than the active one. From the Comment for these CV values table, select the

application. Foreachdimension,selectthecheckboxnexttothedimensionsfor

whichyouwanttoreturncommentsforaspecificmemberorforallmembersof

the dimension. Leaving a check box unselected ignores the members for that

dimension for this comment, and returns comments for any member of that

dimension.

In the What is the priority of the comment? field, select Critical, High,

Medium,Low,orNoPriority. Whenusersreportoncomments,theyareshown

by in order of their priority level.

Whileviewingcomments,youcanupdateacommentoraddmorecomments.

Figure 130: Comment History

IntheDoyouwanttoincludecommenthistoryfield,selectthecheckboxtoshow

multiplecommentsforthespecifiedsearchcriteria. Leaveunselectedtoreturn

onlythemostrecent comment thatmeetsthecriteria.

Afteraddingacommentorchoosingyourviewoptions,youcandisplaythemin

thecommentssummary page. If you justaddedanewcomment, thesummary

pagecontainsthenewlyaddedcommentdataregionandthecommententrythat

was made to the database.

103

第 116 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

If other comments exist for the same data region, those also appear in the

summarypage. Additionally,ifassociateddataregionscontaincomments,those

dataregionsalsoappearinthepage. Ifyoudefinedviewoptions,allassociated

comments are displayed.

Figure 131: Reporting on Comments

You can reach the commentsreports asfollows:

BPCforExcelActionPane,clickOpenSystemReports>CommentsReport

BPCWebActionPane,clickLaunchBPCSystemReports>CommentsReport

Figure 132: BPC System Reports

104

第 117 页 / 共 237 页

BPC310 Lesson: BPC for Excel

When comments are submitted via the action pane without an dimension, the

value ALL is stored in the comment tables.

Figure133: Example: Search CommentsBased onKeywords

UsercansearchkeywordsinSAPBusinessPlanningandConsolidation.

Intheexamplesabove, if thesearchiscarriedoutonwood, allother criteriais

omitted from the search.

Figure 134: Comment Tips

Theambiguityofemptydimensionsinthe“commentcontext”hasbeenremoved

in version 7 for NetWeaver.

105

第 118 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure135: BenefitsoftheDistributionandCollectionWizard

TheDistributorandCollectorprovidereportinganddatacollectionoptionswithin

SAPBPCandareheavilyusedinproductionenvironments.

BPC'sofflinedistributionandcollectionfeatureallowsyoutodistributereports

and input schedules to multiple recipients, based on a predefined distribution

list. Inaddition, youcanthencollect multipleinput schedulesfromusers. The

reportsthataredistributedareoffline,whichmeanstheycanbeuseddisconnected

from the server.

106

第 119 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Figure 136: Functionality

TheoverallpurposeoftheDistributoristhecreationanddistributionofoffline

reportswhiletheCollectorservesasamethodofdatacollectionandupload.

The mechanism to distribute offline reports is similar to publishing a book of

reports,butthisfeaturealsoallowsyoutodistributeandcollectinputschedules.

Other differences between publishing a book and distributing and collecting

reports and input schedules are:

• Reportsor input schedules arelocked (parked)

• Youcan collect changes to offline input schedules and send thedata to a

database

• Reportsandinputschedulesaredistributedthroughemailoradesignated

network folder.

107

第 120 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 137: Distributor and Collector Overview

Access and execution for Distributor and Collector happens via the client.

Distributingofflinereportsinvolvesatleastthreesteps, withanoptionalfourth

step to collect modified input schedules:

Step1: Createareportorinputscheduletemplatethatdefinesthelayoutofthe

report. For input schedules, you should set theinput cells to 'unprotected' and

protecttheworkbook. Also,theworkbookshouldnotcontainanydata. Thisis

becausetheinputscheduleexpandsupondistribution,andclearsallinputcells.

Step2: Createadistributionlistthatdefinesthescope(currentview)ofthedata

for one or more users.

Step3: UsetheDistributionWizardtodistributeofflinereportsorinputschedules

throughemailoranetworkfolder. Reportsarecreatedforeachuserandforeach

current viewdescribedby the distributionreport.

Step 4: (Optional) If you distributed input schedules, the users can input data

offline, and then email the reports back to you or save them in a designated

directory. YoucanthenusetheDistributionWizardtocollectthedatafromthe

modified reports and send it to the database.

Sincetheexecutionhappensontheclientmachine(notontheServer),allreports

willbeexecutedbasedontheauthorizationoftheuserexecutingtheWizard.

108

第 121 页 / 共 237 页

BPC310 Lesson: BPC for Excel

Figure 138: Distribution Lists

PDBOOKSisafixfolder. TheInputSchedulemustbestoredhere.

Figure 139: Default Distribution List

ThisisthedeliveredDistributionTemplate(inApShell).

AtthehighestlevelintheDistributiontemplate,youentertheoverallBookname

and then each Section Name.

Inthisexampleslide, thefirstsectionwilldistributeanInputSchedule,andthe

second section will distribute a Report.

Foreachsectionreport/schedulename,filtersandvarykeysneedtobespecified.

109

第 122 页 / 共 237 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Pleaseremember,thevarykeynamedineachsectionwillspecifywhatdimensions

toburston,andwhoshouldreceiveeachgeneratedreport.

Figure 140: Distribution Template Fields

ADistributionTemplatemaycontainthefollowingfields:

• BOOK: the titleof your distributed book.

• SECTION:denotesthebeginningof asection.

• REPNAME: report filename, location and title (can also be for Input

Schedules).

• FIXKEY:setsdimensionmemberstothefixedvalueswhengeneratingthe

report

For example, you can ensure that a report runs for 2008.TOTAL and

ACTUAL.

Todoasystemlookupfor yearuse: =YEAR(TODAY())&“.TOTAL”this

makes it dynamic for time!

• VARYKEYS:liststhedimensionsanddimensionmembersthatshouldvary

for each report that will get created.

Forexample,ifyouwanttocreateanewofflinereportforeachEntity/Cost

Center/etc,youwouldspecifythatdimensionasaVARYKEY.

YouthenmapaspecificVARYKEYtoaUserIDwhoshouldreceivethat

report(forexample,theCostCentermanager). Thisway,eachuserwhogets

areporthastherequireddimension(s)settotheirindividualvalues.

Note: If youwish todistributebyemail, makesure an email addresshas

been maintained for each user.

• SECTIONEND: denotestheend of asection.

110