Design

Overview

With the Design subtask, you can modify an existing report or create another. Access the Design subtask by selecting the Report task and the Design subtask. 

Creating a New Report Based on an Existing Report

1. Go to the Report section and select the Run subtask.
2. Open an existing report from the list provided in the Report list box.
3. Click on the Design subtask.
   
4. Utilize Excel functionality to make necessary edits to the report. For instance, in the provided example, adjustments include altering cell colors and increasing font sizes for headings.
   
5. Click Properties.
6. Under Display, select between Code or Display Label.
7. Next, to Enable Save, specify None if this is not a data entry report. Specify the Calculation Model if there is only one model used in the report and you want a calculation to be run when the user clicks Save. Specify All Models if there are data cells from more than one model in the report.
8. In the Group section, use the drop-down to select a user group to provide access to the report. To specify more than one group, add rows. The default is no user groups, which means that if you do not provide a user group, only Power Users and the report creator will have access. You can only assign group access to those groups of which you are a member and have access to the model.
   Note:

   There must be a blank row between the Group section and the POV section.
9. Click Save. You will remain in the Design mode. If you would like to run the report, select 'Run' from the subtask selection.

See: How to work with Model, View, and Report Folder and Organization Functionality

Report: New Report

or

With the New Report action, you can have one report based on data from multiple reports saved on different models. For example, you have a corporate model and a model for a foreign subsidiary. Each has an Operating Expense report. To view the Operating Expense report for the corporate model and the foreign subsidiary model in one report, copy and paste each report with captured data intersections to the New Report spreadsheet.

In Practice: In the following example, a new report is added using the New Report feature

1. Select the Report task and the Design subtask.
2. Click New Report.
3. Copy and paste report details from captured data intersections. These are represented by the red carats in the upper right-hand corner of a cell (see Capture Data or Analyze, Design Report for steps to capture data intersections).
4. Click Save.
5. Enter a name for the report in the Name field and click Save.
6. Click the Properties action.
7. Select a model to save the report to in the Save on Model cell.
8. Select the group or groups you want to have access to the report.
9. Click Save.
10. Select Report, and Run to view the report.

Note:

The New Report is available for existing reports with captured data intersections.

Below are the actions available on the Report Design screen: 

Substitution Variables

Substitution variables are used in reports in place of dimension member names. Reports can be defined using substitution variables that can be dynamically modified at runtime by end users. Power Users and Contributors now have the option to set up reports using substitution variables with expressions.

• The substitution variables can be dynamically modified at runtime. Previously, if a report was set up to use a substitution variable, the report would always run with the predefined value of the substitution variable. To change the value, a Power User or Contributor had to change it manually using the Model > Model Administration > Substitution Variables page. 
  • Any level of user can now change the value of substitution variables while running a report. The new value is used during that session, but the report reverts to the original substitution variable value the next time the report is run. For example, a Contributor may set up a report with a substitution variable defined as CurMth = January 2017. The end user can change the value to July 2017 when running the report. The next time the report is run, January 2017 is used by default but again can be changed easily at runtime. 
  • Selecting a different dimension member name is available in SpotlightXL and Spotlight. 
• Power Users and Contributors can set up substitution variables with expressions. Expressions are added to a substitution to derive another member name from the same dimension. 
• Substitution variables are most commonly used in the Time and Scenario dimensions.

Note:

Changing the value of a substitution variable is similar to the concept of selecting a dimension member from a POV on the page axis. When you change the POV or the value of the substitution variable, the data is refreshed. The difference is that a substitution variable can be placed anywhere in the report, such as a row or column axis.

Here is the overall process:

• Power User or Contributor creates one or more substitution variables in the model.
• Power User or Contributor creates one or more reports that use the substitution variables.
• Any user with access to the report can run it and change the value of the substitution variable, provided they have security access to the dimension.

Creating Substitution Variables for Reports

To set up formatted reports with substitution variables for use at run-time, a Power User or Contributor must first create the substitution variables.

In Practice

