BPC320_EN_Col91_FV_Part_A4 (第3页)

第 214 页 / 共 377 页

Unit6: Labs BPC320

Figure 275: Conditional Formatting

13.ClicktheFormatbutton,chooseanoverlineborderandaboldfont,andclick

OK.

14.SelectOKtoclosetheConditionalFormattingdialogbox.

15.With cell D10 selected, select Edit → Copy.

16.SelectcellsD10:F20andselectEdit→PasteSpecial.

17.Select Formats and then select OK.

Note: Thecalculatedvaluesaredisplayedinboldwithanoverline.

18.Verifythatyourscreenlookssimilartothefollowing: (yourvaluesmayvary).

Figure 276: Example 2

19.HighlightcolumnsAandBandselectData→GroupandOutline→Group.

20.Clickontheminus(–)signsabovecolumnCandtotheleftofRow5tohide

the report definition area.

21.FromtheeToolsmenu,selectSaveDynamicTemplates.

196

第 215 页 / 共 377 页

BPC320 Lesson: Labs

22.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→Reports→Managerfolder.

23.IntheFileNamefield, enterLab6–VariancewithFormattingandclick

Save.

24.FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 7 – Modifying an Existing EVGTS Report

Objective: ModifyanexistingreporttodisplayEntitymembersinthecolumns

instead of Category members.

Description: Youcanadaptexistingreportstomeetyourneedsbysimplytyping

member IDs into the appropriate fields. All corresponding functions (such as

EVDES and EVGTS) will update automatically based on the new member ID

references you provide.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. Updating the Columns of the Report

1. Ensurethecurrentviewsettingsareasfollows:

Figure 277: Current View Settings

2. FromtheActionPane,clickReporting&Analysis.

3. FromtheActionPane,clickOpenanexistingreport.

4. OpenLab2–ActualvsBudgetlocatedintheReports→Managerfolderon

the BPC server.

5. Clickontheplus(+)signstodisplaythereportdefinitionarea.

6. EdittheformulaincellA2to=EVDIM(Sheet1!$B$1;“C”).

197

第 216 页 / 共 377 页

Unit6: Labs BPC320

7. In cell C9, type CONEUROPE.

8. In cell D9, type CONUS.

9.

Clickonthe RefreshWorkbookiconontheBPCforExceltoolbar.

10.Verifythatyourscreenlookssimilartothefollowing: (yourvaluesmayvary)

Figure 278: Example 1

Note: ThevaluesreturnedbyEVGTSincellsC10:D15areautomatically

updated. TheEVGTSfunctionspointtocellsC9andD9,andwillreturn

theappropriatevaluesdependinguponwhichdimensionmembersarein

those cells.

11.Clickontheminus(-)signsabovecolumnBandtotheleftofRow5tohide

the report definition area.

12.FromtheeToolsmenu,selectSaveDynamicTemplates.

13.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→Reports→Managerfolder.

14.IntheFileNamefield,enterLab7–EntityComparisonandclickSave.

15.FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 8 – Creating Expansions with EVEXP

Objective: CreateareportthatcontainsarowexpansionEVEXPfunctionanda

columnexpansionEVEXPfunction. Thiswillenabledrilldowncapabilityinboth

the rows and the columns of the report.

198

第 217 页 / 共 377 页

BPC320 Lesson: Labs

Description: Whenexpansionfunctionsareusedinreportsandinputschedules,

userscandynamicallyexpanddimensionaldatabasedoncriteriadefinedinthe

expansionformula. Intheexpansionformula,youcanspecifywhethertoexpand

thereportbyrow,column,orboth,andonwhichmembertobasetheexpansion

criteria. Byclickingonacellthatcontainsanexpansionformula, thereportis

restructuredtoincludethenewmembers,whilemaintainingitsoriginaldesign.

TheEVEXP(Expand)expansionfunctionperformsroworcolumnexpansions.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. Creating the Row Expansion

1. Ensurethecurrentviewsettingsareasfollows:

Figure 279: Current View Settings

2. UsingtheExcelFilemenu, opentheDrillReport.xlsworkbooklocatedin

yourmyComputer→Database. CPM310&CPM320_Training→CPM320

→ CPM320-## folder.

3. Select cell A7 and type Row Expansion.

4. Select cell B7.

5. FromtheActionPane, selectInsertfunction.

6.

FromtheActionPane,selectEVEXPandclickthegreencheck button.

7. EntertheFunctionArgumentsasshownbelow:

199

第 218 页 / 共 377 页

Unit6: Labs BPC320

Figure 280: Function Arguments 1

8. Select OK to insert the formula in cell B7.

9. DrilldownonUSConsolidatedbydouble-clickingontheentitydescription.

10.

Drill up to the original list of entities by clicking on the Go back icon

from the BPC for Excel toolbar.

11.Using the same procedures, drill down, and then drill up on Europe

Consolidated.

2. Creating the Column Expansion

1. Select cell A8 and type Column Expansion.

2. Select cell B8.

3.

FromtheActionPane,selectEVEXPandclickthegreencheck button.

4. IntheFunctionArgumentsdialogbox,enterthefollowing:

Figure 281: Function Arguments 2

200

第 219 页 / 共 377 页

BPC320 Lesson: Labs

5. Select OK to insert the formula in cell B8.

