Applying a Default Dynamic Formatting Template to a Report
Exercise 6: Apply Dynamic Formatting
Exercise Objectives
After completing this exercise, you will be able to:
• You want to format your reports to be more user friendly.
Business Example
Users are requesting that you apply formats for different levels of a hierarchy.
Additionally, for the Input forms, they would like to know which cells can be planned and which cells have been change.
Task 1:
Create a report and apply dynamic formatting.
1. Go to the Excel client for your Sales model in your BPC430_## environment.
Note: If you do not have a connection for the Sales model use the instructions in Exercise #2 to create it.
Note: If you are already in Excel, go to Step #2.
2. Create a new blank workbook.
3. On the EPM - Pane select the Sales - BPC430_## connection.
4. Set your EPM Context as you see below:
Dimension Member ID - Description
CATEGORY Budget - Current Budget
ENTITY STORE1 - Store1
PRODUCT CON_PET_TYPE - Pet Type Consolidation
P_ACCT CE0004010 - Personnel Expenses
RPTCURRENCY LC - Local Currency
TIME 2012.TOTAL - 2012
MEASURES PERIODIC - Periodic
Continued on next page
5. Use the EPM Pane, to create a report with Time on the row axis and Product on the Column Axis.
6. Switch your report to an input form.
7. Change the members for the Time Dimension to Months, Quarters, and Year.
8. Change the members for the PRODUCT Dimension to only display IDs.
9. Apply Formatting to the Hierarchy Levels as shown in the screenshots below:
Figure 107: Hierarchy Level Formatting
Figure 108: Row Formatting
10. Turn on the Apply Dynamic Formatting option.
11. View the formatting results.
Task 2:
Now, we want to add some formatting to let our planners know which cells can be planned, and which cells have been changed.
1. Go to the EPMFormattingSheet.
2. Apply formatting for the Dimension Member/Property Formatting section as shown in the screenshot.
Figure 109: Dimension Member Property Formatting 3. View the formatting results.
4. Save your report to the Server Root Folder.
Solution 6: Apply Dynamic Formatting
Task 1:
Create a report and apply dynamic formatting.
1. Go to the Excel client for your Sales model in your BPC430_## environment.
Note: If you do not have a connection for the Sales model use the instructions in Exercise #2 to create it.
Note: If you are already in Excel, go to Step #2.
a) From your web client's Start Page, choose EPM Office Add-in Excel.
Excel opens and the EPM - Connection Manager appears.
b) Choose the Sales - BPC430_## connection.
c) Click OK.
d) If you are prompted, log on again with your user ID and password as shown below:
User ID Password
BPC-## training
2. Create a new blank workbook.
a) Create a blank workbook by choosing the Office button and choose New.
b) Choose Create.
3. On the EPM - Pane select the Sales - BPC430_## connection.
a) On the EPM -Pane, select the Sales - BPC430_## connection from the dropdown, if not already selected.
4. Set your EPM Context as you see below:
Dimension Member ID - Description
CATEGORY Budget - Current Budget
ENTITY STORE1 - Store1
PRODUCT CON_PET_TYPE - Pet Type Consolidation
P_ACCT CE0004010 - Personnel Expenses
RPTCURRENCY LC - Local Currency
TIME 2012.TOTAL - 2012
MEASURES PERIODIC - Periodic
a) Set your EPM Context as you see above:
5. Use the EPM Pane, to create a report with Time on the row axis and Product on the Column Axis.
a) From the EPM Pane, right-click TIME and select Move to Row Axis.
b) From the EPM Pane, right click on PRODUCT and select Move to Column Axis.
A report is created.
6. Switch your report to an input form.
a) From the EPM Ribbon, go to Options → Sheet Options.
The EPM - Sheet Options window opens.
b) On the General tab, under Data Input, select the checkbox next to Use as Input Form.
c) Choose OK.
7. Change the members for the Time Dimension to Months, Quarters, and Year.
a) On the EPM Pane, in the Row Axis , click TIME.
The EPM - Member Selector window opens.
b) Move the Selected Members Context 2012.TOTAL - 2012 with the Relationship Member and Children to the left.
c) Select 2012.TOTAL - 2012 by placing a checkbox to the left and, on the bottom left, select the Selection Relationship to Member and Descendants. Move this selection to the right under Selected Members by using the arrow.
d) Choose the Member Display Selector and choose ID . Choose OK.
Continued on next page
8. Change the members for the PRODUCT Dimension to only display IDs.
a) Call the EPM - Member Selector by clicking on the PRODUCT dimension in the EPM Pane Column Axis..
The EPM - Member Selector window opens.
b) Choose the Member Display Selector and choose ID.
c) Choose OK.
The report is updated with these selections.
9. Apply Formatting to the Hierarchy Levels as shown in the screenshots below:
Figure 110: Hierarchy Level Formatting
Figure 111: Row Formatting
a) From the EPM Ribbon choose View Formats.
The EPMFormattingSheet opens.
Hint: If the sheet does not open, select it from the available sheets in your workbook. It will be the first sheet.
b) In the Hierarchy Level Formatting section, note that Hierarchy Level Formatting is already selected in cell H5.
c) At the top of the section, choose the radio buttons for Priority to Row Format and Structure Levels in D6.
d) In the Row Section, select Apply to the left of Formatting on Specific Level around cell C37 and C38.
e) Also, in the Row Section, click in the cell under the Data column for Level 1 in F39.
You have selected the cell to apply formats to the rows containing the total for the year which is the 1st level of the hierarchy.
f) Right-click on cell F39.
The palette opens to change the format.
g) Choose a fill color of your choice and set that to Bold.
h) Also, in the Row Section, click in the cell under the Data column for Level 2 in F42.
You have selected the cell to apply formats to the rows for the 2nd level of the hierarchy.
i) Right-click on that cell.
The palette opens to change the format.
j) Choose a fill color of your choice.
Continued on next page
10. Turn on the Apply Dynamic Formatting option.
a) Click on Sheet 1.
b) From the EPM ribbon, choose Options → Sheet Options.
c) Choose the Formatting tab.
d) Select Apply Dynamic Formatting and confirm that the
EPMFormattingSheet is the Default Formatting Sheet. Then choose OK.
Note: If you cannot select Apply Dynamic Formatting, choose OK and choose → View Formats to display the formatting template.
Choose View Formats again to close it.
Choose Options → Sheet Options → Formatting and select Apply Dynamic Formatting and confirm that the EPMFormattingSheet is the Default Formatting Sheet and choose OK.
11. View the formatting results.
a) From the EPM Ribbon, choose Refresh.
You should see your formats!
Figure 112: Hierarchy Level Format Results
Note: You may have noticed we did not deselect the Apply checkbox to the left of Default formats, but that is OK, since our Formatting on the Levels carries precedence over the default formats.
Task 2:
Now, we want to add some formatting to let our planners know which cells can be planned, and which cells have been changed.
1. Go to the EPMFormattingSheet.
a) Choose the EPMFormattingSheet.
Note: If you cannot see the EPMFormattingSheet choose View Formats from the EPM Ribbon.
Continued on next page
2. Apply formatting for the Dimension Member/Property Formatting section as shown in the screenshot.
Figure 113: Dimension Member Property Formatting
a) Scroll down to the Dimension Member/Property Formatting section, and select the checkbox at the top of the section in J52.
This activates Dimension Member/Property Formatting.
b) At the top of the section, choose the radio button for Priority to Row Format in D53.
c) In the Row Section, select the Apply checkbox to the left of Inputable Member Default Format in C84.
d) Select Changed Member Default Format in C90.
e) Place your cursor on the cell F84.
You can now apply formats to the rows containing cells that are available for input.
f) Right-click on that cell.
The palette opens to change the format.
g) Choose a fill color of your choice and change the number to have commas, and no decimals.
h) Also in the Row Section, place your cursor in F90.
You can now apply formats to the cells that have been changed.
i) Right-click on that cell.
The palette opens to change the format.
Continued on next page
3. View the formatting results.
a) Go to Sheet1 and choose Refresh.
b) Double-click BIRD to expand to the base level members.
You should see your formats for the inputable cells, and also the hierarchy cells.
c) Type 125 into B2.
You should see the new format.
Figure 114: Inputable and Changed Formats
Note: Again, the precedence rules apply. Originally, the cell had the inputable format. Then, once the cell was changed, it took on the changed format, since that format was lower in the formatting sheet.
4. Save your report to the Server Root Folder.
a) From the EPM Ribbon, choose Save → Save to Server Root Folder.
The Save window will appear.
b) Stay in the TEMPLATELIBRARY folder and name your report GR##
Formatting.
Lesson Summary
You should now be able to:
• Work with the formatting style sheet to create more attractive reports.