Introduction:
Vault Reporting and Dashboards allow you to track, analyze and visualize your global data. This overview will introduce you to the core capabilities of Vault Reporting and Dashboards and will provide links to helpful additional content if you would like to learn more.
Creating Excel Templates
Excel Templates for Reports allows users to upload Excel templates to a specific report in Vault, and then export report data into those templates. In Excel templates users can format, perform calculations, apply conditional formatting, use pivot tables to group and analyze, access more chart options for visualization and even write macros. Additionally, users can export report metadata such as the report’s name, filters, and export date to the Excel template.
The first step of creating an Excel template is to create your report with all the relevant columns selected. Once you have added the columns, you can navigate to the Advanced Options section of your report and click the download sample button. You will want to start with the sample template because it contains all the tokens that reference your report metadata (name, who exported the report, filters…) and your columns. Below is an example template that has the two default tabs: Coverpage and Export.
You could simply upload this back to the report, and you would have an Excel export option that includes the report metadata. However, you can also do many other things. First, you might want to format the Coverpage and remove the gridlines and add a company logo.
Next you may want to do something similar with the Export tab and add conditional formatting to your rows to either highlight large or low numbers or shade every other row gray. Below you can see conditional formatting being added to the full row that will shade every other row gray. This conditional formatting will be copied down to all rows that are inserted.
We then may want to create some calculations in our Export tab that sum up the Size column and calculate the difference between Created Date and Approval Complete Date factoring out weekends. The sum of Size across rows is defined below using named ranges that Vault adds to the Excel Template. Each column in your report includes three named ranges which help you reference your data.
The calculation within a row that calculates the difference between Created Date and Approval Complete Date is shown below. In this case we do not want to use the named ranges.
We can upload this template back to our report now and when we select Export to Excel Template, we will see the below results.
Note that we have hidden a number of rows in the Export tab so that we can see the summation of the Size column at the bottom.
Users may also add additional tabs within their template where they can define things like charts or pivot tables. Let’s return to the template and define a pivot table and a chart. Below you can see a new tab called Pivot Table that is referencing the full set of data using named ranges: (study__v.name__vLabel:documents___study__v.approval_complete_date__cBottom). By referencing the data with named ranges from the leftmost column’s label to the rightmost column's bottom cell, as data is inserted on export, we will capture the new rows in our pivot table. When the pivot table is defined as we like it, we can add a pivot table chart that references the data in our pivot table. Below we define a combination chart with two y-axes.
You should also set your pivot table’s options to “Refresh data when opening file”. This will ensure users do not have to click the Refresh button when opening up the Excel template they downloaded.
When we upload this template to the report and then select Export to Excel Template, we can now see our populated pivot table and combination chart.
- You can find more on Excel Templates here.
Creating Multi-Pass Reports
Multi-pass reports allow you to build reports on top of other reports. Admins may join reports together by selecting matching columns in a similar manner to SQL. This flexibility allows you to build reports that span wider across your data model, filter or calculate metrics using calculated fields defined on the underlying reports, or join objects that can’t be joined in standard reports. Some platform examples of when you might want to use multi-pass reports are:
- Detecting workflow task owners that are currently inactive
- Finding all documents that are currently not in binders
- Setting up a duplicate document alert
- Seeing which dashboards reference which reports
We will walk through the first two of these platform examples later in this section.
Data Model
The first step to building your multi-pass report is having a good understanding of the objects you want to include in your report and the relationships between those objects. One way to gain an understanding of your data model is to use the standard report type constructor to see the relationships and their direction, ie, inbound and outbound. The list of related objects can also be seen in the relationships section of object configuration. It is often helpful to draw out these relationships. Below are sample data models for the relevant objects included in three multi-pass reports relevant for regulatory, clinical and quality.
Standard Report Types
The second step is making sure you have all of your relevant standard report types created. Standard report types will be needed to create your Report Views, admin only reports used to build multi-pass report types. You will determine which standard report types need to be created by looking at your data model. For example, the Impacted Document Report could use three report types: Registration with Regulatory Objective, Regulatory Objective with Submission, and Content Plan Item with Document. The Training Configuration Report on the other hand could use four report types: Person with Learner Role, Learner Role with Curriculum, Curriculum with Training Requirement, and Training with Document. Overlapping objects are often not required, but are sometimes useful because the report types and report views may be easier to reuse.
Report Views
Report views are admin only reports that are used to construct multi-pass reports. They can be created like standard reports within the Report View tab inside of Configuration. Like standard reports, admins may define groups, filters, conditional fields, formula fields and select columns. The selected columns will be the default fields that appear in the multi-pass report, but all fields from all included objects will be available for selection in the final multi-pass report.
Filters applied to report views are not exposed in the final multi-pass report. Because of this, if you decide to add a filter to your report view, it may be worth mentioning this filter in the report view's name. For example, let's say you added a filter for Status = Active on the Person object in the Person with Learner Role report view. An appropriate name for the view could be Active Person with Learner Role. Adding filters to report views can be useful for report optimization.
When groups are added to report views, only the grouping field and calculated fields may be used for joining. Additionally, only the grouping field and aggregate calculations will be exposed in the final multi-pass report.
Multi-Pass Report Type
After all of the needed report views are defined, the report views can be joined together in a multi-pass report type. To create a multi-pass report type, navigate to the report type tab, click create and change the attribute class to Multi-Pass. Now instead of joining objects, admins may join report views together.
Joins
Admins must select a primary view and may then select a second view to join to the first view. The join is defined by a field on the first view, a field on the second view, and the join type, either Left or Inner. The two fields must either both be IDs, text fields, numbers or dates or one must be an ID and the other must be an object reference field. For example, if you would like to join the Created By field to the User object, you would select ID as the join field for the User object.
Admins may select either Left of Inner joins depending on their use case. Left should be selected if the admin wants all records from the left view to be returned regardless of whether the two selected fields have matching values. Inner should be selected if the admin only wants records to be returned from the left view that have a matching value in the selected join fields. Inner essentially acts like a filter on the left report view, and is similar to the relationship constraint filter "has at least one" in standard reports.
Example: Detecting workflow task owners that are currently inactive
Let’s walk through an example of building a multi-pass report that includes Task Owner user details in a Workflow with Document report. The first step of creating multi-pass reports is creating the standard report types needed to create the reports you will be building your multi-pass report type on top of. In this case we will be joining a Workflow with Document report to a User report, so we need to make sure we have a User report type created (Workflow with Document should be available by default). The second step is creating two report views, one for Workflow with Document and one for User. Don’t worry about which fields you add in your report views because all fields will be available for selection in your final multi-pass report. The columns you select in your report views will appear as the default fields in the final multi-pass report.
The next step is joining the report views together in a multi-pass report type. You can see the two report views above have been joined together using the Task Owner field from the Workflow with Document view and the ID field from the User view. Note, the attribute class on the report type has been changed from standard to Multi-Pass.
After the multi-pass report type has been created, users may create reports in a similar manner to standard reports. Below you can see a report that uses the multi-pass report type and helps identify the status of all users assigned as task owners of open tasks.
Example: Finding all documents that are currently not in binders
In this example we find all document that are not currently referenced by binders; these are sometimes called orphaned documents. The first step is ensuring we have both the standard report types needed, in this case a document report type and a binder with document report type. Both should by default be available in your Vault.
The second step is creating report views for both of these report types. Once we have a document view and binder with document view, we can join them together using the Document Number or Document ID field. Note, you must select the Document Number from the document entity in the binder with document view, rather than the binder entity.
The final step is adding a filter to your multi-pass report. You will filter for all rows where binder fields are blank, eg, Binder Name or the Document Number on the binder entity. Your results should look similar to the image below with only the orphan documents remaining:
Example: Setting up a duplicate document alert
Report views allow you to define groups, which let you create calculated fields in report views such as counts, sums, averages and maxes. Defining calculated fields in report views can enable you to filter on calculated fields in multi-pass reports and create formulas across calculated fields. One use case for this is detecting documents that have duplicate content.
The first step of building this report is using the document report type to create two document views, one that is a normal document report, and the other that is grouped by the field checksum and has the document count column included.
We can then join the normal document report view to the report view grouped by checksum using the checksum field.
Finally, in the multi-pass report, we filter on the document count only returning the records that have a count greater than one. This should only return the documents that have multiple copies of the exact same document uploaded.
Chain Multi-Pass Reports
When adding a third report view to your multi-pass report type, you may either add the relationship to the second report view or directly to the primary report view. If you add the relationship to the second report view you may join the report view to fields on either the primary or second view. We call this multi-pass report type a chain because the report views appear connected in a chain. When you add the relationship directly to the primary view, we call this multi-pass report type a ladder because the non-primary report views appear above each other forming a ladder.
With a chain multi-pass report type, all report views are joined together into one big table. This allows users of the final multi-pass report to group and sort by any field, move report views around within the builder, and define formulas across any views. Given the flexibility of chain multi-pass report types, it should be selected unless it will result in unnecessary duplicates. Unnecessary duplicates occur when there are two one-to-many or many-to-many relationships back to the same left object. Below is an example of a chain multi-pass report type.
Ladder Multi-Pass Report
When the data model of the objects you want to include in your multi-pass report contain two one-to-many or many-to-many relationships back to the same left object, the ladder multi-pass report type should be selected. Ladder multi-pass reports are designed to help you avoid duplicates. Below are examples of data models that require ladder multi-pass report types.
Unnecessary duplicates occur above because the values used for joining end up occurring multiple times on both the left and right report views. A simple example show below can help illustrate the challenge. For example, let's say you have a Study object that is referenced by both Site and Document. A single Study could have many related Documents and many related Sites. Below we show what the results would look like if we joined these objects together:
In Vault reports we handle this data model in a special way to avoid duplicates. Instead of joining everything together into one big table, we join Study with Site and Document separately, and then essentially union the results, placing them side by side.
In multi-pass ladder report types, we do the same thing, but with report views. This allows us to support scenarios where objects may have multiple inbound references, but multi-pass is still required because of how wide the data model is or because of limitations on the entities included.
An example ladder multi-pass report type will look like this:
