Multi-Pass Reporting

Multi-Pass reporting allows you to construct reports that span further across a data model by joining multiple reports together. A Multi-Pass report is a combination of reports, matched on related object fields or object reference fields.

Configuration Overview

• Admins create Report Views, which are admin-only reports used to construct Multi-Pass reports.
• Admins join Report Views together in a Multi-Pass report type.
• Users build and run reports from the Multi-Pass report type in a similar manner to existing reports.

Report Views

Report Views are Admin-only reports used to construct Multi-Pass reports. Report Views allow Admins to define columns, filters, and aliases. Admins may also run the Report View for previewing purposes. Report Views do not support prompts, flash reports, or dashboards. Admins can also leverage the Date report type in Report Views. Though the Date reporting entity is not a true Vault object, this report type allows Admins to configure reports that display records with the date as the primary object.

Configuring Report Views

Configure Report Views from Admin > Configuration > Report Views:

1. Click Create, then select a Report Type. Report Types are limited to Object, Object with Document, and workflow reports.
2. Provide a Name.
3. Click Continue.
4. Optional: Add Filters to your Report View. Filter conditions are applied, but not shown, when you run the final multi-pass report.
5. Optional: Define columns in your Report View with Edit Columns. Columns added to your Report View will appear as default columns on the Multi-Pass report. However, all columns will be available for selection.
6. Optional: Group rows by a desired field.
7. Optional: Run the Report View to preview data.
8. Click Save.

Limitations

You can include a maximum of ten (10) report views per Multi-Pass report.

Grouping on Report Views

Grouping rows by a specific field on a Report View is similar to grouping rows when creating a standard report, however, there are a few key differences:

• When building Report Views that have grouped rows, only the grouped-by field will be available to join on.
• In the final Multi-Pass report, Vault will only display grouped fields.
• You can still filter the Multi-Pass report on any field from any object used in the report, however, Vault won’t display these fields when you view the report.
• You can filter on aggregate fields.

Multi-Pass Report Types

A Multi-Pass report type contains one or more Report Views joined together. To create one:

1. Navigate to Admin > Configuration > Report Types.
2. Create a new report type, and select Multi-Pass in the Class field.
3. Under Report Configuration, select a Report View. This is the primary view for the Multi-Pass report.
4. Click Add Relationship to add a Report View to join.
5. Select fields to match on for each view in the relationship and click Add. You can match on text, date, object fields and number fields. Date and number fields include aggregates on fields and formula fields on objects. Multi-Pass reports are left join by default.
6. If necessary, continue adding relationships as needed by clicking Add Relationship beneath the report views.
7. Click Save.

Running a Multi-Pass Report

Construct a new report from the Reports tab to run your Multi-Pass report. Multi-Pass reports do not support matrix formatting. Multi-Pass reports must be tabular.

Vault groups Multi-Pass report types based on the primary object of the primary Report View. For example, if the primary view of my Impacted Document Report Multi-Pass report type is Registration with Regulatory Objective, the report type would be found under Registration.

You can group rows by any field from any object used in the Multi-Pass report type, define filters and prompts, create flash reports, and use your Multi-Pass reports on dashboards.

Formula Fields

Formula fields allow you to define formulas in your Multi-Pass reports. You can add formula fields as columns in your report, and use formulas for grouping, sorting, and filtering. You can also use formula fields to define conditional fields and report filters.

You can define up to 10 formula fields per Multi-Pass report. This does not include formula fields defined in report views. You can add a maximum of 10 formula fields on each report view.

To define a formula:

1. Under Formula Fields, select Create Formula Field.
2. Select a Report View.
3. Enter a Label.
4. Select a Return Type. If you select the Text return type, also enter a Maximum Length.
5. Optional: If you select the Text return type, use **#define** at the beginning of the formula to simplify long expressions. For example, #define short_path End_of_Study.Vital_Signs.General_Information.
6. Enter a Formula Expression. For more detailed instructions on writing the formula expression, see Creating Formulas in Vault.
7. Click Check Syntax. Vault will let you know if your expression is valid.
8. Click Save.

To edit or delete a formula field, click the formula label.

Filtering on formula fields in Multi-Pass reports does not improve report performance.

Aggregate Functions

If you group rows, you can add several aggregate functions on text, number, and date fields. You can only select one function per field. Vault does not support aggregate functions on long or rich text fields.

Available aggregate functions include:

