Excel Add-in

Overview

The AdaptiveWork Excel Add-in is an advanced reporting tool ​that is used to extract raw AdaptiveWork data to generate complex reports based on an enhanced AdaptiveWork query and the Microsoft 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 view only the 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 to download the Excel Add-in web page.
  2. Approve downloading and running of ‘setup.exe’.

               

    NOTE

    Your browser might 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 be upgraded automatically on your computer when you launch Microsoft 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 into the AdaptiveWork Excel Add-in

This section describes how to log in to the AdaptiveWork Excel Add-in using your AdaptiveWork credentials. To log in using SSO, see below.

  1. Open MS Excel and click the AdaptiveWork ribbon.

    202020547_tab-3-.jpg

  2. Click Login.

    202063818_clicklog-1-.jpg

    The Login with AdaptiveWork credentials dialog opens.

    202020557_clickedlog_unchecked-4-.jpg

  1. Enter your username and password.
  2. Ensure that the Use Federated Authentication check box remains unchecked.
  3. Define the Global Login Settings options:
  • Enable Multiple Accounts — If you have two or more AdaptiveWork accounts and intend to extract combined account data for reports.
  • Remember my password for — Select a time duration for your credentials to be stored. Your password is stored encrypted only on your workstation.
  • Re-enter credentials to view in AdaptiveWork —  When checked, you must resubmit your credentials when using 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 begins.

    202020597_loggininmessage-1-.jpg

    The AdaptiveWork ribbon in Excel displays your name.

    202063828_hifred.jpg

Creating or Editing a Query

 
  1. Access Excel and click the AdaptiveWorks ribbon.
  2. To create a new query, click New.
  3. To edit an existing query, click Edit.
  4. To copy the current query, click Copy.

    The Query window opens.

               

    Click to expand or shrink

    Query dialog

               

     

  5. In the Select item type list, select the item type. For information about the AdaptiveWorks data model, see Data Model.

  6. In the list to the right of the item type, select the item that you want to query.

           

NOTES

  • 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 (beside the Item field) when selecting a work item item type (Milestone, Project, Task, or WorkItem) or when selecting Usergroup. When selected, this option serves as an extra filter that sets ‘has X as an ancestor in the hierarchy’.

202020647_recursive-tab.jpg

Clicking Search after 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.

           

NOTE

Result Ordering is unavailable with recursive query.

           

  1. Select items for the report:

    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 option and then click the Add and/or button.

  2. To define the filtering condition, highlight the 'And'/'Or' node you intend to add the condition under, and define a filter condition:
    1. Use Quick search to search for fields by their names.
    2. The list also contains fields with the same name that are ​ attributes of linked item types.
    3. Select filtering condition.
    4. Add the filter condition to the Select area
  3. 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.
  4. 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.

    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.

  5. Select fields for the report.

    You can select up to 24 fields to appear 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, selecting 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.

  1. 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.
  2. 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.
  3. To remove or re-order (indent or outdent) fields, highlight the relevant fields in the Select area and then click the relevant 202063878_in_out_dent-1-.jpg button.
  4. Specify  the maximum number of results to be generated.
  5. Use the 'Order by' field to sort the report.
  6. Select or clear 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
  7. Click Save and run.

    New and copied queries are presented in a new worksheet that displays the relevant results.
    Edited queries are updated in the current worksheet, which displays 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

    If runtime filters were set on the query, a Runtime Parameters dialog appears.

  2. Select the filter intended to use from the list of filters available, and set the criteria.
  3. Click 202063918_update_btn.jpg after editing the filter. The number of items to be retrieved is updated.
  4. 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 can 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. For more information, see https://support.microsoft.com/.

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

Sample reports that users of the free Run-time version can use:

​ Uploading Sample Reports

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

           

NOTE

Be sure to remove any data and credentials when uploading sample reports.

           

Excel Add-in Change Log

Click here to download the 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.

Enabling Single-Sign-On for Excel Add-in

The AdaptiveWork administrator can configure user accounts to enable single sign on (SSO) for the AdaptiveWork Excel Add-in.

           

