MicroStrategy ONE

Displaying grouping elements that contain null values

Before you begin

You should understand how datasets are joined in a document. For background information, see Using datasets in documents.

Steps to display grouping elements that contain null values

A join between datasets can result in null values.

  • 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.

If null values are produced in the dataset join, you can determine whether or not to display grouping elements that contain null values.

For example, a document contains two dataset reports. 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:

Subcategories displayed for Books category only

Null values are displayed for the Electronics, Movies, and Music subcategories 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.

If you select All for the page-by, the six rows for the Books category are displayed. Three of the rows from the original document is 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 the original nine rows. If you select NULL, the rows for Electronics, Movies, and Music are displayed.

For examples of all the reports discussed, see the Document Creation Help.

To display grouping elements that contain null values

  1. Open the document in the Document Editor. How?

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

  3. Select Advanced.

  4. By default, the Remove the groups that contain null elementscheck 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 Editor.

Related Topics