6. Drilldownonthe2008Q1parentbydouble-clickingonthetimemember

description.

7.

DrilluptotheoriginallistoftimemembersbyclickingontheGoback

icon from the BPC for Excel toolbar.

8. Usingthesameprocedures,drilldown,andthendrillupon2008Q2.

9. Clickontheminus(–)signsabovecolumnBandtotheleftofRow9tohide

the report definition area.

10.FromtheeToolsmenu,selectSaveDynamicTemplates.

11.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→Reports→Managerfolder.

12.IntheFileNamefield,enterLab8–DrillReportandclickSave.

13.FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 9 – Filtering via the Control Panel

Objective: Createareportusingadynamicwizardtemplate,andperformbasic

filtering using the Control Panel.

Description: AllBPCdynamicwizardtemplatesprovideabuilt-inControlPanel,

themechanismbywhichyoucancontrolthedimensions,expansions,andscaling

properties. TheControlPanelalsoallowsyoutofilteronapropertyandproperty

value for the row and/or column members.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. BuildingtheReportusingaDynamicTemplate

1. FromtheActionPane,clickReporting&Analysis.

2. FromtheActionPane,clickBuildareportusingadynamictemplate.

3. SelecttheAnybyAnytemplateandthenclickOpen.

4. Whenprompted“Areporthasbeenbuiltforyou”,clickOK.

5. Verifythatyourscreenlookssimilartothefollowing:

201

第 220 页 / 共 377 页

Unit6: Labs BPC320

Figure 282: Example 1

2. Creating the Control Panel Filter

1. Clickontheplus(+)signsabovecolumnHandtotheleftofrow39toaccess

the Control Panel.

2. Change the Row and Column Dimensions and Expansion options in the

Control Panel settings as you see below:

Figure 283: Control Panel 1

3. Click on the Expand All button.

4. ClosetheControlPanelbyclickingontheminus(–)signsabovecolumnH

and to the left of row 39.

5. Verifythatthereportlookssimilartothefollowing: (yourvaluesmayvary)

202

第 221 页 / 共 377 页

BPC320 Lesson: Labs

Figure 284: Report 1

6. AccesstheControlPanelandchangethesettingstomatchthefollowing:

Figure 285: Control Panel 2

7. ClickontheExpandAllbuttonandthenclosetheControlPanel.

8. Verifythatthereportlookssimilartothefollowing:

(your values may vary)

Figure 286: Report 2

Note: ThereportdisplaysonlyaccountsbelowNetIncomethathavean

account type property equal to INC.

203

第 222 页 / 共 377 页

Unit6: Labs BPC320

9. FromtheeToolsmenu,selectSaveDynamicTemplates.

10.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→Reports→Managerfolder.

11.IntheFileNamefield,enterLab9–IncomeAccountsandclickSave.

12.FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 10 – Utilizing EVPRO in Control Panel Filtering

Objective: CreateareportusingadynamicwizardtemplateandusetheEVPRO

function to filter the accounts based on the account type of the current view

account.

Description: The EVPRO (Property Value) function returns the value of a

specifiedpropertyforaspecifiedmember. YoucanutilizetheEVPROfunction

to make the Control Panel filter dynamic to the Current View. To accomplish

this: use the EVPRO function in the Property Value cell of the Control Panel

rather than hard–coding a value there.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. BuildingtheReportusingaDynamicTemplate

1. FromtheActionPane,clickReporting&Analysis.

2. FromtheActionPane,clickBuildareportusingadynamictemplate.

3. SelecttheAnybyAnytemplateandthenclickOpen.

4. Whenprompted“Areporthasbeenbuiltforyou”,clickOK.

5. Verifythatyourscreenlookssimilartothefollowing: (yourvaluesmayvary).

204

第 223 页 / 共 377 页

BPC320 Lesson: Labs

Figure 287: Example 1

2. AddingEVPROtotheControlPanelFilter

1. Clickontheplus(+)signstoaccesstheControlPanel.

2. ChangetheControlPanelsettingstomatchthefollowing:

Figure 288: Control Panel

3. In cell F24, type

=EVPRO($C$17;$D$20;$F$23)

Note: TheEVPROfunctionwillreturntheaccounttypepropertyofthe

currentviewaccount, whichinthiscaseisINC.

4. Click on the Expand All button.

5. ClosetheControlPanelbyclickingontheminus(–)signs.

6. Verifythatthereportlookssimilartothefollowing:

205

第 224 页 / 共 377 页

Unit6: Labs BPC320

Figure 289: Report 1

Note: The report displays only accounts that are dependants or self of

NetIncomeandhaveanaccounttypepropertyequaltoINC.

7. ChangethecurrentviewaccounttoTotOpExpandverifythatthereportlooks

similar to the following:

Figure 290: Report 2

Note: The report displays only accounts that are dependants of self of

TotOpExpandhaveanaccounttypepropertyequaltoEXP.

8. ChangethecurrentviewaccounttoTotalAssetsandverifythatthereportlooks

similar to the following:

206

第 225 页 / 共 377 页

BPC320 Lesson: Labs

Figure 291: Report 3

Note: The report displays only accounts that are dependants or self of

TotalAssetsandhaveanaccounttypepropertyequaltoAST.

9. ChangethecurrentviewaccountbacktoNetIncome.

