Custom Subject Areas Dates

Once you pick the fields for your custom subject area, you can then review just the date fields for two additional configuration steps: allowing date leveling and picking one canonical date field.

First, you can enable one or more fields for date leveling which enables the aggregation of report data over a period of time. You can also enable one field to be the canonical date which lets you join this custom subject area to the standard Time dimension. Using this common date, your users can filter data across multiple subject areas in a single report, and drill up and down the date hierarchy. Let's look at date leveling and the concept of canonical date in more detail.

Why Date Leveling is Useful

Date leveling gives you additional date context about your transaction data, and enables the filtering of data by calendar attributes. Date leveling means that you're connecting (mapping) a date field in your custom subject area to a very large calendar object in BI Composer. Later, in BI Composer, that date field will display with a series of calendar attributes. (The calendar object supports both the enterprise and fiscal calendar.) When you design your analytics, you pick that date field plus the calendar attributes you're interested in, such as Day Name, Week of the Year, Month of the Year, or Enterprise Quarter. In your resulting analytic, transaction data displays along with the helpful date context that you have added.

For example, a report can display a set of opportunity records along with the enterprise quarter they were created in: Q1, Q2, and so on. That's date leveling. And, maybe you need to know which day the opportunity was created on, such as on a Monday or Wednesday. That's also date leveling.

Maybe you want to see a report of all orders entered, but you would like to know what enterprise quarters they were entered in. If you enable date leveling on the creation date for your sales order object, then you can create analytics that show orders per enterprise quarter.

Let's refine that report a little more. Maybe you want to see only orders created in 2019 Q1. When you design that report, add a filter on the enterprise quarter to display only records where the quarter is equal to 2019 Q1.

If you don't enable date leveling for a date field, then later in BI Composer, that date field will still display for inclusion in an analytic, but without the added calendar attributes that are so helpful.

Configure Date Leveling

To configure date leveling, use the Configure Dates step of the guided train process to either allow or disallow leveling for a date field. You might need to expand the field list in the Date Field column to view the Allow Leveling check box.

Enable date leveling only for those dates that you want to report on. And, even after you publish the custom subject area, you can always come back and select additional dates for date leveling, and then republish.

Why the Canonical Date is Useful

A canonical date in a custom subject area is useful because, when you join your custom subject area to a standard subject area in BI Composer, the canonical date automatically joins to the standard Time dimension. The Time dimension includes the date hierarchy, so this means that the analytics you create can drill up and down the date hierarchy.

But first, let's understand where the canonical date comes from. Canonical dates are already defined for some standard objects. You can see these fields in the Fields step when adding fields from standard objects to your custom subject area. (A date field that's a canonical date will have a check mark in the Canonical Date Candidate column.) If you plan to design a report that lets users drill up and down the date hierarchy, then include a canonical date field in your custom subject area. But that's only the first step.

In the next Configure Dates step, you must select the radio button for that field in the Canonical Date column. Do this for one field only, and only for the lowest standard object in your custom subject area. Canonical dates are available only for standard objects because custom objects don't have corresponding subject areas in BI Composer with the standard Time dimension to join to.

Let's look at an example of drilling up and down the date hierarchy. Maybe users want to see sales commission amounts on their closed opportunities for the quarter, and then drill down to view amounts by week. Maybe they want to drill up to view amounts by year. This is drilling up and down. To achieve this, you must do the following:

In Application Composer, create a custom subject area for opportunity and opportunity revenue, with the Line Close Date as the canonical date from the opportunity revenue object.
In BI Composer:
1. Create an analysis that includes both the custom subject area and a standard subject area, such as Sales - CRM Pipeline.
2. In addition to including at least the Sales Commission field, include the date hierarchy in your analysis, such as Enterprise Time, from the Sales - CRM Pipeline's Time dimension folder.

Configure the Canonical Date

To indicate which date field should be the canonical date, use the Configure Dates step of the guided train process. To enable a date field as the one canonical date for a custom subject area, view the date fields for the lowest standard object in the custom subject area, and select the radio button for the desired date field in the Canonical Date column. You might need to expand the field list in the Date Field column to view the Canonical Date radio button.

You can add a canonical date to both new and existing custom subject areas.

If you publish a custom subject area without a canonical date, then you can always come back later and select a canonical date, and then republish.