Rally Add-in for Excel Installation & User Guide

Print this topicEmail this topic

Connector download:

The Rally Add-in for Excel provides a quick and easy way to import or export data from your Rally subscription for tracking, reporting, or sharing:

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

Note: The add-in is capable of importing and exporting data. It cannot update the fields or statuses of existing work items. If you export an existing item, change a field, and then import, you will see a new copy of the existing work item. This also applies to user upload.

This video provides an overview of the Rally Add-in for Excel, and how it can perform advanced imports and exports of data.

Import and Export with the Rally Add-in for Excel

This installation and user guide includes:

Important: The add-in is not supported with the Mac version of Excel.

Installation

Verify the current version

If you have previously installed the Rally Add-in for Excel, determine the current version installed:

In Windows, go to StartControl PanelPrograms and FeaturesRally Add-in for Excel. Note that the path location may be slightly different based on your version of Windows but can be located in the Control Panel.


Note: If you have an older version of the Rally Add-in for Excel and want to update to the latest version, you will need to uninstall the current version before installing the latest version. When uninstalling and upgrading to a newer version, not all files are deleted. The following file remains after an uninstall:

  • Windows XP: %USERPROFILE%\Application Data\Rally\ExcelAddIn\rallyExcel.xml
  • Windows Vista & 7: %USERPROFILE%\AppData\Rally\ExcelAddIn\rallyExcel.xml

This file is used to store the previous Excel add-In configurations. This file is created when the add-in connects to Rally. After updating to a newer version, this file will remain in the system, and it will contain the previous configuration. When Excel is then started, the old configuration will be loaded and displayed in the Rally login screen after clicking the Rally Connect button. Solution: Rename this file before updating (it may contain saved queries you want to re-use).

Installation

Prerequisites:
  • Windows Vista/7
  • Runs on 32 and 64-bit systems
  • Microsoft Office 2007, 2010, 2013
  • Microsoft .NET Framework 4 (download)
  • Microsoft Visual Studio 2010 Tools for Office Runtime (download)
  • Note: Microsoft frequently updates the download location for the latest version of Microsoft Visual Studio 2010 Tools for Office Runtime. If the link provided above does not direct you to the correct location, you can search for Runtime and download the latest version from the Microsoft website: http://www.microsoft.com/en-us/download/default.aspx.

Connector downloads:

The Rally Add-in for Excel is distributed as a single .msi installer package. To install, simply run the msi file.

Excel Installer

Running the .msi directly will cause the installation to fail if any of the prerequisites are not detected.

A new Rally tab displays in the Excel ribbon.

Most functionality will be disabled because no connection to Rally is available at this point of configuration.

Begin by connecting to Rally in the Excel Add-in by clicking the Connect to Rally icon in the Configuration area.

This launches the Rally Connection Settings dialog:

Important: For On-Premises customers, you will need to select the Trust all SSL Certificates option and be working in your corporate network to connect to Rally from the Excel Add-in.

SSO authentication

Note: SSO is not currently supported for On-Premises users.

Rally follows the SAML specification for SSO authentication. This specification requires that a value called the RelayState be passed among the participants in a 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.

Important notes:

In order to connect to Rally using SSO, you must provide a 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 Rally's internal environment looks like this:

  • https://sso.rallydev.com/sp/startSSO.ping?PartnerIdpId=https://pingidp.f4tech.com

A complete Excel SSO URL in the Rally's 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 Rally's 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 Rally administrator or Rally Success if you are having trouble constructing a URL that correctly sets the SAML RelayState.

Basic authentication

  1. Enter your Rally server (default is rally1.rallydev.com), username and password.
  2. 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.
  3. Click Save. Follow either the export or import instructions below to complete the process.

Uninstallation

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

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

Import and export data

