CA Agile Central Add-In for Excel 2016 Installation and User Guide

This feature is currently in Beta. Select the yellow Feedback button and let us know how we could improve your experience.

The Excel 2016 / Office 365 add-in will only work on versions of Excel 2016 and for Office 365. Information on the Excel 2010/2013 add-in can be found here.

The CA Agile Central Add-in for Excel 2016 / Office 365 provides a quick and easy way to import or export data from your CA Agile Central subscription for tracking, reporting, or sharing:

  • Organize data in a pivot table or display data using Excel's built-in charting capabilities.
  • Export multiple CA Agile Central object types into multiple tabs and cross reference the data for more complex needs.
  • Import multiple CA Agile Central object types into CA Agile Central.
  • Use the add-in to export all work items from a project , then import new items to another project.
  • Create (not only update) new users as a bulk upload.

This installation and user guide includes:

Install the Add-In

Note: If you have an older version of the CA Agile Central Add-in for Excel, you can still install and use the Excel 2016 / Office 365 version. Queries created in the old plug-in will not be automatically moved to the Excel 2016 plug-in.

Connector Download:

The CA Agile Central Add-in for Excel is distributed as a single manifest.xml file.

manifest.xml file

Share the Add-In

Administrators: You can share the add-in with users in your organization without requiring each user to do the installation. Learn more.

Install on a Mac

  1. If the ~/Library/Containers/com.microsoft.Excel/Data/Documents/wef directory does not exist, open a terminal and create it.
    mkdir ~/Library/Containers/com.microsoft.Excel/Data/Documents/wef
    Copy the manifest.xml file to the directory.
  2. Select the Insert tab.
  3. Select the My Add-ins drop-down to show a list of developer installed add-ins.
  4. Select the CA Agile Central Excel add-in.

    Insert the CA Agile Central Excel add-in

Install on Windows (Excel 2016+)

  1. Create a new directory and move the manifest.xml file to it.
  2. Right-click on the directory and share it with yourself.
  3. Copy the network path to this directory.
    shared folder
  4. Go to Options, Trust Center, Trust Center Settings, Trusted Add-in Catalogs and add the network location from step 3.

    Trust Center showing Trusted Catalogs Table
     
  5. Restart Excel 2016.
  6. Select the Insert tab.
  7. Select the My Add-ins button.
  8. Select the SHARED FOLDER link.
  9. Select the CA Agile Central Excel add-in.

Install Excel 2016 for Office 365

  1. Login to your Office 365 subscription.
  2. Open a new Excel workbook.
  3. Select the Insert tab.
  4. Select the Office Add-ins button to open up your add-in catalog.
  5. Select the MY ADD-INS link.
  6. Select the caret next to Manage My Add-ins and choose the Upload My Add-in option.
  7. Upload the manifest.xml file.

Uninstall the Add-In

Disable the add-in in Excel from the File, Options, Addins dialog.

To completely uninstall the add-in, remove it from the Add/Remove Programs or Programs and Features portion of the Control Panel.

Log In to the Excel 2016 Add-In

SSO Authentication

CA Agile Central follows the SAML specification for SSO authentication . This specification requires that a value called the RelayState be passed among the participants in an SSO authentication handshake. The RelayState specifies the final destination that the user wants to visit after being authenticated and is always a URL All SAML SSO providers allow the user to set this value. Usually this is done by appending an additional URL parameter to the normal SSO connection URL. Be aware that different SSO providers use different parameter names to set this value.

If your subscription is set up to use SSO and you are not an SSO exception user, you can log in to the Excel 2016 add-in by entering your username with no password. You will be automatically redirected to your SSO site. Your subscription must be set to Automatically redirect to IDP to use SSO. Check with your Subscription Administrator if you are not redirected.

There are two ways to be an SSO exception user:
  • The user is explicitly listed in the exception list section of the Subscription edit dialog.
  • The user is a subscription administrator.

Authentication set to Automatically redirect to IDP

Important Notes:
To connect to CA Agile Central using SSO, you must provide an SSO URL that will set the SAML RelayState to the following URL value:
https://rally1.rallydev.com/slm/j_sso_security_check?noRedirect=true

The normal SSO URL in the CA Agile Central internal environment looks like this:
https://sso.rallydev.com/sp/startSSO.ping?PartnerIdpId=https://pingidp.f4tech.com