10.FromtheeToolsmenu,selectSaveDynamicTemplates.

11.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→Reports→Managerfolder.

12.IntheFileNamefield,enterLab10–DynamicbyAccountTypeandclick

Save.

13.FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 11 – Filtering on Multiple Properties in an EVGTS

Report

Objective: Createareportusingadynamicwizardtemplateandbuildacustom

filtertofilteraccountsbasedonthreedifferentpropertyvalues.

Description: TheControlPanelallowsyoutoautomaticallyfilterononeproperty

intherowsand/oronepropertyinthecolumns. Ifyouwanttofilteronmorethan

oneproperty,youmustcreateyourownqueryandreferencethatqueryinthefilter

argumentoftheEVEXPfunctionusingthefollowingsyntax:

([DIMNAME].CurrentMember.Properties(“PROPNAME”)=“PROPVALUE”)

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. BuildingtheReportusingaDynamicTemplate

207

第 226 页 / 共 377 页

Unit6: Labs BPC320

1. Change your account dimension CV to NetIncome , then from the Action

Pane, click Reporting & Analysis.

2. FromtheActionPane,clickBuildareportusingadynamictemplate.

3. SelecttheTrendtemplateandthenclickOpen.

4. Whenprompted“Areporthasbeenbuiltforyou”,clickOK.

5. Verifythatyourscreenlookssimilartothefollowing:

Figure 292: Report 1

6. Clickontheplus(+)signstoaccesstheControlPanel.

7. ChangetheControlPanelsettingincellF20toEVMEMBERS.

Note: BychangingtherowexpansionflagtoEVMEMBERS,allmembers

intheaccountdimensionwillbedisplayed.

8. Click on the Expand All button.

9. Close the Control Panel and verify that your screen looks similar to the

following:

Figure 293: Report 2

208

第 227 页 / 共 377 页

BPC320 Lesson: Labs

2. Adding the Custom Filter

1. AccesstheControlPanelandselectcellE32.

2. EnterthefollowingcustomfilterinthecellE32:

([ACCOUNT].currentmember.properties(“FINSTMT”)=“PL”and

[ACCOUNT].currentmember.properties(“ACCTYPE”)=“EXP”and

[ACCOUNT].currentmember.properties(“CALC”)=“Y”)

Note: Makesureyouincludeaspacebeforeandaftereach“and”..

3. SelecttherowexpansioncellF27andeditthefunctionto:

=IF($F$26="ON",EVEXP($C$17,$F$19,C47:C230,D47:P230,TRUE,

$C$2,$F$21,F22,$E$32),"OFF")

Note: Changethelastargument(the$E$32)topointtothecustomfilter

cell.

4. Click on the Expand All button.

5. Close the Control Panel and verify that the report looks similar to the

following:

Figure 294: Report 3

Note: ThereportdisplaysonlycalculatedP&Laccountswithanaccount

type of expense.

209

第 228 页 / 共 377 页

Unit6: Labs BPC320

6. FromtheeToolsmenu,selectSaveDynamicTemplates.

7.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→Reports→Managerfolder.

8. IntheFileNamefield, enterLab11–CalculatedPLExpenseAccounts

and click Save.

9. FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 12 – Filtering with MDX Functions in an EVGTS

Report

Objective: Createareportusingadynamicwizardtemplate. Buildcustomfilters

using the MDX Name and Count functions.

Description: Youcanfilterthemembersthatappearonareportorinputschedule

byutilizinganMDXfunction–suchasNameorCount–andthenreferencing

thatMDXfunctionintheFilterargumentoftheEVEXPfunction.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. BuildingtheReportusingaDynamicTemplate

1. FromtheActionPane,clickReporting&Analysis.

2. FromtheActionPane,clickBuildareportusingadynamictemplate.

3. SelecttheAnybyAnytemplateandthenclickOpen.

4. Whenprompted“Areporthasbeenbuiltforyou”,clickOK.

5. Verifythatyourscreenlookssimilartothefollowing:

210

第 229 页 / 共 377 页

BPC320 Lesson: Labs

Figure 295: Report 1

6. AccesstheControlPanelandchangetheControlPanelsettingstomatchthe

following:

Figure 296: Control Panel

7. Click on the Expand All button.

8. Close the Control Panel and verify that your screen looks similar to the

following:

211

第 230 页 / 共 377 页

Unit6: Labs BPC320

Figure 297: Report 2

Note: NoticethattheEliminationentityisdisplayed. Inthefollowing

steps,youwillbuildacustomfilterthatwillsuppressthatentityfromthe

expansionusingtheMDXNamefunction.

2. Adding the MDX Name filter

1. Access the Control Panel and select E32.

2. Enter the following custom filter in the cell:

[Entity.H1].CurrentMember.Name<>"ELIM_CONREGION"

3. SelecttherowexpansioncellF27andeditthefunctionto:

=IF($F$26="ON",EVEXP($C$17,$F$19,C47:C50,D47:P50,TRUE,

$C$2,$F$21,F22,$E$32),"OFF")

Note: Change the last argument (in this case $E$32) to point to the

custom filter cell.

4. Click on the Expand All button.

5. ClosetheControlPanelandverifythatthereportlookssimilartothefollowing:

Figure 298: Report 3

Note: The Regional Consolidation Elimination entity is no longer

