Source: https://help.rallydev.com/rally-add-excel-installation-user-guide#Installation
CA Agile Central Add-in for Excel Installation & User Guide
Connector Downloads:
The CA Agile Central Add-in for Excel 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 just update) new users as a bulk upload.
This video provides an overview of the CA Agile Central Add-in for Excel, and how it can perform advanced imports and exports of data.
This installation and user guide includes:
- Install the Add-In
- Uninstall the Add-In
- Set up the Add-In within Excel
- Authentication
- Export data
- Import data
- Sample files
- Troubleshooting and log files
- Revision history - enhancements and known issues
Install The Add-In
Verify The Current Version
If you have previously installed the CA Agile Central Add-in for Excel, determine the current version installed:
In Windows, go to Start → Control Panel → Programs and Features → CA Agile Central 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.
Prerequisites:
- Windows Vista/7/8
- 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)
Connector downloads:
The CA Agile Central Add-in for Excel is distributed as a single .msi installer package. To install, run the msi file.
Running the .msi directly will cause the installation to fail if any of the prerequisites are not detected.
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.
Set Up The Add-In Within Excel
Once you install the Add-In, a new CA Agile Central tab displays in the Excel ribbon.
Most functionality will be disabled because no connection to CA Agile Central is available at this point of configuration.
Begin by connecting to CA Agile Central in the Excel Add-in by clicking the Connect to CA Agile Central icon in the Configuration area.
This launches the CA Agile Central 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 CA Agile Central from the Excel Add-in.
Authentication
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.
In order to successfully login using SSO and the Excel Add-in, you must be running a supported version of Internet Explorer. Check with your internal IT department to update to the latest version of IE.
Excel may show an expected identifier script error or un-supported browser message when attempting to login using SSO if you are using an unsupported version of IE. The Excel add-in uses a .NET 4.0 webform component to display the browser-interactive aspects of a successful SSO IdP/Rally login action. By default, .NET will always use the current version of IE installed on the user's computer when displaying a webform.
Basic Authentication
- Enter your CA Agile Central server (default is rally1.rallydev.com), username and password.
- Click Save. Follow either the export or import instructions below to complete the process.
Export Data From CA Agile Central To Excel
- Click the Export option in Excel.
- Define the Workspace and Project that you want to export. Otherwise, the default workspace and project will be used for the export process.
- Click the Manage Queries button to launch the Queries dialog.
- Click New to create a new query.
- Specify the type of CA Agile Central object to export and choose a sort order for the data.
- Optionally, specify a filter to be applied to the query. The filter syntax is that used by the CA Agile Central WSAPI and is documented here, for example: (ScheduleState = Accepted).
- Select a worksheet and then select a query from the Query drop-down.
- Click Run Selected Query to populate the current worksheet with data.
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:
You can also enter sub-object 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.
The Choose button launches a Filter dialog to make it easy to select a basic filter:
{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"))
Once you have defined one or more queries, you are ready to export CA Agile Central data.
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 CA Agile Central 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 CA Agile Central plugin configuration settings file.
Export Test Case Steps
Important: To ensure that test case steps are exported correctly, verify the following:
- Test case Input and Expected Result fields are populated for the test cases you want to export.
- Ensure that test case steps are not just listed in the Validation Input and Validation Expected Result fields. These fields export from the Add-In using the Test Case type.
Click here to learn more about using Excel for test cases and test case steps.
Import Data From Excel To CA Agile Central
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.
The Import to CA Agile Central feature displays a list of importable artifact types available for your CA Agile Central workspace. Imported text fields with XML are undefined in CA Agile Central unless you follow the technique described below.
CA Agile Central 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.
- After connecting to CA Agile Central in Excel, select Import to CA Agile Central in the Excel ribbon.
- On the Choose Import window, select the workspace in CA Agile Central that you wish to import artifact types, then select the type of artifact that you want to import to your designated workspace. The last workspace and artifact type used during the session are selected by default.
- Click Schema to view the schema for the chosen artifact type.
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 CA Agile Central.
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 CA Agile Central Excel Add-in and must be deleted manually in CA Agile Central.
- 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 CA Agile Central. 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 CA Agile Central.
In the example below, is a spreadsheet of user stories that needs to be imported to CA Agile Central. 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 CA Agile Central, the new user stories have been successfully added.
Import Users
Administrators can import multiple users with the Excel Add-in.
To import users, select the User type. Workspace is grayed out as it cannot be selected when importing users.
You can view the schema for the user attribute. The schema defines valid values for each importable field.
Important: Custom drop-down fields, including Cost Center, Department, and Office Location, must be defined in CA Agile Central to import the values from Excel. The number of custom fields is limited to five.
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:
Click Import to import users into CA Agile Central. 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 CA Agile Central server (gets, puts, and so on) and communicates with the CA Agile Central 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 CA Agile Central 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.
You can save logs to a file if you need to send logs to CA Agile Central for troubleshooting.
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.
No comments:
Post a Comment