BPC320_EN_Col91_FV_Part_A4 (第4页)

第 259 页 / 共 377 页

BPC320 Lesson: Labs

23.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbarand

selectYeswhenpromptedtoclearexistinginputdata.

24.Select cell F10.

25.

FromtheActionPane,selectEVMBRandclickthegreencheck button.

26.EntertheFunctionArgumentsasshowbelow:

Figure 334: Function Arguments 2

27.Select OK to insert the formula in cell F10.

Note: IgnoreanyerrormessagesthatappearincellA1.

28.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbarand

selectYeswhenpromptedtoclearexistinginputdata.

29.Verifythatyourscreenlookssimilartothefollowing:

Figure 335: Example 2

30.Clickontheminus(–)signtoclosethereportdefinitionarea.

31.SelecteTools→SaveDynamicTemplatesandsavethereporttotheeEXCEL

→ Input Schedules → Manager folder on the server as Lab 20 – Basic

EVDRE Input Schedule.

2. Adding Custom Buttons

241

第 260 页 / 共 377 页

Unit6: Labs BPC320

1. FromtheExcelViewmenu,selectToolbars→Forms.

2.

FromtheFormstoolbar,clicktheButtontool .

3. DrawthenewbuttonintheareaofcellsJ2:K3.

4. IntheMacroNamefieldoftheAssignMacrodialogbox,type:

MNU_ETOOLS_REFRESH

5. Click OK.

6. ClickonthenewlycreatedbuttonandeditthetexttoRefreshdata,thenclick

off the button.

7.

FromtheFormstoolbar,clicktheButtontool .

8. DrawthenewbuttonintheareaofcellsJ7:K8.

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 336: Example 3

14.SelecteTools→SaveDynamicTemplatesandsavethereporttotheeEXCEL

→ Input Schedules → Manager folder on the server as Lab 20 – Basic

EVDRE Input Schedule.

15.When prompted, “The …file already exists. Do you want to replace the

existing file?” select Yes.

3. Testing the Input Schedule

242

第 261 页 / 共 377 页

BPC320 Lesson: Labs

1. Double-clickcellF6,chooseSTORE12fromtheMemberSelectordialogbox,

and click OK. Set time to 2009.JAN

2. Click the Refresh data button you created and select Yes when prompted

to clear existing input data.

3. Verifythatyourscreenlookssimilartothefollowing:

Figure 337: Example 4

Note: Data is returned for the External Sales and 3rd Party Product

accountsbecausewesentdatatothoseintersectionsinapreviouslab.

4. Enter 10,000 for Raw Materials.

5. Enter 15,000 for Direct Labor.

6. Click the Send data button you created.

7. Verifythatyourscreenlookssimilartothefollowing:

243

第 262 页 / 共 377 页

Unit6: Labs BPC320

Figure 338: Example 5

8. FromtheExcelFilemenu,selectClosetoclosetheworkbook. Donotsave

any changes.

Lab 21 – Working with EVDRE MemberSet Options

Objective: CreatereportswhichutilizedifferentMemberSetoptions.

Description: TheMemberSetparameterdescribesthesetofmemberstoexpand

forthecorrespondingdimension. Youcandefineoneormoreofthefollowing:

1. A comma-delimited list of members,

2. Avalidkeywordthatdescribesthemembersrelativetothecorresponding

dimensionmemberspecifiedinthePageKeyRange,or

3. Afilterthatdescribesthememberstoreturn.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

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

Password: training

244

第 263 页 / 共 377 页

BPC320 Lesson: Labs

1. Utilizing a comma–delimited list

1.

Clickonthe NewbuttonfromtheExceltoolbartocreateanewblank

worksheet.

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

3.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

The EvDRE Builder is displayed.

4. Leave the defaults as is and click OK.

5. Clickontheplus(+)signabovecolumnDtodisplaythereportdefinition

area.

6. SelectcellB15andchooseCATEGORYfromthedropdownlist.

7. IncellB16, typeACTUAL,JanFcst,FebFcst.

8. IncellC16,typeExtSales,Salaries,SuppliesandsetTIMEto2008.Q1.

9.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

10. Clickontheminus(–)signtoclosethereportdefinitionarea.

11. Verifythatyourscreenlookssimilartothefollowing: (Yourvaluesmay

vary).

Figure 339: Example 1

12. Select eTools → Save Dynamic Templates and save the report to the

eEXCEL→Reports→ManagerfolderontheserverasLab21–EVDRE

MemberSet Comma delimited list.

245

第 264 页 / 共 377 页

Unit6: Labs BPC320

2. Utilizing keywords and null fields

1. WithLab21–Commadelimitedliststillopen,clickontheplus(+)sign

abovecolumnDtodisplaythereportdefinitionarea.

2. SelectcellB15andchooseTIMEfromthedropdownlist.

3. In cell B16, type DEP,SELF.

4. In cell C16, type

DEP(GrossMargin),GrossMargin,,BAS(TotOpExp),TotOpExp

5.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

6. Clickontheminus(–)signtoclosethereportdefinitionarea.

7. Verifythatyourscreenlookssimilartothefollowing: (Valuesmayvary)

Figure 340: Example 2