Export data from Rally to Excel

  1. Click the Export option in Excel.
  2. Define the Workspace and Project that you want to export. Otherwise, the default workspace and project will be used for the export process.
  3. Click the Manage Queries button to launch the Queries dialog.
  4. Click New to create a new query.
  5. New Query
  6. Specify the type of Rally object to export and choose a sort order for the data.
  7. Query Dialog

    The fields to export are specified as a comma-separated list in the Columns text box.

    The Choose button launches a Columns dialog to make it easy to select fields:

    Columns

    You can also enter subobject fields in the text box. For example, you could add Release.ReleaseStartDate or Parent.Project.Name to the comma-separated list of fields as well. Leaving Columns empty or specifying True will cause all fields to be returned.

    Important: The only way to specify subfields is to type manually. Adding subfields manually and then selecting items from the chooser erases manually entered subfields. Use caution when switching back and forth between manual entry and the column chooser. AND/OR statements must be added manually to the Filter field.
  8. Optionally, specify a filter to be applied to the query. The filter syntax is that used by the Rally WSAPI and is documented here, for example: (ScheduleState = Accepted).
  9. The Choose button launches a Filter dialog to make it easy to select a basic filter:

    Filter Dialog

    {user} is a special macro that will automatically be replaced with your user ref when the query is run.

    Subfields can be used in query clauses: (Release.Name contains "2011 Q2")

    More than one query clause can be combined using ANDs or ORs: ((ScheduleState = Accepted) AND (Release.Name Contains "2011 Q2"))

    Note: When using a query that looks for user stories and displays the parent of the story, you can use the value of the portfolio item in the column field to view portfolio item parent information.

    Once you have defined one or more queries, you are ready to export Rally data.

  10. Select a worksheet and then select a query from the Query drop-down.
  11. Click Run Selected Query to populate the current worksheet with data.
  12. Note: You can set the query row limit, up to a value of 2,147,483,647. The default value is 200 rows.



You can embed queries in worksheets when you save a workbook. The Refresh All Queries button runs all queries embedded in a saved workbook. Any pivot charts or tables based on the data are automatically refreshed.

A sample workbook is included in the installer. You can access this sample workbook after you have installed the add-in. Note that this workbook is meant to serve as an example only and may not be importable to your Rally workspace.

  • For Windows 64-bit: the sample file is in %PROGRAMFILES(x86)%\Rally Software, Inc\Rally Add-in for Excel\Sample.xlsx.
  • For Windows 32-bit: the sample file is in %PROGRAMFILES%\Rally Software, Inc\Rally Add-in for Excel\Sample.xlsx.

Note: Credentials are not stored in the Excel file—they are stored in the Rally plugin configuration settings file.

Import data from Excel to Rally

You can import defects, iterations, portfolio items (features, initiatives, and themes), releases, tasks, test cases, test case results and steps, test folders, test sets, users, and user stories.

Note: All columns in an importable spreadsheet must map to a valid attribute. If there are extra columns created by an export, remove those columns before importing.

The Import to Rally feature displays a list of importable artifact types available for your Rally workspace. Imported text fields with XML are undefined in Rally unless you follow the technique described below.

Important:If you import a text field with XML and want the XML displayed as text in Rally (such as documentation with notes) , you will need to add div tags as defined below:

<tag> text </tag>

Should be written as:

<div>&lt;tag&gt; text &lt;/tag&gt;</div>

Rally has tested and successfully imported 500,000 rows of data. The number of rows you can import may be dependent upon your version of Excel (2007 or 2010). When upgrading to a new version of Excel, the old version needs to be uninstalled and the new version installed. This process will delete log files, but configuration files will remain. Connection information and queries will be available after upgrading to a new version of Excel.

  1. After connecting to Rally in Excel, select Import to Rally in the Excel ribbon.
  2. On the Choose Import window, select the workspace in Rally that you wish to import artifact types, then select the type of artifact that you want to import to your designated workspace.
  3. Note: If there are duplicate names for the parent, associated requirement, or test case, select the checkbox to associate the work item to the parent in the same project. If you have multiple work items with the same name in different projects, selecting this checkbox prevents you from having to enter a FormattedID or OID to identify which item you want to associate to when there are duplicate named work items.
  4. Click Schema to view the schema for the chosen artifact type.
  5. The schema lists details about the importable values of the chosen artifact type. For example, the defect schema lists all valid fields in a defect. The schema is a subset of all article types available in the system with associated types in Rally.

    Field schema columns can be modified as needed. For example, you might want to have the Required field located to the left of all columns so you can see at a glance the required field for the schema. Drag and drop column headers as needed.

    You can save the schema as a PDF for reference by clicking Save PDF.

