Advanced Reports

Topics:

Advanced Reports

When building reports in BSI, the standard search dialog is used. Regardless of where you build the search, the available options are the same. Below you will find several tips, tools, and examples to help you explore the full potential of reporting in BSI.

Each tab below details a different aspect of searching, including which tables to search on, how to search on multiple tables, some issues you may run into while searching on multiple tables, available search parameters, and how to create complex searches.

Display Tab Table Selection

When reporting in BSI, the most important thing to consider is which fields you will be reporting on. For ease of use, some reportable database tables are grouped under categories such as “Specimen fields”, “Results Fields”, “User fields”, or “General Database Tables”. After selecting the relevant category from the menu at the top of the column, a list of folders opens. The fields available in the folders can represent columns in the table you are looking to create. Custom user-defined reports can use a wide variety of tables to build a search, while standard reports usually only have a few tables available. If you are having trouble locating the correct tables to search on, use the filter to search within the folders and fields in your chosen category.

Example: If you are looking for history on requisition discrepancies in R2017:000519, you may first want to select “Select Requisition Fields” from the menu to show relevant tables. From that table set, you can enter “discrepancy” into the filter. The filtered list of tables then would show, among other results, three relevant tables: Req Discrepancy, Req Discrepancy History, and Req Tasks Discrepancy. Double clicking on the Req Discrepancy History folder will display the fields stored in that table, which you can then choose to display for your report.

Some fields appear on multiple tables, but will return a different set of data depending on which field you select it from. For instance, displaying "User ID" from the Study History table will return history values where a user has created or modified a study record, while displaying the "User ID" field from the Requisition table is only going to return values for users who submitted a requisition.

When displaying fields in a report, you can see the table each field came from shown in parentheses after the field name.

Some underused tables that may be useful to you:

  • Batch – Includes properties data for all Data Entry batches.
  • Esignature Log – Lists completed e-signature users, dates, and triggering objects.
  • Vial Previous Location – Shows the previous location of any vial that is no longer in the inventory (Vial Status= Out, Empty, etc.).

Tables available in BSI can be joined together to produce different results. Table joining refers to actions that would correlate data stored in two or more tables together. In essence, table joining creates relationships between associated tables via fields that are present on both tables or can be linked through a third or fourth table. Table joins result from displaying fields from different tables in a report or searching on fields from different tables.

table joins diagram

The simplest example of joining tables in BSI is running a report that returns sample, specimen, and location data. The current data associated with just one particular specimen is stored in the Vial table, while data that is shared among several specimens belonging to the same sample is stored in the Sample table. The location data for these specimens is stored in a third table called Locations. For a few samples, it might look something like this in the database:

Sample_vial_and_location_table_data_relationship_example

These three tables are stored separately, but can be linked together by the Sample ID and Vial Location ID, which each appear in two of the tables. By joining these tables in a report, we can see that specimen AAA000000 003 has a Date Drawn value of 9/15/17, a Material Type of DNA, and is stored in Box Beta.

Reporting on fields from multiple tables can be tricky. Some fields seem like they are related to each other or even that they are the same field. For example, let’s think about Date Last Modified. There are numerous instances of Date Last Modified in various tables throughout BSI, but they aren’t necessarily related to each other. The Date Last Modified vial table field contains values that match the last time vials had their data modified, while the Date Last Modified field on the Requisition table contains values for the last time a particular requisition was modified. While these two fields are called the same thing, they don’t directly relate to each other. For this reason, it’s important to consider the table on which your report fields are stored.

When joining tables in BSI, sometimes a report will generate fewer records than you are expecting. For example, if you wanted to see a list of requisitions from this year and the status of any discrepancies in them, you might try to display:

  • Requisition ID (requisition table)
  • Repository Status (requisition table)
  • Discrepancy Status (Req Discrepancy table)
  • Field (Req Discrepancy table)
  • Resolved Date (Req Discrepancy table)

