Spreadsheets Module – Desktop

Spreadsheet Basics

A spreadsheet is a table of values that are arranged in rows and columns. In Progeny, you use spreadsheets to organize multiple individuals, pedigrees, samples, or markers by rows and the fields for entering the corresponding data by columns.

Spreadsheet Types

Unlike a datasheet, which displays the data for a single individual, pedigree, sample, or marker, a spreadsheet displays the data for all individuals, all pedigrees, all samples, or all markers. You can create four different types of spreadsheets in Progeny:
Individual spreadsheets – Individual spreadsheets are used to enter and store information about individuals. An individual spreadsheet can contain individual, pedigree, and marker fields.

Pedigree spreadsheets – Pedigree datasheets are used to enter and store information about pedigrees. A Pedigree datasheet contains only pedigree database fields.

Sample spreadsheets – Sample spreadsheets are used to enter and store information about samples. A Sample datasheet contains all four types of Progeny database fields—individual, pedigree, sample, and marker fields.

Marker spreadsheets – Marker spreadsheets are used to store information about markers. A Marker datasheet can only contain marker fields.

Generating a Spreadsheet

Unlike a datasheet, which displays the data for a single individual, pedigree, sample, or marker, a spreadsheet that you generate from the Progeny main window displays the data for all individuals, pedigrees, samples, or markers in a Progeny database. A spreadsheet that is generated for all individuals, all pedigrees, and so on in a Progeny database is sometimes referred to as a global spreadsheet.

To generate a spreadsheet

1. 1. From the Progeny main screen, click the down arrow next to the Indiv SS You can choose either Create New Individual Spreadsheet or Individual Spreadsheet Formats.
      • If you choose Create New Individual Spreadsheet, a new tab opens with Fields, Columns, Sort, and Query panes.
      • If you choose Individual Spreadsheet Formats, a new tab opens allowing you to search for, edit, and run saved spreadsheet formats.
   2. In the Create New Individual Spreadsheet menu, drag fields into the Columns, Sort, or Query panes.
      • The search bar above the field chooser on the left provides a full-text search across your fields. You can enter a whole or partial field name and the search function will display all possible matches.
      • If you add a field to the Columns pane and hover over its row, you will see these three icons on the right side: 
        • Adds the row to the Sort pane.
        • Adds the row to the Query pane.
        • Deletes the row from the Columns pane.
      • To the right of the Columns, Sort, and Query panes are three buttons stacked vertically: 
        • Moves the selected field/row up one position in the order
        • Moves the selected field/row down one position in the order
        • This button will delete all fields/rows within the selected pane.

For all fields, other than table fields, the order in which the fields are dragged is the order in which they will appear in columns (from left to right) in the spreadsheet.

• Optionally, to sort the data in the spreadsheet, drag fields from the left side into the Sort pane or click the Sort button on one or more of the rows in your Columns pane.
  • Use the following two buttons to change the sort order.
    
  • For each row in the sort order, you can choose between Ascending (A-Z) or Descending (Z-A) order.
• Click the Run button to generate the spreadsheet. If any data is entered into the spreadsheet, click the Save button to commit the changes. Lastly, if you have run the spreadsheet and want to make changes to the columns, sort order, or query, click the Edit button to return to the Create New Individual Spreadsheet section.
  
  
  
  
• If you want to view the pedigree of an individual within the spreadsheet or see an individual’s datasheet, right click on the first column displayed for that person’s row and choose either Open Pedigree or Open Individual Datasheet.

Tables in spreadsheets

When you build a spreadsheet, tables are no longer displayed as an individual entity but are broken apart with each of their table subfields being displayed as a distinct entity.

Above is a group of subfields from a table within the Create New Individual Spreadsheet section.

In previous versions of Progeny, you would add the table to the Columns pane then check the boxes for each table subfield you wanted on the spreadsheet – in Progeny 10 you merely need to drag the individual table subfield into the Columns pane to add it to the spreadsheet format.

In Progeny 10, Lookup Table fields are handled the same way as Table fields (see previous section). Each subfield of the lookup table is displayed in the fields list as a separate entity and you merely need to drag the specific subfield you wish to use in the spreadsheet into the Columns pane.

Entering Data into a Spreadsheet

There are a variety of options for entering data into a spreadsheet, including manually entering data, copying and pasting data, finding and replacing data, using the Fill feature, and importing data. Regardless of the method used, after you have completed data entry in a spreadsheet, click the Save button on the spreadsheet toolbar to save the data.