Important notes regarding schemas:

  • There are three ways to specify an object reference: Name (not all object types have this), OID, and Formatted ID (not all object types have this).
  • Acceptable boolean values include Yes/No, True/False, or 1/0.
  • For date fields, the date must be specified in the date format designated on your computer.
  • The Exists column denotes whether the artifact type value occurs in the current version of the spreadsheet.
  • For the Release Artifact Type, ensure that the spreadsheet is valid prior to importing as releases are not deletable from the undo feature in the Rally Excel Add-in and must be deleted manually in Rally.
  • The following values are not supported in the current Excel Add-in: Collections, Attachments, Links, and Read-Only.
  • When downloading the schema for any artifact type where the artifact type includes the Owner value, the valid value listed in the schema is the Owner display value and not the Owner Login Username.

For an import to succeed, the spreadsheet needs to contain a column for all required fields. Columns for all other fields are optional.

Artifact value field validation

Validate that the spreadsheet is ready for import by clicking Validate. This option reads the data from the spreadsheet, ensures that it is valid based on the constraints listed in the schema, and searches any OBJECT references specified as names or formatted IDs. If a validation error occurs, a pop-up window will provide you with information regarding the nature of the error and the column or row where the error occurred in the spreadsheet. You can save the file as PDF.

Click the Import button to pass artifact data to Rally. During the import you will have the capability to pause and resume the import process as needed. The import progress window also details the elapsed time, items remaining, average elapsed time, estimated total time, estimated remaining time, and estimated completion time.

The Import feature also allows you to undo a successful import if needed for any reason, with the exception of releases. Releases must be manually deleted in Rally.

In the example below, is a spreadsheet of user stories that needs to be imported to Rally. We reviewed the schema for the User Story artifact type and validated. Then, we clicked Import for the User Story artifact type.

After importing, there is a pop-up window confirmation that our user stories have been imported.

Upon checking the workspace in Rally, the new user stories have been successfully added.

Import users

Administrators can import multiple users with the Excel Add-in.

Note: If a network issue occurs during the import process, it is possible that a user may be partially provisioned. If this occurs, the import screen will display the number of successful and unsuccessful imported users to aid in troubleshooting. If you decide to undo or remove all users after a network issue, this action will remove all successfully imported users except for any partially created users. The undo screen will list the number of successfully and unsuccessfully removed users to assist in troubleshooting any remaining users that have not been removed.

A Rally subscription administrator must enable the Allow enhanced user capabilities through Web Services setting for any new subscription before Rally users can be created through Web Services API.

To import users, select the User type. Workspace is grayed out as it cannot be selected when importing users.

User Import Type

You can view the schema for the user attribute. The schema defines valid values for each importable field.

User Schema

Important: Custom drop-down fields, including Cost Center, Department, and Office Location, must be defined in Rally to import the values from Excel. The number of custom fields is limited to five.

Custom DropDown

Important notes:

  • If you set a default workspace for a user, you will be required to set a default project.
  • Administrators can only be specified in the Workspace column. Attempting to specify project and team membership will cause an error.
  • A user set as a viewer and team member is automatically promoted to editor.
  • Users must have the project permission set and can optionally be set as a team member.
  • The Session Time Out field represents the number of seconds the user can be logged in before the session times out. This field must be represented with discrete values in the supported range: 1800/3600/7200/14400.
  • You cannot import more than 2,000 users. If attempting to import 2000+ users with detailed logging turned on, an error is generated:
2000 Users Validation Error

Click Import to import users into Rally. When the import is complete, a pop-up displays import and undo statistics. For example, the number of successful imports and undos, number of unsuccessful imports and undos, users imported, users undone, time of import, and undone duration.

Troubleshooting and log files

If you receive this error Value cannot be null. Parameter name: obj in Excel upon export, it means that there was no active spreadsheet for the Excel Add-in to write to. There must be an active spreadsheet for the add-in to work properly.

System event logging

Errors for both importing and exporting are always logged. More detailed logging is enabled when the Log Details option is selected. Logs can be viewed as soon as a transaction occurs. Double-click on any entry in the log field to view additional details for any transaction that has been performed. The plugin issues several different kinds of requests to the Rally server (gets, puts, and so on) and communicates with the Rally server in many different ways during each operation.

Click on an individual log to display the entire log entry in a Log Message pop-up.

You can delete all generated request logs from the system for the Rally Excel Add-in. Click the Purge and Close button and all logs will be deleted from the Log Viewer and also from the RallyAddInForExcelLog in the Windows System Events logs. Once the logs have been purged, the dialogue window closes automatically.

International usage

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

There have been issues with European customers using the Excel Add-in (and import templates) with respect to the decimal point separator and the list separator in Windows 7 Ultimate in combination with MS Excel 2007 or 2010.

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 ,).