1. Select Model > Model Administration > Substitution Variables.
2. Select the model and dimension name from the list boxes.
   Tip:

   You can see a list of all substitution variables created for all models by selecting All Models and All Dimensions from the list boxes.
3. Enter the variable name with plain text. Avoid using the symbol @ in the variable name. For example, CurMth or Current Month. Variable names are case-sensitive.
4. Specify the starting value for this variable. This value must be the same as a dimension member name in your model. For example, Jan-17.
5. Optionally, specify the model that the variable will be used on.
6. Optionally, specify the dimension that the variable relates to. For example, Time.
7. Click Save.
   
   Note:

   Substitution variables used in reports are different than variables created for calculations or maps. Always use Model > Model Administration > Substitution Variables to create substitution variables for reports.

If you make a mistake, simply delete the row containing the substitution variable that you created, then click Save to delete it.

Using Substitution Variables in Reports

1. Setting up a report to use one or more substitution variables
2. Lay out the report with standard items, such as headings, rows, and columns.
3. While in Report Design mode, select the cells containing the dimension member to be replaced with a substitution variable.
4. Click Design Manager. Notice that the current value is a regular dimension member, Jan-2017.
   
5. Click the Member Selection icon, then Select Member.
6. Click Select Member. At the top of the member selection box, notice that Substitution Variables are listed first.
   
7. Select @CurMth@ and click Select.
8. Now the Design Manager reflects the change from the original value (Jan-17) to the Substitution Variable @CurMth@. Also shown in light gray is the predefined value of CurMth.
   
9. Click Update. The report now displays @CurMth@ in place of the 4 cells you originally selected.

Now you need to update the data cells to also use the substitution variable.

In Practice

1. Select all the data cells. In this example, columns B and C are data cells, while columns D and E contain Excel formulas.
2. Click Design Manager. Scroll to the Time dimension. Notice that the current value is a regular dimension member, Jan-2017.
3. Click the Member Selection icon, then Select Member.
   
4. Click Select Member. At the top of the member selection box, notice that Substitution Variables are listed first.
5. Select @CurMth@ and click Select.
6. Now the Design Manager reflects the change from the original value (Jan-17) to the Substitution Variable @CurMth@. Also shown in light gray is the predefined value of CurMth.
   
7. Click Update. The report now displays UPDATED in place of the data cells you originally selected.
8. Click Save to save your report.

Selecting a Different Value for a Substitution Variable at Runtime

When the end user runs a report, if it contains a substitution variable, a new icon automatically appears on the menu ribbon.

In Practice

1. With the cursor anywhere in the report, click Substitution Variables. The Substitution Variables box appears. It shows the name of the variable and the current value.
2. Click the Member Select icon.
   
3. From the member selection box, select the month that you want to see in the report.
4. Click Select.
   
5. Click Refresh to refresh the report.
   The report now displays data for July 17. Using this method, the user can easily move back and forth between months, quarters, or years, without updating the definition of the report and without the need for the Power User or Contributor to update the substitution variable value permanently.
   
   To reset the substitution variable back to its initial value, click Substitution Variables. From the Member Selection icon, select Reset to Default.
   

Using Substitution Variables with Expressions

Dynamic Planning supports the ability to enhance substitution variables by providing expressions. Expressions point to different members in the dimension about the predefined value of the substitution variable. Here is a basic annual revenue report. Expressions will make this report easier to maintain in the coming years.

Define the following substitution variable:

FirstMth = Jan-16

Now go to Report Design to use this variable.

In Practice

1. Select all the cells in the worksheet.
2. Click Design Manager.
   
3. Each member in the Time dimension will be replaced with a substitution variable or expression.
4. Select Jan-16.
5. Click the Member Selection icon, then Select Member.
   
6. Select @FirstMth@ and click Select. Now the Design Manager reflects the change from the original value (Jan-16) to the Substitution Variable @FirstMth@. Also shown in light gray is the predefined value of FirstMth.
7. Repeat steps 3-5 for Feb-16.
8. While still on Feb-16, click the Member Selection icon again, then Expression.
   
9. The Expression box appears and FirstMth is already selected.
10. Click Add Function.
11. Click Lead.
    
