BioRails4Office¶

• BioRails4Office
• Overview
• General Functions
• Task Functions
• Sheets

BioRails4Office (B4O) is a collection of MS Office® add-ins that are used to integrate BioRails with MS Excel® (MS Word® and other desktop applications integration to follow). It is built against the open web services of BioRails and is available under commercial license from TheEdge. Supporting this application is an experimental BioRailsUtilities.xla that provides additional functionality such as catalogue integration, data import routines and template management code. These applications are distributed and documented downloaded from the br-addins project.

Overview¶

BioRails4Office has been implemented to provide a code-free method of integrating Excel with BioRails, specifically to process tasks. Additionally, as part of the implementation, B4O provides a rich client COM API so that a high level of automation can be achieved.

Excel was chosen as the first processing tool for BioRails for a number of reasons

• It is almost universally employed by biologists in pharmaceutical drug discovery
• It is possible to adapt existing templated systems leveraging on the existing investment in Excel
• The document paradigm of Excel facilitates off-line data capture and processing. This means that a workbook can be used without being connected to BioRails, remotely by a CRO for example.

There are a few draw backs. BioRails allows a task to be modified at run-time, adding more rows of data, and even new parameters to the data entry sheet. This level of flexibility is difficult to replicate in Excel ¹, however, for routine experimentation, Excel is eminently suitable.

Tool Bar¶

Using the toolbar as a reference point, this page presents the functionality available through BioRials4Office and how to use these functions is described in the scenarios and COM API for those looking to understand the how to automate the processes.

Toolbar
Icon	Name	Description
!/attachments/download/397(Explore BioRails folders)!	Explore	Explore the BioRails Projects and upload, download or open files and articles
!/attachments/download/398(Current tasks assigned to logged on user )!	My Tasks	Show any tasks assigned to the logged on user that are not complete, selection will insert the task as a Task Sheet
!/attachments/download/396(Browse any task from projects)!	Browse Task	Brows task to insert as a task sheet in the current workbook
!/attachments/download/405(Upload task )!	Upload Task	Upload the task values to the parent task of the active Task Sheet
!/attachments/download/407(Create a new task)!	Create New Task	Add a Task Sheet against a Process Version that will upload as a new Task in BioRails]]
!/attachments/download/399(Open folder for the task in BioRails)!	Open Task Folder	Open the folder for the parent task of the Task Sheet
!/attachments/download/402(Insert a task template)!	Insert Task Template	Insert a Task Template into the active workbook
!/attachments/download/395(Apply task template to task sheet )!	Apply Template	Apply task template to the task sheet
!/attachments/download/400(Refresh task values)!	Refresh Task	Refresh the task values in the Task Sheet from the parent task
!/attachments/download/403(Upload a file to task folder in BioRails)!	Upload File	Upload file to parent task folder of the active Task Sheet
!/attachments/download/404(Upload embedded file to task folder in BioRails)!	Upload Embedded File	Upload embedded file (ole object) in Excel as file or image to parent task folder of the active Task Sheet
!/attachments/download/406(Upload active workbook to task folder in BioRails)!	Upload Workbook	Upload the active workbook and load it into the parent task folder of the active Task Sheet
!/attachments/download/401(Set the registration key)!	Registration	Apply registration keys to access the BioRaiols4Office functionality
!/attachments/download/394(about - version information)!	About	Version information for XLA and COM interface

General Functions¶

BioRails4Office

The login dialog is only shown when a connection must be made to the BioRails server and there is not an existing connection. The connection will be maintained for the life of the Excel process that created it (ie until the Excel session is ended).