To manually enter data into a spreadsheet cell

Data entry for the fields in a spreadsheet is very straight forward. Click in the cell and enter the needed data or select the data from a dropdown list. It is possible to enter data into a spreadsheet cell that has no highlighting or one that is highlighted in yellow (a table field). Fields that are highlighted gray are read-only fields, such as system fields or lookup table subfields, and you cannot enter data directly into these fields.

To move from one field to the next within the same row, press the Tab key on your keyboard. To move back a field within the same row, press and hold the Shift key and then press the Tab key. To move from one row to the next, press the Enter key. (Pressing the Enter key does not delete the data in a spreadsheet cell.)

To add more rows to a table in a spreadsheet

1. Select the individual, sample, pedigree, or marker for which you are adding the data, and on the spreadsheet toolbar, click the Add Row button.
2. Click in each table cell of the blank row to add the necessary data.

To add data to a lookup table field in a spreadsheet

1. Click on the lookup table field and select the appropriate value for the field.
2. On the spreadsheet toolbar, click the Save button.
3. On the spreadsheet toolbar, click the Run button to populate the table subfields for the lookup table.

To copy and paste data

It is possible to use standard keyboard commands and/or menu commands to copy text data or numeric data from one cell into another. To copy text in a range of cells, click and hold the left mouse button and drag the mouse to select the region that is to be copied. Once selected, you can copy the data using Edit > Copy, right-click Copy or CTRL + C on the keyboard. To paste the copied data, select the area where the data will go then use Edit > Paste, right click Paste or CTRL + V on the keyboard to paste the data.

To find and replace data

The Edit menu contains Find and Replace options that you can to search for data in a spreadsheet, and if needed, replace text or numeric data with another value.

To use the Fill feature

The spreadsheet Fill function can fill blank cells with data from adjacent cells as long as the cells are the same field type. The Fill function is found under the Edit menu and has four options:

• Fill Down – fills cells that fall below a selected cell.
• Fill Right – fills cells that all to the right of a selected cell.
• Fill Up – fills cells that are located above a selected cell.
• Fill Left – fills cells that are located to the left of a selected cell.

When filling blank fields that are dropdown list fields, the filled values are stored only in the spreadsheet and not at the database level. For example, the available values for a filled dropdown list field in the spreadsheet are Yes, No, and Pending and then you set all the fields to Pending. Although Pending is displayed in these filled fields, when editing any of these fields, the only available values will be Received, Due, and N/A, which are the values stored in the database for the field.

To fill in Amy Brown as the Referring Physician for Patients2 through 12, select the column that includes Amy Brown as the Referring Physician for Patient 1 and the blank Referring Physician cells for Patients 2 through 12, and on the Edit menu, click Fill > Fill Down.

To fill in the daytime phone as the evening phone number for Patients 1 through 9 select the column that includes the daytime phone number for Patients 1 through 9 and the column that includes the evening phone number for the same patients, and on the Edit menu, click Fill > Fill Right.

To import data into a spreadsheet

It is possible to import data into a spreadsheet from a delimited file. The file can be a comma delimited text file, a tab-delimited text file, or you can specify another delimiter type. You can import flat fields (standard fields) or you can import into table rows. If you are importing flat (standard) fields then the data that is in the spreadsheet and the data that you are importing must be the same field type. If importing dropdown fields, and the data that is being imported has different values than the data in the spreadsheet, then the imported values are stored only in the spreadsheet and not at the database level. For example, if the available values for a dropdown list in the spreadsheet are Yes, No, and Pending and the available values for the corresponding dropdown list in the import file are Received, Due, and N/A, and you set the value in the import file to Received, then after the import, Received will indeed be displayed in the spreadsheet field; however, when editing the field, the only available values will be Yes, No, and Pending, which are the values stored in the database for the field. If importing data into table rows, then the spreadsheet can contain only the table and its subfields. You can import new rows of table data, however, you cannot import into existing rows of table data.

1. On the spreadsheet toolbar, click the Import The Import Data dialog box opens. Tab Delimited is selected by default.
   