8. Select eTools → Save Dynamic Templates and save the report to the

eEXCEL→Reports→ManagerfolderontheserverasLab21–EVDRE

MemberSetKeyWordsandNullFields.

3. Utilizing filters

1. WithLab21–KeyWordsandNullFieldsstillopen,clickontheplus

(+)signabovecolumnDtodisplaythereportdefinitionarea.

2. SelectcellB15andchooseENTITYfromthedropdownlist.

3. Right click in cell B16, and choose EVDRE: Build filter… from the

pop up menu.

4. Enter the Filter as shown below:

246

第 265 页 / 共 377 页

BPC320 Lesson: Labs

Figure 341: Filter 1

5. ClicktheApplybutton,andthefilterisautomaticallyaddedtocellB16

with the proper syntax.

6. Right click in cell C16, and choose EVDRE: Build filter… from the

pop up menu.

7.

Enter the Filter as shown below:

Figure 342: Filter 2

8. ClicktheApplybutton,andthefilterisautomaticallyaddedtocellB16

with the proper syntax.

9.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

10. Clickontheminus(–)signtoclosethereportdefinitionarea.

11. Verifythatyourscreenlookssimilartothefollowing:

Figure 343: Example 3

247

第 266 页 / 共 377 页

Unit6: Labs BPC320

12. Select eTools → Save Dynamic Templates and save the report to the

eEXCEL→Reports→ManagerfolderontheserverasLab21–EVDRE

MemberSet Filters.

13. FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 22 – Working with EVDRE Formatting Options

Objective: UtilizetheFormattabletoestablishformattingforrowandcolumn

headers, and data, and to format calculated members and data differently than

input members and data.

Description: You can define multiple format values when you want to define

formattingfordifferentareasofareport. Tousethisfunctionality, atablewith

six columns is utilized.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

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

Password: training

CreatinganEVDREReportwithAdvancedFormatting

1.

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

worksheet.

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

3.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

4. CompletetheEVDREBuilderasshownbelow:

248

第 267 页 / 共 377 页

BPC320 Lesson: Labs

Figure 344: EvDRE Builder

5. In the EVDRE Builder, click OK.

6. Clickontheplus(+)signtodisplaythereportdefinitionarea.

7. In cell I6, type CONEUROPE.

8. In cell B16, type ALL,SELF.

9. In cell B19, type Y.

10.In cell C16, type ALL,SELF.

11.

Clickonthe Expandalliconandverifythatyourscreenlookssimilarto

the following: (your values may vary)

Figure 345: Example 1

249

第 268 页 / 共 377 页

Unit6: Labs BPC320

12.Format cell C25 for zero decimal places, a thousands separator, and a

dotted line border.

13.SelectcellsA27:F33andchooseEdit/Clear/Contents.

14.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following: (Values may vary)

Figure 346: Example 2

15.Definerows27–31oftheEVDREFormattingTableasshownbelow:

Figure 347: EVDRE Formatting Table 1

16.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

250

第 269 页 / 共 377 页

BPC320 Lesson: Labs

Figure 348: Example 3

17.Definerows33–38oftheEVDREFormattingTableasshownbelow:

Figure 349: EVDRE Formatting Table 2

18.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

251

第 270 页 / 共 377 页

Unit6: Labs BPC320

Figure 350: Example 4

19.SelecteTools→SaveDynamicTemplatesandsavethereporttotheeEXCEL

→Reports→ManagerfolderontheserverasLab22–EVDREFormatting.

20.FromtheExcelFilemenu,selectClosetoclosethetemplate.

Figure 351: EvDRE Builder

252

第 271 页 / 共 377 页

BPC320 Lesson: Labs

Lab 23 – Creating Nested Dimensions in an EVDRE

Report

Objective: Build a report that has nested dimensions in the rows and utilizes

the BeforeRange parameter.

Description: EVDREperformswellwithnesteddimensions. TheBeforeRange

parameter is used to dynamically insert rows and columns at the beginning of

expansions.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

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

Password: training

1. CreatinganEVDREReportwithNestedDimensions

1.

Clickonthe NewbuttonfromtheExceltoolbartocreateanewblank

worksheet.

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

3.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

4. CompletetheEVDREBuilderasshownbelow:

Figure 352: EvDRE Builder

253

第 272 页 / 共 377 页

Unit6: Labs BPC320

5. In the EVDRE Builder, click OK.

6. Clickontheplus(+)signtodisplaythereportdefinitionarea.

7. In cell G6, type CONLARGE.

8. In cell C16, type DEP(CostOfGoods).

9. In cell C19, type Y.

10. In cell D16, type DEP,SELF.

11.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

Figure 353: Example 1

2. Utilizing the BeforeRange Parameter

1. SelectcellsH14:H49andchooseEdit→Clear→Contents.

2. Select row 1 and insert 1 row.

3. IncellH1, type=EVDES($F1)andhit[Enter].

Note: H1 will display as #NODATA.

254

第 273 页 / 共 377 页

BPC320 Lesson: Labs

4. Format cell H1 as bold.

5. FormatcellsH1:M1withaFillofGray–25%.

6. IncellD18,type=EVRNG(H1:M1)andhit[Enter].

