Dynamic Reports Overview

Dynamic Reports Best Practices

Report Sets (Row & Column Sets)

• Use existing report sets as templates for new sets on the same dimension.

• Prefer substitution variables to reduce maintenance:

  • @CURMTH@ for Time.

  • Variables for Scenario selections (Budget, Forecast, Plan).

• For percent‑variance calculations in a column set, set Precedence = Column.

Drill‑Through

• In More → Enable Drill Through, allow transaction‑level review (available for ad‑hoc and report‑set‑based reports).

Dynamic Commentary (Ad‑hoc only)

• More → Comments On to let users add line‑level comments. (Not supported on report‑set‑based reports.)

Header & Footer

• For cleaner output:

  • In the Header/Footer designer, widen cells so long report names wrap nicely.

  • In Print Setup, tweak margins to avoid overlap.

• PDF exports don’t support underline/double‑underline page number formats.

Logos (in Header/Footer)

• One image per cell; don’t mix text and image in the same cell.

• Recommended: JPG, max 64 px (H) × 150 px (W), max size 50 KB.

Save As / Copy

• Duplicate artifacts with Save As in the report, or Copy from the file list (More menu).

Sharing

• Share on a need‑to‑know basis:

  1. In File Cabinet, select the report.

  2. More → Share, choose user/group and access level, then Share.

Dynamic Reports Toolbar

Formatting Pane

1. Select Format on the toolbar.

2. Text tab: fonts, size, style, color.

3. Alignment & Indentation: horizontal/vertical alignment and indents.

4. Borders: apply cell borders.

5. Data tab: number formats. (Currency symbol comes from OS locale.)

6. Other tab: suppress/un‑suppress rows/columns.

Formula Options

• Insert Row/Column (via Formula ▼):

  1. Give the item a Name.

  2. Choose Type and Location (relative placement).

  3. Pick a Function.

  4. Set % of Row/Column range (field changes based on insert context).

  5. OK.

• Formula Exceptions (for calculated cells only):

  1. Formula → Formula Exceptions → Add Rows.

  2. Enter Range (cell ref) and Exception Formula.

  3. OK.

• Edit/Delete Row/Column are enabled only for rows/columns inserted with Insert Row/Column.

Grid Options

• Pivot: swap dimensions between rows and columns.

• Resize Column(s)/Row(s): set width/height.

• Default Column/Row Size: reset to defaults (Column width 82, Row height 24).

More Options

Other Settings

• Comments On/Off: enable/disable line‑level comments (ad‑hoc only; part of Dynamic Collaboration).

• Rank: show Top/Bottom N.

• Show Notes: display a notes column with values entered in input templates.

Output

• Export to PDF / Excel, Print Setup, Send Email, Save as Google Sheet.

Using Header & Footer

Use Properties in Header & Footer

1. Save the report to the File Cabinet.

2. Open the report and click Show Header & Footer.

3. Select a header/footer row.

4. From Properties, pick a property (e.g., report name, author, date).

5. Format as needed (font, size, color, alignment).

6. OK, then Save and Run to view.

Add Variables to Header & Footer

• Variables (including nested sub‑variables) can be mixed with static text.

1. Show Header & Footer.

2. Drag variables from the left pane to a header/footer row.

3. Format.

4. OK, then Save and Run.

Add a Logo to Header & Footer

1. Show Header & Footer → choose a header cell.

2. Click the Image icon.

3. Select an image (upload or choose from File Cabinet).

4. Recommended format/size: JPG, 64×150 px, ≤ 50 KB.

5. OK, then Save and Run.

Export Header & Footer Details as Rows

• Switch the header/footer display from Page Layout to Rows to make the header/footer appear as standard rows in Excel/email output.

1. Show Header & Footer → Header & Footer Display = Rows.

2. Adjust logos/formatting/positions to fit grid rows/columns.

3. OK, then Save and Run.

• You can change this setting at any time.

Suppress Options in Dynamic Reports

Works with dimension‑based or report‑set‑based reports.

A) Turn On Suppression

1. Open Format pane → Other tab.

2. Choose:

   • Empty Rows: hide rows with no data.

   • Empty Columns: hide columns with no data.

   • Empty Header Rows: hide header rows that have no data in descendants.
     Notes: Empty Header Rows requires Empty Rows and applies only with report sets.

