Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Change Impact and Upgrade for OfficeConnect 2019.3

The article summarizes the improvements and changes introduced by the 2019.3 version of OfficeConnect. The articles also provides upgrade actions required by some of the changes. Review this change management article for details around changes to OfficeConnect.

Contains preview content for the upcoming 2019.3 release.

The latest version of OfficeConnect provides an enhanced Excel experience. It supports many of the standard Excel operations. This article highlights significant changes, required upgrade actions, and recommendations. Plan your upgrade and determine the options that fit your business needs. As an administrator, use this information to plan your upgrade and training efforts.

See the 2019.3 Release Notes for Dashboards, Reporting, and OfficeConnect for a summary of the changes.

Watch the video: 9m 48s

EA OfficeConnect 2019.3 Training Video
 

Changes with Upgrade Actions

Before starting, upgrade to the latest version of OfficeConnect. Ensure all users in the organization upgrade at the same time. OfficeConnect files from an earlier version are not supported on the latest version. Update these files as described in this section. Complete the upgrade actions associated with the other changes described in this section.

Log in to OfficeConnect for Excel from the Ribbon

Previously, you launched OfficeConnect for Excel from the Windows Start menu. Or, by double-clicking an existing .xlsxai file. You logged in to OfficeConnect as a separate Excel process. To log out, you had to close Excel.

Now you can log in and log out from the OfficeConnect ribbon in Excel. A new Connection group on the OfficeConnect ribbon includes the following options:

  • Log In and Log Out. After logging in, the Reporting task pane displays your default instance and elements.
  • Update Elements. This option was previously included in the Elements group.
  • Refresh. This option was previously included in the Design group.

start_OC.png

Upgrade Action

  1. Start Excel. You see the OfficeConnect ribbon.
  2. Select Log In on the ribbon.

Best Practice: Before opening a reporting workbook, always log in to OfficeConnect first. This avoids corrupting the data in the workbook.

Set Clear Data in Workbook and User Settings

Previously, during save, OfficeConnect prompted you to clear the sensitive data in a reporting workbook. The security text defined in the user settings replaced the data. Now you have an option to clear data upon save as a user setting and a workbook property. The default user setting is to always clear data upon save.

user_settings.png

Before updating your reporting workbooks to Excel, select the desired user setting. All new and updated workbooks inherit this preferred setting. After update, you can always override the user setting in the workbook properties. This allows you to have some reporting workbooks that always clear data upon save and other that do not.

Upgrade Action

To change the default user setting for all workbooks before updating them to Excel:

  1. Open an xlsxai file.
  2. Select User Settings. The "Always clear data upon save" option is selected by default.
  3. Select Never clear data upon save to change the default. This setting persists across all updated workbooks.
  4. Open another .xlsxai file and save it as an Excel file type (.xlsx).
  5. Make some changes and save the file again. Data is not cleared as the upgraded file inherits the user setting.
  6. Select Workbook Properties. Confirm that Always clear data upon save is not selected. 

To change the clear data setting  for a specific workbook after updating it to Excel:

  1. Open an xlsx file.
  2. Make some changes and save the file. Data is cleared by default and replaced with the text "N/A".
  3. Select Workbook Properties > Never clear data upon save. Select OK
  4. Refresh the file and save again. Note that data is no longer cleared after you change the setting.

We recommend that you select only one of the following options:

  •  File > Options > Save > AutoSave OneDrive and SharePoint Online files by default on Excel 
  • Always clear data upon save 

Selecting both the Excel and OfficeConnect options clears data frequently as you work.

Check Excel AutoRecovery Settings

Previously, you could not save the reporting metadata using Excel AutoRecover. Auto-recovery is now enabled for Microsoft Office 2013 and later versions.

The auto-recovery file saves:

  • Changes in OfficeConnect such as applying reporting elements or updating the metadata
  • Changes in Excel such as applying a formula to a cell or 

 If a reporting workbook crashes, navigate to the AutoRecover file location and recover the file and the metadata.

Excel_autoRecover.png

Upgrade Action

  1. Select File > Options.
  2. In Excel Options, select Save
  3. Under Save workbooks, review or change the following settings:
    • The frequency for saving AutoRecover information
    • The location for saving AutoRecover files
  4. Select OK.