Note: AnerrordisplaysincellA1. Becauseweinsertedarowatthetop

ofthereport,thePageKeyRangeneedstobeupdated.

7. SelectcellB5, hittheF2key, andhit[Enter].

8.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

9. Youwillbepromptedwiththemessageshownbelow:

Figure 354: Message

10. SelecttheDisablethepromptnexttimecheckboxandclickYes.

11. Verifythatyourscreenlookssimilartothefollowing:

Figure 355: Example 2

255

第 274 页 / 共 377 页

Unit6: Labs BPC320

12. Select eTools → Save Dynamic Templates and save the report to the

eEXCEL→Reports→ManagerfolderontheserverasLab23–Nested

Dimensions with BeforeRange.

13. FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 24 – Working with EVDRE AfterRange Options

Objective: BuildareportthatutilizesAfterRangeandEVSUMtocalculatetotals

for the rows in each column.

Description: TheAfterRangeparameterisanoptionalparameterthatyoucanuse

todynamicallyinsertrowsandcolumnsofdataattheendofexpansions. Ifyou

havenestedexpansions,allAfterRangerangesarerepeated. Ifyouwanttoadda

columnthatsumsthecolumndatavalues,youcanaddanEVSUMformula.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

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

Password: training

1. Creating the EVDRE Report

1.

Clickonthe NewbuttonfromtheExceltoolbartocreateanewblank

worksheet.

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

3.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

4. CompletetheEVDREBuilderasshownbelow:

256

第 275 页 / 共 377 页

BPC320 Lesson: Labs

Figure 356: EvDRE Builder

5. In the EVDRE Builder, click OK.

6. Clickontheplus(+)signtodisplaythereportdefinitionarea.

7. In cell G6, type CONEUROPE.

8. In cell C16, type DEP,SELF.

9. In cell C19, type Y.

10. IncellD16,typeRawMaterials,Supplies,OverheadCosts.

11.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

Figure 357: Example 1

257

第 276 页 / 共 377 页

Unit6: Labs BPC320

2. Utilizing the BeforeRange Parameter

1. SelectcellsH14:H28andchooseEdit→Clear→Contents.

2. Select row 1 and insert 1 row.

3. IncellH1, type=EVDES($F1)andhit[Enter].

Note: H1 will display as #NODATA.

4. Format cell H1 as bold.

5. FormatcellsH1:M1withaFillofGray–25%.

6. IncellD18,type=EVRNG(H1:M1)andhit[Enter].

Note: AnerrordisplaysincellA1. Becauseweinsertedarowatthetop

ofthereport,thePageKeyRangeneedstobeupdated.

7. SelectcellB5, hittheF2key, andhit[Enter].

8.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

9. Youwillbepromptedwiththemessageshownbelow:

10. SelecttheDisablethepromptnexttimecheckboxandclickYes.

11. Verifythatyourscreenlookssimilartothefollowing:

Figure 358: Example 2

3. UtilizingtheAfterRangeParameterandEVSUM

1. Select row 1 and insert 2 rows.

2. In cell H1, type Total.

258

第 277 页 / 共 377 页

BPC320 Lesson: Labs

3. Format cell H1 as bold.

4. In cells J1:M1, type EVSUM.

5. FormatcellsJ1:M1asaboldnumberthatdisplaystwodecimalplaces

and uses a thousands separator.

6. FormatcellsH1:M1withaFillofGray–40%.

7. IncellD21,type=EVRNG(H1:M2)andhit[Enter].

Note: AnerrordisplaysincellA1. Becauseweinsertedrowsatthetopof

thereport,thePageKeyRangeneedstobeupdated.

8. SelectcellB7, hittheF2key, andhit[Enter].

9.

Clickonthe Expandallicon,YESandverifythatyourscreenlooks

similar to the following:

Figure 359: Example 3

10. Select eTools → Save Dynamic Templates and save the report to the

eEXCEL → Reports → Manager folder on the server as Lab 24 –

AfterRange and EVSUM.

11. FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 25 – Working with EVDRE Suppression Options

Objective: UtilizetheSuppressfieldoftheExpansionRangetable.

259

第 278 页 / 共 377 页

Unit6: Labs BPC320

Description: The Suppress parameter allows you suppress rows and columns

returning null values in all fields during an expansion. You can also suppress

basedonadifferentregionofdatathantheonebeingretrieved.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

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

Password: training

1. SuppressingRowsAcrosstheEntireReport

1. FromtheActionPane,clickReporting&Analysis.

2. FromtheActionPane,clickOpenablankworkbook.

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

4.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

5. CompletetheEVDREBuilderasshownbelow:

Figure 360: EvDRE Builder

6. In the EVDRE Builder, click OK.

7. Clickontheplus(+)signtodisplaythereportdefinitionarea.

8. In cell I6, type CONLARGE.

260

第 279 页 / 共 377 页

BPC320 Lesson: Labs

9. IncellC16,selectBASMEMBERSfromthedropdownlist.

10. In cell A29, type TIME.CALC=Y.

11. CopytheformulaincellA29downthroughA31.

12.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

Figure 361: Example 1

13. In cell C19, type Y.