3. Apply.

B) Suppressing Empty Row vs. Empty Header Rows (Example)

• Lines present in a report set: Expenses (header), Administration (reference account), Total Expense (header with formula exception).

• If Administration has no data and you choose Empty Rows/Columns → Administration is hidden; Expenses (a header) remains.

• If Administration has no data and you choose Empty Rows + Empty Header Rows → Expenses (header) is also suppressed.

• If Administration has no data and Total Expense derives values only via a formula exception and you choose Empty Rows + Empty Columns → Total Expense is suppressed because the underlying report results are empty (values exist only due to a formula exception).

Drill Through in Reports

A) Enable/Disable Drill Through

• More → Enable Drill Through (adds the Drill Through icon to the toolbar).

• To disable: More → Disable Drill Through.

B) View Drill Through Data

1. Select a data cell (not a calculated cell).

2. Click Drill Through.

3. Review the Transactions/Translations tabs (visible only if such data was loaded), with columns reflecting original load structure.

Output Options

A) Print Setup (settings also apply to PDF export)

1. More → Output → Print Setup.

2. Configure:

   • Paper / Orientation / Margins (default top margin ~3.5 in to prevent header overlap).

   • Page Order (applies when Scaling is on):

     • Down, then over → fill rows first, then continue across pages.

     • Over, then down → fill columns first, then continue down pages.

   • Scaling:

     • Adjust to _% of normal size (zoom).

     • Fit to _ pages wide by _ tall.

   • Options: Show gridlines; Repeat row header on each page (default on); Repeat column header on each page (default on); Delete extra Rows & Columns for cleaner Excel output.

3. OK to save; Print to preview/download/print.

B) Export Dynamic Reports as Google Sheets

• Requires Google for Work configured by an admin.

1. More → Output → Save as Google Sheet.

2. Accept the warning to open in Google Sheets.

   Note:

   Google sheets exports always repeat headers in print, regardless of your Repeat Header settings.

C) Export All Members in a Roll‑up to Excel

• To expand hierarchies fully in Excel:

  1. Press Ctrl+K to maximize the Dimension selector.

  2. Choose the dimension → open Member Selector.

  3. Options → All Children.

  4. Run to preview hierarchy expansion.

  5. Export to Excel.

D) Enable Editing in Exported Reports (Excel)

• Exported files must open outside Excel’s Protected View:

  1. In Excel: File → Options → Trust Center → Trust Center Settings → Protected View.

  2. Uncheck:

     • Enable Protected View for files originating from the Internet.

     • Enable Protected View for files located in potentially unsafe locations.

  3. OK. Export again (More → Output → Export to Excel) and edit as needed.

Troubleshooting

A) No Data Populated

• Try these checks:

  1. Maintenance → Reports → Verify Data with the same criteria to confirm cube load.

  2. Confirm Actual Data Load Months are enabled.

  3. Confirm the Scenario (cube) is processed.

  4. Review Data Load History (DLR Description should read Data Loaded Successfully).

  5. If still empty, ask an Admin to review Dimension Security for the account.

B) Report Totals Do Not Match (Report‑set reports)

1. Open the report sets. Identify the row/column that intersect at the incorrect total.

2. Edit Rule for those lines and verify mappings. If a line maps directly to a hierarchy member, check the other report set for Advanced rule selections.

3. If the row uses a hierarchy member in Advanced Rule Builder, find the member by IDX in Hierarchy Management.

4. Use Maintenance → Reports → Verify Data and inspect column J to see loading details and locate extra balances.

5. If substitution variables are used in Advanced rules, open the Substitution Variable picker in the report and ensure each points to the correct value.

C) Roll‑up Level Totals Don’t Match Leaf Sums (e.g., Net Income)

1. Verify the Default Members set for each dimension used in the report (Admin access may be required).

2. In Change Default Member for a dimension, use Browse Customer Hierarchy to check selection. Dynamic Reports default to these when a dimension isn’t explicitly selected—so a leaf or roll‑up default can skew totals.

3. Ensure the top‑level parent is the default; change from leaf/roll‑up to the main parent and save.