It would then be easy to think that just searching on a range of Date Submitted (requisition table) values would show a list of all requisitions from this year and that the rest of the display fields would be blank if there were no discrepancies. This is not what happens in BSI. Since the Req Discrepancy table is only concerned with requisitions that have discrepancies in them, that table doesn’t contain Requisition IDs for other requisitions. Since at least one table being displayed doesn’t reference those requisitions, they can’t be returned in the report. Instead you would be returning a list of requisitions that have discrepancies in them and the corresponding data – a much shorter list.

BSI's table joins are set up in a few ways. The type of join that links two tables determines what data and how many results are returned in a search. In some cases, you may be expecting more results than BSI returns because a table join limits your results.

The vast majority of tables in BSI are linked with an Inner Join, which means that only results that appear on both tables will be returned. For example, searching on the Vial Movement History table and the Vial table will return only results for vials that were moved from their original inventory location at least once and that exist on the vial table.

inner join

A few tables are linked by Outer Joins, which means that results that appear on one or the other table, but not necessarily in both, will be returned in the report.

left outer join.PNG

full outer join

If you build a report that ends up returning fewer results than you think should be shown or is not returning any results at all, try limiting the displayed fields to just one table and see if that changes the output.

Example: Let's say you want to report on the frequency of material types shipped and where they were shipped to this year. To run this, open a user-defined frequency report. Display the Material Type (vial table) and the Destination specimens were shipped to (Req Tasks table). When searching, use the same Req Tasks table to choose a “Task Type” value of Ship. Also from that table, choose an “End Time” value of This Year.

BSI’s search dialog is set up to allow complex search terms to be easily created with “and” and “or” statements.

When entering a search value for a field, you can choose multiple values. Each value on the same line of the search term will be treated as an “Or” statement.

Example: Using this query BSI would search for any vial with a Material Type of serum, DNA, OR whole blood.

When entering search terms, you can choose multiple fields to search on. Each separate line of the search term will be treated as an “And” statement.

Example: Modifying the above query results in BSI to search for any vial with one of the selected Material Types AND that has a Vial Status of In.

When entering search terms, you can combine multiple search terms to form a complicated query. Each separate search tab will be treated as an “Or” statement.

Example: in addition to the first search tab's criteria, BSI also returns vials with a Vial Status of In and with a Vial Type of 10 ml Red Top.

multiple search criteria example

In the query shown above, the final search query would sound like this: “Return any vials with a Material Type of serum, DNA, or whole blood AND that have a Vial Status of In, OR vials that have a Vial Type of 10 ml Red Top AND that have a Vial Status of In”.

Anytime your search includes terms from different tables in BSI, a table join occurs to link the search terms and search for places where the values overlap.

Example: Let's say you want to create a report on the history of all updates to specimens stored within a specific freezer that were made last month.

Thinking logically about the goals of this report, first we must decide what types of data we need to display. We want to see the history of specimen updates, so we need to display at least some fields from the vial_history table. We also may want to show some current values for the vial, such as the Vial Status, or locations values for the vials.

display fields

The next step is to figure out what criteria to search on. Since we want to see only specimens that were stored in the specific freezer during that time, there are three scenarios we need to account for: vials that were removed from the freezer last month, vials that were moved to the freezer last month, and vials that were stored in the freezer before last month and haven’t been moved.

To do this, we can create a complex search statement with multiple criteria using the vial and vial_ movement_history tables.

To cover vials falling under the first scenario, we can search for all vials that:

  1. Have a history record for being moved from Freezer 45 to a different location.
  2. Were moved out in the last month.
search tab 1

For the second scenario, we can search for all vials that:

  1. Have a history record for being moved into Freezer 45 from a different location.
  2. Were moved there in the last month.
search tab 2

The final scenario is the most complicated. We can search for all vials that:

  1. Are currently in Freezer 45 (vial table).
  2. Were not moved to Freezer 45 last month.
search tab 3

We also need to specify which history records should be returned in the resulting record, since we don’t want all of them. We want only history that was captured last month. In order to ensure that criteria applies to all the results, we need to add “Date Modified (vial_history table) = Last Month” to each part of our search.

example_search_criteria_adding_modification_date_criteria

Important to note: If you click any search term, the corresponding table will be shown on the left side of the dialog and the field will be highlighted. This can be useful if the field name does not obviously tell you which table the field will search against, as in this example where there are two “Date Modified” fields.