Skip to main content

 

Planview Customer Success Center

Excel Add-in

Excel Add-in

Excel Add-in Overview

This page describes is the AdaptiveWork Excel Add-in overview and includes the following sections :

About The AdaptiveWork Excel Add-in

The AdaptiveWork Excel Add-in is an advanced reporting tool ​designed to enable your organization the ability to extract raw AdaptiveWork data in order to generate complex reports based on an enhanced AdaptiveWork query and the MS Excel reporting capabilities. All query and report definitions are extracted and stored within the Excel spreadsheet, which can then be sent to end-users. Other users can run the same report on relevant data.

Viewing Permissions

Users can only view data that their permissions allow them to see.

In cases where the data cannot be displayed due to permissions, Restricted is displayed.

mceclip0.png

Technical Requirements

The Excel Add-in supports MS Excel 2007 and above.

.NET 3.5 Sp1 is required:

  • Microsoft .NET 3.5 Sp1.
  • For Windows XP Microsoft .NET 3.5 Sp1 may be installed by the SETUP.EXE installer (administrative permission may be required).
  • For Windows 7 Microsoft .NET 3.5 Sp1 is already installed.
  • For Windows 8 Microsoft .NET 3.5 Sp1 may need to be enabled via the Control Panel (administrative permission may be required).

Installing the Excel Add-in

  1. Click here for the Excel Add-in web page in AdaptiveWork App Marketplace.
  2. Approve downloading and running of ‘setup.exe’.
    ​Note: Your browser may prompt to approve running the setup.exe file or may not support running the file directly.
  3. Run the Setup.
  4. Close Excel and uninstall any previously installed versions.
  5. Run Setup directly from the download link (or save it to your disk and run it from there).
  6. When prompted, click Install .

The Excel Add-in is installed.

Note: Future versions of AdaptiveWork Excel Add-in will upgrade on your computer automatically upon launching MS Excel.

Uninstalling the Excel Add-in

  1. Click Start -> Control Panel -> Uninstall a program OR Programs and Features
  2. Select 'AdaptiveWork Excel Add-in V3’
  3. Click Uninstall.

    The Excel Add-in is uninstalled.

Using AdaptiveWork Excel Add-in

Logging In

This section describes logging in to the AdaptiveWork Excel Add-in using your AdaptiveWork credentials.

Logging in to the AdaptiveWork Excel Add-in using Federated Authentication (Single-Sign-On-SSO) is supported where relevant (and enabled).
Note: Active Directory Federation Services (AD FS) for the Excel add-in only works using an OAuth configuration. SAML is not supported.

  1. Open MS Excel and click the AdaptiveWork ribbon.

202020547_tab-3-.jpg

Figure 1: AdaptiveWork Ribbon


The AdaptiveWork ribbon is active.

  1. Click Login .


202063818_clicklog-1-.jpg

Figure 2: Click Login

The Login with AdaptiveWork credentials dialog opens.

202020557_clickedlog_unchecked-4-.jpg

Figure 3: Login Dialog

  1. Enter your username and password.
  2. Ensure 202020567_usefedauth_unchecked-2-.jpg remains unchecked (unless logging in via SSO ).
  3. Define the Global Login Settings options:
  • Enable Multiple Accounts — For organizations using two or more AdaptiveWork accounts intending to extract combined account data for reports.
  • Click here for more on reporting from multiple accounts
  • Remember my password for — Select a time duration for your credentials to be 'remembered'.
    Your password is stored encrypted on your station only.
  • Defining Credential Re-submission — Checking Re-enter credentials to view in AdaptiveWork defines that the re-submission of credentials is required when opening a new web browser for viewing data within AdaptiveWork.
  • Proxy Proxy server settings are taken from Internet Explorer settings automatically. Define your organization proxy server's settings only if you fail to login because of proxy issues.

  1. Click 202020587_login_ok.jpg

    Login commences.

202020597_loggininmessage-1-.jpg

Figure 4: Logging In

The Excel sheet AdaptiveWork ribbon displays your name.

202063828_hifred.jpg

Figure 5: Logged In

Creating a New Query or Editing an Existing Query

  1. Click 202063838_new_btn.jpg to create a new query based on AdaptiveWork information.

    A blank Query dialog opens.