14.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

261

第 280 页 / 共 377 页

Unit6: Labs BPC320

Figure 362: Example 2

15. Select eTools → Save Dynamic Templates and save the report to the

eEXCEL → Reports → Manager folder on the server as Lab 25 –

Standard EVDRE Suppression.

2 SuppressingBasedonDataNotFoundintheReport

1. WithLab25–StandardEVDRESuppressionstillopen,selectcellI4

and type BUDGET.

2. Select cell I6 and type CONREGION.

3. Select cell I10 and type 2009.Q1.

4. Select cell B16 and type DEP,SELF.

5. SelectcellC15andchooseENTITYfromthedropdownlist.

6. Select cell C16 and type ALL,SELF.

7. IncellC19,removetheYsothatthecellisblank.

8. SelectcellA35andtypeENTITY.CALC=Y.

9. CopytheformulaincellA35tocellsA36andA38.

10. SelectcellA37andtypeENTITY.HLEVEL=1.

262

第 281 页 / 共 377 页

BPC320 Lesson: Labs

Figure 363: Criteria 1

Hint: If Rows 33-38 are blank, copy them from the Lab 22 EVDRE

formatting template

11.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

Figure 364: Example 3

263

第 282 页 / 共 377 页

Unit6: Labs BPC320

12. SelectcellC19andtypeACTUAL,2008.Q1.

13.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

Figure 365: Example 4

Note: The schedule displays only entities that contain Actual data for

Quarter1of2008. EntitiesthatdonotcontainActualdataforQuarter1of

2008(theeliminationentities)aresuppressed.

14. Select eTools → Save Dynamic Templates and save the report to the

eEXCEL→Reports→ManagerfolderontheserverasLab25–Custom

EVDRE Suppression.

15. FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 26 – Utilizing Suppression in an Asymmetric

EVDRE Report

Objective: UtilizetheSuppressfieldoftheExpansionRangetabletosuppress

across rows in an asymmetric EVDRE report.

Description: The Suppress parameter allows you suppress rows and columns

returningnullvaluesinallfieldsduringanexpansion.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

264

第 283 页 / 共 377 页

BPC320 Lesson: Labs

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

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

Password: training

1. CreatingtheReportandAdjustingtheColKeyRange

1. FromtheActionPane,clickReporting&Analysis.

2. FromtheActionPane,clickOpenablankworkbook.

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

4.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

5. CompletetheEVDREBuilderasshownbelow:

Figure 366: EvDRE Builder

6. In the EVDRE Builder, click OK.

7. Clickontheplus(+)signtodisplaythereportdefinitionarea.

8. Double click cell I6 and select STORE1.

9. Select cell I9 and type LC.

10. Select cell I10 and type 2008.OCT.

11. CompletetheExpansionRangeasshownbelow:

265

第 284 页 / 共 377 页

Unit6: Labs BPC320

Figure 367: ExpansionRange

Hint: Theformattingshouldappearasbelow. Ifitdoesnot,copyrows

33-38fromtheLab22EVDREformattingtemplate.

Figure 368: Formatting 1

12.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

Figure 369: Example 1

266

第 285 页 / 共 377 页

BPC320 Lesson: Labs

13. Select cell E19 and type Y.

14.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

Figure 370: Example 2

Note: Despite the different categories and measures defined in the

columns, EVDRE suppression evaluates all the data in the report and

suppresses the appropriate rows.

15. Select eTools → Save Dynamic Templates and save the report to the

eEXCEL → Reports → Manager folder on the server as Lab 26 –

Asymmetric EVDRE Suppression.

16. FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 27 – Creating Multiple Key Ranges in an EVDRE

Report

Objective: Createareportwithmultiplerowandcolumnkeyranges.

Description: This feature can be activated assigning multiple ranges to

the same EVRNG function. For example, a ColKeyRange that contains

=EVRNG(H13:J13;H19:J19).

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

267

第 286 页 / 共 377 页

Unit6: Labs BPC320

System Data

System: CITRIXCommonTrainingenvironment

Client: Start> Programs> Accessories>Remote Desktop

Connection

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

Password: training

1. Adapting an Existing Report

1. Select eTools → Open Dynamic Templatesand open Lab 19 – Basic

EVDRE Report from the eEXCEL → Reports → Manager folder on

the server.

2. Clickontheplus(+)signtodisplaythereportdefinitionarea.

3. Select cell F6 and type CONREGION.

4. IncellsB29,B30,andB38,removetheYsothecellsareblank.

5.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

6. SelectcellA1andedittheEVDREformulato=EVDRE($F$2,A4:B11).

Note: TheintentionhereistodeletetheoptionalExpandRangeargument.

7. SelectcellsA13:C20andchooseEdit→Clear→Contents.

8. SelectcellsE17:J47andchooseEdit→Clear→Contents.

9. SelectcellsE12:J16andchooseEdit→Copy.

10. Select cell E19 and choose Edit → Paste.

11. Removeanyextraneousborderandfillformatting, andverifythatyour

screen looks similar to the following:

Figure 371: Example 1

12. ChangememberIDsintherowkeyrange(columnE)andcolumnkey

