Using SpotlightXL-Reports Additional Features
  • 18 Minutes to read
  • Dark
    Light
  • PDF

Using SpotlightXL-Reports Additional Features

  • Dark
    Light
  • PDF

Article summary

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

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.

Cascade Reporting

Cascade Reporting is a method of report design that eases ongoing maintenance by letting you indicate that you want a dimension and all its children, or all its members, rather than specifying each member explicitly in the report. When members are added, deleted, or moved in the dimension hierarchy, the changes take effect the next time the report is run.

Cascade Reporting uses relationship filters (such as children of a selected member), which minimizes report maintenance when new members are added to the dimension hierarchies. You can set up one or more rows of data to be displayed for each cascaded dimension member.

For example, you add a newly acquired organization to the Company dimension, which is cascaded in a report. The new organization is automatically included the next time the report is run. This is done by specifying the dimension to cascade from, defining a grouping of rows, called a block, that will be repeated for each cascaded member, and specifying a relationship filter that defines how to cascade the dimension. For each dimension member that meets the criteria in the filter, the block of rows is repeated in the report at run-time.

Cascade dimensions can be defined only on row dimensions. We do not support cascaded column dimensions.

Cascade Options and Filters

Cascade Reporting works well on dimensions with member hierarchies that change or have new members added regularly, such as a Product dimension. Using filters, you can precisely specify which members of the cascaded dimension should appear in the report.

How to Use Cascade Filters?

  1. In SpotlightXL, go to Report > Design. Select a desired report.
  2. Select all cells in the report and click Design Manager.
  3. On the Cascaded dimension, click the Selection icon, then select Filters.
Note:
Once you decide to cascade a dimension, you cannot remove the Cascade option.

The following table provides information on the Cascade options.

ActionDescription

Select Member

Select a specific member to display in the report. For example, the dimension is Time and you have 5 members within Time (2019, 2020, 2021, 2022, and 2023). You want data only for 2022 displayed so you select that member.

Filters

Using Filters, you can precisely specify which members of the cascaded dimension should appear in the report. Select from the following options.

None – Only the member selected is displayed.

Children of <Member> – The children of the specified member are displayed in the report. The specified member is not displayed.

Sel + Children of <Member> – The children of the specified member are displayed in the report, as well as the specified member. The specified member is listed at the top of the report.

AllChildren of <Member> - The children, grandchildren, and all other descendants of the specified member are displayed in the report. The specified member is not displayed.

Sel + AllChildren of <Member> - The children, grandchildren, and all other descendants of the specified member is displayed in the report, as well as the specified member. The specified member is listed at the top of the report.

Leaves of <Member> - The leaf members in the hierarchy under the specified member are displayed in the report. The specified member is not displayed.

Sel + Leaves of <Member> - The leaf members in the hierarchy under the specified member are displayed in the report. The specified member is listed at the top of the report. Note that no other parent-level members under the specified member are included in the report.

Insert New Member

Above – Select to add dimension members to the report above the cascaded rows. 

Below – Select to add dimension member to the report and Below the existing selected dimension member.

Insert Subtotal

Adds a row to the report and provides a subtotal of the data in the columns. The Subtotal disregards the rollup operators on the members and simply does a sum of all the numbers in each column.

To delete the Subtotal, select the Subtotal row in the Design Manager, use the Selection icon, and click Delete.

Delete

Delete the member from the report definition. Use this option to delete excess members listed under the cascaded dimensions.

Move

Top / Up – Move the dimension member up one place or to the top of the report.

Down / Bottom - Move the dimension member down one place or to the bottom of the report.

How to Create a Cascade Report?

To create a cascade report, start by opening a view that includes a specific dimension on the row axis.

  1. In SpotlightXL, navigate to Report > Design and select a desired report.
  2. Select the entire spreadsheet as shown below.
    Note:
    Select the entire spreadsheet before using the Design Manager to ensure accurate dimension representation.
  3. Click Design Manager.

  4. On the Design Manager window, you can cascade row dimensions by selecting the Cascade checkbox for the dimension you want to apply cascading to.
    Note:
    Once a dimension on the row is selected for cascading, all other row dimensions will also be cascaded automatically.
  5. Click the Selection icon to specify the cascade options.
  6. Click Select Member.

  7. The member selection window appears. Select the desired member and click Select.

  8. Click the Selection icon again and select Filters.
  9. Select the desired filter option.

  10. Click Update. Notice that the selected dimension is preceded with Cascade. This indicates that it is a cascading dimension.
  11. Apply formatting, such as highlighting and font size, as desired.
  12. Click Save.
  13. Enter a name for the report and click Save.
  14. Go to Repot > Run to execute the report and apply cascading functionality.