displayed on the report.

6. FromtheeToolsmenu,selectSaveDynamicTemplates.

7.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→Reports→Managerfolder.

8. IntheFileNamefield,enterLab12–NoEliminationEntityandclickSave.

3. Adding the MDX Count Filter

212

第 231 页 / 共 377 页

BPC320 Lesson: Labs

1. WithLab12–NoEliminationEntitystillopen,accesstheControlPanel.

2. ChangetheControlPanelsettingincellF20toEVMEMBERS.

Note: By changing the row expansion flag to EVMEMBERS, all

membersintheentitydimensionwillbedisplayedwiththeexceptionof

theRegionalConsolidationEliminationentity.

3. Click on the Expand All button.

4. ClosetheControlPanelandverifythatthereportlookssimilartothefollowing:

Figure 299: Report 4

5. AccesstheControlPanelandselectcellE32.

6. Enter the following custom filter in the cell:

[Entity.H1].CurrentMember.Children.Count>5

7. Click on the Expand All button.

8. ClosetheControlPanelandverifythatthereportlookssimilartothefollowing:

213

第 232 页 / 共 377 页

Unit6: Labs BPC320

Figure 300: Report 5

Note: ThereportonlydisplaystheUSConsolidatedentity,becauseitis

theonlyentitywithmorethan5dependants.

9. FromtheeToolsmenu,selectSaveDynamicTemplates.

10.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→Reports→Managerfolder.

11.IntheFileNamefield,enterLab12–MoreThan5Dependantsandclick

Save.

12.FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 13 – Drilling in Place in an EVGTS Report

Objective: Createareportusingadynamicwizardtemplate. ChangetheDrill

Down option to Expand by Inserting Rows.

Description: TheDrillDownoptionscontrolmemberexpansionbehavior. When

ExpandbyOverwritingRowsisselected,theexpandedmembersaredisplayed

intherowsbelowtheexpandedmember,clearingexistingmembersasneeded.

When Expand by Inserting Rows is selected, any rows below the expanded

memberareshifteddown,andnewrowsareinsertedtoaccommodatethenewly

displayed members.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. BuildingtheReportusingaDynamicTemplate

1. FromtheActionPane,clickReporting&Analysis.

2. FromtheActionPane,clickBuildareportusingadynamictemplate.

214

第 233 页 / 共 377 页

BPC320 Lesson: Labs

3. SelecttheTrendtemplateandthenclickOpen.

4. Whenprompted“Areporthasbeenbuiltforyou”,clickOK.

5. Double-clickontheaccountOperatingIncometoperformadrilldownand

verifythatyourscreenlookssimilartothefollowing:

Note: Therowsexpandbyoverwritingexistingrows.

6.

DrilluptotheoriginallistofaccountsbyclickingontheGoback icon

from the BPC for Excel toolbar.

Figure 301: Report 1

2. Changing the Drill Down Option

1. FromtheActionPane,clickSetworkbookoptions.

2. IntheDrillDownOptionssection,selecttheExpandbyinsertingnewrows

radio button.

3. FromthebottomoftheCurrentWorkbookOptionsdialogbox,clickOK.

4. When prompted “Changing the drill-down option may result in the loss of

expansionhistory. Doyouwanttocontinue?”clickYes.

5. Again,double-clickontheaccountOperatingIncometoperformadrilldown.

6. Verifythatyourscreenlookssimilartothefollowing:

215

第 234 页 / 共 377 页

Unit6: Labs BPC320

Figure 302: Report 2

Note: Theaccountsexpandbyinsertingnewrowsinsteadofoverwriting

the existing rows.

7. Drill up to the original set of accounts.

8. FromtheeToolsmenu,selectSaveDynamicTemplates.

9.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→Reports→Managerfolder.

10.IntheFileNamefield,enterLab13–DrillinPlaceandclickSave.

11.FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 14 – Custom Suppression in an EVGTS Report

Objective: Createascheduleusingadynamicwizardtemplate. Createcustom

suppressionthatwillsuppressentitiesthatdonotcontainactualdatafortheprior

year.

Description: Control Panel zero suppression, by default, works against the

members defined in the current view. If your report requires any type of

suppression beyond the current view, you need to customize the suppression

referencedbytheFilterargumentofyourEVEXPfunction.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

216

第 235 页 / 共 377 页

BPC320 Lesson: Labs

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. Working with Default Suppression

1. From the Action Pane, click Data Input.

2. FromtheActionPane,clickBuildascheduleusingadynamictemplate.

3. SelecttheEntity–TrendtemplateandthenclickOpen.

4. Whenprompted“Aschedulehasbeenbuiltforyou”,clickOK.

5. Verifythatyourschedulelookssimilartothefollowing:

Figure 303: Schedule 1

6. AccesstheControlPanelandchangetheControlPanelsettingstomatchthe

following:

SetyourcurrentviewtomatchF15:22(butdonottypeinF15:24asthereare

formulae in there). Copy the values in F15:22 to E15:22 via Paste Special

→ Values.

217

第 236 页 / 共 377 页

Unit6: Labs BPC320

Figure 304: Control Panel

7. Click on the Expand All button.

8. WhentheExpandOptionsdialogboxisdisplayed,selectClearinput,then

click OK.

9. ZerooutallstoresforallmonthsexceptforStore1andSendthedata.