Update Existing Reporting Workbooks to Excel Files

Previously, reporting workbooks were .xlsxai files and OfficeConnect Book types. The Adaptive Insights logo displayed next to these files.

xlsxai_files.png

Now, reporting workbooks are standard Excel files and Microsoft Excel Worksheet types. The Excel icon displays next to the files. 

Excel_files.png

To tell if an Excel file is a reporting workbook, open the file. If the instance drop-down in the Reporting pane is locked, it's a reporting workbook.

Now that reporting workbooks are Excel files, you can use standard Excel functions. For example, you no longer need the Export Workbook option to open reporting workbooks in Excel. This option is now removed.

Upgrade Action

Update your existing .xlsxai files by saving them as standard Excel (.xlsx) files. 

save old xlsxai files as regular Excel files

  1. Start Excel and open a blank workbook.
  2. Select the OfficeConnect tab and select Log In.
  3. Select File > Open to open an existing .xlsxai file.
    An Excel message informs you that the file format and extension don't match and the file could be corrupted.
  4. Select Yes to open the file anyway. 
  5. Select File > Save As.
  6. Remove the .xlsxai extension. Select Save to save the file as a Microsoft Excel workbook (.xlsx extension). 
  7. Close the file. Browse back to the file in your file manager and verify:
    • The file has the same name as before but displays the Excel icon next to it.
    • The type displays as Microsoft Excel Worksheet.
  8. After updating all files, move your .xlsxai files to a separate folder for backup. Going forward, use the Excel-based files when working in OfficeConnect.

Update PowerPoint and Word Links

If you have OfficeConnect PowerPoint presentations or OfficeConnect Word documents that link to OfficeConnect Excel workbooks, update these files to link to the updated Excel files.

Upgrade Action

  1. For OfficeConnect PowerPoint presentations, update the source files to link to the updated OfficeConnect reporting workbooks. See Update Links with New Data and refer to the section Update Source Files.
  2. For OfficeConnect Word documents, update the source files to link to the updated OfficeConnect reporting workbooks. See Connect OfficeConnect Excel and Word Files.

Manage Zeros and Blanks

Post-upgrade, the Hide Zeros & Blanks capability might not work as expected. For example, you can have a worksheet where the Hide Zeros & Blanks button is active but rows and columns containing all zeros or no data still display. 

Upgrade Action

  1. Change the Hide Zeros & Blanks button to the desired state or select Refresh
  2. For existing reporting workbooks, select or clear the following workbook properties:
    • Show zeros in cells with no data
    • Hide rows or columns with all zeros
    • Hide rows or columns with no data
  3. For workbooks that you create in the new OfficeConnect version, select or clear the Show zeros in cells with no data user setting. 

Set Defaults in Workbook Properties

Previously, when you created a workbook, the New Workbook prompt displayed. In the prompt, you set the reporting defaults. Now, you are no longer prompted to set defaults when creating a reporting workbook. You can set the defaults later in the workbook properties. 

workbook_props.png

Upgrade Action

  1. Select General. In Report date, enter the current date or the closest available date as defined in your instance.
  2. To make new time elements relative to the report date, select the check box.
  3. Select Format. Set the options for:
    • Whether to show zero is cells with no data.
    • Whether to show rows and columns with all zeros or blanks.

Changes Without Upgrade Actions

OfficeConnect includes more updates that enhance your Excel experience. The changes described in this section need no upgrade actions. 

Use Excel Recent Files Capability

Previously the Recent option on the OfficeConnect ribbon listed your recent .xlsxai files. This option is no longer needed and removed. Now you can use the Excel, Home > Recent capability to view your recent reporting workbooks.

Access Unlimited Reporting Workbooks 

Previously, you could open three reporting workbooks at the most. These workbooks launched in separate Excel sessions. Now you can use File > New to open an unlimited number of reporting workbooks in the same Excel session. Logging into OfficeConnect logs you into all open workbooks. Logging out of OfficeConnect logs you out of all open
workbooks.

We recommend that you follow Microsoft Excel guidelines for the largest number of open workbooks.

You can access only one instance in a reporting workbook. If your user login allows, then you can access multiple parent and child instances in multiple workbooks. For example, you can access a parent instance in one workbook and a child instance in another workbook.