Use your BioRails username and password (this would be the same as your Windows credentials if LDAP is implemented. The web service is the connection string and must end in /biorails/api, however, this will be appended for you if you simply copy and past he value from your web browser.

This session will be maintained for as long as you keep Excel open. It can be changed form the Explorer dialog using the connect button.

Exploring¶

The content stored in BioRails folders can be made available to Excel through the project explorer. Thus images, files and text can be downloaded to the client or uploaded into BioRails. This functionality obeys all the same access control rules as BioRails.

To explore press the Explore button on the BioRails4Office-Toolbar. Login is managed automatically. The next stage is to select the project to explore. By default this will point to the last project. The Glossary-ProjectTree will then populate.

File operations are included to upload and download images, or simply open in a Browser or word. Functions also exist for inserting content directly into Excel. Search functionality is included to home on on specific content.

Addins

Registration¶

Licenses are registered through this form. Once all the data has been added press Check Key and the licensed modules will be checked. Licenses are available from TheEdge

Options & About¶

Yes there are two about buttons, we seem to be short of icons, the first one is actually options!

Options¶

Logging

Logging can be enabled to multiple levels

1. No Logging
2. Errors only to immediate window
3. All events to immediate window
4. All events to log file

The log file location can be set and cleared. You can open an immediate window to see its logging by using the key combination alt-F11 then alt-G

Options

• Automatically Apply Template Formula should be checked if the user wants Task Templates to be automatically applied to Task Sheets when they are inserted into the workbook.
• Web service time out will not cancel the request but sets the amount of time that the code will wait for a response from the server before moving on. It should only need extending with large Task Sheets that have many thousands of data points, or when there is very poor performance accessing the server.

About¶

• !/attachments/download/420(About BioRails4Office)!
  the version information for the BioRails4Office XLA
• !/attachments/download/421(ABout the client API to BioRails)!
  The about box for the .NET assembly BioRailsClientAPI

Task Functions¶

My Tasks¶

When an experiment is registered in BioRails it will contain one or more tasks. Each task has a status and can be assigned to a user. The purpose of My Tasks therefore is to present the current tasks to the scientists in Excel so they can download these tasks, run them and load up the information, all without having to leave Excel.

A task is current when it has a status of

• New
• Processing
• Validation
  and not
• Waiting
• Aborted
• Rejected
• Complete

The My Tasks dialog shows all the tasks in a list view which is sorted by date (oldest to the top). Select and press OK or double-click on a task icon will insert that task sheet, with data into the workbook. If there is a Task Template available in the workbook, then it is automatically applied to the task sheet thus integrating the Task Sheet fully into the workbook.

Addins

Browse Tasks¶

Any task can be browsed and inserted into a workbook no matter what it status or to whom it is assigned, as long as the user has access to the Project that contains it. Double-clicking on a Task or selecting and pressing OK will insert a Task Sheet from that Task. Search functionality is included.

Upload Task¶

The data in a Task Sheet can be uploaded to its parent Task at any point. When the upload button is pressed, a document is sent to the server containing all the details for the task and its values. This is then processed at the server and each value undergoes the same validation it would if it were entered in a BioRails Task Sheet. If the task has been created, then a new Task is inserted into the experiment as well as the data.

Once the data has loaded the user is prompted to open the Task folder where the data can be reviewed and the task status updated.

Create Task in XL¶

It is more usual for a task to be created in BioRails with an experiment, however, it is also possible to create a new task in Excel. The user has to select the Process Version against which the Task Sheet is built and the Experiment to which it belongs.

When the task is uploaded, a new Task is also inserted in the experiment.

Open Task Folder¶

Simply opens the folder for the parent Task of the active task sheet. This will only work if there is either a Task Sheet active, or there is a Task Sheet in the Active Workbook. If there is more than one then the user will need to select their intended Task.

Insert Task Template¶

Purpose¶

A Task Template is used to store formula and validation rules for any Task Sheets that are inserted to the same workbook and have been built against the same process version. The Task Template looks exactly the same as a Task Sheet other than

• it has no Experiment name in its headers
• its Task Id = 0
• it has a blue sheet tab

Apply Template to Task¶

A Task Template can be applied to a Task Sheet at any point by pressing the Apply Task Template button. This will only work if there is

• at least one Task Sheet in the Active Workbook
• the Task Sheet has a Process Version ID that matches a Task Template

Refresh Task¶

The data in a Task Sheet can be refreshed, picking up changes made to the data from the server. The user is warned that this will overwrite any cell values since added to the TaskSheet. Protected cells in the TaskSheet are not refreshed.

Upload File¶

A file can be uploaded into the parent Task Folder for the active Task. This is useful as a mechanism to store raw data files in BioRails an a structured manner.

Upload Embedded File¶

Files embedded in Excel can be loaded directly into the parent Task folder of the Task Sheet. This can include binary files, XL charts as well as images (or both). The fist step is to browse the embedded file that is to be uploaded. This is because any file embedded within Excel can be loaded. Once slected, the user has the option to modify the title and filename. There is also the option only to upload an image of the file which is useful if the binary needs special software to render it.

Upload Workbook¶

This button simply uploads the active workbook to the folder of the parent Task of the Task Template providing a mechanism for long term storage of this file.

Sheets¶

TaskSheet¶

A Task Sheet is a BioRails task that has been downloaded and inserted into Excel. The sheet contains enough persisted information so that it can be managed off-line and the data uploaded at a later data to the parent task.

Structure¶

The Task Sheet is generated directly from either a task in BioRails, or a Process Version if it has been created in Excel. It is composed of a number of sections.

1. Headers - where information is held about the task
2. Parameters - describes the type of data to be captured in cells of that column, including default units
3. Contexts - contains the values that will be returned to the task assuming they pass validation.

Headers

The sheet has a headers section used to store details about the Task, its parent Experiment, connection details and so forth. These headers are for information only and can be safely hidden.

Parameters

The parameters for the Task are each written to their own column in Excel (Contrasting BioRail's Task Sheet with its embedded table format) which makes the worksheet easier to add Excel formula.

Context

Each row of the Task Sheet below the parameters is a row in a context. Each row is identified uniquely. At the intersect of the context rows and the parameter columns we find the task values. By default, no data validation occurs at this level by default, however, utilities are available that can be used to extend the data validation in a similar way to that we see in BioRails.

Any data stored in the parent Task is included in the download and can be refreshed from the server at any point.

Formatting¶

A task sheet tab is coloured green (as opposed to the blue-tabbed task sheet). Cells that will return data to BioRails are shaded and bordered, the border colour indicating the role of the parameter linked to the cell. If the cell is linked to a dictionary, then a comment is added to identify that dictionary. This provides the necessary hook to add data validation using the B4O utilities.

Working Off-line¶

A task sheet can be inserted into any unprotected workbook. There is no requirement for a connection to the server unless data is to be exchanged. This means that the file can be saved and e-mailed to external sources to be filled in. When it is returned, anyone with BioRails4Office installed on their computer and an account that can access the parent Task, can upload the data back into BioRails.

Multiple Tasks¶

Multiple Tasks Sheets can be added into a workbook. This means that Excel can be used to consolidate data from across multiple tasks and experiments.

Task Template¶

Events¶

There are two events that fire the copy

1. When a Task Sheet is inserted
2. Clicking the Apply Template button

Conditions¶

A Task Template works by acting as a repository for default values, formula and validation rules. These are entered any where on the Task Template. For the cell functions etc to be copied across the containing cell must be locked .

The cells in the Task Template are copied across into Task Sheet if

• The cell is marked as locked
• The Task Sheet has the same Process Version id as the Task Template
• The corresponding cell in the Task Sheet has not been protected

It is usual to hide a Task Template sheet in a workbook to avoid confusion.

---

See also

1. br-addins
   1. B4O
   2. B4OUtilities