12. The Expression box now shows the Offset option. By default, the offset is 1. Offset refers to the number of members above or below the current member in the dimension hierarchy. Offset works within the current level and it skips over rollup members.
13. Lead refers to members below the current member in the hierarchy. For example, Jan-16, Lead(1) points to Feb-16. Lead is equivalent to fetching the next member in the hierarchy within the current level.
14. Lag refers to members above the current members in the hierarchy. For example, Jan-16, Lag(1) points to Dec-15. Lag is equivalent to fetching the previous member in the hierarchy within the current level.
15. Click Update to accept Lead(1).
16. Repeat steps 3-5 and 7-10 for the rest of the months to express all of the months about FirstMth.
    
17. For the year 2016, a different expression is needed.
18. Repeat steps 3-5 for 2016.
19. While still in 2016, click the Member Selection icon again, then Expression.
20. The Expression box appears and FirstMth is already selected.
21. Click Add Function.
22. Click Parent. The parent of Jan-16 is Q1-16, so we want the parent of the parent to go up 2 levels to 2016. This is done by chaining two expressions together. For more information on chaining expressions, see Expression Chains.
23. Click Add Function again.
24. Click Parent. Now you see @FirstMth@.Parent.Parent and 2016 are displayed to show you what value is returned with this expression.
    
25. Click Update.
26. The Time dimension now reflects all members to the substitution variable FirstMth.
    
27. Click Update to save your changes and exit the Design Manager.
    The report now displays UPDATED in place of the data cells, and @FirstMth@ and the expressions in place of the monthly headings.
    
28. Save the report design by clicking Save or Save As. Consider changing the name to preserve the original report until you are confident that the new report is accurate.
    Now when you run the report, you can use the Substitution Variables box to change the starting value to the first month of any year in your hierarchy.
    
29. After selecting Jan-17, click Refresh.
    Notice that the same report now works for other fiscal years. Users just need to select the first month of the fiscal year and the entire report is derived from that substitution variable dynamically. Because of the introduction of expressions, a Power User or Contributor can design more dynamic reports. And it will reduce the report maintenance going forward.
    

Converting Existing Reports with Substitution Variables to Use Expressions

Many existing reports are set up with multiple substitution variables. These reports can be converted to use one substitution variable and many expressions for easier maintenance.

Consider the following model and its current list of substitution variables.

These substitution variables are used in all of the company reports that show data by month, quarter, or year. Below is a report of Salaries by Department. You can see the use of 17 substitution variables in the report.

To convert this report and any other reports to use a single substitution variable, first, decide which member should be the starting point from which you can create expressions. For example, if you start with the first month of your fiscal year, then you can use Sibling, Lead, or Lag to offset at the same level as other months. Or go back one level with Parent and use Lead or Lag to offset to quarters, or go back two levels with Parent. Parent and use Lead or Lag to offset to years. Or you can start with the year and use Child expressions to get to the quarters and months.

For this example, we will base all the time entries in the report on the first month of the year. A Power User or Contributor user must create the substitution variable.

1. Define the following substitution variable for the model Sample Salaries and the dimension Time: FirstMth = Jan 2015
   Note:

   All the previous substitution variables defined for this model still exist (those rows are hidden in the screenshot below); you are adding a new one at the bottom. After you convert all your reports over to using just one substitution variable, you can delete all the extraneous substitution variables.
   
2. Open the report and go into Design mode.
3. Select all the cells in the worksheet, then click Design Manager. Scroll down to the Time dimension.
4. Select the @Month1@ variable, which is currently set to Jan 2015.
5. Click the Member Select button and click Select Member.
   
6. Scroll to find the new variable that you created, FirstMth. Select it and click Select.
   
   The New Value column now shows the @FirstMth@ substitution variable. In grey, Jan 2015 is displayed.
   
7. Click Update. The report now displays UPDATED in place of the data cells and @FirstMth@ in place of Jan 2015.
8. With all the cells in the worksheet still selected, click Design Manager again.
9. Scroll down to the Time dimension and select @Month2@.
10. Repeat steps 5-6 to replace @Month2@ with @FirstMth@.
11. Click the Member Select button and click Expression.
12. Click Add Function and select Lead.
13. Keep the default Offset of 1, and click Update.
    
    The New Value column now shows the @FirstMth@.Lead(1) substitution variable plus expression. In grey, Feb 2015 is displayed.
    
