BPC310_EN_Col91_FV_Part_A4 (第2页)

第 62 页 / 共 473 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Figure 65: Assigning the Library File

YoucanincludelibraryfilesontheOptionssheetoftheaccountdimensionusing

the *syslib command.

ApShellincludesthesystemfunctionsinfileMdxlib.LGL.

Figure 66: Mdxlib.LGL Example

AmajorityoftheMDXfunctionsdefineindustry-standardfinancialratios.

You can use ratios to evaluate the performance of your business and identify

potential problems.

第 63 页 / 共 473 页

BPC310 Lesson: Introduction to Logic

Ratiosexposefactorssuchastheearningpower, solvency, efficiency, anddebt

load of your business.

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

functions.

Figure67: BenefitsofDimensionMemberFormulas

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.

• Considerationmustbegiventotheimpactofanydimensionformulastoall

applicationssharingthisdimensionorallmembercombinationswithinan

application.

• Theseformulascanaffectretrievalperformanceifnotusedproperly.

Lab 11 – BPC Logic

(part of the last unit)

Lab 12 – Solve Order

(part of the last unit)

第 64 页 / 共 473 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

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.

YouwillhavetwofilesforeachScriptlogicyoucreate.

• .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.

第 65 页 / 共 473 页

BPC310 Lesson: Introduction to Logic

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

第 66 页 / 共 473 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

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.

Figure71: ScriptLogic–ExecutionviaDataManager

Iftheadministratorexecutesthescriptlogiccentrallyadatamanagerpackagehas

tobecreatedandaddedintotheDataManager.

第 67 页 / 共 473 页

BPC310 Lesson: Introduction to Logic

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

IfyouusedifferentdimensionnamestothoseusedintheLibraryfileyouwill

havetoupdatetheConstantsFilebysettingyourdimensionnameontheright

handside. Leavethelefthandsideunchanged.

Youcanalsoincludeapplicationconstantsandmemberconstantsinthedimension

constants file.

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

applicationwherevaluestobeusedintheformulawerestored.

第 68 页 / 共 473 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Figure 73: Benefits of Script Logic

Benefits related to Script Logic include:

• Real-time–allowsforreal-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.

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.

第 69 页 / 共 473 页

BPC310 Lesson: Introduction to Logic

BPC'sbusinessrulessupportbothmanagementandlegalconsolidationreporting.

• BusinessRulesareparameterbasedlogicthatresultsindatastoredwithinan

application.

• Legal consolidations scenarios can use the delivered business rule tables

to do:

– Eliminations

– Validations

– end of month currency translations

– ConsolidationsofInvestmenteliminationsieOwnershipofsubsidiaries,

retained earnings, minority interest etc

– Balance carry forward.

Figure 75: Logic Summary

TheBPCmodelinglogicenginehasprovenovertimetobeaveryflexibleand

effectivetooltoperformallsortsofcalculationswithinourdatabase,delivering

the desired results efficiently and accurately.

Lab 13 – Introduction into Script Logic

(part of the last unit)

Lab 14 – Script Logic and Data Management Package

(part of the last unit)

第 70 页 / 共 473 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Lesson Summary

You should now be able to:

• UnderstandthevarioustypesofBPClogicandwhenyouusethem.

第 71 页 / 共 473 页

BPC310 Lesson: Loading of Transaction Data

Lesson: Loading of Transaction Data

Lesson Overview

Lesson Objectives

Aftercompletingthislesson, youwillbeableto:

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

transformationmethodology, anderrorhandling.

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.

第 72 页 / 共 473 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Figure 77: Uploading Files on to the Server

Inordertoimportafile,thefilemustresideintheDataManagerDataFilesfolder

on the server.

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

Data Files folder on the server.

TheDownloadfunctionallowsyoutomovefilesfromtheDataFilesfolderonthe

server to your local hard drive.

Figure 78: Preview Data File

第 73 页 / 共 473 页

BPC310 Lesson: Loading of Transaction Data

Youcanpreviewyourdatafiles. Thisisusefulwhenyouwanttoseethewayyour

dataislaidoutwhencreatingatransformationfile. Youcanpreviewdatabase

(mdb),Excel,andASCIItextdatafiles. Youcanpreviewdatafilesthatresideon

theserverinCompanysiteorotherSitedirectories.

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. If

youchoseDelimited,selectadelimiterfromtheSelectadelimiterlist. b.

ForExcelfiles,selectaworksheetfromtheSelectaworksheetlist. c. For