OR

  • Click 202020607_edit_btn.jpg to edit the query saved in the currently selected sheet.

​OR

  • Click 202020617_copy_btn.jpg for a copy of the query in the currently selected sheet.
Note: If the 'Remember my password for' login option was unchecked upon last login; or the defined period lapsed; or you changed password since last login , the Login with AdaptiveWork credentials dialog opens to enable submitting login credentials.


The Query window opens.

202020627_query.jpg

Figure 6: Blank Query Dialog

  1. From the drop-down menu, select an item type intended to query.
    The three types include:


The right-hand drop-down contains items relevant to the selected item type in the left-hand drop-down.

202020637_query_bug.jpg

Figure 7: Select Item

Note:
  • Combo fields support Autocomplete word prediction/completion.
  • Nested items ( items with hierarchical sub items, indicated by '+') in Quick Search fields are supported by entering the period character to adjoin sub items.
  • Nested fields cannot be used in the query for 'Sort By'

Recursive Query

A 'Recursive Query option is available (adjacent to the Item field) when selecting a work item item type (Milestone, Project, Task, or WorkItem) or when selecting Usergroup . When checked, this option serves as an extra filter that sets ‘has X as an ancestor in the hierarchy’.

202020647_recursive-tab.jpg

Figure 8: Recursive Query

  • Clicking 202063848_recursive-search.jpg following the initial selection changes the root item.

A recursive query can be useful for querying items within a hierarchical structure with several levels, such as a 'master' or 'portfolio' project. Other elements on the query form (including other filters) work the same way.

Notes:
Result Ordering is unavailable with recursive query.

  1. Select items for the report:

a) The AdaptiveWork Excel Add-in enables filtering items in various combinations of 'And'/'Or'. Click 202020657_sql-2-.jpg to display a SQL like equivalent of the condition to confirm that report conditions are set as intended.

  • 'And' is the default setting , to which criteria can be added
  • For additional 'And'/'Or' nodes in the Select area, highlight the node you intend to add, select the filter
    condition
    radio button and click 202020667_add-and_or.jpg

b) To define the filtering condition, highlight the 'And'/'Or' node you intend to add the condition under, and define a filter condition:

  • Use the Quick search field to quickly find fields by their name.
Note: The list also contains fields with the same name that are ​ attributes of linked item types.

  • Select filtering condition
  • Add the filter condition to the Select area ​

c) To remove or re-order (indent or outdent) filters, highlight the relevant filtering conditions in the Select area and click the relevant 202063858_updownremv.jpg button.

d) Run-time Filters: You can define filters to be available for users to modify at run-time, by checking the checkbox located adjacently to each criterion.

Note: The 'Number of items' selected is located below the Select field. For performance reasons, each query is limited to 30,000 items, and 24 description fields. AdaptiveWork displays the top number of items based on a limit that you set. We strongly recommend limiting the number to 1000 items.

  1. Select fields for the report:

AdaptiveWork Excel Add-in enables selecting up to 24 fields to show in the report. Fields can either be the direct attributes of a selected item type class or attributes of directly linked item types.
Relevant items show a ' DblClick to View in AdaptiveWork' link that, once clicked, directs a new web browser to the item in AdaptiveWork (suggested).

Tip: Upon login, checking the Re-enter credentials to view in AdaptiveWork option sets the re-submission of credentials as required
when opening a new web browser for viewing data within AdaptiveWork.

You can reorder or remove fields.

a) Use the Quick search to quickly find fields by name. Please note that the list will also contain fields with the same name that are attributes of linked item types.

b) Check the checkbox of the fields to use on the left-hand side and click 202063868_arrow_right.jpg to move them across to the selected fields.
You can also double-click the field to move it across to the other side.

c) To remove or re-order (indent or outdent) fields, highlight the relevant fields in the Select area and

click the relevant 202063878_in_out_dent-1-.jpg button.

d) Define the maximum number of results to be generated.

e) Use the 'Order by' field to sort the report.

f) Check or uncheck the Results Handling parameters:

  • Convert all duration fields to (select Minutes , Hours , Days , Months )
  • Ignore time part of date/time fields [Recommended]
  • Date/time in organizational time zone [Recommended]
  • Auto-refresh results when loading workbook
  • Append to existing results

g) Click 202020677_save-and-run.jpg