14. Repeat steps 5-6 and 11-12 for the rest of the months, adding one more to the Offset for each subsequent month. Now all the months are expressed about FirstMth.
    
15. Click Update to insert these changes into the report.
16. With all the cells in the worksheet still selected, click Design Manager again.
17. Scroll down to the Time dimension and select @Qtr1@.
18. Repeat steps 5-6 to replace @Qtr1@ with @FirstMth@.
19. Click the Member Select button and click Expression.
20. Click Add Function and select Parent.
21. Click Update.
    
22. Select @Qtr2@.
23. Repeat steps 5-6 to replace @Qtr2@ with @FirstMth@.
24. Click the Member Select button and click Expression.
25. Click Add Function and select Parent.
26. Click Add Function and select Lead with Offset of 1.
    
27. Click Update.
28. Repeat for the remaining quarters, by creating the following chain expressions for them:
    1. @FirstMth@.Parent.Lead(2) = Q3 2015
    2. @FirstMth@.Parent.Lead(3) = Q4 2015
       
29. Click Update to insert these changes into the report.
30. With all the cells in the worksheet still selected, click Design Manager again.
31. Scroll down to the Time dimension and select @Year@.
32. Repeat steps 5-6 to replace @Year@ with @FirstMth@.
33. Click the Member Select button and click Expression.
34. Click Add Function and select Parent.
35. Again, click Add Function and select Parent.
36. Click Update.
    
37. Click Update to insert these changes into the report.
    
38. Now save the report design by clicking Save or Save As. Consider changing the name to preserve the original report with multiple substitution variables until you are confident that the new report is accurate.
39. Compare the data to your original report to ensure it is accurate.
40. Click Run to go into Run mode.
    When running the report, you can click the Substitution Variables button on the ribbon to change the FirstMth variable to the first month of any year.
    

Design Manager

Design Manager offers advanced, more granular reporting capabilities. You can specify data intersections and dimension members from one or more models. You can also select POV options.

Overview of Cell Types in Reports

In this example, select the 2018 cell and then click Design Manager. It displays the name of the dimension (Time) and the member currently selected (2018).

You can select a new value for Time by clicking the member select icon. Select 2017 and click Update to replace 2018 with 2017 in the spreadsheet.

All row and column level dimensions and dimension members are considered to be "member" cells.

If you select a data intersection such as 977,500 and click Design Manager, the associated dimensions and dimension members are displayed. If the dimension is on the page level (above the rows and columns), you see POV in the Exclude POV column. See Identifying POV Dimensions for more information.

The cell is referred to as a "data" cell. Data cells represent data intersections, which are formed from the intersection of all dimensions in the model. If you select a new value for any of the dimensions, the data cell is automatically updated based on selections once you click the Update button on the Design Manager interface.

Select a blank cell and click Design Manager. Notice how the interface is updated to give you the option to create a data cell, dimension member cell, or POV cell.

You can also report off of any one model or multiple models available to you in the Model list box. For example, if you want a report with 3 data intersections from Model A, 1 data intersection from Model B, and 5 data intersections from Model C, Design Manager gives you the flexibility to do so.

Description of Fields

• Data - data intersection
• Member - Dimension Member
• POV - Option to Update Report Data

Data - Data Intersection

Report on the combination of two or more dimension members. Select members from a dimension intersection to populate the value within the report. You can pull data from an intersection for any model to which you have access.

When Data is selected, the Model list box is available along with the dimensions that belong to the selected model as shown below.

You can select to exclude point of view (POV) dimensions in reports. For example, if your report has a page-level member for Company (My Cycles Company) and you want to show one cell of Total Corporate (My Holding Group) as a comparison, you can add the Total Corporate cell and exclude (or override) the Company POV for that cell.

Member - Dimension Member