range(rows12&19)tomatchthefollowing:

268

第 287 页 / 共 377 页

BPC320 Lesson: Labs

Figure 372: Example 2

2 UpdatingtheRowKeyRangeandCellKeyRangeParameters

1. SelectcellB5andeditittomatchthefollowing:

=EVRNG(G12:J12;G19:J19)

2. SelectcellB6andeditittomatchthefollowing:

=EVRNG(E14:E16;E21:E23)

3.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

4. Verifythatyourscreenlookssimilartothefollowing:

Figure 373: Example 3

5. Copy the member IDs from cells E14:E16 and paste them into cells

E21:E23.

6.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

7. Verifythatyourscreenlookssimilartothefollowing:

269

第 288 页 / 共 377 页

Unit6: Labs BPC320

Figure 374: Example 4

8. Select eTools → Save Dynamic Templates and save the report to the

eEXCEL → Reports → Manager folder on the server as Lab 27 –

Multiple EVDRE Key Ranges.

9. FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 28 – Retrieving Data from Multiple Applications

with EVDRE

Objective: Createareportthatwillretrievedatafromtwoapplications.

Description: Youcancreatereportsthatretrievedatafrommultipleapplications

using the EVDRE function The applications must reside within the same

application set.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

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

Password: training

1. Creating the Initial Report

1.

Clickonthe NewbuttonfromtheExceltoolbartocreateanewblank

worksheet.

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

270

第 289 页 / 共 377 页

BPC320 Lesson: Labs

3.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

4. In the EVDRE Builder, delete Lab 22 – EVDRE Formatting from the

ImportstylefromserverfieldandselecttheUsedefaultformatting

radio button.

5. VerifytheAllowformattingareaoftheEVDREBuildermatcheswhat

is shown below:

Figure 375: EvDRE Builder 1

6. DeselecttheAllowformattingcheckbox,andverifytheGridDesignarea

oftheEVDREBuildermatcheswhatisshownbelow:

Figure 376: EvDRE Builder 2

7. ChangetheIncolumnsandInrowsselectionstocompletetheEVDRE

Builder as shown below:

Figure 377: EvDRE Builder 3

271

第 290 页 / 共 377 页

Unit6: Labs BPC320

8. In the EVDRE Builder, click OK.

9. Clickontheplus(+)signtodisplaythereportdefinitionarea.

10. DoubleclickcellF6andchooseCONREGION.

11. IncellB16,selectMEMBERSfromthedropdownlist.

12. In cell C16, type STORE5,STORE7.

13.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

Figure 378: Example 1

2 AddingAnotherEVDRERangetotheReport

1. SelectcellsA1:I20andchooseEdit→Copy.

2. Select cell A22 and choose Edit → Paste.

3. EdittheformulaincellA22to=EVDRE($F$23,A25:B32,A35:C41).

4. Select cell F23 and type RATE.

5. EdittheformulaincellG23to=EVAPD($F$23).

Note: IgnoreanyerrorsthatappearincellA22asyoucontinuefollowing

the steps below.

6. SelectcellE24andtypeRATE,thentabovertocellF24andtypeAVG.

7. SelectcellE26andtypeRATESRC,thentabovertocellF26andtype

RateCalc.

8. SelectcellE27andtypeINPUTCURRENCY,thentabovertocellF27

and type USD.

9. Select cell E28 and type TIME.

Note: CellsG24andG26displayas#NODATA.

272

第 291 页 / 共 377 页

BPC320 Lesson: Labs

10. SelectcellsE29:G31andchooseEdit→Clear→Contents.

11. EdittheformulaincellB25to=EVRNG(F24:F28).

12. SelectcellB36andtypeINPUTCURRENCY.

13. Select cell C36 and type RATE.

14. IncellC37,selectMEMBERSfromthedropdownlist.

15. SelectcellB31anddeleteAutoFitColsothecellisblank.

16.

Clickonthe ExpandalliconfromtheBPCforExceltoolbar.

17. Whenpromptedwiththe“Expandingcannotfinish…”message,selectthe

DisablethepromptnexttimecheckboxandclickYes.

18. Resize column E as needed.

19. SelectcolumnsG:Jandformatthemwithawidthof12andAlignLeft.

20. Verifythatyourscreenlookssimilartothefollowing:

Figure 379: Example 2

21 Select eTools → Save Dynamic Templates and save the report to the

eEXCEL→Reports→ManagerfolderontheserverasLab28–EVDRE

Multi Applications.

22. FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 29 – Expanding Across Sheets with EVDRE

Objective: Create a “book” of reports by utilizing SHEET in the ExpandIn

parameter.

273

第 292 页 / 共 377 页

Unit6: Labs BPC320

Description: The expansion has basically replicated the sheet defining the

expansion in several sheets (one sheet per expanded member), generating a

“book”ofreportsforthedesiredsetofmembers. Thegeneratedsheetswillbe

named after the member being expanded in the sheet. The page key defining

thepagememberofthesheetdimensionwillalsocontainthehardcodedIDof

thecurrentmember. Eachoftheresultingtabscontainsareplicaofthestarting

EVDREfunction,wheretheENTITYmemberspecifiedinthePAGEKEYisthe

