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.
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.
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:
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.
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.
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.
Before you begin a search, carefully consider the parameters which will limit your results and how to achieve desired results with the operators and options available in BSI's search dialog:
= (Equals) & != (Not Equals) Operators
These operators can be used when searching on any type of field. The results returned will either have the specified value or will not have it.
Example: Searching on "Material Type != Cell Line; DNA" will return all results for all vials where the Material Type value is something other than Cell Line or DNA.
Searching on “Material Modifiers != @@Missing” will return all results which have any value for the Material Modifiers field.
The "@@Missing" variable for these two operators allows you to search for records which either have any value or have no value for a field.
< (Less), <= (Less or Equals), > (Greater), & >= (Greater or Equals) Operators
These operators can be used with any non-choice type field.
Example: The search line "Mass (g) <= 12.555" will yield results for all vials that have a Mass value of 12.555 or less.
=@ (Like), !=@ (Not Like), & =^ (Equals Ignore Case) Operators
These operators can only be used on character fields and work best when combining them with wildcard characters. Wildcard characters include the vertical bar (|) or underscore (_), which both stand in for a single missing character, and the asterisk (*), which stands in for any number characters.
- =@ (Like) allows you to search on similar values using wildcards.
- !=@ (Not Like) allows you to search on values that are not similar to the specified one.
- =^ (Equals Ignore Case) is the same as a =@ (Like) search except that it ignores capitalization.
Example: Searching on "Notes =^ *green*" will return results for records that have a value of "green", "Green" or "GREEN" with any number of characters on either side of it.
=& (All Term), =| (Any Terms), and !& (does not contain) Operators
These operators only apply to multi-choice fields.
- =& (All Term) returns records that include all specified values.
- =| (Any Terms) returns records that include at least one of the specified values.
- !& (does not contain) returns records that do not include any of the search terms entered.
Example: Entering a search of "Tests Requested =& Electrolyte Panel; Liver Function Panel; Urinalysis" would return results for records that include all 3 specified values. Returned records might include additional values.
Entering a search of “Daily Medications !& Aspirin=2; Insulin=*; Oxycodone=1” would return results where the specified fields are not present with the specified values. A Daily Medication value of “Aspirin=3; Vitamin D” would be returned because Aspirin does not have a value of “2” and Insulin is not part of the value.
Relative Search Dates
Relative dates are available for date fields. Operators include:
- Last _ Days, which allows you to search on dates in the previous x number of days.
- Next _ Days, which allows you to search on dates in the upcoming x number of days.
Example: Searching on "Date Drawn is Last 15 Days" returns everything with a value from 15 days ago until today's date.
The following Equals or Not Equals relative date values can be selected: Today, Yesterday, Tomorrow, This Week, Last Week, Next Week, This Month, Last Month, Next Month, This Year, Last Year, or Next Year. Each value depends dynamically on the current date.
Example: If you search for "Date Entered = This Month" on November 16th, 2017 then save your report, it will return all results with a Date Entered value in November 2017. Running the same report on December 21st, 2017 will return results with a Date Entered value in December 2017.
Choosing Multiple Values
You can manually enter multiple values for the same field by separating those values with a semicolon (;). You can also paste a delimited list of values directly into the Value(s) box and BSI will auto-format the search to separate the values with a semicolon.
Certain system fields have an option under the Operators box to Specify IDs. This button opens a dialog where you can search to find IDs based on input criteria, import a list of items, or check if a value exists.
Example: Searching on the Sample ID field, you can open the Specify IDs dialog and use the Find button to find all Sample IDs for subject 123. After selecting OK, those Sample IDs will be added to your Specify IDs dialog, where you can select the Exists button. A new column will open, showing you the number of vials associated with that Sample ID. Selecting OK on this dialog will add these vials to your search.
For any choice field, a list of options is available to select from. The Specify Values button, located under the Operators box can be used to select values to search on.
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.
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.
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:
- Have a history record for being moved from Freezer 45 to a different location.
- Were moved out in the last month.
For the second scenario, we can search for all vials that:
- Have a history record for being moved into Freezer 45 from a different location.
- Were moved there in the last month.
The final scenario is the most complicated. We can search for all vials that:
- Are currently in Freezer 45 (vial table).
- Were not moved to Freezer 45 last month.
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.
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.