10.Close the Control Panel and verify that the schedule looks similar to the

following:

Figure 305: Schedule 2

Note: OnlytheStore1entitycurrentlyhasbudgetdatafor2009.

218

第 237 页 / 共 377 页

BPC320 Lesson: Labs

11.AccesstheControlPanelandchangetheSuppressZeroflagfromNotoYes

for the rows only.

12.Click on the Expand All button.

13.WhentheExpandOptionsdialogboxisdisplayed,selectClearinput,then

select OK.

14.Close the Control Panel and verify that the schedule looks similar to the

following:

Figure 306: Schedule 3

Note: Bydefault,theControlPanelsuppressesdatabasedonthecurrent

view, which is set to 2009 Budget. All entities except Store 1 (and its

parent,RegionalConsolidation)aresuppressedbecausethereisno2009

Budgetdata. InordertoonlysuppressentitiesthatdonotcontainActual

datafor2008,acustomsuppressionfiltermustbecreatedthatwillreferto

adifferentcategoryandtimeperiodthanarereferencedinthecurrentview.

Note: Yourschedulewillalsoinclude4otherstoresbutonlybecauseof

rounding. Theirtotaldoesnotexactlyequalzero.

2. Creating Custom Suppression

1. AccesstheControlPanelandselectcellG28.

2. Enter the following custom filter in the cell:

([ENTITY].Currentmember,[2008.TOTAL],Actual)<>0

Note: suppression filter references 2008 time periods for the Actual

category.

219

第 238 页 / 共 377 页

Unit6: Labs BPC320

3. Select the row expansion cell H24.

4. Changetheexpansionfunctiontothefollowing:

=IF($H$23="ON",EVEXP($E$14,$H$16,E46:E47,F46:AV47,

TRUE,$E$2,$H$18,H19,$G$28),"OFF")

Note: Changethelastargument($G$28)topointtothecustomfiltercell.

5. Click on the Expand All button.

6. WhentheExpandOptionsdialogboxisdisplayed,selectClearinput,then

select OK.

7. ClosetheControlPanelandverifythatthereportlookssimilartothefollowing:

Figure 307: Schedule 4

Note: Theschedulenowdisplaysentitiesthatcontain2008Actualdata.

Entities that do not contain 2008 Actual data (the elimination entities)

are suppressed.

8. FromtheeToolsmenu,selectSaveDynamicTemplates.

9.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→InputSchedules→Managerfolder.

10.IntheFileNamefield,enterLab14–BudgetExternalSalesbyEntityand

click Save.

11.FromtheExcelFilemenu,selectClosetoclosethetemplate.

220

第 239 页 / 共 377 页

BPC320 Lesson: Labs

Lab 15 – Asymmetric Suppression in an EVGTS Report

Objective: Create custom suppression for a report that contains different

categoriesandmeasuresforeverycolumninthereport.

Description: Asymmetricreportscontaindifferentcategoriesandmeasuresfor

every column of the report, and therefore require customized suppression that

must be referenced by the Filter parameter of your EVEXP function. Due to

the different categories and measures defined in the columns, the appropriate

dimensionsmustbeincludedinyourzerosuppressionfilters.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. CreatingtheAsymmetricSuppressionFilter

1. From the Excel File menu, select Open.

2. OpenthefileAsymmetricSuppression.xlslocatedinyourMyComputer→G

Drive→CPM310&CPM320_Training→CPM320→CPM320-##folder.

3. OpentheControlPanelandenter2008.OCTinC19. ExpandAll.

4. Verifythatyourscreenlookssimilartothefollowing:

Figure 308: Report 1

221

第 240 页 / 共 377 页

Unit6: Labs BPC320

5. CopyandPastevaluesfromJ79:K79toJ81:K81andalsofromP79:Q79to

P81:Q81.

Note: Initially,thereisnosuppressiontakingplaceintherows.

6. ScrolltothebottomofthereportandverifythattheCurrentTotalvalues(the

sumofalltherowscalculatedusingExcel’sSUMfunction)andtheCorrect

Total values (the value that the Current Totals should equal for verification

purposes)areequivalent,asshowintheexamplebelow.

Figure 309: Report 2

7. Access the Control Panel.

8. SelectcellF25andselectYEStoturnonzerosuppressionfortherows.

This will add a zero suppression filter of:

([ACCOUNT].Currentmember,[ACTUAL])<>0

in cell E2, and the filter argument of the EVEXP function in cell F27 will

automaticallybeupdatedtoreferencethiszerosuppressionfilter.

9. Click on the Expand All button.

10.Scroll down and across and verify that your report looks similar to the

following:

222

第 241 页 / 共 377 页

BPC320 Lesson: Labs

Figure 310: Report 3

Note: Thetotalsforthecurrentmonthcolumnsarecorrect,butthetotals

fortheYTDcolumnsarenot. Specifically,thesetotalsarelessthanthey

should be: too many rows being suppressed because the current filter

is based off the current view and is only evaluating data for October

2008. ThezerosuppressionfilterthatwasautomaticallycreatedbyBPC

referencesthecurrentview,whichissettothePeriodicmeasure.

11.Select cell E32 and enter:

([ACCOUNT].Currentmember,[ACTUAL],[2008.TOTAL])<>0

