Copying Spotlight Formulas from an Excel View or Report to PowerPoint

Overview

Users who have SpotlightXL views and reports can copy all or part of them to PowerPoint. There are two methods:

• Copy Formulas method, available in views and reports

• Design With, PowerPoint Report method, available in views only

Copying Spotlight Formulas to PowerPoint using Copy Formulas

1. Begin by opening and running the View or Report in SpotlightXL containing the data you want to copy to PowerPoint. Views are run from the Analyze menu. Reports are run from the Report menu.

   

2. Highlight the portion of the View or Report that you want to copy to PowerPoint.

   

3. Click the More menu, then Copy Formulas.

   Note:

   Spotlight uses the power of user-defined functions to connect Excel, PowerPoint, and Word to Dynamic Planning. All data and metadata from Dynamic Planning are represented in formulas, just as they are with Excel Reporting.

   

4. Open PowerPoint and select the slide that you want to paste the data into.

5. On the SpotlightPPT ribbon, click Paste Formulas.

   

   The formulas are pasted in and semi-grouped together. Select, move, and resize the objects however you want them on the slide.

   

6. On the SpotlightPPT ribbon, click Refresh. The #REFRESH data cells are then filled in.

   

   If you have multiple slides with data from Dynamic Planning, you can use the drop-down on the Refresh menu to select Refresh All Open Reports. This command refreshes the data in all slides in all open presentations. This is similar to how reports are refreshed in Excel Reports.

Copying Spotlight Formulas to PowerPoint using Design With, PowerPoint Report

Using the Design With, PowerPoint Report method copies all metadata and data from the view into PowerPoint.

In Practice

1. Begin by opening and running the View in SpotlightXL containing the data you want to copy to PowerPoint. Views are run from the Analyze menu.

2. On the Data menu, select Design With, PowerPoint Report.

   

   While the data is being copied, you see a status bar, such as this one.

   

3. When the operation is complete, PowerPoint displays the metadata and data points for the entire view. It may exceed the limits of the slide if you have a lot of cells in the view. Click SpotlightPPT on the menu bar to see the Spotlight for Office menu. Your menu options will reflect whether you are a Power, Contributor, or Reviewer user.

   

4. Use your mouse to select and drag and drop elements to lay out the report.

   Note:

   If you ungroup the cells in order to move them, please try to align them reasonably close to a grid structure. Cells that are in a grid structure work best if you later want them to be used as the range of data in a chart.

5. To view the data, click Refresh.

   

Copying Spotlight Formulas from an Excel View or Report to Word

Users who have SpotlightXL views and reports can copy all or part of them to Word. There are two methods:

• Copy Formulas method, available in views and reports

• Design With, Word Report method, available in views only

Copying Spotlight Formulas to Word using Copy Formulas

1. Begin by opening and running the View or Report in SpotlightXL containing the data you want to copy to Word. Views are run from the Analyze menu. Reports are run from the Report menu.

   

2. Highlight the portion of the View or Report that you want to copy to Word.

   

3. Click the More menu, then Copy Formulas.

   Note:

   Spotlight uses the power of user-defined functions to connect Excel, PowerPoint, and Word to Dynamic Planning. All data and metadata from Dynamic Planning are represented in formulas, just as they are with Excel Reporting.

   

4. Open Word and select the page that you want to paste the data into.

5. On the SpotlightWord ribbon, click Paste Formulas.

   

   The formulas are pasted into a table in the document.

   

6. On the SpotlightWord ribbon, click Refresh. The #REFRESH data cells are then filled in.

   

   If you have multiple slides with data from Dynamic Planning, you can use the drop-down on the Refresh menu to select Refresh All Open Reports. This command refreshes the data in all pages in all open documents. This is similar to how reports are refreshed in Excel Reports.

Copying Spotlight Formulas to Word using Design With, Word Report

Using the Design With, Word Report method copies all metadata and data from the view into Word.

In Practice

1. Begin by opening and running the View in SpotlightXL containing the data you want to copy to Word. Views are run from the Analyze menu.

2. On the Data menu, select Design With, Word Report.

   

3. When the operation is complete, the metadata and data points for the entire view are placed into a table within the margins of the document. It may be a huge table if your view is large. Click SpotlightWord on the menu bar to see the Spotlight for Office menu. Your menu options will reflect whether you are a Power, Contributor, or Reviewer user.

   

4. Use Word table commands to select, move, delete, and drag and drop elements to lay out the report.

5. To view the data, click Refresh.

   

Using Spotlight for Office in PowerPoint

The menu ribbon changes based on what you are doing with the slides and data. Here are three variations of the SpotlightPPT menu.

Once you have copied data from a SpotlightXL View or Report into PowerPoint, you are ready to work with the Spotlight menu ribbon.

Understanding the Spotlight Menu Ribbon

Refresh, Refresh All Open Reports - Connects to the Dynamic Planning data source and retrieves the data value for each cell on the current page. Each cell is defined by a member intersection, which you can view with the Design Manager or Metadata Manager. You can update all data for all pages in all open documents by selecting Refresh All Open Reports.