2. Select the type of delimited file that you are importing. (If the file is not tab delimited or comma delimited, then you must specify the delimiter that the file uses.)
3. Specify the values for the following:
   • Number of Unique Identifiers in the Import File that match columns in the spreadsheet – enter the number of columns in the text file that you are importing that match to the number of columns in the spreadsheet. No data in the matched columns is overwritten. Only data from the matched point forward is overwritten. For example, if your spreadsheet and import text file have three columns each, and you indicate “1” for the number of matched columns, then when the data is imported into the spreadsheet, the data in Column 1 of the spreadsheet is not overwritten; however, the data in Columns 2 and 3 of the spreadsheet is overwritten by the data in Columns 2 and 3 of the import file.
   • Data options:
     1. Import into tables only – select this option only if you are importing data into table cells in the spreadsheet.
     2. Import blank cells over existing data – allows data in the spreadsheet to be replaced by blank cells.
     3. Ignore blank cells – if the import file contains blank cells, do not replace the data in the spreadsheet with blank cells.

4. Click Import. The Open File dialog box opens.
5. In the Open File dialog box, browse and select the file that you are importing, and then click Open. If there are no errors with the import, a message opens indicating the import operation was successful.
6. Click OK. The message closes. You will return to the spreadsheet tab and see the imported information displayed in the spreadsheet.

Exporting Spreadsheet Data

Just as you can import data into a spreadsheet from a text file, you can export data from a spreadsheet to a text file. The file can be a comma delimited file, a tab-delimited file, or you can specify another delimiter type. You can export any user-created field as well as system fields.

To export spreadsheet data

1. On the spreadsheet toolbar, click the Export button. The Export Data dialog box opens.
2. Select the Export Format.
   •  If the file is not tab delimited or comma delimited, then you must specify the delimiter that the file uses.
   • If you select Linkage, then the data is exported to a pedigree file format (.PED). In addition to the exported data, the first five columns of file also contains the following pedigree relationship fields in the order indicated – UPN, Father ID, Mother ID, and Gender. You must also specify the way in which the individuals in the pedigree are to be identified. The default value is the UPN (Unique Person Number) for each person in the pedigree, but you can select a different value.
   • If you select BRCAPRO, then the data is exported in a format that can be used in the BRCA Risk Analysis application.
3. Select the data that is to be exported.
   • Export Table Rows Only – If you are exporting table data, and want to export only the table rows that contain data (and not the blank row that indicates a table), then select this option.
   • Export One Row Per Individual – If you are exporting table data, and want to maintain a single row per individual (or sample), then select this option.
4. Click Export. The Save As dialog box opens. The Save as type field is automatically populated with the file type of text (.txt) and you cannot change this value.
5. Browse to the location in which the exported file is to be saved, and then in the File name, enter a name for the file.
6. Click Save. A message opens indicating, The export operation was successful.
7. Click OK. The message closes. You will then return to the spreadsheet tab.

Saving and Loading a Spreadsheet Format

After you have generated a spreadsheet with a specific format, you can save the format. You can then retrieve this saved format and load it into another spreadsheet of the same type.

To save a spreadsheet format

1. Format and generate the spreadsheet as needed.
2. On the spreadsheet toolbar, click the Save Format button.The Save Format dialog box opens.
3. Enter a name for the spreadsheet format and optionally, a description of the spreadsheet format then do one of the following:
   1. Select an existing folder.
   2. Click New Folder and on the Format Folder dialog box that opens, do the following:
      1. Enter the name of the new folder in which to save to format.
      2. Select the location for the new folder. The folder can be stored at the root level (the same level as the default folders), or it can be a sub-folder of one of the default folders, or of another folder.
      3. Click Save.

4. Click Save on the Save Format dialog box.

To load a spreadsheet format

There are two primary methods for loading a saved spreadsheet:

1. Click the dropdown icon next to the Indiv SS button on the main screen and choose Individual Spreadsheet Formats. This opens a new tab with the Individual SS Formats menu.
   • Within the Individual SS Formats menu you can first search for existing formats using the search bar at the top. This is a full-text search across both format names and format descriptions.
   • The left-side pane will show your spreadsheet format folders while the right-side pane will display a list of formats saved within a selected folder.
     • The right-side pane will display the format name in black text and the description in grey text. You will additionally be shown the user account that created the format (with timestamp) and the user account that last modified the format (with timestamp)
   • Click the Run button to launch the spreadsheet or click the Edit button to open the New Individual Spreadsheet menu prepopulated with the selected saved spreadsheet format’s parameters.
     
     

2. Click the Query button on the main interface.
   • The Query menu offers a shortcut into your saved spreadsheet formats. Choose a saved format from the dropdown menu under Query Criteria and the fields configured for the format’s query section will be displayed. You can then make changes to the query parameters.
   • Click the Query button in the top right to launch the spreadsheet format with your modified query parameters.