12.Select the row expansion cell F27.

13.Changetheexpansionfunctiontothefollowing:

=IF($F$26="ON",EV-

EXP($C$17,$F$19,C48:C66,D48:S66,TRUE,$C$2,$F$21,F22,E32),"OFF")

Note: Change the last argument (E32) to point to the custom zero

suppression cell.

14.Click on the Expand All button.

15.Scroll down and across and verify that your report looks similar to the

following:

223

第 242 页 / 共 377 页

Unit6: Labs BPC320

Figure 311: Report 4

Note: Now all totals are correct. This is because the filter has been

updatedtoincludenotonlyOctober2008,butalldatafor2008,thecorrect

rowsarebeingsuppressedinboththeperiodicandYTDcolumns.

16.Close the Control Panel.

17.FromtheeToolsmenu,selectSaveDynamicTemplates.

18.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→Reports→Managerfolder.

19.IntheFileNamefield, enterLab15–AsymmetricSuppressionandclick

Save.

20.FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 16 – Indenting Row Headers in an EVGTS Report

Objective: Createareportusingadynamicwizardtemplateandaddindentation

to the row descriptions.

Description: You can utilize BPC and Excel functions to indent your row

headingsbaseduponthehierarchicalstructureofthedimension. TheHLEVEL

property represents the hierarchy level of a member. The lower down in the

hierarchyamemberis, thehighertheHLEVELvalue. REPTisusedtorepeat

text a given number of times.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

224

第 243 页 / 共 377 页

BPC320 Lesson: Labs

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. BuildingtheReportusingaDynamicTemplate

1. FromtheActionPane,clickReporting&Analysis.

2. FromtheActionPane,clickBuildareportusingadynamictemplate.

3. SelecttheTrendtemplateandthenclickOpen.

4. Whenprompted“Areporthasbeenbuiltforyou”,clickOK.

5. Access the Control Panel.

6. ChangetheControlPanelsettingincellF20toEVMEMBERS.

7. ChangetheControlPanelsettingincellF25toYES.

8. Click on the Expand All button.

9. Scroll down and across and verify that your report looks similar to the

following: (Your numbers may vary)

Figure 312: Report 1

10.Select cell K47.

11.Changetheformulabyenteringthefollowing:

=REPT(" ";$D47)&EVDES($C47)

12.ClickontheExpandAllbuttontoapplytheupdatedfunctiontoalltherow

headers.

13.ClosetheControlPanelandverifythatthereportlookssimilartothefollowing:

225

第 244 页 / 共 377 页

Unit6: Labs BPC320

Figure 313: Report 2

14.FromtheeToolsmenu,selectSaveDynamicTemplates.

15.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL→Reports→Managerfolder.

16.IntheFileNamefield,enterLab16–IndentedAccountsandclickSave.

17.FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 17 – Creating a Custom Button in an EVSND Input

Schedule

Objective: Createacustombuttononaninputschedulethatcanbeusedtosend

data to the BPC server.

Description: Custombuttonscanbeaddedtoreportsandschedulestoprovideone

clickaccesstocommonlyusedtasksformanyoftheBPCactionpaneselections.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

226

第 245 页 / 共 377 页

BPC320 Lesson: Labs

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. Creating the Custom Button

1. Changethecurrentviewtothefollowingsettings:

Figure 314: Current View

2. From the Action Pane, click Data Input.

3. FromtheActionPane,clickOpenanexistingschedule.

4. Open Lab 1 – Budget Input Schedule located in the Input Schedules →

Manager folder.

Hint: Ifyoudon'tseeyourLab1templategotoeTools→OpenDynamic

Templates

5. FromtheExcelViewmenu,selectToolbars→Forms.

6.

FromtheFormstoolbar,clicktheButtontool .

7. DrawthenewbuttonintheareaofcellsB12:B13.

8. Verifythatyourscreenlookssimilartothefollowing:

227

第 246 页 / 共 377 页

Unit6: Labs BPC320

Figure 315: Example 1

9. IntheMacroNamefieldoftheAssignMacrodialogbox,type:

MNU_eSUBMIT_REFSCHEDULE_SHEET_REFRESH

10.Click OK.

11.ClickonthenewlycreatedbuttonandeditthetexttoSendData,thenclick

off the button.

12.Close the Forms tools dialog box.

13.Verifythatyourscreenlookssimilartothefollowing:

Figure 316: Example 2

14.FromtheeToolsmenu,selectSaveDynamicTemplates.

15.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selecttheeEXCEL\InputSchedules\Managerfolder.

16.IntheFileNamefield,enterLab17–CustomSendDataButtonandclick

Save.

228

第 247 页 / 共 377 页

BPC320 Lesson: Labs

2. Testing the Custom Button

1. Enter200,000forExternalSalesforJanuaryand250,000forFebruary.

2. Enter300,000for3rdPartyProductforJanuaryand350,000forFebruary.

3. ClickonthenewlycreatedSendDatabutton.

Note: The MNU command you used ends data and refreshes the

worksheetwithoutanyadditionalprompts.

4. To verify that the data has been sent, scroll to the right and make sure the

scheduleisrefreshedwiththetotalyearvaluesasshownbelow:

Figure 317: Schedule

5. FromtheExcelFilemenu,selectClosetoclosethescheduleanddonotsave