'New' and 'Copied' queries are presented in a new worksheet populated by the relevant results.
'Edited' queries update the existing (selected) worksheet and is populated by the relevant results.

The query definition is saved on that sheet.

Refreshing the Report​

  1. Click 202063888_refreshall_btn.jpg to refresh all the queries in the workbook, or 202063898_refersh_btn.jpg to refresh the currently selected
    worksheet with your current data.

202063908_refresh_all.jpg

Figure 9: Refresh All

  1. If runtime filters were set on the query, a Runtime Parameters form is shown. Select the filter intended to use from the list of filters available, and set the criteria.

  2. Click 202063918_update_btn.jpg after editing the filter. The number of items to be retrieved is updated.

  3. Click 202020687_save-and-run.jpg and receive the updated results from AdaptiveWork. Workbook pivot tables and graphs update automatically.

Note: The maximum number of items retrieved is predefined. When updating the filters, ensure the query does not exceed the maximum number of items defined for that query.

Working with the Results

Upon completion of defining a new query, a new Excel Worksheet is created with the name of the item type class that the query was defined on.

You may change the Worksheet name. The process to create more queries in the same Spreadsheet.

Each new Query is placed in a different Worksheet. As query results are over-written every time a query is run or refreshed, ensure to design reports on a separate Worksheet.

Joining Queries

We recommended using MS Excel's VLOOKUP or INDEX and MATCH functions to join query results.

Presenting Data

Use PivotTables to generate Graphs in Excel .

Excel Function Tips

Tip 1: INDEX AND MATCH

VLOOKUP has limited capability as it requires the reference fields to be the left-most column and sorted.
INDEX and MATCH enables more flexibility and allows you to reference the data you extract from AdaptiveWork without moving it around or worrying about its location on the Worksheet or the sort order.


Example of how it looks: =INDEX(Milestone!B:B,(MATCH(D6,Milestone!$F$1:$F$3000,0)))
Example of how it works: =INDEX(THE COLUMN FROM WHICH YOU WANT TO RETURN THE VALUE FROM, (MATCH(THE VALUE IN YOUR CURRENT WORKSHEET,THE COLUMN WHERE THAT VALUE IS IN THE TARGET WORKSHEET,0)))

Tip 2: ISNA

Sometimes your formula returns some good data and a load of NA’s resulting in a Excel which is difficult to read.
There’s an “ISNA” Excel function which you can use in an IF.
Example of how it looks:=IF(ISNA(MATCH(D6,Milestone!$F$1:$F$3000,0)),"-",INDEX(Milestone!B:B,(MATCH(D6,Milestone!$F$1:$F$3000,0))))

Basically, if the MATCH returns an NA value, then just show a “-“

Sample Reports

Scroll down to the bottom of the page to download sample reports including these reports that users of the free Run-time version can use:

  • Example of Timesheets Dashboard
  • Sample of VLOOKUP use in a Projects hours reported this month Vs. % completed

Uploading Sample Reports

To submit your own reports for free download from this site, please contact your AdaptiveWork Customer Success Manager.

Note: Please take special care to remove any data and credentials when uploading sample reports.

Excel Add-in Change Log

VBA automation object

The Excel add-in supports the VBA automation object.

The automation object supports the following methods:

  • DefaultLogin (silent as Boolean) as String – Logs in the user with the credentials saved by current user.
    If the silent flag is false the Login form is shown. In this case the credentials are not saved or the saved credentials can’t log you in.
    This method returns an empty string if no error occurs or an error message displays if an error occurred.
  • LoggedIn as Boolean – returns True if the Excel add-in is logged in or False if the Excel add in is not logged in.
  • Login (user as string, password as string) as String– Logs in with the specified user name and password.
    This method returns an empty string if no error occurs or an error message displays if an error occurred.
    It is recommended that you protect the VBA project if you use this method to keep the credentials more secure.
  • RefreshAll (silent as Boolean) as String – Refreshes all queries on the workbook.
    If silent is true then the message boxes are not displayed if one of the following conditions are true:
    1. There are no queries on the workbook
    2. The query returns more results than specified on the query item.
    This method returns an empty string if no error occurs or an error message displays if an error occurred.
  • RefreshSheet (sheet as object) as String – Refreshes the query on a particular sheet (specified by a name or index).
    This method returns an empty string if no error occurs or an error message displays if an error occurred.
  • RefreshCurrent () as String – Refreshes the query on the current selected sheet.
    This method returns an empty string if an error occurs or an error message displays if an error occurred.