All open workbooks support the following actions:

  • Refreshing worksheets or individual workbooks.
  • Linking across reporting and non-reporting workbooks.
  • Copying and pasting reporting data across worksheets and workbooks. Use the OfficeConnect ribbon buttons or the context menu options.
  • Copying and pasting non-reporting (Excel) data across worksheets and workbooks. Use standard Excel capability. 
  • Moving or coping worksheets between workbooks.

Link Workbooks

Previously, linking reporting workbooks to regular Excel workbooks was challenging. You had to open a.xlsxai file in Excel or know the full path to link cells. Now you can use standard Excel capability to link reporting and non-reporting workbooks.

Move or Copy Worksheets Between Workbooks

Previously, you could only copy or move worksheets within the same reporting workbook using the File > OfficeConnect > Merge Workbook option. This options has been removed.

Now you can use the following options to copy or move worksheets to a new or existing workbook: 

  • The Move or Copy Report button in the Elements group on the OfficeConnect ribbon.
  • The Move or Copy Report option in the context menu.
    move_copy_report.png

The OfficeConnect Move or Copy Report option prefixed with the {corp}} logo also moves or copies the reporting metadata. The Excel Move or Copy option does not move or copy the metadata. The OfficeConnect Move or Copy Report dialog is different from the regular Excel Move or Copy dialog.

Copy and Paste Formulas

Previously, to copy and paste Excel formulas between reporting and standard Excel workbooks, you had to open the .xlsxai files in Excel. Now you can use the standard Excel options.

Use Updated Workbook Properties

The workbook properties include the following updates:

  • The Summary tab is renamed to General and includes currency information.
  • The Currency tab is no longer available. Currency information is now available from the General tab. 
  • The following properties are removed.  Some of these are now replaced with Excel file properties.
    • Book name 
    • Book description (removed and not replaced)
    • Category (removed and not replaced)
    • Created by 
    • Modified by 

Use Updated System Labels 

The system variables include the following updates in the Label Type Values dialog:

  • Book Description: Removed. This variable is no longer relevant.
  • Book Name: Updated. This variable now refers to the reporting workbook file name.

Delete Worksheets

Previously, when you deleted a reporting worksheet, OfficeConnect displayed a warning message. Now a standard Excel message displays to confirm the deletion. 

Filters Worksheets and Workbooks

Previously, the Enable filters checkbox in the Filters tab of the Reporting pane affected all worksheets in a workbook. For example, if you enabled filtering in one worksheet, filtering got enabled for all worksheets in the workbook. 

Now you can select the Enable filters checkbox for an individual worksheet only without affecting the whole workbook. To set filters at the global level, use the Filters tab in the workbook properties.

See Add Elements to Report Filters.

Log In to OfficeConnect for PowerPoint from the Ribbon

Previously, there was no option to log in or log out from the OfficeConnect ribbon. You launched OfficeConnect for PowerPoint from the Windows Start menu or by double-clicking a .pptx file. To log out, you had to close PowerPoint.

Now a new Log In option appears on the OfficeConnect ribbon in PowerPoint. Start PowerPoint and log in and log out from the OfficeConnect ribbon. If you already logged in to OfficeConnect for Excel and you start OfficeConnect for PowerPoint, you are automatically logged in when you select Log In from the ribbon. 

Log In to OfficeConnect for Word from the Ribbon

Previously, there was no option to log in or log out from the OfficeConnect ribbon. You launched OfficeConnect for Word from the Windows Start menu or by double-clicking a .docx file. To log out, you had to close  Word.

Now a new Log In option appears on the OfficeConnect ribbon in Word. Start Word and log in and log out from the OfficeConnect ribbon. If you already logged in to OfficeConnect for Excel and you start OfficeConnect for Word, you are automatically logged in when you select Log In from the ribbon. 

Merge OfficeConnect for Word Files

Previously,  when saving an OfficeConnect for Word document as an existing file, the only option available to you was to replace the existing file. 

Now, if you select Save As and then select an existing document, the following options are available. Select Save to see these options:

  • Replace existing file.
  • Save changes with a different name.
  • Merge changes into existing file.

The merge capability is currently not supported for OfficeConnect files. If you select to merge the files, you lose the links to OfficeConnect.

 

  • Was this article helpful?