Understanding Dynamic Reports

Dynamic Reports is a reporting tool that provides easy-to-use, ad hoc query capabilities and structured financial reports that utilize reusable report sets. Dynamic Reports use Report Sets and dimension data to produce a specific output.

Dynamic Reports have three components: page, row, and column. Dimensions, attributes, and report sets are defined for components by dragging and dropping them directly onto the component.

A Dynamic Report consists of a report grid. For example, an income statement report might be shown on the column axis and income statement accounts along the rows axis. At the intersection of each member along each axis, a cell displays the measure data for the selection of company and department.

One or more Dynamic Reports can be mapped to a single or multiple Planning Templates. Admin can map the Dynamic Reports on the Template Setup page. The mapped reports are run from the Planning Control Panel and displayed on the Planning Template Input and/or View page.

Set up Dynamic Report

You can do the following to create a Dynamic Report:

1. From the reports home page, click Add from the top menu.
2. Select Dynamic Report, and the Add Dynamic Report window appears. The Reporting Area represents the multidimensional cube upon which the report will be built: financial, sales, and workforce. Once you select a Reporting Area and click OK, the Dynamic Report page appears. Dimensions, Attributes, and Report Sets displayed are based on the Reporting Area selected.
3. Select the required reporting area and click OK. The dynamic report setup page appears.

Using the dynamic report setup page, you can perform the following activities:

• Map (drag and drop) the dimensions, attributes, or report sets displayed in the left menu with the appropriate page, row, or column fields depending on your requirement. You can drag and drop dimension members to the page level and dimension members and report sets to the row and column levels.
• Select the required dimension members once you define the page, row, or column fields.
• Launch the Header and Footer pane from the top menu to select options like style, color, and format to display header and footer details for your Dynamic Report.
• Now that all the basic settings are defined, you can Run the report from the top menu to view the data as per the setup you made and access additional options needed to format a report.
• Use the Refresh option on the top menu to get the latest data into your report.
• Save the dynamic report from the top menu. If you use the Save As option, you can save the same report with a different name. When you create a new Dynamic Report using Save As, the Pick List member selections are copied and applied automatically.

Refer to Working with Dynamic Reports to know more about these additional options.

Header and Footer Set up

When you launch the Header / Footer pane, variables appear in the left pane. Drag and drop variables to the report header and footer. To remove a variable from the header or footer, drag and drop the selection back to the Variables list.

Variables with an arrow (shown below) contain sub-variables that can be added to the header or footer

Properties

The Properties bar (partially shown below) is where you select font type, color, and other properties for the header and footer.

A description of all properties is provided:

• Code – Includes the report code in the header or footer.

• Name – Includes the report name in the header or footer.

• Author - Add the name of the report author to a header or footer.

• Page Number – Add page numbers to a header or footer; available when you export the report (not when the report is run).

• Page 1 of ? - Add page number to a header or footer; available when you export the report (not when the report is run).

• Run by user- Add the name of the user that runs the report to a header or footer.

• Current Date– Add the date to a header or footer.

• Font - Select a font to apply to a selected header or footer.

• Size - Select the font size. The default font size is 10.

• - Bold the text for a selected header or footer.

• - Italicize the text for a selected header or footer.

• - Underline the text for a selected header or footer.

• - Double underline the text for a selected header or footer.

• - Change the color of the text for a selected header or footer.

• - Change the background color for a selected header or footer.

• - Decrease the indentation for a selected header or footer.

• - Increase the indentation for a selected header or footer.

• - Align the text to the left for a selected header or footer.

• - Align the text in the center for a selected header or footer.

• - Align the text to the right for a selected header or footer.

• - Open the Image Selector dialog page to browse and select an image from your computer or the File Cabinet to display in the selected header or footer. See Logo Limitations. Only image file types like jpeg, bmp, jpg, and png are supported.

• - View name, code, or label (name and code) details for each selected header or footer dimension.

