Filtering reports by entity

By tagging items to entities in Projects, you can generate entity-specific reports in Reports. This tutorial illustrates how you can use entities to filter a controls data set in Reports.

Scenario

Filter control information by entity

You need to create a report that includes control information from different projects. When you share the report with the relevant stakeholders, you want them to be able to filter the data set by one or more entities. The entities include business units, departments, locations, and key initiatives that are within the scope of the organization's project function.

By enabling your report readers to filter control information by entity, different cross-sections of the business can quickly obtain the information they require.

In Projects, you see that entities are organized in a tree view composed of parent and child relationships. In Reports, you notice that the tree view is flattened, and entities are displayed as comma separated lists.

Before you start

This tutorial walks you through a number of features in Reports using a mock scenario. Before you start this tutorial, you need to do the following:

Projects setup

You need to tag controls to entities.

For more information on entity tagging in Projects, see Setting up entity tagging.

Note

Any entity names that include commas are represented as two different entities in Reports. For example, specifying Washington, DC as the entity name in Projects becomes Washington and DC in Reports. To prevent incorrect data from displaying in Reports, do not enter a comma in the entity name in Projects.

Reports setup

An administrator must:

  • Assign you the Report Admin or Report Writer role
  • Create content folders and sub folders to store reports

Create a report

Let's get started by creating a report using a View. A View is a component that defines the categories and fields from Diligent One that you can use to build a report.

  1. Open the Reports app.
  2. Select Create > Report.

    The New Report dialog box opens.

  3. Select the Fieldwork View from the list.

    Note

    You can only use the Fieldwork View to report on entities tagged to controls. To report on entities tagged to other items, you must use the following Views:

    • Issues / Issues by Entity or Actions / Actions by Entity entities tagged to issues
    • Archived Issues / Archived Issues by Entity entities tagged to archived issues
    • Project and Users / Projects and Users by Entity entities tagged to projects

    Result You now have a new blank report. You can begin building the report by using any combination of fields provided in the Fieldwork View.

Define fields

Now that you have a new blank report, you can define the fields that need to be displayed in the report. You want your report readers to be able to view some high level project and control information.

From the Data tab in Report Builder, search for and drag the following fields into the Columns field:

  • Project Name
  • Control Number
  • Control Description
  • Control Type
  • Control Who Performs

Result You have defined the fields in the report.

Specify filters and save the report

After defining the fields in your report, you can specify one or more control entities filters. You want to give your report readers at least three filters they can use to filter the data set.

  1. From the Data tab in Report Builder, search for the Control Entities field.
  2. Drag the Control Entities field from the data fields list to Filters list three times.
  3. Under the Filters list, click Advanced settings.

    The Configure Filters window opens.

  4. Set all the filters to Contains, use the following default settings, and then click the to close the Configure Filters window:
    • Conditional logic (combine filters) And
    • Filter type User Prompt

  5. Click the Publish tab, and provide a name and description for the report.
  6. Specify the appropriate content folder and sub folder to define where the report will be saved, and click Save > Activate.

    Result The report is now available to users with the appropriate access.

    You decide to try out the filters in the report, and notice that the filter values display as a comma separated list. However, you want to provide a unique list of values to your report readers.

Create a master report

To provide a list of unique values for your report readers to filter on, you need to create a master report. This report is solely for displaying the entities filter values uniquely, instead of the comma separated list that the previous report provides.

  1. Click to close the first report you created, and select Create > Report to create a new master report.

    The New Report dialog box opens.

  2. Select the Fieldwork by Entity View from the list.
  3. From the Data tab in Report Builder, drag in any field from the data fields list to the Columns list.

    To build a report, you need to include at least one data field. However, you do not need to worry about what data you show in this report as it won't be displayed.

  4. Search for the Entity field, and drag the field in three times to the Filters list.

    You will eventually link these filters to the filters in your previous report, so it's important to have the same number of filters in both reports.

  5. Under the Filters list, click Advanced settings, and apply the same filter settings as you did in the previous report:
    • Logical operator Contains
    • Conditional logic (combine filters)And
    • Filter typeUser Prompt
  6. Click the to close the Configure Filters window.

    Result You have created your master report.

    Tip

    If you have many entity filters, you may want to hide them so you don’t see all of the filters above your data set. You can format your filters to display on the left side of your report. To achieve this, click Filter Formatting in the formatting toolbar, change the Filter Display to Left Side Nav, and click close .

Link the reports

The last step is to link the report you first created (a child report) to your master report. When you link a child report to a master report, you define a relationship between the reports either based on common fields or filters that exist. This relationship allows you to display both reports within a single report.

  1. Under Analysis Style, select Co-Display.
  2. Click the Related Content tab.

    The Related Content page opens.

  3. Search for the first report you created, and drag the report into the Co-Display Top area.
  4. Under Link On, select Filters, select each Entity master filter for each Control Entities child filter, and click Update:

  5. Click the Publish tab, and provide a name and description for the report.
  6. Specify the appropriate content folder and sub folder to define where the report will be saved, and click Save > Activate.

    Result The two reports are now linked and display on the same page.

Discussion

Let's take a look at how your report readers can use your master report to filter a data set by entity, and discuss the difference between "by Entity" Views and other Views.

Filtering a data set by entity

When report readers view your master report, they will see three entity filters accompanied by a complete data set. Clicking the search beside an entity filter displays the Search Filter Values dialog box.

The Search Filters Value dialog box lists all entities individually. Report readers can select a single entity per filter, click Submit, and click Go to view a filtered list of controls based on tagged entities.

Entity filters Search Filter Values dialog box

For example, let's say a report reader wants to view all controls tagged with the following entities: South America, Sao Paulo, Information Technology. They can select an entity from each filter, and restrict the data set to only show those controls tagged with the respective entities: 

Tip

You can also use entity categories to filter a data set by specific categories, such as locations, departments, or key initiatives. To do this, you can create a calculated field within your master report:

CASE WHEN Entity Category = 'Location' THEN Entity ELSE '' END

This allows the master report to have filters labeled by categories that return only the entities related to that category.

Views

In this tutorial, you built your first report (child report) using the Fieldwork View, and your master report using the Fieldwork by Entity View.

There are two versions of most Views in Reports. The difference lies in the way that the data is displayed in a report.

  • Views not suffixed with "by Entity" Values are returned in a comma separated list in the Entities field.
  • Views suffixed with "by Entity" Values are split so that each entity is returned in its own row in the Entity field, and the rest of the row’s information is duplicated.

For more information, see View reference.