Spotlight for Google Sheets

Introduction

Planful Spotlight add-on for Google Sheets is used for a wide range of financial planning, forecasting, budgeting, and ad-hoc analysis tasks. It fetches data from SpotlightXL and Spotlight Web in a fraction of the time. An intuitive user interface makes it easy to use and navigate and seamlessly creates a sync between your Dynamic Planning modules and Google Sheets.

You can access data sets from the Dynamic Planning interface using your login and specifying environment details. Once connected, you can perform tasks such as 

• Quick ad-hoc analysis using the Default view even if you do not have a view setup.
• Zoom in to a specific level within the hierarchy.
• Zoom out from a specific level within the hierarchy.
• Swap row and column dimensions using different Pivot options.
• Quickly segregate data and only see what you want using the Keep Only or Remove Only options.
• Customize your view and organize your data the way you like by using the different More options like Display, Suppress Rows, Indent Row Members, Number Format, etc. 
• View the reports and analyze the data further through graphs and charts available in the reports.

Prerequisites

• The user must predefine all models, dimensions, calculations, maps, and ad-hoc analysis on SpotlightXL or Spotlight Web.

• By using Google spreadsheet, you can only display the data in the generated reports, and views and perform ad-hoc analysis from the available or predefined dimensions for the model.
• You can not create any Views or Models using Google Sheets as it is currently in read-only mode.

Analyze

The Analyze task allows you to perform actions by selecting a model and an associated view, refreshing the data or zooming in and out of the model. You can also keep or remove certain aspects of the view and pivot the dimensions as required.

When you select Analyze, the available models will be listed with ad-hoc analysis and predefined views. 

This page will contain a list of models and views in the below pattern:

1. Analytical Models (listed alphabetically), followed by their view
2. ESM Models (listed alphabetically, followed by their view
   Note:

   A default view will be created if there is no predefined view.
3. Favorites
   Note:

   This displays a list of views selected as favorites in SpotlightXL or Spotlight Web.
4.  Recent
   Note:

   This displays the recent view opened by you.

Default Views (Ad-hoc analysis)

A default layout is created with all dimensions listed for each model, which can be used for ad-hoc analysis.

View Navigation Actions

The Analyze tab gives you the ability to execute the following operations on a model and its view using the actions in the ribbon. The view data changes by your actions:

• Zoom In
• Zoom Out
• Pivot
• Keep Only
• Remove Only
• Get Data
• Display
• Suppress Row
• Suppress Column
• Indent Row Members
• Number Formats

Zoom In

Under Zoom In action, there are specific actions that define how to zoom in on the displayed dimension intersection data, such as:

• Children 
• Leaves
• All Children
• Data Leaves
  

Children

When you click Zoom In and select Children, the immediate children for the selected dimension will appear.

In Practice: Zoom In - Children 

1. Select a dimension and navigate to Zoom In > Children.
   
2. The immediately associated children for the selected dimension will appear.
   

Leaves

When you click Zoom In and select Leaves, only the leaf-level members associated with the selected dimension member will appear.

In Practice: Zoom In- Leaves

1. Select a dimension and navigate to Zoom In > Leaves.
   
2.  When Leaves is selected, leaf-level members associated with the selected dimension member appear with the existing member.
   

All Children

When you click Zoom In and select All Children, all the members associated with the selected dimension are displayed.

In Practice: Zoom In- All Children

1. Select a dimension and navigate to Zoom In > All Children.
   
2. When All Children is selected, all children members will appear for the selected dimension member.

Before Zoom In- All Children

After Zoom In- All Children

Data Leaves

When you click Zoom In and select Data Leaves, only the leaf-level members containing data associated with all the dimensions on the row axis are displayed. 

In Practice: Zoom In- Data Leaves

1. Select a dimension on the row axis and then navigate to Zoom In > Data Leaves.
   
2. When Data Leaves is selected, only leaf-level members containing data associated with all the dimensions on the row axis are displayed, and all parent members in the dimension are removed from the view.

Before Data Leaves

After Data Leaves 

Zoom Out

Zoom Out helps you reach the next highest level within a dimension hierarchy. For example, if you are currently in the "City" level within a geography dimension hierarchy, zooming out will take you to the "State" level.

Zoom Out - Parent

When you select Parent, it helps you to view the highest level parent member of the selected dimension.

In Practice: Zoom Out- Parent

1. Select a dimension member and navigate to Zoom Out > Parent. 
2. When Parent is selected, only the next highest-level parent is displayed.

Before Zoom Out, Parent

After Zoom Out, Parent

Zoom Out - Top Level

When you select Top Level, it helps you to go back to the root member of the selected dimension. 

In Practice: Zoom Out- Top Level

1. Select a dimension member and navigate to Zoom Out > Top Level.
   
2. When Top Level is selected, the view changes to go back to the Top Level (the top or root of the dimension or the topmost member in the dimension you have access to).

Before Zoom Out, Top Level

After Zoom Out, Top Level 

Pivot

The Pivot feature helps you to swap the dimensions between page, row, and column by defining the view as required.

In Practice: Pivot

1. Click the Pivot  icon and select Pivot.
   
2. The column data is swapped to a row, and the row data is swapped to the column. 

Before Pivot

After Pivot

Pivot to Page

Pivot dimensions to the page level help you to pivot a dimension from a row to a Page section or from a column to a Page section.

In Practice: Pivot to Page

1. Select a dimension on a row or column and navigate to Pivot > Pivot to Page.
   
2. The selected dimension is swapped to the page level.
   

Pivot to Row

With Pivot to Row, you can select a single dimension and pivot it from the column to the row or from page to row. Dimensions not selected remain on the column/page, respectively. 

In Practice: Pivot to Row

1. Select a dimension from the column, and navigate to Pivot > Pivot to Row.
   
2.  The selected dimension is swapped from column to row.
   

Pivot to Column

With Pivot to Column, you can select a single dimension and pivot it from a row to a column or from page to column. Dimensions not selected remain on the row/page, respectively.

In Practice: Pivot to Column

1. Select a dimension on the row from the view, and navigate to Pivot > Pivot to Column.
   
2. The selected dimension is swapped from row to column.
   

Keep Only, Remove Only & Get Data

Keep Only

The Keep Only feature helps you to retain specific data within view. This action works the opposite of Remove Only.

1. Select a cell containing members from a dimension you want to retain and click Keep Only.
   
2. All other dimension members are removed, and only the data and metadata within the selected intersection are displayed.
   

Remove Only

The Remove Only feature helps you to remove the data and metadata for the selected member(s) from a view. 

1. Select the required cell and click Remove Only.
   
2. The selected cells are removed.

Get Data

This action updates data in the spreadsheet after selections are made.

1. Select any view.
2. Click the Get Data icon to update or refresh the selected view.
   

In Practice: To Display the Hierarchical Order by Updating the dimension members of a view

1. Select any dimension member or view, and click Update Selection.
   
2. The selected dimension member of view is displayed on the sidebar in hierarchical order.
   
   Note:

   In the above instance, we have selected Design Marketing Report as a view.

   
3. Likewise, select any dimension member from the sidebar, and click Apply.
4. The selected dimension member is displayed on the screen.
   
   Note:

   In the below instance, we have selected Net Income as a dimension.
   
   Notes:

   If you want to update dimension members for any other dimension in the view, follow the same steps again.

   1. Select the dimension, and click Update Selection on the sidebar.
   2. Select the member and click Apply.
   3. Select the POV members by clicking the Update Selection option in ESM views.

   Otherwise, you can also update members using the drop-down list for each dimension.

Suppress Row, Suppress Column, Indent Row Members & Number Formats

Suppress Row

Suppressing rows in a spreadsheet is the process of hiding certain rows from view. It helps you to create summary reports or hide some information. The below set of options are available to do ad-hoc analysis:

• None: By selecting None, all rows, including Blank and Zero rows, are displayed in the view.

• Zero: By selecting Zero, the rows with zero values will be hidden from the view.
• Blank: By selecting Blank, the rows that do not have data (blank) will be hidden from the view
• Both: By selecting Both, the rows with zero values or blank rows are hidden from the view.

In Practice: Suppress Row

1. Select the required row, and click the vertical ellipsis icon.
   
2. The sub-menu bar appears; click the Suppress Row icon.
   
3. Select the required option. 

Suppress Column

Suppress columns help to hide or remove unnecessary information from a dataset, reduce the size of the dataset, and help to keep only the columns that are required for a particular task. 

• None: By selecting None, all columns, including Blank and Zero columns, are displayed in the view.

• Zero: By selecting Zero, the columns with zero values will be hidden from the view.
• Blank: By selecting Blank, the columns that do not have data (blank) will be hidden from the view
• Both: By selecting Both, the rows with zero values or blank columns are hidden from the view.

In Practice: Suppress Column

1. Select the required row, and clickthe vertical ellipsis icon.
   
2. The sub-menu bar appears, click the Suppress Column icon.
   
3. Select the required option. 

Indent Row Members

Indenting row members help to identify the hierarchical relationship between the parent and its children members and are especially useful for large datasets with multiple levels of hierarchy. Another benefit is that it helps to improve the readability of the data. Additionally, it also helps simplify navigation through the dataset. 

• YES: Select YES to indent data. 
• NO: Select NO to keep the data unindented.

In Practice: Indent Row

1. Select the row, and click the vertical ellipsis icon. The sub-menu bar appears.
   
2.  Click the Indent Row icon, and select Yes.
   
3. The row is indented as per hierarchical order. 

Number Formats

The Number Format helps you to show the digits of your data in three formats: 

• Currency: By selecting Currency, you can view your data in your local currency format.

1. Click the Number Format icon > Currency.
   
2. The numbers available in the cell are converted to currency format.
   

• Percent: By selecting Percent, your data is displayed in a percentage format.

1. Click the Number Format icon  > Percentage.
   
2. The numbers available in the cell are converted to percentage format.
   

• Numeric: By selecting Numeric, the data is displayed in numeric format.

1. Click the Number Format icon > Numeric.
   
2. The numbers available in the cell are converted to numeric format.
   

Display

Using this feature, you can customize the display of dimension members in two sub-options:

• Display-Label
• Code

Display-Label

This feature helps to display the dimension member, including both code and label. 

In Practice: Display-Label

1. Open a view and click the vertical ellipsis icon.
2. The sub-menu bar appears; click the Display  icon > Display-Label.
   
3. The dimensions will appear with the member code and display label.
   

Code

With this feature, you can display the dimension member with code only. 

In Practice: Code

1. Open a view and click  the vertical ellipsis icon.
2. The sub-menu bar appears; click the Display  icon > Code.
   
3. The dimensions will appear with the member code.
   

Reports

This section allows you to refresh your existing reports. The Report section is divided into four subsections: Reports, Snapshots, Favorites, and Recent.

In Practice: To Open a Report

1. Click Report.
   
2. Expand the Reports option and select the report you want to open.
   
3. Double-click on the report to open it.
4. Select the required option from the drop-down list.
5. Click Refresh to update the selected view.
   
6. Optionally, you can also use the Update Selection option in the sidebar to specify specific data from the drop-down list.
   
7. Click Apply to display the selected data.
   

Let's consider a scenario where you are working with Google Sheets and have opened a report named 2015_Report in Planful Spotlight. If you need to modify the value for All Scenarios, you can easily do so by accessing SpotlightXL. Within SpotlightXL, you can make the necessary changes to the same report. Once you have saved the modifications, return to your Google Sheets and click the Refresh icon. This action will automatically update your Google Sheet with the latest modifications made in SpotlightXL.