A complete Excel SSO URL in the CA Agile Central internal environment looks like this:
https://sso.rallydev.com/sp/startSSO.ping?PartnerIdpId=https://pingidp.f4tech.com&TargetResource=https://rally1.rallydev.com/slm/j_sso_security_check?noRedirect=true

In the CA Agile Central internal environment we use a URL parameter named TargetResource to set the RelayState. In other environments that we have tested, the SAML RelayState is set using RelayState or Target. Your environment may use one of these parameter names or something completely different.

Contact your internal CA Agile Central administrator or CA Agile Central Success if you are having trouble constructing a URL that correctly sets the SAML RelayState.

Basic Authentication

Note: The proxy settings are not required for most users. However, if your network traffic is routed through a proxy you will need to provide a valid proxy server address. If your proxy is authenticated, a username and password should also be specified.
  1. Enter your CA Agile Central server (default is rally1.rallydev.com), username, and password.
  2. Select Save. Follow either the export or import instructions below to complete the process.

Import Data from Excel to CA Agile Central

Important: The items to be imported must be in table format, with column headers. If you have many required fields, it may be easier to use the export option for your selected Type first so you have the correct column headers.
  1. In the Excel add-in pane, select Import.
  2. Select the Project scoping for your import.
    The project scope is defined by the Workspace and Project where you want to import data. You can also scope up or scope down (or both).
  3. Select the Type for your import from the drop-down list.
  4. The add-in will then display the required fields for the selected Type and Project Scope.
  5. After you have a table populated with the items you wish to import, select Validate to check the data for errors. Once it has passed validation , you can import the data into CA Agile Central.
  6. When your import has completed you will have the option to undo the import which will send all the items you just imported to the recycle bin in CA Agile Central.
Note: Importing with the new add-in is different with regard to associating imported items with other items. This means using Formatted ID instead of name for the existing item.

Export Data from CA Agile Central to Excel

  1. In the Excel add-in pane, select Export.
  2. Select the Project scoping for your export.
    The Project scoping defaults to your CA Agile Central Workspace and Project.
  3. Select the Type of work items to export.
  4. Use the column selector to determine which fields you wish to export. Any column names preceded by an arrow will let you choose nested attributes on that type. For example, the arrow next to Blocker means that typing "Blocker" followed by a period in the column selector will show you the nested attributes on Blocker that you can display. To select multiple attributes without having to type the root field name each time, hold the Shift key and select additional attributes.
    column selector showing dropdown list
  5. Define conditions and expressions.
  6. Enter the maximum number of results to export in Result Limit. The default is 200.
  7. Specify a field to sort on, and whether the sort should be ascending or descending.
  8. Select the Target sheet. The default is New Sheet, meaning that any time a query is run, it will open a new sheet. You can change this to select an existing sheet, which will cause the resulting data to overlay previously exported data.

After you have set your Project scoping, Type, Columns, Filters, Result Limit, and Sort item, you will have the option to save your query parameters to use again later.

Conditions and Expressions

Conditions and expressions are defined in the Filters section.

  1. Add one or more conditions.
    1. Select Add Condition.
    2. Select the Attribute, Operator, and Value.
    3. Select Done.
  2. Use the Expression field to choose how to apply the conditions to the results.
    conditions and expressions
  3. For date-specific fields (like Accepted Date), you can select Tokens to match relative dates like Today or Yesterday, which will filter on a different day depending on when the query is run.
    add a condition

Refresh

You can refresh your exported data in one or more sheets using the Refresh tab. This only impacts your exported sheets and does not re-import sheets.

Refresh choices

Troubleshooting

International Usage

Some countries use a comma (,) instead of a period (.) to represent nominal values.

Workaround: Changing the list separator in Windows into a comma does not work if the decimal point separator in Excel is also set to comma. European users will need to change to US number settings: Decimal (. Iso ,) and thousands separator: , iso .) This allows you to change the list separator in windows (from ; to ,).

Revision History

  • 1.0.0 - October-2018
    • Beta release

Feedback

Need more help? The CA Agile Central Community is your one-stop shop for self-service and support. To submit feedback or cases to CA Agile Central Support, find answers, and collaborate with others, please join us in the CA Agile Central Community.