(hard–coded)memberIDassociatedwiththecurrentsheet.

Notethatthefollowingrestrictionsapplywhentheexpansionisperformedacross

sheets:

• TheremustbeonlyoneEVDREfunctioninthesheetdefiningtheexpansion.

• Only one dimension can be expanded across sheets (no nested SHEET

expansions).

• TheBeforeRangeandtheAfterRangeparametersfortheSHEETexpansion

are ignored.

• TheInsertparameterfortheSHEETexpansionisignored.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

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

Password: training

1. Creating the “Book” of Reports

1. Ensureyourcurrentviewsettingsareasfollows:

Figure 380: Current View Settings

2. FromtheActionPane,clickReporting&Analysis.

3. FromtheActionPane,clickOpenablankworkbook.

4. SelectcellA1andtype=EVDRE()andpress[Enter].

5.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

6. CompletetheEVDREBuilderasshownbelow:

274

第 293 页 / 共 377 页

BPC320 Lesson: Labs

Figure 381: EvDRE Builder

7. In the EVDRE Builder, click OK.

8. Clickontheplus(+)signtodisplaythereportdefinitionarea.

9. In cell C16, type ALL,SELF.

10. In cell A29, type TIME.CALC=Y.

11. CopytheformulaincellA29downthroughA31.

12. Confirmthattheformattingsectionsinrow33-38werecopiedfromthe

Lab22template. \ifnotcopytheminmanually.

Figure 382: Formatting

13.

Clickonthe Expandallicontogeneratetheexpansionacrosssheets.

14. Verifythatyourscreenlookssimilartothefollowing:

275

第 294 页 / 共 377 页

Unit6: Labs BPC320

Figure 383: Example

15. Theworkbookhasmultipleworksheets,asshownbelow:

Figure 384: Worksheets

Note: Expansion#3isonlyfilledinonthefirstsheet.

16. Clickoneachworksheetinturnandnotethatthedataoneachworksheet

isspecifictothememberIDspecifiedintheworksheetnameandhard

coded in cell I6.

17. Select eTools → Save Dynamic Templates and save the report to the

eEXCEL → Reports → Manager folder on the server as Lab 29 – 3D

EVDRE Expansion.

18. FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 30 – Working with EVDRE Sorting Options

Objective: UtilizetheSortRangetableinareport.

276

第 295 页 / 共 377 页

BPC320 Lesson: Labs

Description: TheKeysrangeofanEVDREfunctionmaycontainanoptional

rangecalledSortRange,pointingtoarangeofcellsdefininghowtherowsofthe

reportshouldbesorted. Thisrangemustbemadeof4columnsand4rows. The

firstcolumnofsuchrangemustcontainthenameofthesortingparameterandthe

remaining3columnswilldefineupto3possiblesortingmethods.

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

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

Password: training

1. Enabling Sorting in the EVDRE Builder

1. SetyourCurrentViewEntitytoCONREGION.

2.

Clickonthe NewbuttonfromtheExceltoolbartocreateanewblank

worksheet.

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

4.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

5. In the EVDRE Builder, delete Lab 22 – EVDRE Formatting from the

ImportstylefromserverfieldandselecttheUsedefaultformatting

radio button.

6. VerifytheAllowformattingareaoftheEVDREBuildermatcheswhat

is shown below:

Figure 385: EvDRE Builder 1

7. DeselecttheAllowformattingcheckbox,andverifytheGridDesignarea

oftheEVDREBuildermatcheswhatisshownbelow:

Figure 386: EvDRE Builder 2

277

第 296 页 / 共 377 页

Unit6: Labs BPC320

8. ChangetheInrowsselectiontocompletetheEVDREBuilderasshown

below:

Figure 387: EvDRE Builder 3

9. In the EVDRE Builder, click OK.

10. Clickontheplus(+)signtodisplaythereportdefinitionarea.

11. EditcellB10toAutoFitCol,HideColKeys,HideRowKeys.

12. IncellB16, selectBASfromthedropdownlist.

13. IncellC16, selectBASfromthedropdownlist.

14. IncellD16,selectDEPfromthedropdownlist.

15. In cell C19, type Y.

2 UtilizingaPropertyintheSortRangeTable

1. In cell B24, type ENTITY.CURRENCY.

2. In cell B29, type Total %KEY%.

3. FormatcellsB29:C29asboldwithaSkyBluefillandaTopandDouble

Bottom border.

4. In cell D29, type EVSUB.

278

第 297 页 / 共 377 页

BPC320 Lesson: Labs

5. Format cell D29 as a number that uses a thousands separator and

displaystwodecimalplaces,whichisalsoboldwithaYellowfillanda

Top and Double Bottom border.

6. In cell B27, type =EVRNG(B29:D29).

7.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

Figure 388: Example 2

8. Select eTools → Save Dynamic Templates and save the report to the

eEXCEL→Reports→ManagerfolderontheserverasLab30–EVDRE

Sorting.

9. FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 31 – Utilizing EVDRE Drill Down and PageKey

Functionality

Objective: Utilize drill down functionality. Specify multiple members in the

PageKey to produce “on the fly” calculations.