Running Spreadsheet Queries

You can query the Progeny database to retrieve a specific subset of data. You can run a query one time from within a given spreadsheet, or if you need to run the query frequently, you can save the query as part of the spreadsheet format, and then run the query on as-needed basis. When you set up the spreadsheet for the query, the spreadsheet does not have to contain the fields against which the query was run. For example, you could set up a query for all female individuals under the age of 50 that have been diagnosed with bilateral breast cancer and the spreadsheet is to display the demographic information for the individuals who meet the query criteria. You would set up the spreadsheet with the following fields—First Name, Last Name, Age at Diagnosis, Current Age, and so on, but you would not need to include the Gender field and the BRCA Diagnosis1 field as these fields would display the same value (Female or BL BR, respectively) for all the individuals who are retrieved by the query.

To run a spreadsheet query

1. Create a new spreadsheet or go into your Individual Spreadsheet Formats and choose the Edit button for your desired spreadsheet format.
   • If creating a new spreadsheet, add fields to your Columns pane to generate the query output formatting
2. Either drag and drop fields from your Fields pane into the Query pane or click the Query button for a field within your Columns pane.
   
   • If you need to remove a row from your Query pane, either right-click the field and choose Delete Row or click the Delete button on the right side of the query row.

3. Click the Run button to run the query. The results of the query are displayed onscreen in the spreadsheet.
   • If needed, you can now save this query as a spreadsheet format if you will be using it again in the future.

Sorting and Filtering Spreadsheet Data

Sorting determines the order of the data that is displayed in a spreadsheet. For example, do you want to display your data based on a birth date that is sorted in reverse chronological order? You can sort the data in a spreadsheet at any time – immediately after you create a spreadsheet, or after you query the spreadsheet data to retrieve a subset of the data. Different sorting options are available for the different types of data columns (text, date, and numeric). After you select a sorting option, the spreadsheet display is dynamically updated.

You can also filter spreadsheet data. Filters retrieve a specific subset of the spreadsheet data based on specific constraints (or filters) that you set. For example, you might want to filter spreadsheet data to show only those individuals that were born in the 1970s. You can set multiple filters for a spreadsheet. Multiple filters are mutually inclusive. Only those individuals, pedigrees, samples, or markers that meet all the filtering criteria are displayed in the spreadsheet. As with sorting, you can filter the data in a spreadsheet at any time – immediately after you create a spreadsheet, or after you query the spreadsheet data to retrieve an initial subset of the data.

Although sorting and filtering spreadsheet data are two completely separate actions, you can combine them as needed to produce a customized dataset that best fits your business needs. For example, you can sort spreadsheet data based on reverse chronological birth dates, and you can then filter the sorted data to show only those individuals that were born in the 1970s.

To sort spreadsheet data

1. Click in the column header for any text, date, or numeric data column. A menu opens with a Filter option and sorting options that are specific to the column type.
2. Select a sorting option for the column.

 

To filter spreadsheet data

1. Click in the column header for any text, date, or numeric data column. A list opens with a Filter option and sorting options that are specific to the column type.
2. Select Filter. The Filter column dialog box opens.
3. Set the filter for the column. For example, the image below shows how to filter spreadsheet data to show only those individuals who were born in the 1970s.
   • In another example, the image below shows how to filter spreadsheet data based on multiple filter values. The values are separated by a semi-colon (;) which acts as an OR operator for the filter. The filter shown below would filter the spreadsheet data based on Medical Released Received that have been have a value of Yes or Pending.
4. Click OK. The spreadsheet display is updated based on the filters that you have set. Any column for which you have set a filter is marked with a Filter icon.
   • Optionally, at any time, clear a filter by right-clicking on the column header for the filtered column, and on the context menu that opens, click Clear Filter.
5. The spreadsheet display is dynamically updated to include all of the data in the column.

Printing Spreadsheet Data

After you generate a spreadsheet, you can print the spreadsheet. You can print all the pages in a multipage spreadsheet or you can print selected pages.

To print a spreadsheet

1. On the spreadsheet toolbar, click the Print The Print dialog box opens.
2. On the Print dialog box, select the needed printing options, and then click Print.

Counting Spreadsheet Data

After you generate a spreadsheet, you can click the Count button on the spreadsheet toolbar to count the number of individuals, pedigrees, samples, or markers (that is, the number of rows) in the spreadsheet. A Count dialog box opens, indicating the number of items (rows) that the spreadsheet contains.