Design Manager - Opens the Design Manager box and displays the member intersection or dimension information for the current cell selected. If the current cell is a data item, Design Manager displays the model, and all the dimensions and members that describe the data. If the current cell is a dimension or member name, Design Manager displays the model, dimension, and member name.

In the Design Manager, you can change all non-POV dimension members associated with the current cell. Click the Member Select icon next to the current value, select a new member, and click Select.

Number Format - Opens the Format Cells box and lets to you select a formatting style for the selected data cells containing numbers, similar to the options in Excel. To change the format, select the category of number format you want, specify options, and click OK.

Copy Formulas - Copies the metadata and data formulas associated with the selected cell(s) to the Clipboard. These formulas can then be pasted into another page or document in Word, into SpotlightXL, or into SpotlightPPT.

Paste Formulas - Pastes the metadata and data formulas from the Clipboard to the current page in SpotlightWord.

Select Data - Specifies the range of data items to be used in a chart.

Edit Data in Excel - Allows you to use the Excel interface to paste in the cells from Spotlight that will be used in a chart.

Insert - Opens a file selection box where you can select an Excel workbook to embed into the document. If the workbook contains Spotlight formulas, you can Refresh the data.

Once a workbook is embedded, the Edit menu item appears.

Edit - Opens Excel where you can edit an embedded workbook.

This menu item appears only after you have Inserted an embedded workbook.

Publish - Opens a list box of report templates that you can use to publish the document to the Dynamic Planning cloud.

Setting the Value of a Substitution Variable in PowerPoint

A Substitution Variable menu item is available on the SpotlightPPT menu ribbon in PowerPoint to make it possible for users to change the value of substitution variables while running a PowerPoint Report.

Power or Contributor users can set up the substitution variables and expressions in an Excel Report and then convert to a PowerPoint Report using Copy Formulas from Excel and Paste Formulas from PowerPoint. The process is the same as for formatted reports.

In Practice

1. Power or Contributor users create one or more substitution variables in the model.

2. Power or Contributor users create one or more Excel Reports and inserts the substitution variables. See Using Substitution Variables and Expressions with Excel Reports for steps to do this.

3. Power or Contributor users optionally specify substitution variable expressions in the design of the report. See Using Substitution Variables with Expressions.

   

4. Power or Contributor users copy the Excel Reports formulas to Spotlight PowerPoint. Here's how:

   1. In the Excel Report, select all the cells that you want copied into PowerPoint.

   2. Select More menu, Copy Formulas.

   3. Open PowerPoint and display the slide where the report will appear.

   4. Select the SpotlightPPT menu.

   5. Click Paste Formulas.

      

5. Click Refresh. The data is retrieved and the menu expands to provide additional options, including the Substitution Variables button.

   

6. With the cursor anywhere in the slide, click Substitution Variables. The Substitution Variables box appears. It shows the name of the variable and the current value. In this case, there is only one variable in use, @CurMth@, because the other months and the year are derived from expressions.

   

7. Click the Member Select icon.

8. From the Member Selection box, select the month that you want to see in the report.

   

9. Click Select.

10. Click Refresh to refresh the report. The report now displays data for Jul-17.

    Using substitution variables, 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 Contributor or Power user to update the substitution variable value permanently.

    

Using Spotlight for Office in Word

The menu ribbon changes based on what you are doing with the document and data. Here are three variations of the SpotlightWord menu.

Once you have copied data from a SpotlightXL View or Report into Word, you are ready to work with the Spotlight menu ribbon.

Understanding the Spotlight Menu Ribbon

Refresh, Refresh All Open Reports - Connects to the Dynamic Planning data source and retrieves the data value for each cell on the current page. Each cell is defined by a member intersection, which you can view with the Design Manager or Metadata Manager. You can update all data for all pages in all open documents by selecting Refresh All Open Reports.

Design Manager - Opens the Design Manager box and displays the member intersection or dimension information for the current cell selected. If the current cell is a data item, Design Manager displays the model, and all the dimensions and members that describe the data. If the current cell is a dimension or member name, Design Manager displays the model, dimension, and member name.

In the Design Manager, you can change all non-POV dimension members associated with the current cell. Click the Member Select icon next to the current value, select a new member, and click Select.

Number Format - Opens the Format Cells box and lets to you select a formatting style for the selected data cells containing numbers, similar to the options in Excel. To change the format, select the category of number format you want, specify options, and click OK.

Copy Formulas - Copies the metadata and data formulas associated with the selected cell(s) to the Clipboard. These formulas can then be pasted into another page or document in Word, into SpotlightXL, or into SpotlightPPT.

Paste Formulas - Pastes the metadata and data formulas from the Clipboard to the current page in SpotlightWord.

Select Data - Specifies the range of data items to be used in a chart.

Edit Data in Excel - Allows you to use the Excel interface to paste in the cells from Spotlight that will be used in a chart.

Insert - Opens a file selection box where you can select an Excel workbook to embed into the document. If the workbook contains Spotlight formulas, you can Refresh the data.

Once a workbook is embedded, the Edit menu item appears.

Edit - Opens Excel where you can edit an embedded workbook.

This menu item appears only after you have Inserted an embedded workbook.

Publish - Opens a list box of report templates that you can use to publish the document to the Dynamic Planning cloud.