Description: Drill down functionality is automatically enabled in EVDRE

reportsandinputschedules. YoucanenteroneormoremembersinaPageKey

rangecellusingacomma–separatedlist. Forexample,ifyouspecify“ENTITY:

STORE1,STORE5”inthePageKeyrange,allretrievedvaluesforthepagewill

containthesumofthevaluesforSTORE1andSTORE5. Thisallowsyourreport

tobemoredynamic, andpotentially, performfaster.

279

第 298 页 / 共 377 页

Unit6: Labs BPC320

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

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

Password: training

1. Utilizing Drill Down Functionality

1. SetyourCurrentViewTimePeriodto2008.TOTAL.

2.

Clickonthe NewbuttonfromtheExceltoolbartocreateanewblank

worksheet.

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

4.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

5. CompletetheEVDREBuilderasshownbelow:

Figure 389: EvDRE Builder

6. In the EVDRE Builder, click OK.

7. Clickontheplus(+)signtodisplaythereportdefinitionarea.

8. ChangethecurrentviewTIMEperiodto2008.TOTAL.

9. IncellB16, selectDEPfromthedropdownlist.

10. IncellC16,selectDEPfromthedropdownlist,setEntity=CONREGION

11.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

280

第 299 页 / 共 377 页

BPC320 Lesson: Labs

Figure 390: Example 1

Note: Values may vary.

12. DrilldownonPRETAXINCOMEbydouble–clickingontheAccount

ID or description.

13. Drill down on OPERATINGINCOME by double–clicking on the

Account ID or description.

14. Drill down on GROSSMARGIN by double–clicking on the Account

ID or description.

15. Drill down on 2008.Q1 by double-clicking on the Time Period ID or

description.

16.

DrilluptotheoriginalreportbyclickingontheGoback iconfrom

the BPC for Excel toolbar four times.

2 UtilizingthePageKeytoPerform“OntheFly”Calculations

1. In cell F6, type STORE1,STORE5.

Note: CellG6willdisplayas#NODATA.

2.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

281

第 300 页 / 共 377 页

Unit6: Labs BPC320

Figure 391: Example 2

Note: BecauseyouspecifiedbothSTORE1andSTORE5fortheEntity

dimensioninthePageKey, allretrievedvaluesforthepagecontainthe

sumofthevaluesforSTORE1andSTORE5.

3. Select eTools → Save Dynamic Templates and save the report to the

eEXCEL → Reports → Manager folder on the server as Lab 31 –

Multiple IDs in PageKey.

4. FromtheExcelFilemenu,selectClosetoclosethetemplate.

Lab 32 – Indenting Row Headers in an EVDRE Report

Objective: Create a report using EVDRE and add indentation 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.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

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

Password: training

1. Indenting Row Headers

1.

Clickonthe NewbuttonfromtheExceltoolbartocreateanewblank

worksheet.

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

3.

Clickonthe RefreshWorkbookiconfromtheBPCforExceltoolbar.

4. CompletetheEVDREBuilderasshownbelow:

282

第 301 页 / 共 377 页

BPC320 Lesson: Labs

Figure 392: EvDRE Builder

5. In the EVDRE Builder, click OK.

6. Clickontheplus(+)signtodisplaythereportdefinitionarea.

7. IncellC16,selectALLfromthedropdownlist.

8.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

Figure 393: Example 1

283

第 302 页 / 共 377 页

Unit6: Labs BPC320

9. SelectcolumnEandchooseInsert/Column.

10. Select cell E14.

11. FromtheActionPane, selectInsertfunction.

12.

From the Action Pane, select EVPRO and click the green check

button.

13. EntertheFunctionArgumentsasshowbelow:

Figure 394: Function Arguments

14. SelectOKtoinserttheformulaincellE14.

15. CopytheformulaincellE14downthroughcellE47.

16. Select cell G14.

17. Edittheformulabyenteringthefollowing:

=REPT(“ ”,$E14)&EVDES($F14)

18.

Clickonthe Expandalliconandverifythatyourscreenlookssimilar

to the following:

Figure 395: Example 2

19. Select eTools → Save Dynamic Templates and save the report to the

eEXCEL→Reports→ManagerfolderontheserverasLab32–EVDRE

Indented Accounts.

20. FromtheExcelFilemenu,selectClosetoclosethetemplate.

284

第 303 页 / 共 377 页

BPC320 Lesson: Labs

Lab 33 – Seeding the Budget

Objective: Seed the budget for 2009.

Description: It is now September 2008 and Pet Depot is about to begin the

planningprocessfor2009. Ratherthanstartthe2009planfromablankslate,the

companyhasdecidedthatusing2008datawillbeagoodstartingpoint. Usethe

DataManagertoexportofActualandBudgetdatafor2008andthenimportit

intotheBudgetcategoryfor2009witha2%increase. Createareporttoverify

your results

ThislabisperformedusingyourassignedPETDEPOT_##applicationset.

Note: Labs33–39correspondtoTrainingIISession4.

System Data

System: CITRIXCommonTrainingenvironment

Client: Start/Programs/Accessories/RemoteDesktopConnection

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

Password: training

1. Exporting2008ActualandBudgetDataUsingtheExportPackage