Header & Footer Display

Header & Footer section - This option is selected by default and will be applied for Excel export in the report output menu and Excel file attached to an email. If you select the Header and Footer sections and export the Dynamic Report to Excel, the report will be displayed in the page layout view, not the normal view. The purpose of the page layout view is to provide better visibility for header and footer details.

Rows – Select to export header and footer details as rows in the Excel output and when attached to an email. Logos, formatting, and positioning (left/center/right columns) should be adjusted to fit within the rows and columns of the Excel sheet. If you select Rows, the report is opened in Excel in normal view mode. You can change the Header & Footer options at any time.

Point of View

Point of View (POV) is available for Dynamic Report headers and footers. POV and default members are applied to the page-level dimensions selected in a header or footer.

Page, Row, and Column Set up

You can drag and drop dimension members to the page level and dimension members and report sets to the row and column levels.

Report Sets are created on a specific dimension and typically contain members of other dimensions. Report Sets contain the link from the report lines to the account segment hierarchies. These links are created using Rule formulas.

Use a Rule to map a Gross Margin line of an Income Statement to a Gross Margin member on an Account Segment Hierarchy. Typical examples of report sets are income statements, balance sheets, and cash flow statements.

A list of all Standard Variables can be accessed from the File Cabinet by clicking the Report Sets folder. Open the Planful Default Report Sets folder and the Rule folder to view the Standard Variables list.

Report sets are applicable to a single axis only. Once the report set row and column details are selected, click a selection to display the Member Selector. You can lock properties. The Lock icon indicates that there are locked properties.

Page

Drag and drop dimensions at the Page level applicable to the entire report. The Page axis is displayed in the report when you create a report, run a report, and modify an existing report.

Member labels are separated with a comma. When there are too many label names, an ellipsis appears.

You can reorder the labels by dragging and dropping them on the Mapped Members pane.

If you use the same dimension in multiple places on a page, column, or row using calculated members (MDX), the column axis is the highest priority followed by the row and lastly the page. In a typical situation where the same dimension is not used in multiple places, the page takes precedence, followed by the row and lastly the column.

Row

Drag and drop dimension(s) or report sets at the Row level, which will populate across the report horizontally based on the hierarchical structure. You can select multiple dimensions for the Row. The row axis does not contain object details like the page axis.

Column

Drag and drop dimension(s) or report sets at the Column level, which will populate vertically based on the hierarchical structure. The column axis does not contain object details like the page axis.

When a Report Set is on the row or column axis, you can click and open it directly from the Dynamic Report. This eliminates the need to return to the File Cabinet to open the Report Set. Click the carrot icon to select and/or lock Report Set member properties.

Dimension Members

Display Dynamic Report members by Name, Code, or Label (name+code). On the Members pane, the Label selection is available as shown below.

Click the Label selection in the left pane to display a list-box where you can select to show the Label, Code, or Name for the dimension members listed.

Whatever label you apply here (Label, Code or Name) will also appear in Dynamic Report output. By default, Label is selected and displayed. Code, Name, and Label display set up for a dimension within the Dynamic Report member selections is retained when you view the drill through metadata. If display currency configuration is enabled in the application for Reporting dimensions, LC & CC values are replaced with the appropriate currencies in the drill through metadata.

When the Show Pick List check box is selected for a dimension added to the Page axis and you run the Dynamic Report; Code, Name, and Label display is retained for the members displayed in the pick list.

This selection applies to Dynamic Reports added to a Report Collection or Financial Package.

Additionally, select the Index button to toggle the Index on and off. The Index number (also referred to as IDX) can be displayed in the Dynamic Report if toggled on.

You can use the search box in the Members section to search for a member. The member hierarchy is not displayed along with the search result. You can contact Planful Support to enable the search behavior, that is to display search results with the associated member hierarchy.

Custom Members in Dynamic Report

