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
- Technical Requirements
- Installing the Excel Add-in
- Uninstalling the Excel Add-in
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.
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
- Click here to download the Excel Add-in web page.
- Approve downloading and running of ‘setup.exe’.
NOTE
Your browser might not support running the file directly.
- Run the Setup.
- Close Excel and uninstall any previously installed versions.
- Run Setup directly from the download link (or save it to your disk and run it from there).
- 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.
Using AdaptiveWork Excel Add-in
This page describes using the AdaptiveWork Excel Add-in and includes the following sections:
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.
- Open MS Excel and click the AdaptiveWork ribbon.
- Click Login.
The Login with AdaptiveWork credentials dialog opens.
- Enter your username and password.
- Ensure that the Use Federated Authentication check box remains unchecked.
- 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.
- Click . Login begins.
The AdaptiveWork ribbon in Excel displays your name.
Creating or Editing a Query
- Access Excel and click the AdaptiveWorks ribbon.
- To create a new query, click New.
- To edit an existing query, click Edit.
- To copy the current query, click Copy.
The Query window opens.
-
In the Select item type list, select the item type. For information about the AdaptiveWorks data model, see Data Model.
- 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’.
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.
- Select items for the report:
The AdaptiveWork Excel Add-in enables filtering items in various combinations of And/Or. Click 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.
- To define the filtering condition, highlight the 'And'/'Or' node you intend to add the condition under, and define a filter condition:
- Use Quick search to search for fields by their names.
- 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
- To remove or re-order (indent or outdent) filters, highlight the relevant filtering conditions in the Select area and click the relevant button.
- 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.
-
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.
- 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.
- Check the checkbox of the fields to use on the left-hand side and click to move them across to the selected fields. You can also double-click the field to move it across to the other side.
- To remove or re-order (indent or outdent) fields, highlight the relevant fields in the Select area and then click the relevant button.
- Specify the maximum number of results to be generated.
- Use the 'Order by' field to sort the report.
- 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
- 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.
- Click to refresh all the queries in the workbook, or to refresh the currently selected worksheet with your current data.
If runtime filters were set on the query, a Runtime Parameters dialog appears.
- Select the filter intended to use from the list of filters available, and set the criteria.
- Click after editing the filter. The number of items to be retrieved is updated.
- Click 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:
- 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
Be sure to remove any data and credentials when uploading sample reports.
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:- There are no queries on the workbook
- The query returns more results than specified on the query item.
- 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.
- Navigate to Settings > Extensions > Connected Web Apps.
- Click Allow Access beside SSO for Excel Add-in.
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
- 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.
- Open MS Excel and click the AdaptiveWork ribbon.
-
Click Login.
-
Click the Federated Authentication link (not the check box). The Organization ID dialog opens.
-
Enter the Organization ID number.
-
Click
NOTE
Depending on your SSO provider, the Sign in dialog might be different.
-
Enter your SSO credentials.
-
Click
- Click
When you are logged in, your details appear in the Excel ribbon.
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.
- Open Microsoft Excel and click the AdaptiveWork ribbon.
- Click Login.
- In the Login with AdaptiveWork credentials dialog, click the multiple accounts link.
- In the Manage Accounts dialog, click the New button.
Another Login with AdaptiveWork credentials dialog opens.
- Enter the user name and password for the first account.
- 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.
-
Click the New button. The Login with AdaptiveWork credentials dialog opens again.
-
Enter the user name and password for the next AdaptiveWork account (of the same item type).
-
Click OK. The The Manage Account displayes the details for both accounts that you enabled.
-
Repeat this procedure for each account to be enabled.
-
Check or clear the Active check boxes for the accounts to be configured and/or to extract data from.
- Click to sort report order.
- Click . The Excel sheet opens. Multiple ( enabled) accounts are displayed. The AdaptiveWork ribbon displays the initial account holder's name.
Selecting Account for Query
- 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.
- Clicking opens the Manage Accounts form.
- Clicking clears the Query window data fields.
- Clicking copies the current query data to the account selected in the adjacent drop-down menu.
- With the first account listed selected, configure the Query window as described in the steps for creating or editing a query.
- Check the checkbox of the second account listed, and configure the Query window for data from that account.
- Repeat for additional accounts to be enabled, as required.
- Click
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:
- Remove your existing plug-in.
- Open the Windows command line ( Start > Run ).
- Run this command: rundll32 dfshim CleanOnlineAppCache.
- Re-install the developer version.
- Log in using your AdaptiveWork credentials in the Excel document.
If the same error occurs, try this:
- Ensure Excel is closed.
- Browse (type this on the address bar of windows explorer, or 'Start+Run' ) to this directory on your computer: %localappdata%\Apps\2.0
- 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.
- When the directory is empty retry the installation.
AdaptiveWork tab does not appear in the menu bar after the installation
Complete the following steps:
- Go to File -> Options -> Add-ins.
- Select 'Disabled Items' on the combo on the lower part of the form and click Go.
- Select 'AdaptiveWork Excel Add-in' and click Enable.
- Exit Options and restart Excel.
If the add-in is not listed in the disabled items, check the Inactive items:
- Go to File -> Options -> Add-ins.
- Select Com Add-ins on the combo on the lower part of the form and click Go.
- 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:
- Open Excel Options.
- Click Customize Ribbon.
- Click Reset.
- Select Reset all Customizations.
- Click OK.
- Check whether the AdaptiveWork tab appears in the menu bar.