Aggregate Function	Definition	Notes
List	Returns concatenated text of all row values in a group separated by a delimiter and space	Displays a maximum of 1,024 characters; only available for text fields
Distinct List	Returns concatenated text of all row values in a group, excluding duplicates, separated by a delimiter and space	Displays a maximum of 1,024 characters; only available for text fields
Std Dev	Returns the population standard deviation of expression	Only available on number type fields
Std Dev Samp	Returns the sample standard deviation of an expression	Only available on number type fields

To add an aggregate function, click the Function drop-down on a grouping in the report editor and select a function.

Advanced Logic

Advanced logic allows you to group together filters, objects, and views, and add AND or OR operators between them. You can also add OR operators on aggregate columns aggregated in the report view.

Once two eligible filters or prompts have been added, Add advanced logic appears below the filters. Click Add advanced logic to edit the formula using the numbers next to the filters. The Validate button checks the syntax.

AND operations are evaluated before OR operations by default. You can use parentheses to determine which logic is evaluated first.

Limitations

The following limitations apply to advanced logic:

• Some filters are ineligible for advanced logic, and will not have a corresponding number.
• Dashboard previews are not available for reports with advanced logic.
• While you can add an OR operator between parent and down views in ladder configuration, you cannot add an operator across multiple down views.
• Vault does not support advanced logic on Multi-Pass reports with a Union-All report view.

Example Use Case: Documents with Binders

Users may need to identify which documents are not included in binders. To see this, they could create a Multi-Pass report that shows the relevant documents and all binders with documents, which will allow them to determine which documents are not in a binder. This example report type uses a single down relationship. In this use case, a Multi-Pass report might look like this:

Document is the primary report view of this Multi-Pass report. It is joined with the Binder with Document report view, which is filtered for blank rows. The report views are matched on the document’s Document Number field.

The final report will contain the following records:

• From the primary view: Every document.
• From the second view: Every binder containing a document.

Example Use Case: Impacted Document Report (RIM)

When changes to a registration occur, regulatory information managers need to quickly find all affected documents and take necessary actions. With a Multi-Pass report, they would be able to report on a registration, all related documents, and any other related object that may have been affected by the change. This example chain report type uses multiple left joins.

A Multi-Pass report type to construct such a report might look like this:

Registration with Regulatory Objective is the primary view of this Multi-Pass report. It is joined with the Regulatory Objective with Submission view, matched on the Regulatory Objective object’s ID field.

The third view, Content Item with Content Plan and Document, is joined to the Regulatory Objective with Submission view, matched on the Submission object’s ID field and the Content Plan object’s Submission reference field.

The final report will contain the following records:

• From the primary view: Every Registration and Regulatory Objective record.
• From the second view: Submission records that have a matching ID with one of the Regulatory Objective records from the primary view.
• From the third view: Content Plan records with Submission field values that match one of the Submission record’s ID fields, and the documents related to those Content Plan records.

Example Use Case: Document Change Controls with Documents (both Released and Obsolesced)

Users may need to identify which Document Change Controls have associated documents that are ready for release, obsolescence, or both. To see this, they could create a Multi-Pass report that shows the relevant Document Change Controls and all related documents. This report will allow them to quickly determine which actions are necessary on each document. This example ladder report type uses multiple down relationships.

In this use case, a Multi-Pass report type might look like this:

Document Change Control is the primary view of this Multi-Pass report. It is joined with two (2) views:

• The Documents to be Made Obsolete view, matched on the Document Change Control’s ID field and the document’s Obsolete Change Control field
• The Documents to be Released view, matched on the Document Change Control’s ID field and the document’s Release Change Control field

The final report will contain the following records:

• From the primary view: Document Change Control records which have either a document to be made obsolete or a document to be released.
• From the Documents to be Made Obsolete view: Documents associated with the Document Change Control’s ID value that have a matching Obsolete Change Control value.
• From the Documents to be Released view: Documents associated with the Document Change Control’s ID value that have a matching Release Change Control value.

Join Types

Join	Description	Icon
Left	Vault returns all records from the view on the left side of the relationship and only records with matching fields from the view on the right side of the relationship.
Inner	Vault returns records that have matching values from both views.

Limitations

Vault does not support join fields if one of the rows contains a comma. We recommend joining by ID to prevent errors. However, if you must join on the field with a comma, you can create a formula field using the substitute() function to remove the comma.