any changes.

Lab 18 – Customizing Dynamic Wizard Templates

Objective: CustomizetheconditionalformattingandcompanylogofortheTrend

dynamic wizard template.

Description: Excel’s conditional formatting is built into all BPC dynamic

templatestodeterminetheappearanceofrowheadingsanddatacells. Youcan

utilize the default conditional formatting, or customize it to meet your needs.

Application administrators have rights to: add and delete wizard templates,

updatethetextandgraphicsassociatedwithwizardtemplates,andreorderwizard

templates.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

229

第 248 页 / 共 377 页

Unit6: Labs BPC320

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. Editing a Dynamic Wizard Template

1. Changethecurrentviewtothefollowingsettings:

Figure 318: Current View

2. FromtheActionPane,clickReporting&Analysis.

3. FromtheActionPane,clickBuildareportusingadynamictemplate.

4. SelecttheTrendtemplateandthenclickOpen.

5. Whenprompted“Areporthasbeenbuiltforyou”,clickOK.

6. Verifythatyourscreenlookssimilartothefollowing:

(Your values may vary)

Figure 319: Report 1

230

第 249 页 / 共 377 页

BPC320 Lesson: Labs

7. From the Excel Edit menu, select Go To.

8. Click on the Special... button.

9. From the Go To Special dialog box, select the Conditional Formats radio

button, then select OK.

Note: Excelhighlightsallcellsthathaveconditionalformattingdefined.

10.Access the Control Panel.

11.SelectcellK47andselectConditionalFormattingfromtheExcelFormat

menu.

Note: The conditional formatting is based on the Row Style and Calc

values in cells E47 and F47.

12.View the formatting and then click Cancel.

13.SelectcellL47andselectConditionalFormattingfromtheExcelFormat

menu.

Note: The conditional formatting is based on the Row Style value in

cell E47.

14.UsingtheFormatbuttonforCondition1,changetheFontStyletoBoldItalic

andclickOKtoclosetheFormatCellsdialogbox.

15.ClickOKtoclosetheConditionalFormattingdialogbox.

16.

ClickontheExpandall toolfromtheBPCforExceltoolbartoapplythe

new conditional formatting to all data cells.

17.Close the Control Panel.

18.VerifythatthePretaxIncomeandNetIncomeaccountvaluesappearinthe

font style Bold Italic as shown below:

Figure 320: Report 2

231

第 250 页 / 共 377 页

Unit6: Labs BPC320

19.SelecttheSAPlogoincellsI39:J40andclickthe[Delete]key.

20.Select cell I39 and type Pet Depot.

21.FormatcellI39tobeboldwitha24point,bluefont.

22.Verifythatyourscreenlookssimilartothefollowing:

Figure 321: Report 3

23.FromtheeToolsmenu,selectSaveDynamicTemplates.

24.

Clickonthe Companybuttonontheleftsideofthedialogbox,then

selectTemplate2.xltintheeEXCEL→Reports→Wizardfolder,asshown

below:

Figure 322: Wizard

232

第 251 页 / 共 377 页

BPC320 Lesson: Labs

25.Click Save.

26.Whenprompted, “Template2.xltalreadyexists. Doyouwanttoreplacethe

existing file?” click Yes.

27.Whenprompted,“Doyouwanttoincreasethetemplateversion?”clickYes.

28.FromtheExcelFilemenu,selectClosetoclosethetemplate.

2. ConfirmingtheDynamicWizardTemplateChanges

1. FromtheActionPane,clickReporting&Analysis.

2. FromtheActionPane,clickBuildareportusingadynamictemplate.

3. SelecttheTrendtemplateandthenclickOpen.

4. Whenprompted“Areporthasbeenbuiltforyou”,clickOK.

5. Verifythatyourscreenlookssimilartothefollowing:

Figure 323: Report 4

6. FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 19 – Creating an EVDRE Report

Objective: UsetheEVDREfunctiontobuildareport. WorkwiththeOptions

table. Overridespecificmembersintheheaderareaandformatotherstoindicate

members can be selected.

Description: TheEVDRE(DataRangeExchange)functionallowsyoutocreate

largereportsorinputscheduleswithoptimalperformance.

EvDREsupportsOptionsthatcanbeactivatedenteringtheappropriatekeywords

intheOptionRangecelloftheKeyRange. Theseoptionscanbecombinedusing

commaasdelimiter. Forexample,youcanenterSumParent,AutoFitCol,NoSend.

Alternatively, theOptionRangemaycontainanEVRNGfunctionpointingtoa

rangeofcells. Thefirstcolumnintherangemustcontainavalidoptionkeyword.

The second column will activate the corresponding option with a Y or with a

numeric value, where appropriate.

233

第 252 页 / 共 377 页

Unit6: Labs BPC320

You define a PageKey range to specify default dimension members in a

one-column range. The PageKey range is optional, or you can limit it to only

specificdimensionsintheapplication. Ifanydimensionsarenotspecifiedinthe

PageKeyrange, theyaretakenfromthecurrentview.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. Creating an EVDRE Report

1. Ensureyourcurrentviewsettingsareasfollows:

Figure 324: Current View Settings

2.

Click on the New button from the Excel toolbar to create a new blank

worksheet.

3. IncellA1type=EVDRE()andpress[Enter].

4.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

5. CompletetheEVDREBuilderasshownbelow:

234

第 253 页 / 共 377 页

BPC320 Lesson: Labs

Figure 325: EvDRE Builder

6. In the EVDRE Builder, click OK.

7. Clickontheplus(+)signabovecolumnDtodisplaythereportdefinitionarea.

8. Verifythatyourscreenlookssimilartothefollowing:

(Your numbers may vary)

Figure 326: Example 1

9. Double–click cell F6.

10.FromtheMemberSelectordialogbox,selectCONUSandclickOK.

11.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

Note: BPCqueriesthedatabaseandreturnsdatafortheCONUSmember.

235

第 254 页 / 共 377 页

Unit6: Labs BPC320

12.Double–click cell F10.

13.FromtheMemberSelectordialogbox,select2008.Q1andclickOK.

14.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

Note: BPCqueriesthedatabaseandreturnsdataforthe2008.Q1member

and its dependants.

15.Select cell F3 and type NetIncome.

Note: The intent here is to replace the EVCVW function with a

hard-coded member.

16.Select cell F4 and type ACTUAL.

17.Select cell F5 and type TOTALADJ.

18.Select cell F7 and type All_Interco.

19.Select cell F8 and type Periodic.

20.Select cell F9 and type USD.

21.FormatcellsF3:F5andF7:F9withaFillofGray–25%.

Figure 327: Format Cells 1

236

第 255 页 / 共 377 页

BPC320 Lesson: Labs

22.FormatcellsF6andF10withaFillofWhiteandaThickBoxBorder.

23.Verifythatyourscreenlookssimilartothefollowing:

Figure 328: Example 2

24.Edit cell B16 to DEP,SELF.

25.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

26.Edit cell C16 to ALL.

27.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

28.Verifythatyourscreenlookssimilartothefollowing:

Figure 329: Example 3

29.Select cell G14 and format the cell to show zero decimal places with an

outline border and a no color pattern.

30.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

31.Verifythatyourscreenlookssimilartothefollowing:

237

第 256 页 / 共 377 页

Unit6: Labs BPC320

Figure 330: Example 4

32.In cells B29, B30, and B38, type Y to activate the HideColKeys,

HideRowKeys, andShowNullAsZerooptions.

33.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

34.Clickontheminus(–)signtoclosethereportdefinitionarea.

35.Verifythatyourscreenlookssimilartothefollowing:

Figure 331: Example 5

36.SelecteTools→SaveDynamicTemplatesandsavethereporttotheeEXCEL→

Reports→ManagerfolderontheserverasLab19–BasicEVDREReport.

37.Leave the template open for the next lab.

238

第 257 页 / 共 377 页

BPC320 Lesson: Labs

Lab 20 – Creating an EVDRE Input Schedule

Objective: Usethereporttemplatecreatedinthepreviouslabtocreateaninput

scheduletemplate. Overridespecificmembersintheheaderareaandutilizethe

EVMBRfunctionforotherstoallowforselectionofmemberswithoutaffecting

the current view. Add custom buttons.

Description: TheWorkbookTypefieldoftheCurrentWorkbookOptionsdialog

boxdetermineswhetheranEVDREworkbookwillbehavelikeareportorinput

schedule.

YoucanusetheEvMBRfunctioninPageKeyrangestodefinespecificmembers.

The EvMBR function returns the ID of a member (selected from the Member

Lookupdialogbox)tothecellwithoutchangingthecurrentview.

You can add custom buttons to your reports and input schedules to provide

one–clickaccesstocommonlyperformedtasks. YoucanusetheBPCmenutasks

as the macros to program on the buttons.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

User ID: CPM-##(with##=monitornumber)

Password: training

1. Creating an EVDRE Input Schedule

1. With Lab 19 – Basic EVDRE Report open, click on the plus (+) sign to

display the report definition area.

2. In cells B29, B30, and B38, remove the Y to deactivate the HideColKeys,

HideRowKeys, andShowNullAsZerooptions.

3.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

4. FromtheActionPane,selectSetworkbookoptions.

5. In the Type field, select Input Schedule.

6. UnderRefreshOptions,selectRefreshafterdatasendandthenclickOK.

7.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

8. WhentheExpandOptionsdialogboxisdisplayed,selectClearinput,then

click OK.

9. SelectcellB15andchooseCATEGORYfromthedropdownlist.

239

第 258 页 / 共 377 页

Unit6: Labs BPC320

10.Select cell B16 and type NBUDGET.

11.Select cell C16 and BAS,SELF.

12.Select cell F5 and type Input.

13.Select cell F7 and type Non_Interco.

14.Select cell F9 and type LC.

15.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

16.WhentheExpandOptionsdialogboxisdisplayed,selectClearinput,then

click OK.

17.Verifythatyourscreenlookssimilartothefollowing:

Figure 332: Example 1

18.Select cell F6.

19.FromtheActionPane, selectInsertfunction.

20.

FromtheActionPane,selectEVMBRandclickthegreencheck button.

Note: EVMBRfunctionsallowmemberstobeselectedwithoutaffecting

the current view.

21.EntertheFunctionArgumentsasshowbelow:

Figure 333: Function Arguments 1

22.Select OK to insert the formula in cell F6.

Note: IgnoreanyerrormessagesthatappearincellA1.

240