Display a specific member name from a dimension. This replaces the cell with the name of the selected dimension member. When Member is selected, the Model and Dimension list boxes are available. Select the model from which you want to populate the report with dimension member information. Select the dimension that belongs to the selected model to populate your report.

When using POV and substitution variables, the member names will change based on the selected context.

POV - Option to Update Report Data

Make POV (point of view) selections based on a model and dimension. A POV is a method to allows users to select a particular member from a list box and then see all data in the report updated for that member. For example, if you want the user to be able to view different accounts, then put the Account dimension on the page level of the report, and make it a POV cell. As a result, data displayed in the report is controlled to display the selected Account dimension member. When POV is selected, the Model and Dimension list boxes are available.

Attributes

Select this checkbox to display the attributes that are available in the selected model. Uncheck this checkbox to hide the attributes from the Design Manager.

Filtering Members

Filtering member options is helpful when updating an existing report, finding specific report dimension members, and previewing members you might filter. Here is a breakdown of each functionality:

• Filter Member
• Preview Filter Member

Filter Member

To use Member, open a report in design mode by selecting the Report task and the Design subtask.

Select the entire spreadsheet as shown below and click Design Manager.

Select a dimension member to filter. In this case, Jan 17 is selected (shown below).

Now, select Filter Member.

The result is that the Jan 17 member filtered is selected in the design manager and is also highlighted within the spreadsheet as shown below.

If you would like to make any changes to the report and you want to select only a specific member from the report design and then make the change, in this example Jan-17 the Filter Member option is very helpful.

Preview Filter Member

To use Preview Filter Member, open a report in design mode by selecting the Report task and the Design subtask. Select the entire spreadsheet and click Design Manager. Select a dimension member you want to preview before filtering. In this case, the Temp Mountain Bikes is selected along with the Preview Filter Member option as shown below.

The result is that the cell range is displayed as shown below. This way you know exactly where the member exists within the report.

Identifying POV Dimensions

You often use the Design Manager to pinpoint what dimension intersections are being used for the data cells. Design Manager clearly shows how POV cells are being used.

In a report, select one or more data cells and then click Design Manager. The dimensions on the page axis (Company, Product, Project, and Measures) indicate the POV. (In the report below, Region is an attribute.)

In another version of that report, Time has been moved to the page axis as a POV. The report displays Q4 2014, Budget and Actual.

We want the Actual column to use Exclude POV so that it always displays Actual for the whole year.

1. Select the cells in the Actual column and click Design Manager.
2. Check the Exclude POV box and check the Q4 2014 box next to the Time dimension.
3. Click the member select icon and change the dimension member to 2014 then click Select.
   
4. Click Update.
5. Save the report changes.
6. Select Run.
   Now, no matter which member the user selects for the Time POV, the Actual column reflects the whole year.

When the Contributor User uses the Design Manager, it becomes more clear that some cells are using the POV and some are not.

1. From Report Design, select all the data cells of interest, then click Design Manager.
2. Under the Time dimension, note that the selected cells are using both Exclude POV 2014 and a regular POV Q4 2014.
   
3. If you do not remember which cells are using which member, select one of the rows, then select Preview Filter Member.

The cells are highlighted for you until you click Cancel.

Similarly, if a user is running the report and wants to see what dimensions are being used in the report, they can select Metadata Viewer from the More menu. Then use Preview Filter Member to see which cells are using 2014 and which cells are using Q4 2014.

Excluding POV Dimensions in Reports

You can select to exclude point of view (POV) dimensions in reports. For example, in a model, if the price is maintained at the region level (Region dimension) versus the customer level (Customer dimension), you can exclude the Customer dimension as a POV in the report.

1. While in Report Design mode, select all the data in the report, then click Design Manager.
   
2. On the Design Manager window, click the Exclude POV checkbox.
3. Next to Customer, click the Exclude POV checkbox.
   
4. Click Update.
5. Save the report changes.
6. Run the report. When you change the POV for Customer and Refresh the data, no change is reflected. Only your changes to the Region or Scenario dimension affect the data.

Shared POV Dimensions Across Models in a Report