1. LaunchBPCforExcelandlogontoyourPETDEPOTapplicationset.

2. FromtheActionPane, selectManageData.

3. FromtheActionPane,selectRunadatamanagementpackage.

4. FromtheDataManagerdialogbox,selecttheExportpackageandthen

click the Run button.

5.

InthePleaseenteranoutputfilefield,clicktheSelect button.

6. IntheFileNamefield,typeFinanceActual2008.txtandthenclickthe

Save button.

7. LeavetheremainingfieldsasisandclicktheNextbutton.

8. FortheCategorydimension, selectACTUAL.

9. For the Time dimension, select month level members from January

through September 2008.

10. For the Entity dimension, select the base level members of STORE1

through STORE24.

11. For the Account dimension, filter to select only CALC=N members.

Refresh → Copy All Members

12. FortheDataSrcdimension, selectINPUT.

285

第 304 页 / 共 377 页

Unit6: Labs BPC320

13. FortheIntCodimension, selectNon_Interco.

14. FortheRptCurrencydimension, selectLC.

15. Verifythatyourdatarangelookssimilartothefollowing:

Figure 396: Data Range 1

16. ClicktheNextbuttonandthentheFinishbutton.

17. Whenprompted,“Therequesttorunthepackage…”clickOK.

18. FromtheDataManagerdialogbox,selecttheExportpackageandthen

click the Run button.

19.

InthePleaseenteranoutputfilefield,clicktheSelect button.

20. IntheFileNamefield,typeFinanceBudget2008.txtandthenclickthe

Save button.

21. LeavetheremainingfieldsasisandclicktheNextbutton.

22. FortheCategorydimension, selectBUDGET.

23. For the Time dimension, select month level members from October

through December 2008.

24. LeavetheEntity, Account, DataSrc, IntCo, andRptCurrencyselections

as is.

25. Verifythatyourdatarangelookssimilartothefollowing:

Figure 397: Data Range 2

286

第 305 页 / 共 377 页

BPC320 Lesson: Labs

26. ClicktheNextbuttonandthentheFinishbutton.

27. Whenprompted,“Therequesttorunthepackage…”clickOK.

28. FromtheDataManagerdialogbox,clicktheViewstatusbutton.

29. VerifythatyourPackageStatusdialogboxlookssimilartothefollowing:

Figure 398: Package Status 1

30. ClicktheClosebuttontoclosethePackageStatusdialogbox.

31. ClicktheCancelbuttontoclosetheDataManagerdialogbox.

2 Creating the Conversion Files

1. Select eData → New Conversion File.

2. Completeyournewconversionfileasshownbelow:

Figure 399: Conversion File 1

3. SelecteData→Validate&ProcessConversionFile

4. IntheFileNamefield,typeact_budandthenclicktheSavebutton.

If prompted, overwrite the existing file.

5. Whenprompted,“Theconversionfileupdatedsuccessfully”,clickOK.

6. Select eData → New Conversion File.

7. Completeyournewconversionfileasshownbelow:

Figure 400: Conversion File 2

287

第 306 页 / 共 377 页

Unit6: Labs BPC320

8. SelecteData→Validate&ProcessConversionFile.

9. IntheFileNamefield,type2008_2009andthenclicktheSavebutton.

If prompted, overwrite the existing file.

10. Whenprompted,“Theconversionfileupdatedsuccessfully”,clickOK.

11. Select eData → New Conversion File.

12. Completeyournewconversionfileasshownbelow:

Figure 401: Conversion File 3

13. SelecteData→Validate&ProcessConversionFile.

14. IntheFileNamefield,typeIncreaseandthenclicktheSavebutton.

15. Whenprompted,“Theconversionfileupdatedsuccessfully”,clickOK.

16. Close all 3 conversion files.

3. Creating the Transformation Files

1. SelecteData→NewTransformationFile.

2. CompletetheOPTIONSsectionofyournewtransformationfileasshown

below:

Figure 402: Options 1

Note: You will need to insert a row.

3. Complete the CONVERSION section of your new transformation file

as shown below:

Figure 403: Conversion 1

288

第 307 页 / 共 377 页

BPC320 Lesson: Labs

4. SelecteData→Validate&ProcessTransformationFile.

5. FromtheValidate&ProcessTransformationdialogbox,clicktheSave

As button.

6. In the File Name field, type Act to Bud with Increase and then click

the Save button.

7. WhentheValidateStatusdialogboxdisplaysas“Thistaskhascompleted

successfully”, click Cancel.

8. SelecteData→NewTransformationFile.

9. CompletetheOPTIONSsectionofyournewtransformationfileasshown

below:

Figure 404: Options 2

Note: You will need to insert a row.

10. Complete the CONVERSION section of your new transformation file

as shown below:

Figure 405: Conversion 2

11. SelecteData→Validate&ProcessTransformationFile.

12. FromtheValidate&ProcessTransformationdialogbox,clicktheSave

As button.

13. In the File Name field, type Bud to Bud with Increase and then click

the Save button.

14. WhentheValidateStatusdialogboxdisplaysas“Thistaskhascompleted

successfully”, click Cancel.

15. Close both transformation files.

289