BDM Hyperion Pivot Basics Contents of this Guide About Pivots Pivot Menu Command Reference Insert Menu Command Reference – Pivot Pivots The Parts Creating a Pivot Table Adding Data Labels Adding Corner Labels Selecting Columns, Rows & Labels (Titles) Moving Pivot Elements Removing Pivot Elements Sorting Pivot Tables Hiding Pivot Facts Restoring Hidden Pivot Facts Hiding Pivot Dimensions (Row/Column Label) Restoring Hidden Pivot Dimensions (Row/Column Label) Focusing on Pivot Dimensions (Row/Column Label) Restoring not Focused on Pivot Dimensions (Row/Column Label) Drilling Down into Dimensional Data Drill down into a Pivot report Practice Drill Down session Drill up from data Surface Values & Underlying Values Adding Totals & Subtotals Pivot Total Exercise Removing Totals Pivoting Total Data – Pivot Handles Adding Cumulative Totals Using Data Functions in a Pivot Pivot Data Function Surface Values & Underlying Values Data Functions applied to Underlying Values Data Functions applied to Surface Values Formatting a Pivot Report Formatting Pivots Font Style Numbers Justify Borders Spotlighter Adding a Header & Footer
BDM Hyperion Pivot Guide 1/25/2010
Page 2 2 3
4 5 5 6 6 7 8 8 9 9 9 10 11 12 13 13 14 16 16 16 18 19 19 21 23 24 25 26
27 27 28 28 28 28 29 31
Page 0
BDM Hyperion Pivot Basics
BDM Hyperion Pivot Guide 1/25/2010
Page 1
About Pivots The Pivot section enables you to extract meaningful information from the query Results section. You can use the Pivot section to rotate or pivot rows and columns to see different summaries of data or display the details for areas of interest. You also can automatically include subtotals and grand totals, or use your own formulas by adding computed items. Pivot reports are analytical tools that resemble spreadsheets. A Pivot report allows data to be sliced and diced for ad hoc, interactive and multidimensional analysis. This is also known as drill down. The Pivot section quickly performs the most sophisticated computations and ad-hoc analyses you can use on your data. You can pivot your data at any time to change the way you view it, or you may drill down to see how it all adds up or summarizes. Pivot reports also allow you to add, move, rename, focus on and group information to customize views of the data. You can use these tools to present data in different context and with heightened emphasis.
Pivot Menu Command Reference
(modified list)
The following table provides a quick reference to the commands available on the Pivot menu and lists any related shortcuts. Command
Description
Keyboard Shortcut
Shortcut Menu
Add Selected Items
Adds the selected item as a Column Label, Row Label, or Fact.
Remove Selected Items
Removes the selected item.
Del
*
Modify
Modifies the selected computed item.
Ctrl+M
*
BDM Hyperion Pivot Guide 1/25/2010
*
Page 2
Command
Description
Keyboard Shortcut
Shortcut Menu
Sort
Reorders the selected item by labels, by values, ascending, or descending.
Add Totals
Adds the selected item to the Measures pane.
*
Add Computed Item
Adds a new data item derived from calculations performed on an item.
*
Add Cume
Adds cumulative totals to break totals by dimension and restarts them at each dimensional grouping.
*
Data Function
Applies a prebuilt data function to the selected item.
*
Drill Anywhere
Enables you to drill to any item.
*
Drillup
Returns the original view of data that you drilled.
*
Focus On Items Updates the pivot table to include only the selected data.
*
Hide Items
Hides the selected item.
*
Show Hidden Items
Restores the selected hidden item.
*
Show All Items Updates the pivot table to include all items. Group Items
*
Groups the selected dimensions.
Ctrl+G
Ungroup Items Ungroups the selected dimension.
Ctrl+U
Restore Name
Restores the original name of a renamed item.
Refresh Data
Updates the data according to the selected option. Select between After Process, When Section Displayed, Manually, or Refresh Now.
Pivot Options
Enable surface values, true computed totals, and null facts in computed items.
Insert Menu Command Reference – Pivot
*
(modified list)
The following table provides a quick reference to the commands available on the Insert dropdown menu. The command listed is available in the Query and Results sections. Command New Pivot
Description Inserts a new Pivot section.
Chart This Pivot Generate a chart from the current pivot. This command is only available in the Pivot section.
….
BDM Hyperion Pivot Guide 1/25/2010
Page 3
Pivot tables are made up of Row Labels, Column Labels and Facts. Rows and Columns are often referred to as Dimensions. Facts are your numbers. • •
Dimensions (Labels)—. In the Pivot section, dimensions are either Row Labels or Column Labels. They are descriptive elements that break aggregate data (facts) into logical categories. Facts—Core numeric data that you slice and dice dimensionally in your analysis.
For example, if you analyze Budget Balance Available by Accounts. The Budget Balance Available (numbers) are your Facts (data values) and Accounts is a dimension (label). Presented in aggregate, facts are subdivided by your chosen dimension labels.
The Parts 1. 5.
Sort Catalog Pane
2. 6.
Data Layout Row Labels
3. 7.
Contents Pane Column Labels
4. 8.
Section Pane Facts
1
2
1
4 3
5
6
BDM Hyperion Pivot Guide 1/25/2010
7
8
Page 4
Creating a Pivot table Select Insert > New Pivot If the data layout is not visible, click Data Layout on the Section title bar Add Selected Items Adds the selected item as a Column Label, Row Label, or Fact.
Perform one of the following actions: Drag & Drop each Request item to be included in the pivot table from the Catalog pane to a pane in the data layout (Column Labels, Row Labels, or Facts). Or In the Catalog pane, select one of more Request items and select Pivot > Add Selected Items > Column Labels, Row Labels or Facts.
Or Tip: Add Request items such as Encumbrance or Budget Balance Available to the Facts pane to create the data grid. Add dimensions such as Account Number or Subcode to the Column and Row Labels panes to create subdivisions. Tip: Items are hierarchically ordered in the sequence (left to right) in which they are displayed in the data layout pane.
Adding Data Labels In the actual pivot you can add the element’s name(s) that are included in the Facts pane of the Data Layout. The default setting is Column. You can choose None, Column, Row.
BDM Hyperion Pivot Guide 1/25/2010
Page 5
Adding Corner Labels. In the actual pivot you can add the element’s name(s) that are included in the Row Labels and Column Labels panes of the Data Layout. The default setting is None. You can choose None, Column, Row or Both.
Data and Corner Label Example In the following example the settings are: Data Labels Column (Budget Bal Avail) Corner Labels Both (Account Num and Unit Description)
Selecting Columns, Rows & Labels (Titles) 1. Select a Fact column - Click anywhere inside the column. Do not click the label.
BDM Hyperion Pivot Guide 1/25/2010
Page 6
2. Select a Row Labels or Column Labels column (dimension) – Click the top cell of the column or the first cell of the row with the grey line through it.
3. Select an individual - Column Label or Row Label, select the label itself.
4. Select more than one - rows or columns, press the Alt key (Windows) or Ctrl+Alt (other). Then select the row or column labels.
Moving Pivot Elements To move a Column in the Content Pane, Select the column and Drag & Drop it to a new position. The Data Layout updates to reflect the repositioning or reassignment of the item.
Using the Data Layout to move a pivot element, Select the element name, Drag & Drop the element to a new position or to another data layout pane. Tip: To move elements between Column Labels or Row Labels panes and the Fact pane (or vice versa), you must first remove them from the data layout, and then add them again to the chosen pane.
BDM Hyperion Pivot Guide 1/25/2010
Page 7
Removing Pivot Elements Remove Selected Items Removes the selected item. Del
Columns from the Content Pane, Select the column, drop down menu Pivot > Remove Selected Item Columns from the Data Layout, Select the element, drop down menu Pivot > Remove Selected Item or Columns from the Data Layout Select the element Right Click > Remove or Columns from the Data Layout Select the element , Click the delete button Note: When you delete a column, you cannot use the Undo feature to reinsert the column.
Sorting Pivot Tables Sort Reorders the selected item by labels, by values, ascending, or descending.
Sorting facts or dimensions enables you to display objects in ascending and descending order according to value. Sorting Icons - ascending/descending icons Tip: By default Interactive Reporting plots data in ascending order. To sort in descending order, click the descending icon.
Pivot dropdown menu -
Pivot Sort Bar To sort plotted values and labels: If the Sort line is not visible, click Sort on the section title bar In the Sort list, select an item to use as the basis of your sort
In the by drop-down list box, click the sort type (either a label or fact value). BDM Hyperion Pivot Guide 1/25/2010
Page 8
In the using drop-down list box, select the method of calculation for a data value.
Hiding Pivot Facts Hide Items Hides the selected item.
You can restrict the data displayed in Pivot fact columns using the Hidden Item feature. When you select this feature, the fact column is removed from the report, but not the data layout. You can restore the hidden data item at any time. In the Facts pane, Select one or more fact column names, Right Click > Hidden Item The Fact column name is dimmed
Restoring Hidden Pivot Facts Show Hidden Items Restores the selected hidden item. Show All Items
Updates the pivot table to include all items.
To restore a hidden fact column, select the dimmed column in the Facts data layout pane , Right click > Show Hidden Item
Hiding Pivot Dimensions (Row/Column Label) In a Row Label column, Select the cell(s) you want to hide, Right click > Hide Items Tip: Use the Shift or Ctrl key to select multiple cells
BDM Hyperion Pivot Guide 1/25/2010
Page 9
or In a Row Label column, Select the cell(s) you want to hide, from the drop down menu Select Pivot > Hide Items
Note the Row Labels pane displays a drill bit in front of the element you have hidden
Restoring Hidden Pivot Dimensions (Row/Column Label) Show Hidden Items Restores the selected hidden item. Show All Items
Updates the pivot table to include all items.
In the Row Label column of the hidden cell, Select any cell, from the drop down menu Select Pivot >Show Hidden Items (or Show All Items)
BDM Hyperion Pivot Guide 1/25/2010
Page 10
or In the Row Labels pane, Select the hidden element name, Right Click > Show Hidden Item
Focusing on Pivot Dimensions (Row/Column Label) Focus On Items Updates the pivot table to include only the selected data.
*
In a Row Label column, Select the cell(s) you want to focus on, Right click > Focus On Items or
Use the Pivot drop down menu, Pivot > Focus On Items
or
BDM Hyperion Pivot Guide 1/25/2010
Page 11
The follow is an example of the Tran Code 022 that has been focused on.
Interactive Reporting updates the report to focus on the data. A drill icon is displayed in the data layout next to the column you selected. This allows you to progressively narrow your focus on selected items.
Restoring not Focused On Pivot Dimensions (Row/Column Label) This is how you restore all the data that was not focused on Show Hidden Items Restores the selected hidden item. Show All Items
Updates the pivot table to include all items.
In the Row Label column of the focused on cell, Select any cell, from the drop down menu Select Pivot >Show Hidden Items (or Show All Items)
or BDM Hyperion Pivot Guide 1/25/2010
Page 12
In the Row Labels pane, Select the focused element name, Right Click > Show Hidden Item
Drilling Down into Dimensional Data Drill Anywhere Enables you to drill to any item.
*
Drillup
*
Returns the original view of data that you drilled.
The drill into feature enables you to drill into items in the Pivot section that are in the Results section without having to return to reprocess your query. Drilled into items are automatically added as new row label items in the Data Layout. The advantage of this feature is that it instantly enables you to add items to reflect temporary or hypothetical situations. You can always suspend or delete the item to return to the original Pivot display. The extent to which you can drill into your data depends on how the original query was built, since Drill Into retrieves data from the Results section. This feature does not enable you to query the data warehouse. Tip: Only dimensions (Row and Column Labels) can be drilled into. To Drill Anywhere into a Pivot: Select one or more items for analysis Select Pivot > Drill Anywhere > Select the data element Interactive Reporting recreates the pivot drilled to the selected data element. In the data layout, the data element selected for drill-down is identified with a drill-bit icon in the Pivot Data Layout. The data element you choose to drill down into is added to the Pivot Data Layout
Drill down into a Pivot report 1. 2. 3. 4.
Select a label for analysis On the shortcut menu, click Drilldown into The Drilldown submenu appears and displays the next item into which you can drill Click the drill down item with which you want to work with
See the following example of before drilling down into Account Descr Play Money data cell:
BDM Hyperion Pivot Guide 1/25/2010
Page 13
See the following example of after drilling down into the Account Desc Play Money data cell and choosing the Subcode Agg data element
Practice Drill Down session Using the Pivot (YTD data) document (Found in BDM Hyperion Training Documents folder) 1. Add Subcode, Tran Code, and Tran Dt as row labels (in that order) 2. Add Tran Amt to the Facts pane 3. Add Corner Labels Both 4. The Pivot and Data Layout should be similar to the following:
BDM Hyperion Pivot Guide 1/25/2010
Page 14
5. Drill down into a Tran Code (for example: 022 One Time Budget Allocation) and select Ref 6. The Pivot and Data Layout should be similar to the following:
BDM Hyperion Pivot Guide 1/25/2010
Page 15
Drill up from data 1. Click the item that you previously drill-downed into (Ref) 2. On the Pivot menu, click Drill Up or 3. Use the shortcut menu by clicking the right mouse button and select Drill Up Hyperion redisplays the report, reversing the drill-down and displaying the original pivot.
Surface Values & Underlying Values Hyperion allows you to use underlying or surface values when working with totals in Pivot sections. The two approaches yield different results and produce values that may appear inconsistent with the values in the Pivot report. • Underlying values refer to values from the original Results (or table) section. This is the Default setting. • Surface values refers to values in the actual Pivot report section Important Concept: When using Surface values the values in the visible cells of the pivot are recalculated. The values in the Results section are not used or affected.
To understand this difference between “underlying” and “surface” values, consider • A simple Pivot report with two values of 20 and 30 • Each of these is already a total of underlying values o 20 = 8 +12 o 30 = 10 + 20 • An average of the underlying value 8, 12, 10, 20 [(8 + 12 + 10 + 20) /4] o Is 12.5 • An average of the surface values 20, 30 [(20 +30) / 2)] o Is 25
Adding Totals & Subtotals Add Totals Adds the selected item to the Measures pane.
You can calculate totals for both columns and rows in a pivot table. If you layered dimension items along the column or side of your pivot table, you can calculate totals for any layer in the hierarchy. When you select inner dimensions for totaling, subtotals are created for each of the categories in the outer dimensions. BDM Hyperion Pivot Guide 1/25/2010
Page 16
Tip: An intelligent aggregate is applied to the specified data when totaling unless you specify otherwise. For example, the total of a column of averages calculates an average rather than a sum total. The following examples will help you understand pivot tables. In each case you will be selecting a column and applying a total. Applying a Grand Total to the pivot: Example 1: Grand Total Select the Dept Description column label/dimension in your pivot; Click the top cell of the column or the first cell of the row with the grey line through it.
Right mouse click, Select Add Totals
Example 2 Select the Fiscal/Project column label/dimension in your pivot; Click the top cell of the column or the first cell of the row with the grey line through it.
Right mouse click, Select Add Totals
Or Click the Total icon
on the standard toolbar.
Example 3 Subtotal Select the Account Num column label/dimension in your pivot; Click the top cell of the column or the first cell of the row with the grey line through it.
Right mouse click, Select Add Totals BDM Hyperion Pivot Guide 1/25/2010
Page 17
or Click the Total icon
on the standard toolbar.
Tip: In the examples above, there are no Column Labels only Row Labels. In the following exercise, you will build a pivot with both.
Pivot Total Exercise In this exercise you will create a pivot and add a total to a row and column label/dimension in the pivot. 1. You will be using the Monthly Expenses & Revenues document. 2. Filtering as follows: a. Choose an account b. Fiscal year = 10 c. Load Date = 07/01/09 d. Proc Month Name = July, August, September and October (the first quarter) e. Subcode = 2130 (General Repairs) and 3140 (Office Supplies) 3. Insert new Pivot
4. View Section/Catalog
5. 6. 7. 8.
Row Labels – Sl Subcode Desc Column Labels – Month Name Facts - Tran Amt Add labels to the Row and Column dimensions; a. Anywhere in the Content Pane Right mouse click, Select Corner Labels, Select Both
Tip: There must be a Fact element 9. Set a row/dimension total on Month Name 10. Set a column/dimension total on Sl Subcode Desc
BDM Hyperion Pivot Guide 1/25/2010
Page 18
Removing Totals To remove totals, click on a total label and press the Delete key.
Pivoting Total Data - Pivot Handles Use the Pivot feature to reorient the axes of a pivot table and view your data in new ways. Pivoting a table enables you to more easily compare the new data to data in the originating table, which makes pivot tables more powerful than common spreadsheets. By default, pivot handles are transparent. They only appear when you select a label from a top or side. When the pivot handle appears, it is shown in white with a light grey handle ( ). Once the selection is off, the pivot handle is transparent again
BDM Hyperion Pivot Guide 1/25/2010
Page 19
To pivot data in a table, Select a dimension handle and drag it to any position on the same or opposite dimensional axis. Pivot Handle Exercise To move a column and row dimensions total using the Pivot you created in the previous exercise: 1. Select the Month Name row and note the handle at the end of the row
2. Click inside the handle 3. Drag/pivot the row and Drop it after Sl Subcode Desc 4. Note: a. Month Name totals b. Data Layout changed and Month Name is now found in the Row Labels pane
BDM Hyperion Pivot Guide 1/25/2010
Page 20
Adding Cumulative Totals Add Cume
Adds cumulative totals to break totals by dimension and restarts them at each dimensional grouping.
Cumes work best when all dimensions are located at the column or side of the pivot table, and data label column heads are placed orthogonally (right angles, side-by-side). To add a cumulative total: 1. Select a fact in the data grid of the pivot table. 2. Select Pivot > Add Cume.
3. If desired, type a new name for the Pivot Cume. 4. Select the scope of the Pivot Cume from the drop-down list box. The “Scope:” drop-down list box includes all of the dimensions in the pivot table. The default scope is the lowest level dimension that is displayed in the pivot table. 5. Click OK. A new fact column is added that maintains a cumulative running total of the original fact by the dimension (scope) specified. The following two examples show the relationship of the dimensional (Row label) element order and the Cume of the “Actual” data element (Fact). BDM Hyperion Pivot Guide 1/25/2010
Page 21
BDM Hyperion Pivot Guide 1/25/2010
Page 22
Using Data Functions in a Pivot A data function enables you to change the nature of the values displayed in a pivot table and enables you to decide the kind of value represented in a pivot table. When you use a data function, Interactive Reporting recalculates the selected values according to the function applied to the underlying data values or the surface values. Tip: In a Pivot, the default value setting is Underlying Data Values. This is values found in the Result section. Data functions are particularly useful if you want your Pivot report to display different types of values side by side. For example, you can show a total, average and maximum of an element. Each of these dimensions is based on the same underlying values or surface values. They differ only in the data function that is applied. By dragging the same numeric data item (such as “Actual”) from the Catalog pane to the Data Layout several times, you can apply a different data function to the very same dimension. Each dimension refers to the same basic underlying values or surface values.
To apply Data Functions 1. Drag a numeric data item from the Catalog pane to the Facts pane of the Data Layout 2. Drag dimension labels into the Top and Side label panels of the Data Layout 3. Again drag the same numeric data item from the Catalog pane to the Fact pane of the Data Layout You may repeat this procedure several times. When you drag the same numeric data item to the Facts panel, the name changes to indicate how many times that item is in the Data Layout (such as Actual2, Actual3). 4. Select a column of facts (such as Actual2) 5. On the shortcut menu or Pivot drop down menu, click Data Function and select a function such as Average
or 6. If desired, toggle on Surface Values on the Pivot menu 7. If desired, change the label of the new column or row Pivot Data Functions Function Sum
Description Returns sum of all values. This is the default setting.
BDM Hyperion Pivot Guide 1/25/2010
Page 23
Function
Description
Average
Returns average of all values.
Count
Returns number of values.
Maximum
Returns highest value.
Minimum
Returns lowest value.
% of Column
Returns surface values as a percentage of their respective column item.
% of Row
Returns surface values as a percentage of their respective row item.
% of Grand
Returns surface values as a percentage of all like values in the pivot table.
Non-Null Average Returns average of values; null values excluded. Null Count
Returns number of null values.
Non-Null Count
Returns number of values; null values excluded.
Tip: Null values are empty values for which no data exists. Null values are not equal to zero.
Pivot Data Function Surface Values & Underlying Values Column or row totals added to your Pivot report are aggregates (literally, totals of totals) and can be recalculated using data functions. When applied to totals, data functions can either apply to “surface” or “underlying values”. The following table shows which values are used for a selected Data Function when using Underlying or Surface Values. ** Please note that % of Column, % of Row and % of Grand will always use the Surface value of the Pivot Data Function
Description
Underlying Values Enabled (Default)
Surface Values Enabled
Sum
Returns sum of all values. This is the default setting.
X
X
Average
Returns average of all values.
X
X
Count
Returns number of values.
X
X
Maximum
Returns highest value.
X
X
Minimum
Returns lowest value.
X
X
% of Column
Returns surface values as a percentage of their respective column item.
**
X
% of Row
Returns surface values as a percentage of their respective row item.
**
X
% of Grand
Returns surface values as a percentage of all like values in the pivot table.
**
X
Non-Null Average Returns average of values; null values excluded.
X
X
Null Count
Returns number of null values.
X
X
Non-Null Count
Returns number of values; null values excluded.
X
X
The following two examples use the same elements in the Data Layout. One pivot is using Underlying Values and the other is using Surface Values.
BDM Hyperion Pivot Guide 1/25/2010
Page 24
Data Functions applied to Underlying Values When applied to underlying values, data functions return to the unaggregated Results section values and are calculate based on those values. When “underlying values” are used, the results often appear inconsistent with the aggregate “surface values”. In other words, a total of the underlying values do not match the total of the surface figures.
Important Concept: When setting data functions to underlying values (the default setting) you must remember the following: When applying the Average Data Function to a Fact element in the Pivot: The Average value is calculated using the values of the Pivot row label (dimension) This is the sum of all of the values of the Pivot row label in the Results section (used to create the Pivot) The sum is divided by the number of row in the underlying Result section. When applying the Count Data Function to a Fact element in the Pivot: The Count value is a sum Summing occurrences (instances) of the Fact element values For the underlying Result or Table section Row Label values When Applying the Maximum Data Function to a Fact Element in the Pivot: The Maximum value is the highest value Of all the Fact occurrences For the underlying Result or Table section Row Label values When Applying the Minimum Data Function to a Fact Element in the Pivot: The Minimum value is the lowest value Of all the Fact occurrences For the underlying Result or Table section Row Label values When Applying the % of Column Data Function to a Fact Element in the Pivot: The surface values are used Calculating the percent of the chosen Fact Value
BDM Hyperion Pivot Guide 1/25/2010
Page 25
Using Underlying Values example
Data Functions applied to Surface Values When applied to surface values, data functions recalculate the values in the visible cells or “surface” of the Pivot report rather than the values in the Results section. For example, if you apply a surface average to a total, the total will be converted to the average of the surface values in the corresponding row or column of the Pivot report not the Results section values. Using Surface Values example
BDM Hyperion Pivot Guide 1/25/2010
Page 26
Formatting Pivots Drop Down menu
Speed menu (right click)
The following table lists common formatting techniques that you can use when working with pivot tables. Format Description Font
To modify the font of an item in a pivot table, select the item and click Font on the Format menu or shortcut menu. When the Font tab of the Properties dialog box is displayed, apply any desired changes.
Style
To modify the appearance of an item in a pivot table, select the item and click Style on the Format menu or shortcut menu, and select the style. Styles include: bold, underline, italic formats, and so on.
Number To modify the way numbers, dates, and currency are displayed in a pivot table, select the item and click Number on the Format menu or shortcut menu. When the Number tab of the Properties dialog box is displayed, apply any desired changes. Justify
To modify the way an item is justified within a row or column in a pivot table, select an item, click Justify on the Format or shortcut menu. Select the type of justification.
Font Font To modify the font of an item in a pivot table, select the item and click Font on the Format menu or shortcut menu. When the Font tab of the Properties dialog box is displayed, apply any desired changes.
BDM Hyperion Pivot Guide 1/25/2010
Page 27
Style Style To modify the appearance of an item in a pivot table, select the item and click Style on the Format menu or shortcut menu, and select the style. Styles include: bold, underline, italic formats, and so on.
Numbers Number To modify the way numbers, dates, and currency are displayed in a pivot table, select the item and click Number on the Format menu or shortcut menu. When the Number tab of the Properties dialog box is displayed, apply any desired changes.
Justify Justify To modify the way an item is justified within a row or column in a pivot table, select an item, click Justify on the Format or shortcut menu. Select the type of justification.
Borders
BDM Hyperion Pivot Guide 1/25/2010
Page 28
Spotlighter
Use the Spotlighter to emphasize report elements so that they stand out by applying color and font styles to selected labels. Spotlighter formats are applied conditionally by building an equation and applying the constraint to your report. To spotlight exceptional values: Select an element to spotlight Select Format > Spotlighter Build a conditional format in the Spotlighter: o Select a comparison operator from the Operator list o 14Enter a comparison value in the Value field Add formatting or colors to spotlight the data by clicking the format toolbar color controls and font controls. The sample text in the dialog box previews your spotlighter formatting.
Select to apply the spotlight format to the selected report element. The condition is displayed in the format editor with the spotlighter format applied.
Repeat steps 1–5 to specify additional conditions within the same selection. You can apply multiple conditions to a selection.
For example, you could set values over 5,000 within an item to display red, and those over 10,000 to display bold and green. To modify Spotlighter formats Select a spotlighted report element to modify. Select Format. Spotlighter. Double-click the spotlighter format to be modified in the Format editor. All conditional formats applied to the selection are captured to the Format editor. Make any desired changes to the condition or format. Select to reapply the modified spotlighter format. BDM Hyperion Pivot Guide 1/25/2010
Page 29
The modified format is reapplied to the report element. To copy Spotlighter formats Select the spotlighted report element to copy. Select Format >Spotlighter. The Spotlighter is displayed with the formats applied to the item displayed in the Format editor. Select Capture. Select the report element to which to apply the spotlighter formats. The target area can be in the same report or in a different report. Select Restore. The captured formats are applied to the selected report element. To remove a format from a selected report element Select a report element. On the Format menu, select Spotlighter. The Spotlighter formats applied to the report element are displayed in the Format editor. Select the format to remove in the Format editor. Click Delete. Spotlighter Fields Field Operator
Value
Description Logical operators including: = equal to less than, greater than < less than greater than > = greater than or equal to Comparison value to define the condition Accepts the equation from the Format editor and applies it to the report element. Clears the Format editor and resets the style preference to the previous setting.
Formatting Color and Style
bold text italicize text underline text change fill color change text color Preview the formatting to be applied.
Format Editor
Displays the spotlighter format from a row, column or item.
Capture
Captures a spotlighter format from a row, column or item.
Restore
Copies a spotlighter format to a row, column or item.
Apply
Applies a spotlighter format to a row, column or item.
BDM Hyperion Pivot Guide 1/25/2010
Page 30
Adding a Header and Footer to the Pivot Adding a Header and Footer to the Pivot section can give it a polished look. It is recommended that the columns (data) fit the page orientation (portrait/landscape). You must be in the Print Preview mode to add you Header and Footer. Print Preview Placing the Results or Table report in print preview mode, File > Print Preview
or use the print preview button Adjusting margins
Adjust the top, bottom and side margins as needed The scrollbar is used to scroll what is on the screen, not through the pages of the report. Print Preview Speed Menu Right mouse Click
Moving from page to page, Click on one of the four “Page” choices - First, Previous, Next or Last
“Start Page Number…” allows you to change the page numbering of your document. This is helpful if you are adding the Result or Table report to another document and want the document numbered consecutively.
BDM Hyperion Pivot Guide 1/25/2010
Page 31
Page Header/Footer allows you to add your own Header/Footer information
Use the buttons in the dialog box to add 1. current date 2. time 3. file name 4. page 5. page total 6. limit values Click OK.
BDM Hyperion Pivot Guide 1/25/2010
Page 32
