MicroStrategy ONE

Displaying grouping elements that contain null values

A join between datasets can result in null values. If null values are produced in the dataset join, you can determine whether or not to display grouping elements that contain null values. Null values are produced in the following scenarios:

  • If the attributes in the datasets are not in the same hierarchy, no null values are produced. For example, Dataset 1 contains Region and Profit, while Dataset 2 contains Category and Revenue. Since Region and Category are from different hierarchies, no null values are produced.

  • If the attributes in the datasets are in the same hierarchy, and a full dataset relationship exists to support the join, no null values are produced. For example, Dataset 1 contains Category, Subcategory, and Profit, and Dataset 2 contains Category and Revenue. Since both datasets contain Category, and each category can be matched to its subcategories, no null values are produced.

  • If the attributes in the datasets are in the same hierarchy, and only a partial dataset relationship exists to support the join, null values are produced. For example, Dataset 1 contains Category, Subcategory, and Profit. Dataset 2 contains Category and Revenue, filtered to display the Books category only. Although Category and Subcategory are from the same hierarchy, because of the filter, each category cannot be matched to its subcategories, so null values are produced.

  • If the attributes in the datasets are in the same hierarchy, and no dataset relationship exists to support the join, null values are produced. For example, Dataset 1 contains Category and Profit, while Dataset 2 contains Subcategory and Revenue. Although Category and Subcategory are from the same hierarchy, they do not exist in the same dataset in the document. Therefore, the category cannot be matched to its subcategories, so null values are produced.

When null values are produced, you can decide whether their related grouping elements are displayed.

For example, a document contains two datasets. Dataset 1 contains Category and Profit. Dataset 2 contains Category, Subcategory, and Revenue, filtered to display Books only. The document contains Category, Subcategory, Profit, and Revenue in the Detail section. The dataset join is supported by a partial relationship between Category and Subcategory: the Books category is matched to its subcategories, but the other categories are not.

As shown below, the document contains nine rows:

Null values are displayed for the Electronics, Movies, and Music subcategory and Revenue, because that data is not available in either dataset.

Now, group the document by Subcategory. When you execute the document, only the subcategories for the Books category are displayed in the page-by options. When you select Art & Architecture, only the row for Art & Architecture is shown, as displayed below:

If you select All for the page-by, six rows are displayed, as shown below:

Three of the rows from the original document are missing, the ones containing the data for Electronics, Movies, and Music. You are grouping by Subcategory and that row does not contain any information about categories. The rows for Electronics, Movies, and Music are not displayed, because they contain null values for Subcategory and by default, groups that contain null elements are removed.

To see the information for all the categories, clear the Remove groups that contain null elements check box. The page-by options are now the book subcategories, NULL, and All. When you select All for the page-by, the document displays with nine rows, as shown below:

If you select NULL, the rows for Electronics, Movies, and Music are displayed.

To display grouping elements that contain null values

  1. In MicroStrategy Web, open the document in Design Mode.

  2. From the Tools menu, select Document Properties. The Properties dialog box opens.

  3. Under Document Properties on the left, select Advanced.

  4. By default, the Remove groups that contain null elements check box is selected. To display any grouping elements sections that contain null elements, clear this check box.

  5. Click OK to return to the document.