Custom Members are supported in Dynamic Reports. Custom members can be defined on any dimension including attribute and attribute hierarchies. You can select Custom Members from the Members section while creating a report. The Custom Members header is displayed in the Member section only if at least one Custom Member exists on the dimension or attribute or attribute hierarchy. You cannot add Custom Members to the Page axis.

Custom Members when evaluated can result in a single or multiple hierarchy members from a single dimension or a multi-dimensional rule or a calculation. You need to update the Dynamic Report MDX query to evaluate Custom Members.

When you run a Dynamic Report with Custom Members, the MDX in Custom Member is calculated and displayed in the Dynamic Report output. If a Substitution Variable is used in the Custom Members, then it is replaced with the variable value that is set up in Dynamic Report. You can use Custom Members that return dynamic output and static output together in Dynamic Report on the Row or Column axis.

Custom Members honors the following:

• Dimension security - Scenario security, Budget Entity security, dimension, attribute, and attribute hierarchy security are honored as configured for the application. Also, any rollup aggregation or the MDX calculations are derived based on dimension security. After evaluation of the Custom Member, if no data is returned due to user’s dimension security on a specific row or column, then those rows/columns are ignored and the rest of the report is generated without any errors.

• Options - When you run a Dynamic Report, the MDX for the Custom Members is calculated with selected Options and data is displayed in Dynamic Report. Options apply only to the resulting rollup members derived from Custom Members. All the available Options are honored on Custom Members.

• Report Set based Dynamic Report - You can use Custom members on dimensions or rows or columns. If a Report Set is used on the Row or Column axes, Custom Members cannot be used on that particular axis. Also, Custom Members cannot be used in a Report Set.

• Code/Name/Label Display - The Code/Name/Label display set up for a dimension within the Dynamic Report member selections is honored on the hierarchy members derived from a Custom Member. If display currency configuration is enabled in the application for reporting dimensions, then LC & CC values are replaced with the appropriate currencies when derived from a Custom Member. If Code or Name does not exist for a member, Label is displayed. Also, the Code/Name/Label display is honored on the multi-dimension rules and calculations. Custom Members displayed in the Header or Footer section honors the Code/Name/Label display as set up for the dimension.

Cell-Level Formatting - All text and data formatting options as available in Dynamic Reports are applicable to Custom Members. The format is retained when you Save, Run, Refresh, Save As, Copy a Dynamic Report. Also, the cell level formatting is retained when you use Option in a Dynamic Report. Additionally, the cell-level formatting is applicable for calculated Custom Member rows or columns.

• Insert Formulas and Formula Exceptions - Insert formulas and Formula exceptions are supported in all Dynamic Reports where custom members are used.

• Exports - Rows or columns derived from Custom Members are included in all Dynamic report exports, Report Collections, FPP, email attachments, print, cloud scheduler, mapped reports and so on.

• Drill Down - Drill down is enabled on all roll up members across all dimensions derived from a Custom Member. Drill down is not enabled on multi-dimensional rules and calculated Custom Members.

• Drill Through - Drill through is enabled for all hierarchy-based members and multi-dimensional rules derived from Custom Members. Drill through is not enabled on calculated Custom Members.

The following features are disabled when you use Custom Members in a Dynamic Report:

• Notes - If Custom Members are selected on any dimension on the row/column axis of a Dynamic Report, Notes is disabled in the toolbar and the Notes column is removed from the report. However, the Notes column is displayed in all the drill through reports. This is applicable to hierarchy-based or multi-dimensional rules or calculations derived from Custom Members.

• Rank - When Custom Members are used in a Dynamic Report, Rank is disabled in the toolbar and the existing Rank settings are removed from the report.

Limitation

Custom Members defined as a combination of functions from different functional groups such as Array, Dimension, Numeric, and so on, creates an inconsistent MDX query and results in an error.

Dimension Member Pick List

Show Pick List is a quick selection list, similar to favorites. It is available on the Member Selector pane. Show Pick List is not selected by default and displayed for Page object selections only. Select Show Pick List to add member selections to the report pick list.