It is possible to design a report with a single Point of View (POV) cell that influences the data visible across multiple models represented by the data. This feature provides flexibility by reducing the need for multiple dimension selections when creating a consolidated report. A consolidated report amalgamates data from multiple models.

Consider two models, Sales Rep, and Sales Forecast, set up for an enterprise. The objective is to generate a consolidated report from both models. Some dimensions, such as Scenario and Region, are common between the reports. Despite the models sharing common dimensions, individual selections of dimensions from each model are necessary to create a consolidated report. Shared POVs enable the configuration of a single POV dimension for common dimensions within a report.

Note:

A common POV among these two reports is the Scenario.

In Practice: Setting up Scenario as a Shared POV

1. Select Report Design.
2. Select the first Scenario POV cell and click Design Manager. The Design Manager tells you that the cell represents the model Sales Forecast.
3. Use the horizontal scrollbar under Share POV to locate and select the checkbox for Sales Rep.
   
4. Click Update.
5. Next, delete the row containing the second Scenario POV cell.
   
6. Save the report design, then Run it.
7. When you change the Scenario selection and click Refresh, the data for both reports is updated.

Refreshing the Report Data on a POV Change

With Refresh on POV change, you can specify if you want to refresh the report each time you change POV to see report data. Suppose you set Refresh on POV change to Yes. When you run a report, choose a POV to view the report, the report data contains refreshed data. By default, this property is set to No.

While running the report you can change the POV dimension member and if this property is set to Yes then the report will automatically refresh once you select any POV dimension member. In a sample report, if you have the Time dimension as a POV dimension and you select 2015 from the Time dimension then the report will be refreshed and the data for 2015 will be displayed in the report.

Format Manager

Apply formatting options and lock cells. For example, display reports in various amount formats (such as thousands, millions, and billions). Select a cell or range of cells and click Format Manager to launch the Format Manager dialog page.

A description of each of the fields on the Format Manager dialog page is provided below:

• Locked - Select Yes to lock a selected cell or cell range to prevent modification. For example, you want users to input only certain portions of report data.
• Amount Format - Select to format numeric cells in Tens, Hundreds, Thousands, or Millions. Amount Format applies to reporting only.
• Applies To - The cell range selected.
• View/Select Range - Change the cell or cell range applied for the format you are changing.
  
• Update - Apply Format Manager selections to the cell or cell range selected. Save the report and then run it to display the selected cells in the appropriate format.

Note:

Locked and Amount Format properties are applied for reporting purposes only. When a report with locked and formatted cells is run, you will not be able to modify the data in these cells.

Locking and Unlocking Cells in a Report

You can lock data cells in a report to prevent modification by users. For example, you want users to input only certain portions of report data.

Note:

Users only have the option to save data changes if the Enable Save property is set to Calculation Model or All Models.

In Practice

1. Select the data cells, then click Format Manager from the menu ribbon.
2. Specify Yes for Locked, and then click Update.
   
3. Alternatively, you can click the cell selector icon to specify the range of cells to lock.
   
4. Select the cells, then click the icon to Set as the New Range.
   
5. In the Format Manager box, click Update.

Note:

If Save on Model is not enabled, the report cells will unlocked to allow data input.

Selections	Image	Results
Enable Save = None Calculation Model = None Selected Calculation on Save = None Selected		No data is saved. No Calculation is executed.
Enable Save = None Calculation Model = Model Selected Calculation on Save = Calculation Selected		No data is saved. The calculation is executed.
Enable Save = Calculation Model Selected Calculation Model = Model Selected Calculation on Save = None Selected		Data saved on Calculation model. No Calculation is executed.
Enable Save = Calculation Model Selected Calculation Model = Model Selected Calculation on Save = Calculation Selected		Data saved on Calculation model. The calculation is executed.
Enable Save = All Models Selected Calculation Model = Model Selected Calculation on Save = Calculation Selected		Data is saved on all models. The calculation is executed.
Enable Save = All Models Calculation Model = None Selected Calculation on Save = None Selected		Data is saved on all models. No Calculation is executed.

Applying Scale to Numbers