NOTE

Active Directory Federation Services (ADFS) for the Excel add-in only works using an OAuth configuration. SAML is not supported.

 

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

             

    Click to expand or shrink

    Allow Access

               

               

    TIP

    The AdaptiveWorks administrator can set up a custom action on the Organization level in AdaptiveWorks so that users can obtain the organization ID.

               

    Logging into Excel Add-in using SSO

  1.  The first time that you sign to Excel Add-in using SSO, you require your organization ID. If you do not have your organization ID, please contact your Customer Success Manager.
  2. Open MS Excel and click the AdaptiveWork ribbon.

    202020707_tab-3-.jpg

  3. Click Login.

    202063948_clicklog-1-.jpg

    The Login with AdaptiveWork credentials dialog opens.

  4. Click the 202020717_usefedauth_unchecked-1-.jpg Federated Authentication link (not the check box). The Organization ID dialog opens.

    202020727_orgid-1-.jpg

  5. Enter the Organization ID number.

  6. Click 202063958_next-4-.jpg

              

    NOTE

    Depending on your SSO provider, the Sign in dialog might be different.

           

        202063968_sso-sign-in-1-.jpg

  7. Enter your SSO credentials.

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

    202020747_clauthacc-5-.jpg

  9. Click 202020757_grantaccess-2-.jpg

    When you are logged in, your details appear in the Excel ribbon.

    202020767_hifred.jpg​

Reporting From Multiple AdaptiveWork Accounts

You can enable the AdaptiveWork Excel Add-in to extract data for reports from multiple AdaptiveWork accounts.

Clicking the Enable multiple accounts link (located on the Login with AdaptiveWork credentials dialog ) opens the Account Management dialog, 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 type).

A special ‘AdaptiveWork Account Name’ field can be added to the result fields to show which account the results belong to.

Enabling Multiple Accounts

  1. Open Microsoft Excel and click the AdaptiveWork ribbon.
  2. Click Login.
  3. In the Login with AdaptiveWork credentials dialog, click the multiple accounts link.

    202064008_clickedlog_unchecked-4-.jpg

  4. In the Manage Accounts dialog, click the New button.

    202020777_enable_multi_dialog.jpg

    Another Login with AdaptiveWork credentials dialog opens.

    202064028_enable_multi_new_dialog.jpg

  1. Enter the user name and password for the first account.
  2. Specify the settings for the Remember my password for and Re-enter credentials to view in AdaptiveWork options, and then click OK.

    The Manage Account displays the account details.

    202020817_1st_account_logged_in.jpg 

  3. Click the New button. The Login with AdaptiveWork credentials dialog opens again.

    202064048_login_next.jpg

  4. Enter the user name and password for the next AdaptiveWork account (of the same item type).

  5. Click OK. The The Manage Account displayes the details for both accounts that you enabled.

    202020827_both_logged_in.png

  6. Repeat this procedure for each account to be enabled.

  7. Check or clear the Active check boxes for the accounts to be configured and/or to extract data from.

  1. Click 202064078_sort_arws.jpg to sort report order.
  2. ​ Click 202020837_gray_ok.jpg. The Excel sheet opens. Multiple ( enabled) accounts are displayed. The AdaptiveWork ribbon displays the initial account holder's name.

Selecting Account for Query

  1. Perform Step 1 for creating or editing a query as described above.

    The Query window opens with enabled accounts listed, the first account is selected.

               

    Click to expand or shrink

    Query dialog

               

  • Clicking 202064088_manage_accs.jpg opens the Manage Accounts 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 or editing a query.
  2. Check the checkbox of the second account listed, and configure the Query window for data from that account.
  3. Repeat for additional accounts to be enabled, as required.
  4. 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

Error message appears when trying to install the “Developer” version

Error: 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.

AdaptiveWork tab does not appear 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 the add-in is not listed in the disabled items, 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.

If the 'AdaptiveWork' tab does not appear in the menu bar but 'AdaptiveWork Excel Add-in' appears in File > Options > Add-ins > Active Application Add-ins, do the following:

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