Let's say Dept-780 is selected. It becomes the primary selection on the report. This means that report results use this selection. Dept-790 is not selected, which means that it will be available in the pick list. A user can quickly modify the selection to run the report for this particular department without having to use the member selector interface.

When the report is run, it uses the primary member selections only and not the pick list. You can add a pick list without adding members to the primary list. If this is the case, the report will run for the default members or Point of View as applicable. Lock, Suppress Indents, and Options details are displayed in the grid when Show Pick List is selected.

Notice that the checkbox next to Mapped Members is opened only when Show Pick List is selected. Select the checkbox next to Mapped Members to select all pick list members.

If Show Pick List is selected for the page object, a carrot icon is displayed for the object in the layout area. If you click the object, Member Selector is loaded. If you click the carrot icon, a pick list pop-up is provided for quick selection.

When member selections are complete and you click on an object in the Page axis (in setup, design, header/footer, and run modes), the name of the dimension and the list of primary and pick list selections for that dimension are displayed.

You can select multiple members for Measures and Scenario dimensions for the Workforce and Financial reporting areas. For example, select the Measures dimension on the Page axis and select the Show Pick List checkbox, then, you can select multiple members.

However, you can run the report with only one member. You can later select a different member from the axis drop-down as shown in the below image.

Dimension Security on Pick List

When you run a Dynamic Report with the Show Pick List check box selected for dimensions on the Page axis, only the members for which the user has access to (as defined in Dimension Security) are displayed.

For example, if you select the Balance Sheet and Income Statement on the Pick List for the Account dimension (as shown in the image below), these members will be displayed in the report if the user has dimension security access to them. This functionality is applicable to only the Financial Reporting Area.

Pick List Dimension Security applies to Report Collections and Financial Packages containing the corresponding Dynamic Reports.

When you select the Show Pick List check box:

• By default, the first member in the list is selected and the report is run for the selected member.

• The report is run for the default member if you do not select any member from the Pick List.

• The report will not show any data if you do not have access to the default member.

When you share a report with another user, and you have selected members in the Pick List:

• The report is run for the same selected members only if the other user also has access to the selected members.

• The report is run for the first member available in the Pick List when the other user does not have access to at least one of the selected members.

When Point of View (POV) is defined on a dimension:

• If you run a Dynamic Report with POV defined on the same dimension for which the Pick List is configured, then the Dynamic Report is run only for the members that are configured in the POV.

Lock Dimension Members

You can lock dimension members to prevent modification. To lock dimensions, select dimension members to open the Member Selector pane. Move the selected member to the Mapped Members area by clicking the forward arrow. If a dimension is locked, member selections cannot be modified by regular users with read-only access.

Click the Lock checkbox and select an option:

• Selected - The report returns the members selected.

• Children - The report returns the first level children of the selected members.

• All Children - The report returns all of the descendants of the selected members.

• Sel+Children - The report returns the selected members and the first level children of the selected members.

• Sel+AllChildren - The report returns the selected members and all of the descendants of the selected members.

• Leaves - The report returns the posting levels below the selected members.

• Sel+Leaves - The report returns the selected members and the posting levels below the selected members.

• Sel+Parents - The report returns the selected members and the summary levels above the selected members.

The option selected (i.e. Leaves, Children, etc.) also applies to substitution variables used. For example, if you select the @CURYR@ substitution variable as the member for a Time dimension on the column or a Dynamic Report and then you select the Leaves option, the Dynamic Report output will display with all current month output. This is because the @CURYR@ substitution variable has current month leaf members.

Integrate with Financial Package Publisher

Add Dynamic Reports to the Financial Package Publisher from within the Financial Package Publisher interface. Within Financial Package Publisher, select Dynamic Report as the Content-Type and select the Dynamic Report you would like to include in the Financial Package Publisher report.