You can use scaling in SpotlightXL to see numeric values in the tens, hundreds, thousands, and so on. Displaying numbers in a smaller form can make a report appear cleaner and easier to understand. For example, on the Report Design page for the Annual Sales Revenue report, the Thousands scaling Amount Format is applied and then saved.

When you run the report, numbers are divided by 1000 and displayed in the worksheet. The source data is not changed.

Save / Save As

The Save allows you to save the report. When you select 'Save As' from the Report Design page to save the report, all Report Properties are reset to default settings except for 'created' and 'modified' properties. Update the Report Properties accordingly. Define group access for the new report. The 'Created On' and 'Created By' fields are automatically updated by the system based on the user initiating the 'Save As' action. The 'Modified On' and 'Modified By' fields remain blank until modifications are made to the report.

If 'Save As' is chosen from the Report Design Properties page, most Report Properties, such as Design Manager, Format Manager, and Point of View (POV) settings, will remain the same as in the source report, except for group access, 'created,' and 'modified' properties, which can be modified. Define group access for the new report. The 'Created On' and 'Created By' fields are updated automatically by the system. The 'Modified On' and 'Modified By' fields will be empty until changes are made to the report content.

In Practice: Perform a Save As for a Report in SpotlightXL with Default Selections

1. Select the Report task and the Design subtask.
2. Select Save or Save As.
3. Enter a name for the report and select Save.

In Practice: Perform a Save As for a Report in SpotlightXL with Source Selections

1. Select the Report task and the Design subtask.
2. Click Properties.
3. Select Save or Save As.
4. Enter a name for the report and select Save.

More - Capture Data/Capture Entire Workbook

This action captures metadata for report intersections and converts the report to an Excel Report. You can Capture Data from either Run mode or Design mode. Click here, for more details.

Calculation

The Calculation option provides a quick way to adjust underlying Calculations and associated Formulas to achieve desired results whether in Report/View Design or Run mode. For example, let's say you have a View where you forecast units and multiply the price to result in sales. Now, you can quickly change a Calculation and associated Formula to recalculate forecasted sales to provide another sales scenario.

Report Properties

Clicking the Properties option allows you to save the report to a specified model, select groups with access to the report, and define Point of View (POV) selections and filter criteria. Below is a description of each option within the Properties section:

Description

Enter a brief description of the report.

Display

Display dimension members by label (Display Label ) or Code. This functionality provides the flexibility to both analyze and report the data based on Code or based on Display Labels for the dimensions in the Model.

Display Label Example

Display Code Example

Important!

This functionality eliminates the need for lookups. However, you can continue to use lookups. If you want to use Display Label functionality for a model that has been sourced from Structured Planning, Consolidation, and Reporting applications with lookups, we recommend that you recreate the model.

For models sourced from Structured Planning, Consolidation, and Reporting applications, the Display Label defaults to the dimension member code name. For example, if a dimension member is named Executive and the code is Exec, the Display Label would be Exec - Executive. If no name exists for the dimension member, the dimension member code is displayed. You can set the Display Label for each report or view.

See Also: Customizing the Display Label

Hide Gridlines

Yes - Hide gridlines from display in the report.

No - Show gridlines in the report which is the default.

Hide Headers

Yes - Hide header lines from display in the report.

No - Allow header lines to display in the report, which is the default.

Without and With Headers

Enable Save

Save data for all models available in the report and run the calculation without saving the data.

None - This is the default option, when selected no data will be saved.

Calculation Model - This option saves the data of the model where the calculation is running. This means that when you save the report, the calculation is executed automatically as part of the save function.

All Models - This option saves the data for all models available in the report or a specific model in the report.

Click to view a drop-down that provides the possible selections:

Calculation Model - Select a model to save the report to. You can display data from multiple models on a single report, if you would like to make changes and save them back to a model, only one model can be selected.

Calculation on Save - Include a Calculation, which provides the order in which the calculations take place. The calculation will be executed when you click Save. The calculation name should be provided in the available property. This property is dependent upon the Enable Save property when creating a model. Enable Save must be set to Yes to execute this calculation.