Adding Grouping Functionality to a Cascade Report

Add row and column groupings within the Cascade Block of a Cascade Report. The Cascade Block includes rows from the cascaded dimension to the innermost dimension member. When you save the report with groups collapsed or expanded, this setting is preserved. Upon refresh, the groups will appear as defined in the report design. For instructions on grouping rows, click here.

How to Ungroup Rows in SpotlightXL?

  1. In SpotlightXL, select the rows you want to ungroup.
  2. Click the Data tab.
  3. Click the Ungroup option.
  4. Return to the SpotlightXL tab.

See Also: Cascade Reporting

This feature does not work when you have a 1-row cascade report design.

Updating a Cascade Report

Click the Report task and the Design subtask, then select the report you want to update. Select the entire worksheet and click Design Manager. Update as needed and click the Update button. When returned to the worksheet, Updated indicators are displayed.

Note:
Remember to click Save before running the report.

ModelingImages401to450image422.png

You can have one report with Cascade dimensions and each dimension member can have a different filter. In this example, the Cascade dimension is Company, parent, and children are applied to DecisionWorks Golf, children are applied to DW Golf Eliminations, and None is applied to North America, Europe, and Asia-Pac.

ModelingImages401to450image423.png

You can also select several Cascade dimensions for one report as shown below.

ModelingImages401to450image424.png

Cascade Support for Excel Formulas

Excel formulas in the report are honored in a Cascade report in the following circumstances:

  • The formulas are inside the block.
  • The formulas are on the row immediately following the block.

When the report is run, the formulas are calculated using the data retrieved from the model. The formulas are retained.

The following example shows Excel formulas inserted in the row below the data.


When the report is run, the formulas are calculated using the data retrieved from the model. The formulas are retained.

Cascade Support for Subtotals

Subtotals are available for cascaded dimensions. When you insert a Subtotal, a row is added to the report with subtotals of the data in the columns. The Subtotal disregards the rollup operators on the members and simply does a sum of all the numbers in each column.
To add the Subtotal, select the Subtotal row in the Design Manager, use the Selection icon, and click Insert Subtotal.


After you click Update, you see the Subtotal row added to the report.


After you run the report, you see Subtotals under each block of cells.

To delete the Subtotal, select the Subtotal row in the Design Manager, use the Selection icon, and click Delete.

Note:
You can change the text of the cell "Account: Subtotal" in Report Design mode.

Cascade Support for Saving Data, Saving Data and Running a Calculation, and Breakback

Cascade reports support saving data, whether through data entry or Breakback operations. You can use the standard Calculation on Save options, including saving data in multiple models, running a calculation after saving the data, and running a calculation without saving data. As with non-cascade reports, data can be saved only on leaf-level cells. Dimension security is honored.

For general information on enabling Data Save options, see Enabling Menu Items in Views and Reports: Save Data and Breakback.

Note:
If the report contains attributes, we recommend that Save not be enabled.

In the following example, a report has leaf members on the page axis and column axis. This ensures that leaf-level data appears in the report. Report Properties allow data to be saved and a calculation to be run. Additionally, note that Suppress Rows and Columns are set to None since the user may want to enter data into those cells.

ModelingImages501to550image541.png

When the report is run, the Save Data and Breakback buttons are provided on the menu ribbon. The user can enter data and click Save Data when complete.

Cascade Support for Drill-Through

When creating a report using data from a Master model, you can use Drill-Through from any of the data cells in a cascading report.

Cascade Support for Suppress Rows and Columns

Cascaded dimensions automatically have blank rows suppressed. You may want to also specify to suppress Zero rows in the report properties.

In the following example, the highest-level parent in the Account dimension has no data because it was defined with a null rollup operator (). The account is a cascaded dimension defined as Sel+ Children of Account.

If Suppress Rows and Suppress Columns are set to None, when the report is run, you see blank data for Account and #DIV/0 for the percentage calculations.


If Suppress Rows is set to Suppress Both - Zero and Blank, when the report is run, you no longer see the #DIV/0 errors, but you also lose the top row of each block because the Account is blank.


The solution is to specify that the Account dimension be cascaded as Children of Account so that the Account member is not included. With Suppress Rows set to Suppress Both - Zero and Blank, when the report is run, you see a complete set of data.

ModelingImages501to550image545.png

Remove Inner Block Rows

If you use the "Suppress Rows" feature in a Cascade report, any rows that don't have any data will be hidden. However, if there is any data in a row, the entire block will be visible, including any cells that contain formulas but don't have data. This means that those cells will not be suppressed and will be displayed as intended.