A sample document with the above VBA macros are available to see how the code works. Please click here to download it.

Video Demonstration

The video tutorial below demonstrates the following actions:

  • Reviewing Requirements, Data Model, and Building Query
  • Combining two queries using formulae and VLOOKUP
  • Presenting your data in a pivot table and filtering or sorting as needed

Sample Report Tutorial – Building a simple Timesheet report with the Excel Add-in in under five minutes


Enabling Single-Sign-On for Excel Add-in

Enabling Single-Sign-On for Excel Add-in

This page describes configuring user accounts by your organization's Administrator in order to enable Single-Sign-On (SSO) for the AdaptiveWork Excel Add-in.

Note: SSO for Excel add-in works with Active Directory Federation Services (AD FS) using the OATH configuration. SAML IDP is not supported.

  1. Navigate to Settings -> Extensions -> Connected Web Apps .
  2. Click Allow Access located adjacent to SSO for Excel Add-in.

202020697_settings-1-.jpg

Figure 1: Allow Access

  1. Install the AdaptiveWork application, located here, to set up a Custom Action (on the Organization level) to enable users to obtain the Organization ID number.

Logging in to Excel Add-in via Single-Sign-On

Logging in to Excel Add-in via Single-Sign-On

This page describes logging in to the AdaptiveWork Excel Add-in via Federated Authentication Configuration Single-Sign-On (SSO).

1. Get your Organization SSO Login ID

You will need your organization ID for a 'once only' registration to log in to the Excel Add-in.

If you do not have your organization ID, you can contact your Customer Success Manager.

2. Logging In

  1. Open MS Excel and click the AdaptiveWork ribbon.

202020707_tab-3-.jpg

  1. Click Login.


202063948_clicklog-1-.jpg

The Login with AdaptiveWork credentials dialog opens.

  1. Click the 202020717_usefedauth_unchecked-1-.jpg Federated Authentication link.

The Organization ID dialog opens.


202020727_orgid-1-.jpg

  1. Enter the Organization ID number.
  2. Click 202063958_next-4-.jpg
Note: The resulting Sign In dialog may differ from the one shown below depending on your SSO provider.

202063968_sso-sign-in-1-.jpg

  1. Enter your SSO credentials.

  1. Click 202020737_sso-sign-in_loggin-1-.jpg

202020747_clauthacc-5-.jpg

  1. Click 202020757_grantaccess-2-.jpg
  2. Logging in commences.

202063978_loggininmessage-1-.jpg

The Excel ribbon updates with your details.

202020767_hifred.jpg

Reporting From Multiple Accounts

Reporting From Multiple Accounts

This page describes enabling AdaptiveWork's Excel Add-in to extract data for reports from multiple AdaptiveWork account s and includes the following sections :

About Reporting from Multiple Accounts

Should you intend to extract report data from two or more AdaptiveWork accounts used by your organization, clicking the Enable multiple accounts link (located on the Login with AdaptiveWork credentials dialog ) opens the Account Management form
on which you define and configure the accounts to use, specify the account report order and define a separate query for each account (providing that the accounts selected are of the same AdaptiveWork item typ).

A special ‘AdaptiveWork Account Name’ field can be added to the result fields to indicate which account the result(s) belongs to.

Enabling Multiple Accounts

  1. Open MS Excel and click the AdaptiveWork ribbon.

202063988_tab-3-.jpg

Figure 1: AdaptiveWork Ribbon


The AdaptiveWork ribbon is active.

  1. Click Login .


202063998_clicklog-1-.jpg

Figure 2: Login

The Login with AdaptiveWork credentials dialog opens.

202064008_clickedlog_unchecked-4-.jpg

Figure 3: Login Dialog

  1. Click the 202064018_enabl_mtpl_acnts_unchecked-2-.jpg link .

    The Manage Accounts form opens.

202020777_enable_multi_dialog.jpg

Figure 4: Manage Accounts​ Form

  1. Click 202020787_enable_multi_new_btn.jpg

    Another Login with AdaptiveWork credentials dialog opens.

202064028_enable_multi_new_dialog.jpg