databasefiles,selectatablefromtheSelectatablelist.

5. Usethescrollbarstolookatthedata. Thepreviewwindowloadsonlythe

first 200 lines of your data.

6. Ifyouwanttoopenadifferentfile, clicktheOpenbutton, selectanother

file, 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.

Youcreateoneconversionfileperdimensioninatransformation. Eachconversion

filecancontainoneormoresheetsfordifferenttypesofdatatransformations.

第 74 页 / 共 473 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Asabestpractice,conversionfilesshouldbenamedthesameasthedimension

for which they are being used.

DataManagersupportsVBscriptinconversionfiles. WhileVBAcanbeused

successfullyinsomeinstances, wecannotguaranteeitwillworkwithBPCfor

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.

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

FORMAToptionissettoDELIMITED,thisoptiondefinesthesinglecharacter

thatisthedelimiterbetweencolumns. UsethekeywordsSPACEorTABifspace-

or tab-delimited.

第 75 页 / 共 473 页

BPC310 Lesson: Loading of Transaction Data

SKIP=Default: 0(zero)Numberoflinestoskipatthetopofadatafile. Ifyour

datafilehasaheader, setthisvaluesothatthose

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: norounding. Specifiestheamountof

decimalplacestoroundvaluesduringthetransformation.

Figure 81: Delivered Packages in ApShell

Clearcreatesanadditionalrecordwithareversedsignandiseliminatedupon

compressiontoachievetheeffectofadeletion.

ThecopypackagecopiesdatabetweendimensionswithinBPCapplication.

TheexportpackageexportsdatavaluesfromtheBPCcube(includingcalculated

values) into an ASCII file.

TheimportpackageimportsoneormoreASCIIfilesintothecurrentapplication

ThesignsoftheamountsarereversedbasedontheAccounttype.

Themovepackageletsyoumoveanyselectionofdatawithinanapplication.

TheFXTranslationisusedforcurrencytranslation. Forexample,whenLCis

loadedintoFinance,USD&EURreportingcurrenciesarealsoloaded.

第 76 页 / 共 473 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

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

第 77 页 / 共 473 页

BPC310 Lesson: Loading of Transaction Data