You can select or deselect the Remove Inner Block Rows option available in the Selection menu of the Design Manager for a Cascade Report. This option is selected by default. If you deselect this option, the rows are hidden and not deleted.

DynamicPlanningAug21Indent1.png

Report Output when Blanks and Rows are Hidden: They are not deleted. This will ensure that any dependent Excel formulas will not impact the overall report.

DynamicPlanningJuly21Casacade2.png

Report Output when Blanks and Rows are Included: This is applicable when the inner block does not have any dependent Excel formulas in the inner block that might be affected.

DynamicPlanningJuly21Casacade4.png

How to Select the Remove Inner Block Rows Option?

  1. In SpotlightXL, go to Report > Design and select the desired report.
  2. Select the entire spreadsheet.
    Note:
    It is important when using Cascade Reports to select the entire spreadsheet before selecting the Design Manager to ensure the dimensions are represented correctly if the cascade option is used.
  3. Click Design Manager
  4. For the dimension that you want to use Cascade, click the Cascade checkbox.
  5. On the Cascaded dimension, click the Selection icon and select or deselect Remove Inner Block Rows.

Cascade Support for Leaf Levels with Data Only

Cascade reporting provides functionality similar to Zoom In to Data Leaf Levels in Views.

When a cascade report is run, you can see only rows that have data at the leaf level. Additionally, rows that do not have data are removed from the report so that they do not consume excess data analysis cells unnecessarily. (The maximum number of Analysis Cells is specified in Application Settings.)

How to Set Up the Cascade Report to Show Data at Leaf Levels?

  1. In SpotlightXL, go to Report > Design.
  2. Open the report with cascaded dimensions on the row axis.
  3. Select all cells in the report, then click Design Manager.
  4. Set the cascaded dimensions to a filter of 'Leaves of' the dimension then click Update.
  5. Click Report Properties and set Suppress Rows to Blank, then click Save.

ModelingImagesReportimage557.png

Now you can run the report by going to Report > Run. For information on Zoom In to Data Leaf Levels in Views, see Dynamic Planning: How to Find Data Cells Quickly.

Cascade Support for Attributes

Cascade Reporting supports attributes in two ways:

  • An attribute can appear on the page or column axis in a report with cascaded dimensions on the rows.
  • An attribute can be cascaded on the row axis.

In the following example, Region is an attribute of the Company dimension. The region appears in the row axis and is cascaded.

Indent Row Members

You can indent all inner block dimension members in a Cascade report based on the levels defined in the dimension. This functionality indents the inner block members based on their levels defined in the hierarchy.

You can select or deselect the Indent Row Members option available in the Selection menu of the Design Manager for a Cascade Report. This option is selected by default. If you deselect this option, the dimension members are not indented.

DynamicPlanningAug21Indent11.png

How to Select the Indent Row Members Option?

  1. In SpotlightXL, go to Report > Design and select the desired report.
  2. Select the entire spreadsheet.
    Note:
    It is important when using Cascade Reports to select the entire spreadsheet before selecting the Design Manager to ensure the dimensions are represented correctly if the cascade option is used.
  3. Click Design Manager
  4. For the dimension that you want to use Cascade, click the Cascade checkbox.
  5. On the Cascaded dimension, click the Selection icon and select or deselect Indent Row Members.
Note:
When including attributes on the report, we recommend that Save not be enabled.

Repeat Outer Dimension Members Automatically in Cascade Reports

In SpotlightXL, you can repeat outer dimension members automatically in cascade reports by selecting the Repeat option available in the Selection menu. When you select the Repeat option while creating a Cascade report, you can view the outer dimension members repeating automatically for all inner dimension members.

The following image shows the Repeat option:

DynamicPlanningWinter21Repeat1.png

Let us take the following example to understand the details.

Suppose you have a Report containing 2 dimensions on rows(ex:- Furniture_Type and Season dimensions). Home living and Furniture as outer dimension members and different Seasons as inner dimension members. For this report, when you create a cascade report and select the Repeat option, then the outer dimension members, Home & Living, and Furniture will repeat automatically for all inner dimension members.

The image below shows the outer dimension members repeating themselves for all inner dimension members:

DynamicPlanningWinter21Repeat2.png

How to Select the Repeat Option in Reports?

  1. In SpotlightXL, go to Report > Design and select the desired report.
  2. Select the entire spreadsheet.
    Note:
    It is important when using Cascade Reports to select the entire spreadsheet before selecting the Design Manager to ensure the dimensions are represented correctly if the cascade option is used.
  3. Click Design Manager.
  4. For the dimension that you want to use Cascade, click the Cascade check box.
  5. On the Cascaded dimension, click the Selection icon and select Repeat.