Figure 5: Enable First Account

  1. Enter credentials for the first account to the relevant fields.

  1. Click 202020797_login_ok.jpg

    Login to the first account commences.

202020807_additional_login_proc.jpg

Figure 6: First Account Login

The Manage Account form updates with details of the enabled account.

202020817_1st_account_logged_in.jpg

Figure 7: First Account Logged In

  1. Click 202064038_enable_multi_new_btn-3-.jpg

    The Login with AdaptiveWork credentials dialog opens again.

202064048_login_next.jpg

Figure 8: Enable Additional Account

  1. Enter credentials for an additional AdaptiveWork account (of the same item type) to the relevant login fields.

  2. Click 202064058_login_ok.jpg

  3. Login to the additional account commences.

202064068_login_tony-1-.jpg

Figure 9: Additional Account Login

The Manage Account form updates with details of enabled accounts.

202020827_both_logged_in.png

Figure 10: Two Accounts Logged In

  • Continue as necessary to enable additional accounts in the same manner.

  1. Check/uncheck Active column checkboxes adjacent to the accounts to configure and/or extract data from.
  2. Click 202064078_sort_arws.jpg to sort report order.
  3. ​ Click 202020837_gray_ok.jpg


The Excel sheet opens. Multiple ( enabled) accounts are displayed. The Excel sheet's AdaptiveWork ribbon displays the initial account holder's name.

Selecting Account for Query

  1. Perform Step 1 for creating a new query or editing an existing query as described in Using AdaptiveWork Excel Add-in.

    The Query window opens with enabled accounts listed (as sorted in Step 11 from the above procedure), the first account is selected.

202020847_multi_query-2-.jpg

Figure 11: Multiple Accounts Enabled

  • Clicking 202064088_manage_accs.jpg opens the Manage Account form .
  • Clicking 202064098_clear_q.jpg clears the Query window data fields .
  • Clicking 202020857_copy_to_account.jpg copies the current query data to the account selected in the adjacent drop-down menu.
  1. With the first account listed selected, configure the Query window as described in the steps for creating a new query or editing an existing query.
  2. Check the checkbox of the second account listed, and configure the Query window for data from that account.
  • Continue in the same manner for additional accounts to be enabled, as required.
  1. Click 202064108_save-and-run.jpg

    A new sheet is created and the relevant results are populated in it. The query definition is saved on that sheet.


Troubleshooting

Troubleshooting

This page describes Troubleshooting for the AdaptiveWork Excel Add-in .

I. Error message pops up when trying to install the “Developer” version:

Unable to install this application because an application with the same identity is already installed...

Complete the following steps:

  
  1. Remove your existing plug-in.
  2. Open the Windows command line ( Start -> Run ).
  3. Run this command: rundll32 dfshim CleanOnlineAppCache.
  4. Re-install the developer version.
  5. Log in using your AdaptiveWork credentials in the Excel document.

​If the same error occurs, try this:

  1. Ensure Excel is closed.
  2. Browse (type this on the address bar of windows explorer, or 'Start+Run' ) to this directory on your computer: %localappdata%\Apps\2.0
  3. Delete everything under this directory (it's just a cache). If some files are used and cannot be deleted, you'll have to log off Windows.
  4. When the directory is empty retry the installation.

II. No error messages are shown but the 'AdaptiveWork' tab does not show up in the menu bar after the installation.

Complete the following steps:

  1. Go to File -> Options -> Add-ins
  2. Select 'Disabled Items' on the combo on the lower part of the form and click Go .
  3. Select 'AdaptiveWork Excel Add-in' and click Enable .
  4. Exit Options and restart Excel .

​If it wasn't shown in the disabled items then check the Inactive items:

  1. Go to File -> Options -> Add-ins
  2. Select Com Add-ins on the combo on the lower part of the form and click Go .
  3. Check 'AdaptiveWork Excel Add-in' and click OK .

III. If the 'AdaptiveWork' tab is still not shown in the menu bar but 'AdaptiveWork Excel Add-in' does appear in the File -> Options -> Add-ins -> Active Application Add-ins:

Complete the following step:

  1. Open Excel Options .
  2. Click Customize Ribbon .
  3. Click Reset .
  4. Select Reset all Customizations .
  5. Click OK .
  6. Check if the AdaptiveWork tab is shown in the menu bar.