Defining a dataset as primary or secondary

If a document contains multiple dataset reports, you can define the join behavior of each dataset as either primary or secondary. This functionality allows you to decide which datasets determine which attribute elements appear in the document results.

Primary and secondary datasets examples

For example, consider the following three datasets:

• Dataset 1 contains Region and the Revenue metric, filtered for Central, Mid-Atlantic, Northeast, and Southeast

  

• Dataset 2 contains Region and the Profit metric, filtered for Mid-Atlantic, Northeast, Northwest, and South

  

• Dataset 3 contains Region and the Customer Count metric, filtered for Mid-Atlantic, Northwest, Southeast, and Southwest

  

A document contains all three datasets. The Detail section contains Region and the three metrics. The document could contain a Grid/Graph with the same objects, instead of text fields, and the results would be the same.

By default, all three datasets are primary datasets. Since the datasets are joined together using compound joins, all of the elements from all three datasets are therefore displayed in the seven rows of results. A metric value is displayed only when that region appears in the dataset report that contains the metric. For example, Southwest shows a value only for Customer Count because Southwest appears only in Dataset 3 (the Customer Count dataset). Similarly, Mid-Atlantic shows values for all three metrics because Mid-Atlantic is the only region included on all three datasets.

Change the join behavior:

• Dataset 1 and Dataset 2 = primary

• Dataset 3 = secondary

Dataset 1 and Dataset 2, as primary datasets, are joined with compound joins, and all their regions are displayed on the document. Those results are left outer joined with Dataset 3, so the only regions from Dataset 3 that appear in the document are the regions that also appear in one of the primary datasets. In this case, Southwest appears only in Dataset 3, so it is not displayed in the results. Only six rows are displayed, as shown below:

Change the join behavior:

• Dataset 1 and Dataset 3= primary

• Dataset 2 = secondary

Again, only six regions are displayed, but now Southwest is shown instead of South, since South is available only in Dataset 2.

Change the join behavior:

• Dataset 1 = secondary

• Dataset 2 and Dataset 3 = primary

This time, Central does not appear because it is available only on Dataset 1, a secondary dataset.

Change the join behavior:

• Dataset 1 = primary

• Dataset 2 and Dataset 3 = secondary

Since the document has only one primary dataset, all the other datasets are joined to the primary dataset with left outer joins. Only the four regions from the primary dataset are displayed in the document. Those are the regions that have Revenue values, since Revenue is the metric in Dataset 1. South, which is only in Dataset 2, and Southwest, which is only in Dataset 3, are not displayed. Northwest is in both Dataset 2 and Dataset 3, but since it is not in the primary dataset, it is not displayed.

Change the join behavior:

• Dataset 1 and Dataset 3 = secondary

• Dataset 2 = primary

Since Dataset 2 is primary, only the four regions that are in that dataset are displayed.

Change the join behavior:

• Dataset 1 and Dataset 2 = secondary

• Dataset 3 = primary

Similarly, since Dataset 3 is primary, only the four regions that are in that dataset are now displayed. Notice that these regions all have values for the Customer Count metric, since that is the metric in that dataset.

Change the join behavior:

Dataset 1, Dataset 2, and Dataset 3 = secondary

Since the document does not contain any primary datasets, all datasets are joined using inner joins. Only the one region (Mid-Atlantic) that is in all the datasets is displayed.

To define the join behavior of a dataset

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

2. Make sure that the Dataset Objects pane is displayed. (If it is not displayed, click Dataset Objects in the list on the left.)

3. Right-click the dataset in the Dataset Objects pane, point to Join Behavior, and select either Primary or Secondary.