Using SpotlightXL-Reports Additional Features

  • 4 minute(s) read
Prev Next

Article Summary

This article outlines advanced reporting features in SpotlightXL, including support for Excel-like functions, negative number formatting, repeating headers across pages, multi-tab report viewing, and tab naming behavior. It also explains the difference between "Refresh" and "Refresh All Open Reports" for keeping data current across report tabs.

There are additional functionalities available in the Reports section. See below for examples:

Function Support

The following functions are supported in SpotlightXL:

  • Arithmetic Operators: + - / *

  • Logical Operators used with IF function: = <> < <= > <=

  • Functions: IF/THEN, AND, OR, ROUND, FLOOR, CEILING, LN, ISBLANK, ABS, SQRT, POWER, MOD

Note:

All function and operator syntax is similar to Excel syntax.

Conditional Formatting

Conditional Formatting provides visual highlights for numeric values in your reports based on predefined rules. This helps users easily identify trends, outliers, or important data patterns in both SpotlightXL and Spotlight Web views.

How to Apply

You can apply conditional formatting in Design Mode in SpotlightXL using Excel’s native conditional formatting capabilities. Once applied:

  • The formatting is retained when the report is viewed in Run Mode.

  • The same formatting is automatically applied in Spotlight Web.

Supported Comparison Operators

  • Between / Not Between

  • Equal / Not Equal

  • Greater than / Less than

  • Greater than or equal to / Less than or equal to

How it Works

  1. Open a report in Design Mode in SpotlightXL.

  2. Apply conditional formatting to the desired numeric measures.

  3. Save the report.

  4. Open the report in Run Mode or Spotlight Web to view the dynamic formatting.

Current Limitations

  • Not supported in Cascaded Reports.

  • Only applicable to numeric measures.

  • Formatting does not appear in:

    • Excel or PDF exports

    • Report distributions

Report Commands

Report Commands allow report editors to control report layout and behavior using text-based commands directly entered into the report grid in Design Mode. This makes hiding, locking, or controlling rows/columns faster and more efficient. Report Commands applied in SpotlightXL are reflected identically in Spotlight Web.

Supported Commands

Commands

Description

/hiderow

Hides the entire row in Run Mode

/hidecolumn

Hides the entire column in Run Mode

/lockrow

Locks the entire row from edits in Run Mode

/lockcolumn

Locks the entire column from edits in Run Mode

Commands can also be inserted via formulas, allowing for dynamic, data-driven logic.

You can also apply commands conditionally using Excel-like formulas. Both outcomes (TRUE and FALSE) must return a valid command:

=Lock actuals and allow forecasts to remain editable:

=IF([Scenario]="Actual", "/lockcolumn", "/unlockcolumn")

This ensures forecast periods stay open for planning while actuals are protected.

How it Works

  1. Open the report in Design Mode.

  2. Type the command in Column A or Row 1.

  3. Save the report.

  4. Switch to Run Mode to see the effect:

    1. Commands are executed.

    2. Command text is hidden from the user view.

    3. Locked cells are non-editable; hidden rows/columns are not displayed.

Current Limitations

  • Row-based commands are not supported in Cascaded Reports.

  • Report Commands are not reflected in:

    • Excel or PDF exports

    • Report distributions

Negative Amounts in Reports

Dynamic Planning supports native Microsoft Excel negative number selections. In Report Design mode, select the report, right-click, and select Format Cells as shown below.

ModelingImages401to450image435.png

When the Format Cells dialog page is displayed, select the Number tab and then select the Number or Currency Category as shown below.

ModelingImages401to450image436.png

Select a negative number format and click OK. Save the report in SpotlightXL.

Note:

Displaying negative numbers in black versus red and with/without parenthesis is supported for Number and Currency formats.

Repeat Headers Across Multiple Pages

Repeating headers across printed pages allows you to see the column and row headers when a report exceeds the width and height of the page. You can select to repeat the top row(s) and the leftmost column(s) of a report.

How to Repeat Headers Across Multiple Pages?

  1. In SpotlightXL, go to the Report > Design, and select a desired report.

  2. Select Page Layout, Print Titles, and Sheet.

  3. Select Rows to repeat at the top and Columns to repeat at the left as shown below.

  4. Select SpotlightXL and save to retain the changes in SpotlightXL.

ModelingImages401to450image428.png

Note:

There is no visual indicator that displays the rows or columns repeated at print time.

You can repeat rows, columns, or both. Reviewer users cannot change the Repeat setting. Page break settings are saved with the report definition. That means each user that runs the report will have these settings applied when printing.

How to Use Multi-tab Reporting?

Multi-tab reporting allows you to open multiple tabs with different reports.

  1. To open multiple tabs with different reports in SpotlightXL, select Report > Run.
  2. Select the report from the Report list box.
  3. Add another tab by clicking the + icon to the right of the tabs.
  4. Select Report >Run, then select another report from the Report list box. The tab name will be updated with the report name.

To display the same report in multiple tabs, perform the same steps above, but select the same report from the Report list box.

Note:

For all tabs other than Report, the tab name defaults to the Report Name when opened. If the name exceeds 31 characters, it is truncated based on native Excel functionality. Also, Excel does not support all characters for tab names. Unsupported characters in the Report Name are replaced with an underscore. For example, if the report name is Test*1, it will be changed to Test_1 in the tab. For more information on unsupported characters, see http://answers.microsoft.com/en-us/office/forum/office_2013_release-customize/characters-not-allowed-in-sheet-names/ccab0b53-152a-4757-8903-751c6aacad11?auth=1. The report name in Dynamic Planning will not change. Identical report names are appended with a number in parenthesis.

You can save your workbook with multiple tabs displaying and use this as a starting point for future analysis and reporting with updated data. Open the workbook and update all open reports by selecting the Refresh All Open Reports option shown below. In addition, you can update each report individually by selecting the Refresh option on each tab, if desired.

ModelingImagesReportimage397.png

Note:

If the design of a report has changed since the last time that a user refreshes the saved workbook, the user will need to open the report again. The Refresh and Refresh All options only update the data associated with the saved report tabs.

Difference between refresh and refresh all

Refresh: Retrieves data values for each cell on the current page only. It connects to the Dynamic Planning data source and updates the values based on the defined member intersections.

Refresh All Open Reports: Retrieves and updates data for all pages in all open documents, not just the current one. This applies similarly across Spotlight for Word and PowerPoint.