After these changes are in place to import, the templates and Excel should work as expected.

Sample files

The sample files below can be used to import users, user stories, releases, or portfolio item features into a default Rally instance scoped to Workspace → Sample Project. Each file contains the required columns and fields for the specified Rally artifact.

Revision history

  • 2.1.2 - Apr-2014
    • Enhancements/Fixes:
      • Support new Project Admin privilege when importing a User
      • Users can be imported without defining any privileges
      • Plugin tested successfully on Excel 2013
      • Project scoping for duplicate parents, associated requirements or test cases
      • Time zone allowed values now correct
      • Properly handles queries containing special characters such as "&"
      • Will create a new spreadsheet if necessary during export
      • Properly handles saving schemas to .pdf
      • Able to strip xml/html from exported text fields
      • Fixed user import bug
    • Known Issues/Limitations:
      • We've upgraded to the latest release of Ping Identity and discovered that our Excel plug-in doesn't work with version 7.1.3. If you haven't updated your Ping Identity server, we expect that it will the plug-in will work correctly. We are actively working on resolving this issue. The Excel plug-in was tested successfully with Ping Identity version 6.10.
  • 2.1.1 - Jan-2014
    • Enhancements/Fixes:
      • Supports 1,000+ projects and workspaces
  • 2.1.0 - Dec-2013
    • Enhancements/Fixes:
      • Users can now use Single sign-on (SSO) for Excel
      • Users can now be specified by username or OID
      • Supports the latest 1.43 version of Web Services API
  • 2.0.0 - Aug-2013
    • Enhancements/Fixes:
      • Bulk upload of new users, but it does not allow update on existing users.
      • Removed 10,000 row query limit.
      • Validation error files can now be saved via PDF.
      • Supports the latest 1.42 Web Services API (1.30, 1.37).
    • Known issues/limitations:
      • When importing a large number of objects, the duration of the import is directly correlated with the number of objects to import. The more objects to import, the longer the duration of the import process.
      • The validation step does not correctly identify duplicate users that are listed in a different subscription than the subscription the user running the connector is listed in. However, duplicate users are listed at the end of the import process.
      • If a massive network failure interrupts the Import Users process, you may need to manually remove a partially provisioned user. The final import screen displays any unsuccessfully imported users to aid in troubleshooting partially created users. If you choose to remove all users using the undo option, the final screen will display users that have not been successfully removed. Other helpful statistics on this screen include number of successful imports/undos, number of unsuccessful imports/undos, users imported, users undone, time of import, undone duration and more.
  • 1.11 - Jan-2013
    • Enhancements/Fixes:
      • Fixed issue with ribbon tab label not showing when there is no configuration file.
  • 1.10 - Dec-2012
    • Enhancements/Fixes:
      • Updated to support importing from Excel.
    • Known issues/limitations:
      • When uninstalling then upgrading to a newer version, not all files are deleted. The following file remains after an uninstall:
        Windows XP: %USERPROFILE%\Application Data\Rally\ExcelAddIn\rallyExcel.xml
        Windows Vista & 7: %USERPROFILE%\AppData\Rally\ExcelAddIn\rallyExcel.xml
        This file is used to store the previous Excel add-In configurations. This file is created when the add-in connects to Rally. After updating to a newer version (i.e. uninstall, reinstall), this file will remain on the system, and it will contain the previous configuration. When Excel is then started, the old configuration will be loaded and displayed in the Rally login screen after clicking the Rally Connect button. Solution: Rename this file before updating (it may contain saved queries you want to re-use).
  • 1.09 - Aug-2012
    • Enhancements/Fixes:
      • Updated to work with Excel 2007 as well as Excel 2010.
      • Supports UTF-8 character set.
      • No longer requires installation of PIAs.
      • Includes ObjectID in query sorts if not already present in order to mitigate an issue (DE14047 - when a query uses an Order by value that is not a unique value, the data returned could have duplicates and/or missing records).
    • Known issues/limitations:
      • none
  • 1.08 - Jan-2011
    • Enhancements/Fixes:
      • Fixed missing data issue when refreshing a previously run query
    • Known issues/limitations:
      • none

English

Feedback

Please send us your feedback regarding our help site. For feedback regarding the Rally product, click here to open a support case or click here to submit a new feature request
English
By submitting this form, you accept the Mollom privacy policy.
© 2014 Rally Software Development Corp | Legal