Figure83: RuntheImportDataManagerPackage(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

sothatyoudonotneedtomodifythepackagesinorderforthemtoworkwith

your applications and dimensions. But you might want to modify packages to

behavedifferentlyortoperformadditionaltasks. Youcanmakemodifications

tothepackagessuppliedwithBPCorusethosepackagesastemplatestobuild

packages as per your requirements.

第 78 页 / 共 473 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Figure84: RuntheImportDataManagerPackage(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.

Youcancheckandchangepackageschedulesforallscheduledpackagesusing

the Package Scheduler Status window.

第 79 页 / 共 473 页

BPC310 Lesson: Loading of Transaction Data

Figure 86: View Package Status

ThePackagelogliststhemetadataforthepackagesuchastheappandappset.

TheFormulalogdisplaysdatarecordsandpossibleerrormessagesatthebottom

of the log.

Figure 87: Error Processing

第 80 页 / 共 473 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Figure 88: Error Processing

Ifyoudontuseatransformationfile,thereisadefaultlimitof500recordswhich

willrejectoutuntiltheloadfails. Ifyoudouseatransformationfile,youcanset

themaxnumberofrecordsbeforetheentireloadfails.

Figure 89: Error Processing

第 81 页 / 共 473 页

BPC310 Lesson: Loading of Transaction Data

Figure 90: Run a report to see the data

Toruntheabovereport: InBPCExcel→ReportingandAnalysis→Builda

reportusingadynamictemplate→Selecttrendreport→OK→setthecurrent

view as shown. Well go into more detail later.

Figure91: MergeandReplacefortheImportDataManagerPackage

ThenextslidesshowhowMergeandReplace&Clearworkonthedatabase.

第 82 页 / 共 473 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Figure92: ExampleofMerge/Replace/Append(1of4)

Pleasenotethatthemergeoptiondoesnotdeleteanythingfromtheexistingdata

setandnewrecordsaremergedintotheexistingrecords.

Thereplaceoptionhoweverdoesnotdeleterecordseither. Itactuallygenerates

offsettingrecordswiththereversesignthusprovidingthedeletioneffectwhen

the application is compressed.

Figure93: ExampleofMerge/Replace/Append(2of4)

第 83 页 / 共 473 页

BPC310 Lesson: Loading of Transaction Data

Figure94: ExampleofMerge/Replace/Append(3of4)

Figure95: ExampleofMerge/Replace/Append(4of4)

IfyouwanttotestthesystemreactiononMerge,Replace&Clear,Appenddoa

fulloptimizationonthecubesoastoclearthewritebackandfac2tables. Then

you will have a clean slate to start with.

Thencreateyourownflatfilestoreplicatetheexamples.

Tip: Onthereplaceandclearexample,useaSetofmembersthataretotallynew

tothecubebecauseinthatscenariothesystemwillbecheckingforpreviously

loadedrecordswiththesamekeyanditwilllookinallthreefacttables.

第 84 页 / 共 473 页

Unit2: CreatingApplications,IntroductiontoLogicandLoadingofTransaction

Data BPC310

Lab 15 – Loading Currency Exchange Rates into the

Rate Application

(part of the last unit)

Lab 16 – Loading Actual & Budget Data into the

Finance Application

(part of the last unit)

Lab 17 – Creating Forecast Data for the Finance

Application

(part of the last unit)

Lab 18 – Loading Data into the Sales Application and

Executing a Currency Translation

(part of the last unit)

第 85 页 / 共 473 页

BPC310 Lesson: Loading of Transaction Data

Lesson Summary

You should now be able to:

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

transformationmethodology, anderrorhandling.

第 86 页 / 共 473 页

UnitSummary BPC310

Unit Summary

You should now be able to:

• Understandhowtocreate,modelandoptimizeApplications. Youwillalso

haveanideaabouthowtosecureadimension.

• UnderstandthevarioustypesofBPClogicandwhenyouusethem.

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

transformationmethodology, anderrorhandling.

第 87 页 / 共 473 页

第 88 页 / 共 473 页

第 89 页 / 共 473 页

Unit 3

Working with BPC for Excel, BPC

Web, BPC for Word and BPC for

PowerPoint

Unit Overview

Unit Objectives

After completing this unit, you will be able to:

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

navigating with Delivered Templates, creating comments and using the

OfflineDistributionWizardandthePublishBookTool.

• UnderstandwhichfeaturesBPCWebdelivers.

• Understand BPC for 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 页 / 共 473 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Lesson: BPC for Excel

Lesson Overview

Lesson Objectives

Aftercompletingthislesson, youwillbeableto:

• 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

BusinessPlanningandConsolidation(BPC)forOfficecombinesthepowerof

BPCwiththerichfunctionalityofMicrosoftExcel,WordandPowerPoint. With

BPCforOffice,youhavealloftheMicrosoftfunctionalityyouareusedto,plus,

yourworksheets,documents,andslideshowscanbelinkeddirectlytotheBPC

databasethathousesyourcompany'sreportingdata.

第 91 页 / 共 473 页

BPC310 Lesson: BPC for Excel

Figure 97: The Action Pane

Logon

• ThelogonsectionshowstheactiveuserIDandtheapplicationset.

• Tochangetheapplicationset,selectthelink,makeyourselection,thenclick

OK.

Current View (CV)

• Thissectioncontrolswhichapplicationwithintheselectedapplicationset

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

representedintheactivereportorinputschedule.

• Itallowsyoutodynamicallychangethecurrentviewbyenteringvarious

members,orselectingthemfromtheMemberLookup. Allthedimensions

andmemberstowhichyouhaveaccessareavailable, whenthesectionis

expanded.

第 92 页 / 共 473 页

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.

Multiplesinglevaluesorrangesarenotallowed.

Figure 99: See Also (Help!)

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

languages via:

第 93 页 / 共 473 页

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.

It'spossibletomodifytheTemplates. WewilldothisinBPC320. InBPC310the

focusisonusingthedeliveredReportsandInputSchedules.

Figure 101: BPC for Excel – eMenus

第 94 页 / 共 473 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

WhenselectingtheeDatamenuforthefirsttime, thereisaslightdelayasthe

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

informationthatyourmemberaccessprofileallows.

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 页 / 共 473 页

BPC310 Lesson: BPC for Excel

Figure 103: The Member Lookup 1/2

To select a member

1. OpentheMemberLookupdialogbox. SeeAccessingtheMemberLookup.

2. Select Hierarchy to view the members in hierarchical view, or Table to

see 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,orusetheShowPropertyfieldtodisplaymembersthatuseaspecific

property. Ifyoudefineafilter,thememberswillalwaysbeshownintable

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 页 / 共 473 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 104: The Member Lookup 2/2

Youcanfilterthememberlistbasedonmemberproperties.

To filter by properties

1. From the Member Lookup, make sure the Filtering area is displayed. (If

not, 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 页 / 共 473 页

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 页 / 共 473 页

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 Thiscustomreportperformssortingandranking. 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 页 / 共 473 页

BPC310 Lesson: BPC for Excel

Year Over ThiscustomreportcontainsYearoverYearcomparisons.

Year

NestedRow Thiscustomreportallowsanytwodimensionstobenestedin

therowsandathirddimensioninthecolumn.

Double Thiscustomreportallowsyoutodrillfromonedimensionto

Expansion another in the rows.

FX Thiscustomreportprovidestheabilitytoverifytheresultsof

Verification currency translation and check rates in the Rate application.

Does not apply to all applications.

Figure 107: Analysis & Reporting

BPCreportsaresavedinonlytwoplacesingeneral: Yourharddriveandonthe

BPC server.

IntheeMenus,Myreportsorinputschedulesrefertoyourharddrive.

TherearequiteafewfoldersontheBPCServerbutyoucanaddmore.

FilesontheCompanyfolderareavailabletoeveryone.

FilesontheTeamfolderareavailabletouserswhobelongtothatteam.

Onlyteamleaderscansave,delete,andmodifydocumentsintheteamfolder.

Thewizardfolderisuniqueinthatitalwayscontainsthedynamictemplates.

Its possible to create additional dynamic templates on the server. They can be

madeavailabletoallusersontheclients. ThiswillbeshownindetailinBPC320.

第 100 页 / 共 473 页

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.

ThesystemhelpexplainsalloftheEVfunctions.

第 101 页 / 共 473 页

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, #RFR may appear which means that a refresh is required. It isnt an

error,itjustmeansthatpieceofdatahasntbeencalledduringthecurrentsession.

Figure 110: The Control Panel

第 102 页 / 共 473 页

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.

You can prohibit access to the control panel via eTools → Workbook Options

→ 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 页 / 共 473 页

BPC310 Lesson: BPC for Excel

Figure 112: Specify Expansion Options

Thesystemvalidatesexpansionoptionsifyoutypethemin.

Figure 113: EV Expansion Options

Hereyourcanseethedifferentexpansionoptions.

第 104 页 / 共 473 页

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

TheupperslideshowstheFreezeDimensionsettings.

第 105 页 / 共 473 页

BPC310 Lesson: BPC for Excel

Figure 116: Freeze Dimension Settings 2/2

Whentheworkbookissavedthesesettingsarestoredwiththeworkbook.

Figure 117: Scaling

ThefunctionargumentsboxisaccessedviathefixbuttonontheExceltoolbar

input field.

The help link is not functional.

第 106 页 / 共 473 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 118: Utilize Expansions

Althoughthesearedynamicreports, sometimesyoumayneedtoholdasetof

roworcolumnmembersconstanttoprovideastaticviewofdataforcomparison

purposesetc. sothatswhyyoumightneedtoturnoffexpansion.

Anotherreasonwouldbewhenyouwantacertaingroupofmembersintherows

andyouwanttomanuallyenterthemembers,thenexpansionshouldbeturnedoff

so your work is not overwritten.

Figure 119: Filtering with Properties

Thisisagreatwaytoselectmembersfortheroworcolumnexpansion.

第 107 页 / 共 473 页

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.

Lab 19 – BPC for Excel Navigation

(part of the last unit)

Lab 20 – Working with Dynamic Report Templates

(part of the last unit)

Lab 21 – Using the Park N Go Feature

(part of the last unit)

第 108 页 / 共 473 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 121: Data Input

BPCprovidesallthetoolsyourcompanyneedstocontrolthedatainputprocess

and analyze that data.

WithBPCreports,userscanquicklyaccessaccurate,current,andrelevantdata,

andworkwithitwithinthefamiliarExcelinterface.

WithBPCinputschedules,userscanenterandsenddatatoadatabase,directly

fromExcelspreadsheets. BPCprovidesseveraltemplatesthathelpyougetstarted

working with reports and input schedules.

TheeSubmitmenucanbeusedtocreate,access,modify,andsaveinputschedules,

andsubmitsupportingdocumentstotheContentLibraryonBPCWeb.

第 109 页 / 共 473 页

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 页 / 共 473 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure 123: The EvSND Function

The EvSND (Send) function references another cell and sends its value to the

databasewhenyouselectSendandRefreshSchedules.

Theparametersspecifythedimensionsthatidentifywherethedatavalueisto

be sent in the database.

EvSNDalsoreturnsthevaluefromthedatabasetothereferencedcell.

This is useful for two reasons:

1. YoudonothavetoaddanyEvGETfunctionstothesheetinordertoreturn

values to the cell;

2. Becausethevaluesarereturnedtothereferencedcell,BPConlysendsthose

cellsthathavechangedsincethelastsendinthecurrentsession.

Lab 22 – Working with the Dynamic Schedule

Templates

(part of the last unit)

第 111 页 / 共 473 页

BPC310 Lesson: BPC for Excel

Figure 124: Submitting Comments

Therearethreewaysofhowtocreatecomments.

Thefirstiscreatingcommentsinextracommentcolumns.

ThesecondiscreatingcommentsinExcelPop-ups.

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

commentsatonetimeinoneTemplateorSchedule.

Thelastmethodisthatyoucreateacommentbutyoucan'tseethecommentatthe

sametimeinaTemplateorSchedule,sofiguresandcommentarekeptseparately.

The upper screenshot shows the first method.

第 112 页 / 共 473 页

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 页 / 共 473 页

BPC310 Lesson: BPC for Excel

TheotherEVCOMoptionistohavetextsinanothercellotherthanthedata. That

optionisverysimilartothecellpopupexceptthereadoptionsforcommentwill

be Within Cell input cell will not have data.

Figure127: SubmittingCommentsviatheActionPane

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 页 / 共 473 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Figure128: MetaDataIncludedwithComments

Keywordsareusedtohelpusersorganizeandsearchforcommentsinthedatabase.

WhencommentsaresubmittedviaEVCOM,thekeywordisEVCOM!

Figure 129: Viewing Comments

Theactivereportorschedulecellreturnscommentsassociatedwiththeactivecell.

102

第 115 页 / 共 473 页

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

only the last comment that meets the criteria.

Afteraddingacommentorchoosingyourviewoptions,youcandisplaythemin

thecommentssummarypage. Ifyoujustaddedanewcomment, thesummary

pagecontainsthenewlyaddedcommentdataregionandthecommententrythat

was made to the database.

103

第 116 页 / 共 473 页

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

Youcanreachthecommentsreportsasfollows:

BPCforExcelActionPane,clickOpenSystemReports→CommentsReport

BPCWebActionPane,clickLaunchBPCSystemReports→CommentsReport

Figure 132: BPC System Reports

104

第 117 页 / 共 473 页

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: SearchCommentsBasedonKeywords

UsercansearchkeywordsinSAPBusinessPlanningandConsolidation.

Intheexamplesabove, ifthesearchiscarriedoutonwood, allothercriteriais

omitted from the search.

Figure 134: Comment Tips

Theambiguityofemptydimensionsinthe“commentcontext”hasbeenremoved

in version 7 for NetWeaver.

105

第 118 页 / 共 473 页

Unit3: WorkingwithBPCforExcel,BPCWeb,BPCforWordandBPCfor

PowerPoint BPC310

Lab 23 – Working with Comments

(part of the last unit)

Lab24 – Comments in Columns and in Excel Comment

Box

(part of the last unit)

Figure135: BenefitsoftheDistributionandCollectionWizard

TheDistributorandCollectorprovidereportinganddatacollectionoptionswithin

SAPBPCandareheavilyusedinproductionenvironments.

BPC'sofflinedistributionandcollectionfeatureallowsyoutodistributereports

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

list. Inaddition, youcanthencollectmultipleinputschedulesfromusers. The

reportsthataredistributedareoffline,whichmeanstheycanbeuseddisconnected

from the server.

106

第 119 页 / 共 473 页

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:

• Reportsorinputschedulesarelocked(parked)

• You can collect changes to offline input schedules and send the data to a

database

• Reportsandinputschedulesaredistributedthroughemailoradesignated

network folder.

107

第 120 页 / 共 473 页

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 the input cells to 'unprotected' and

protecttheworkbook. Also,theworkbookshouldnotcontainanydata. Thisis

becausetheinputscheduleexpandsupondistribution,andclearsallinputcells.

Step2: Createadistributionlistthatdefinesthescope(currentview)ofthedata

for one or more users.

Step3: UsetheDistributionWizardtodistributeofflinereportsorinputschedules

throughemailoranetworkfolder. Reportsarecreatedforeachuserandforeach

currentviewdescribedbythedistributionreport.

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 页 / 共 473 页

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