How to Add a Dimension Member to the Cascade Report?

  1. In SpotlightXL, go to Report > Design and select the desired report.
  2. Click Design Manager, select the dimension member to add, and click Update.
  3. Select the entire spreadsheet.
  4. For the added dimension, click the Cascade check box and click Update.
  5. After saving, run the report by going to the Report > Run.

Formatting Tips in Cascade Reporting

When designing a cascading report, here are some formatting tips.

  • You can rename the Subtotals cell to be more descriptive, and use cell formatting on the data cells.
  • You can add merged cells in the heading and additional text above the block. Any text cells on the right side of the block will not be repeated with the block. Think of the block as the box from column A to the rightmost column of data cells with the red cell-comment indicators. Anything to the right of the box is ignored.

When the report is run:


When starting with a view and then creating a report, it is a best practice to remove excess rows from the report design and let the Cascade feature fill in the rows. 

SpotlightXL and Web Chart Formatting and Types

Below are the available options for designing a report:

Chart Formatting Supported 

Chart Title - Text (SpotlightXL and Web)

Chart Legend (SpotlightXL and Web)

Chart Color Palette (SpotlightXL and Web)

Chart Style (SpotlightXL and Web)

Chart Tooltip (SpotlightXL and Web)

Chart Position (SpotlightXL and Web)

Bar Chart

Clustered Bar (SpotlightXL and Web)

Stacked Bar (SpotlightXL and Web)

100% Stacked Bar

3D Clustered Bar (SpotlightXL and Web)

3D Stacked Bar (SpotlightXL and Web)

Note:
This is not supported in Excel 2016.

3D 100% Stacked Bar (SpotlightXL and Web)

Note:
This is not supported in Excel 2016.

Column Chart

Clustered Column (SpotlightXL and Web)

Stacked Column (SpotlightXL and Web)

100% Stacked Column (SpotlightXL and Web)

3D Clustered Column (SpotlightXL and Web)

Note:
This is not supported in Excel 2016.

3D Stacked Column (SpotlightXL and Web)

Note:
This is not supported in Excel 2016.

3D 100% Stacked Column (SpotlightXL and Web)

Note:
This is not supported in Excel 2016.

3D Column (SpotlightXL and Web)

Line Chart

Line (SpotlightXL and Web)

Stacked Line (SpotlightXL and Web)

100% Stacked Line (SpotlightXL and Web)

Line with Markers (SpotlightXL and Web)

Stacked Line with Markers (SpotlightXL and Web)

100% Stacked Line with Markers (SpotlightXL and Web)

3D Line (SpotlightXL and Web)

Pie Chart

Pie (SpotlightXL and Web)

3D Pie (SpotlightXL and Web)

Pie of Pie (SpotlightXL and Web)

Bar of Pie (SpotlightXL and Web)

Doughnut (SpotlightXL and Web)

Radar Chart (SpotlightXL and Web)

Scattered Charts (SpotlightXL and Web)

Other Unsupported Chart Types in SpotlightXL

Combo Chart

Surface and Surface 3D Chart

Stocks Chart

Bubble Chart

Water Fall Chart

Hierarchy Chart

Funnel Chart

Histogram

Box & Whisker Chart

Tree Map Chart

Sunburst Chart

Excel Formatting Supported

Font Size 

Bold

Italic

Underline

Double Underline

Background Color

Text Color

Style

Border - Top

Border - Right

Border - Bottom

Border - Left

Border Style

Border Properties*

Border Color

Alignment - Left

Alignment - Center

Alignment - Right

Indentation

Format - Number

Format - Currency

Format - Percent

Column Width

Row Height

Hide Gridlines

Hide Headers

Images in Reports*

Print Property* (not supported in Spotlight)

*Border Properties - All border properties are supported in SpotlightXL except Diagonal Borders (left and right). If selected in the design mode of a report, when the report is rendered they will be removed from the report entirely and they will also be removed from the Design mode.

*Images in Reports - All image types (for example, JPG, BMP, and PNG) that are supported by Microsoft Excel are also supported for Dynamic Planning. This includes the insertion of Clip Art. Image format properties such as size, effect, and crop (available under the Microsoft Excel FORMAT tab) are supported as long as the image is formatted before saving the report in SpotlightXL. There are no image size limitations or the number of images per report, however, we recommend you use no more than 3 images per report as performance may be impacted.

*Print Property - To save print settings for a specific report, open the report in Design Mode by selecting the Report task and the Design subtask. Select File, Print. Select print settings such as Orientation and Margins. Exit the Print menu and return to the SpotlightXL tab. Click Save. Print settings are now saved to the associated report. Print settings are report-specific, however, they are not user-specific.

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.

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.


Was this article helpful?