By default, Calculations run in the foreground, and users must wait until the calculation is complete before continuing to use the report. Power Users can set an application-wide setting to run calculations in the background for all views and reports that trigger calculations. Those calculations must have been created with the option Run in Background set to Yes. After the calculation has completed its processing, an email is automatically sent to the user that triggered it.

Our recommendation is to set this option to Yes.

In Practice

1. Select Manage, Application Administration, Application Settings. Scroll down to the Calculation Property section. After making changes, click Save. Then logoff and log in again to see the effect of the changes.
   
2. Select Model, Calculation to ensure that the calculation was set up with Run in Background set to Yes.
   
3. You can see a list of all calculations and their current background setting by choosing Model, Setup. Select the model, then scroll down to the Calculations section.
   
4. Verify that the calculation is set up to run when the user clicks Save from a view or report by reviewing the view or report properties.
   
5. When the user clicks Save in the view or report, they see the following message:

Note:

The view or report does not refresh the data automatically after the user clicks Save.

Refresh on POV Change

Yes - automatically update the report based on point-of-view changes.

No - the report will not be updated when point-of-view changes are made.

Suppress Rows

None - This is the default. Blank and Zero rows will appear in the report.

Zero - Rows with zero values will be hidden in the report.

Blank - Rows that contain no data (blank) will be hidden in the report.

Both - Zero & Blank - Both Blank and Zero rows will be hidden in the report.

Suppress Columns

None - This is the default. Blank and Zero columns will appear in the report.

Zero - Columns with zero values will be hidden in the report.

Blank - Columns that contain no data (blank) will be hidden in the report.

Both - Zero & Blank - Both Blank and Zero columns will be hidden in the report.

Created On

The date the view was created. The system populates this cell.

Created By

The username of the user who created the view. The system populates this cell.

Modified On

The date on which the view was last modified. The system populates this cell.

Modified By

The username of the user who last modified the view. The system populates this cell.

Group

The name of the user group you want to have access to the report. You can assign more than one group access to the report. This field is optional. You do not need to specify a group for the newly created report. If no group is specified, the report can only be viewed by you.

You can add group access to only the group(s) for which you are a member. For example, if you are a member of Group “Finance” but not “Operations”, you can assign Group “Finance” and you will receive a validation error if you try to set access to “Operations”. The list of available groups is the complete list of groups to which you have access and also have access to the model for which you are trying to save a report.

POV

Select POV to define a dimension to control the data displayed in a report. POV is a user-specific method of modifying settings to desired dimension members. POVs apply to all objects on a report (for example, grids and charts).

Filter

Select a dimension member based on the POV dimension selected.

Variable Name

The name of the substitution variable applied to the report.

Model

The name of the model the report is associated with.

Dimension

The dimension member will be replaced with the entered/selected substitution variable.

Enabling Menu Items in Views and Reports: Save Data and Breakback

For more details, click here.

Adding Groupings to Reports

Rows and columns can be grouped and saved for a report from SpotlightXL. Multiple groupings can be defined for a single report. When the reports defined in SpotlightXL are accessed from Spotlight (web version), the grouping defined on the report in SpotlightXL is retained. This functionality is available for all reports created for any model.

In Practice

1. Access SpotlightXL.
2. Access a report (Report > Run) that you want to add a group to.
3. Select Design (under Run).
4. Highlight the rows or columns you want to group as shown below.
   
5. Click the Data tab and select Group.
   
6. Return to the SpotlightXL tab.
7. Save the report.
8. Run the report. Notice the grouping is applied.
   
9. Login to Spotlight. When you run the same report, the grouping is applied.
   

Adding a Chart to a Report

1. While designing your report, click the Insert menu tab.
   
2. In the Chart area of the menu bar, click the bar chart, then select a 2-D chart.
   Note:

   You can have up to 4 charts per report. See Chart Formatting and Types for other types of charts supported.
3. Drag and drop the chart to position it within the spreadsheet. Format the report as desired. Specify the data range, legend, and title.
   
4. Click the SpotlightXL tab and click Save. Select Data from the subtask selections to return to analyzing the data.