Contents x
    Office Reporting in SpotlightXL
    • 18 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Contents

    Office Reporting in SpotlightXL

    • 18 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Article summary

    Office Reports provides users the optional ability to publish reports built with Excel, Word, or PowerPoint to the Dynamic Planning cloud. The ability to publish and store reports in the cloud is a collaboration feature to make it easier to store your most common monthly reports in one place. Published reports can be downloaded by other users.

    Setup

    With the Setup subtask, you can create a template that acts as a placeholder for each report that will be published and made accessible to users. One template can be used for either an Excel Report, Word Report, or PowerPoint Report. Each template reserves a spot for just one report in the Dynamic Planning cloud.

    PropertyValueDescription

    Name

    Select from the drop-down for the type of template to create. Or select an existing template to modify it.

    This uniform resource identifier (URI) connects Dynamic Planning and Structured Planning, Consolidation and Reporting.

    Description

    Optional

    Enter a text description of the report.

    Created On

    This cell is populated by the system.

    The date the template was created.

    Created By

    This cell is populated by the system.

    The username of the user who created the template.

    Modified On

    This cell is populated by the system.

    The date in which the template was last modified.

    Modified By

    This cell is populated by the system.

    The username of the user who last modified the template.

    Group

    Access

    Select the group that you want to specify access for from the drop-down.

    Under Access, choose from Publish or Download. Groups with Publish access can both upload and publish reports, and download reports. Groups with Download access can only download reports.

    For step-by-step instructions, see Publish the Report.


    One Spotlight Login for all Office Products

    To login to Spotlight for Office, you can begin from Excel, PowerPoint, or Word. Once you are logged in from one of the Office applications, you are logged in on all three of them. Similarly, if you logoff one of the Office applications, you are logged off on all three of them.

    1. Open Microsoft Excel or PowerPoint or Word.

    2. In Excel, click the SpotlightXL tab. In Word, click the SpotlightWord tab. In PowerPoint, click the SpotlightPPT tab.

      ModelingImagesSpotlightOfficeLoginXL.png

      ModelingImagesSpotlightOfficeLoginWord.png

      ModelingImagesSpotlightOfficeLoginPPT.png

    3. Click Login. You must have a document open before you click Login or you will see an error message.

    4. Enter the URL to connect to your tenant (as given to you by Planful Support or your Power user).

    5. Enter your Username and Password.

      • Optionally, click the Reset Password checkbox to change your password.

      • Enter a new password in the New Password and Confirm Password fields and click Login.

      • Optionally, select the Remember Me checkbox to retain your Username for future logins.

    1. Click Login.

    2. If requested to do so, select your application from the drop-down, then click Select.


    Purpose of Spotlight for Office

    Spotlight's reporting capabilities extend to Microsoft PowerPoint and Microsoft Word in addition to Microsoft Excel with Spotlight for Office. You can use Excel, PowerPoint, and Word to retrieve data from Planful Dynamic Planning or Structured Planning, Consolidation, and Reporting (PCR) applications. Within the Office applications, you can take advantage of all the native formatting capabilities that Microsoft offers for formatting and presenting the data and prepare board-ready reporting packages.

    Spotlight for Office is available for all Spotlight and Dynamic Planning users.

    Benefits

    • Ability to access both Dynamic Planning and PCR data from Excel, PowerPoint, and Word.

    • Ability to use the full power of Microsoft Office for reporting.

    • Ability to create board books and presentations with complete control over the format.

    • Ability to refresh the data points in Excel, PowerPoint, or Word with one click.

    • Eliminates the need to copy and paste data points into your presentations and reports.

    • Ability to place the Spotlight formula once and then Refresh the data as needed.

    • Reduces financial reporting processing time.

    Spotlight for Office 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 for each cell copied into the Office application. This is similar to the way Excel Reporting uses Spotlight formulas for metadata and data. With a live connection to the model, Office users can just click Refresh to update the data.

    Note:
    To access PCR data using Spotlight for Office, create a model with direct access to the Financial Reporting area. Then use that model as the data source for the reporting capabilities in Spotlight for Office. For more information, see Using Direct Access to PCR Financial Reporting.


    Embedding Excel Workbooks in PowerPoint and Word
    • Embedding a workbook in PowerPoint or Word gives you the ability to use Excel formulas and formatting within the object in PowerPoint or Word while still having access to the latest data with the click of the Refresh button. 
    • With the Spotlight for Office Add-In, you can refer the data directly in PowerPoint and Word, however, you may also have a need to use Excel formulas and formatting. This is accomplished by embedding the workbook.
    • To embed the Excel workbook, use the Excel Reporting feature in SpotlightXL to lay out the report according to your needs, then use Spotlight for Office to insert it into a presentation or document.

    Best Practice Recommendations when Embedding an Excel Workbook

    • Have only one worksheet in the workbook. That worksheet must use Excel Reporting formulas to display and retrieve data and dimensions.

    • Have only a few columns and rows in the workbook.

    • Have data references from only one model.

    For information on creating Excel Reports, see Creating Excel Reports.

    How to Embed a Workbook in PowerPoint

    1. In SpotlightXL, create an Excel Report and save the workbook.

    2. Start PowerPoint or Word. In this example, PowerPoint is opened.

    3. Click SpotlightPPT.

    4. On the SpotlightPPT or SpotlightWord menu, select Insert.

    5. From the Open dialog box, find and select the workbook and click Open. The report from the workbook appears in a box.

    6. To add formulas or formatting, click the Edit button. The selected document opens in Excel, giving you the ability to utilize the power of Excel.


      To return to PowerPoint or Word, close the Excel window when modifications are complete. click Refresh.

      Whenever you want to get the latest data, click Refresh. When you click Refresh, the cells with references to Spotlight formulas (Excel Reporting cells) are refreshed from the model, and the cells with regular Excel formulas are recalculated. Any formatting that is applied on the embedded sheet is retained.

    How to Add a POV (Point of View)

    1. First insert the workbook into the PowerPoint presentation or Word document. The workbook should contain Excel Reporting formulas from only one model.

      Notice that this report has no POVs.

    2. Click outside the box containing the embedded workbook, then click Design Manager.

      1. Click the POV button.

      2. Select the model and dimension from the list boxes. The model must be the same model that is used in the embedded workbook.

      3. Select a member of the dimension to display first. By default, the root of the dimension is displayed.

      4. Click Update to insert the POV into the document.

    3. The POV is inserted near the top of the embedded worksheet. Use the mouse to place the POV above or to the left of the worksheet. Use the native formatting options in PowerPoint or Word to set the font and size.

    4. Double-click the POV to open the member selection box. Select a department from the Department dimension and click Select.


    5. Click Refresh. Notice that the data in the embedded workbook changes to display the data for the selected member.

    How to Embed a Workbook in Word

    1. Click Insert and select the workbook you want to embed. The worksheet appears with a box around it.


    2. Click Edit to manipulate the data formatting or add formulas.

    3. Use the word processing features inside Word to add headings, commentary, or callouts to the data for a polished summary.

    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

    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.

      ModelingImagesSpotlightOfficeViewMoreCopyFormulas.png

    4. Open PowerPoint and select the slide that you want to paste the data into.
    5. On the SpotlightPPT ribbon, click Paste Formulas.
      ModelingImagesSpotlightOfficePPTPasteFormulas.png

    6. The formulas are pasted in and semi-grouped together. Select, move, and resize the objects however you want them on the slide.
    7. 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, PowerPointReport.
    3. While the data is being copied, you see a status bar, such as this one.
      ModelingImagesSpotlightOfficePPTDesignPPTReportStatus.png

    4. 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.
    5. Use your mouse to select and drag and drop elements to lay out the report.
    6. 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.
      ModelingImagesSpotlightOfficeViewMoreCopyFormulas1.png

      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.
      ModelingImagesSpotlightOfficeWordPasteFormulas.png

    6. The formulas are pasted into a table in the document.
    7. On the SpotlightWord ribbon, click Refresh. The #REFRESH data cells are then filled in.
    8. 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 subtask, select Design With > Word Report.
      ModelingImagesSpotlightOfficeWordDesignWordReport.png

    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.

    ModelingImagesSpotlightOfficePPTRibbon.png

    ModelingImagesSpotlightOfficePPTRibbon2.png

    ModelingImagesSpotlightOfficePPTRibbon3.png

    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, the 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, the 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 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 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 - This 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 them to a PowerPoint Report using Copy Formulas from Excel and Paste Formulas from PowerPoint. The process is the same as for formatted reports. See Using Substitution Variables with Expressions

    1. 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.
        ModelingImagesSpotlightOfficeSubVars2.png

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

    3. 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.
    4. Click the Member Select icon.
    5. From the Member Selection box, select the month that you want to see in the report.
    6. Click Select.
    7. Click Refresh to refresh the report.

    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.

    ModelingImagesSpotlightOfficeWordRibbon.png

    ModelingImagesSpotlightOfficeWordRibbon2.png

    ModelingImagesSpotlightOfficeWordRibbon3.png

    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.

    Creating and Formatting a Chart

    Word and PowerPoint natively offer the ability to create charts in the document or presentation. Spotlight for Office makes it possible to use cells with Spotlight formulas and then select those cells as the range to be used in the chart.

    Specifying the Range of Cells Using Copy Formulas

    PowerPoint and Word offer different methods to specify the data range for a chart. Using Copy Formulas is the most streamlined method to select Spotlight cells as the range.

    In Practice

    1. Start in SpotlightXL and run a view or report.

    2. Select the cells to be used in the chart in Word or PowerPoint.

    3. Select Copy Formulas from the More menu.

    4. Start Word or PowerPoint and select Paste Formulas from the SpotlightWord or SpotlightPPT menu.

    5. Click Refresh to see the data.

    6. Insert the chart type of your choice.

      The chart is inserted with a set of preliminary data (dummy data), which is displayed in an Excel-style sheet. Click the X to close the Excel sheet.

      ModelingImagesSpotlightOfficeChart2DummyData.png

    7. Select the rows and columns containing the data to be used in the chart. Do not select POV cells.

    8. Click Copy Formulas.

    9. Now simply select the chart and click Paste Formulas.

      ModelingImagesSpotlightOfficeChart15.png

      The chart first appears blank.


      Click Refresh to see the data.

      The chart is connected to the data source in Dynamic Planning and you can click Refresh to update the data regularly.

    Specifying the Range of Cells Using the Edit Data Window

    Word and PowerPoint provide an Edit Data window that looks like an Excel worksheet where you specify row and column headings and data to be represented in the chart. Using the Edit Data window is one way to specify the range of cells for the chart.

    In Practice

    1. Start Word or PowerPoint and open the file that contains the Spotlight for Office metadata and data items.

    2. Select the group of cells containing the rows and columns of data to be used in the chart, and click Copy Formulas from the Spotlight ribbon. Do not select POV cells.

    3. Put your cursor in a blank area of the document or slide and insert the type of chart you want. For example, in Word, select Insert, Chart, Bar, then click OK.

      The chart is inserted with a set of preliminary data (dummy data), which is displayed in an Excel-style sheet.

      ModelingImagesSpotlightOfficeChart2DummyData1.png

    4. Click the X to close the Excel sheet.

      ModelingImagesSpotlightOfficeChart3DummyData.png

    5. Select the chart, then on the SpotlightWord or SpotlightPPT ribbon, click Edit Data in Excel. The Excel-style sheet reappears, this time as a complete workbook with an Excel menu.

      ModelingImagesSpotlightOfficeChart4EditData.png

    6. Click the SpotlightXL menu item to see the SpotlightXL ribbon.

      ModelingImagesSpotlightOfficeChart5EditData.png

    7. Select all the preliminary data in the sheet and press Delete to remove it. You should now have a blank sheet. Select cell A1.

      ModelingImagesSpotlightOfficeChart6EditData.png

    8. Click Select Task and select Excel Report.

    9. Click More, then Paste Formulas.

      ModelingImagesSpotlightOfficeChart7EditData.png

      The formulas (metadata and data) that you copied from Word or PowerPoint are then copied into the sheet.

      Note:
      If nothing is pasted, you can switch back to your Word or PowerPoint window, select the cells, and Copy Formulas again. See Step 2.

      Data appears as #REFRESH. There is no need to refresh the formulas at this point.

    10. Click the X to close the Excel sheet. This saves the data in Word or PowerPoint. Now you need to tell Word or PowerPoint that this is the data you want in the chart.

    11. Back in Word or PowerPoint, select the chart and then click Select Data from the Spotlight ribbon.

      ModelingImagesSpotlightOfficeChart9SelectData.png

      Once again, the Excel-style sheet reappears. In it, you see #NAME? for each metadata and data formula that you pasted in step 9.

      To confirm, we see that #NAME? appears in cells and ends at cell F12.

      Note:
      In a future release, #NAME? will be replaced with the appropriate values.

    12. Highlight the cells (A1 to F10) and you see the range dynamically filled in the Select Data Source dialog box. Click OK.

      ModelingImagesSpotlightOfficeChart10SelectData.png

    13. Click the X to close the Excel sheet.

    14. Back in Word or PowerPoint, your chart should still be selected. Click Refresh. The chart is now populated with data and you can fine-tune the layout and presentation of it.

      The chart is connected to the data source in Dynamic Planning and you can click Refresh to update the data regularly.

    Other Chart Options

    • Once you have a chart in place that is connected to the data source in Dynamic Planning, you can copy and paste the whole chart to another page or slide in Word or PowerPoint. Each copy of the chart is a separate instance, so you can change the dimensionality displayed in each chart independently. For example, if you set up the first chart to view all 2018 data, you can make copies of that chart to view individual quarters of 2018.

    • To make changes to the data you are seeing in the chart, use the Edit Data in Excel button to edit the rows and columns of data. For example, in the chart above, account 6000 is the Total Operating Expense, and account 6400 is Taxes (Expenses). I can delete those rows from the Edit Data in Excel window so that I can see only the categories of expense with relevant data in the chart.

    Was this article helpful?
    What's Next

      Planful. 150 Spear Street, Suite 1850, San Francisco, CA 94105

    • Copyright © 2024 Planful. All Rights Reserved.

    Terms of Use - Privacy Policy