Using datasets in documents

A dataset is a MicroStrategy report that is used on a document and all of the report's elements–attributes, custom groups, consolidations, and metrics.

Datasets provide the data that appears in documents. Datasets define which information the Intelligence Server should retrieve from the data warehouse or cache. This information can include attributes, custom groups, consolidations, and metrics.

These dataset objects are all of the objects from the dataset report, regardless of whether they are displayed on the report. That is, if a metric, for example, is in the Report Objects but not displayed on the grid, that metric is listed as a dataset object. For background information about these subset reports, including the different methods to add them to a document, see Using MicroStrategy OLAP Services reports as datasets.

Single dataset

For a document with only one dataset, the number of rows in the dataset determines the number of times the Detail section is repeated. That is, anything in the Detail section is repeated once per row in the dataset. (This explains why Grid/Graphs cannot be placed in the Detail section – the Grid/Graph would be repeated on each row.) Multiple datasets introduce more complexity—for more information, see the next section.

Multiple datasets

You can create a document with multiple dataset reports, and you can add more dataset reports after you create a document. One dataset must be defined as the grouping and sorting dataset; you can group and sort only by the objects on this dataset. If the document contains multiple layouts, each layout can have its own grouping and sorting dataset. For instructions on changing the grouping and sorting dataset, see Changing the grouping and sorting dataset.

The grouping and sorting dataset was referred to as the primary dataset in previous releases. You can now set the join behavior separately from the grouping and sorting behavior, as described below.

You can define the join behavior of each dataset as either primary or secondary. This functionality allows you to control which datasets determine the attribute elements that appear in the document results.

• All of the elements from the primary datasets are displayed in the results.

• Elements from the secondary datasets are displayed only if they also appear in a primary dataset.

These rules do not affect Grid/Graphs that use a single dataset report. In this case, an element from a secondary dataset is displayed in a grid or graph report in a document even if it does not also appear in a primary dataset. If the Grid/Graph uses data from multiple dataset reports, the join behavior affects the content of the Grid/Graph. You can use MicroStrategy Web to create a Grid/Graph that uses multiple dataset reports; for steps, see the Document Creation Help. For more details on how join behavior affects Grid/Graphs, see the Document Creation Help.

Datasets are joined following these rules:

• If a document contains one primary dataset, then all secondary datasets are joined to the primary dataset using left outer joins.

• If a document contains at least two primary datasets, all primary datasets are joined using compound joins. The results are used to left outer join all secondary datasets.

• If a document does not contain any primary datasets, all datasets are joined using inner joins.

For a compound join, Intelligence Server joins the data in the datasets as described below:

• If the datasets have any of the same attributes, the common attribute elements are matched.

• Then beginning with the first row of each dataset and continuing to the last, a row is created in a virtual dataset—the complete set of joined rows held in memory—consisting of all attributes, consolidations, custom groups, and metrics. You can determine whether sections that do not have metric data are displayed and whether grouping elements that contain null values are displayed.

The compound join saves memory space and processing time on the Intelligence Server executing the document. For detailed examples, see the Document Creation Help.

Intelligent Cubes as datasets

An Intelligent Cube is a multi-dimensional cube (sets of data) that allow you to use OLAP Services features on reports, as well as share sets of data among multiple reports and documents. You can use an Intelligent Cube as a dataset, allowing you to use one Intelligent Cube for many different documents, while reducing access to the data warehouse. You can have multiple Intelligent Cubes in the same document, or a mix of Intelligent Cubes and reports.

For more information about using Intelligent Cubes as datasets, see Using Intelligent Cubes as datasets.

Multiple datasets

When a document contains multiple datasets, a threshold or a view filter on a Grid/Graph can include any objects from any of the datasets, regardless of whether the Grid/Graph uses that dataset. Thresholds are special formatting that is automatically applied to data in a Grid/Graph, when the data meets a specified value. A view filter on a Grid/Graph places conditions on attributes and metrics which restrict the amount of data displayed on the Grid/Graph.

For example, a document contains a Regional Revenue dataset and a Regional Profit dataset. A Grid/Graph is created for the Regional Revenue dataset. Profit is not displayed on the Grid/Graph, nor is it in the data source for the Grid/Graph.

In the Grid/Graph, you can use MicroStrategy Web to create a threshold to change the formatting of the revenue amounts when the profit is greater than a specified amount. For a more detailed example, see the Document Creation Help. For background information on thresholds in general, see Applying thresholds.

In the Grid/Graph, you can use MicroStrategy Web to create a view filter to display only the regions with profits greater than a specified amount. In MicroStrategy Developer, you can execute the document to see the results. For a more detailed example, see the Document Creation Help. For background information on view filters in general, see Creating view filters in Grid/Graphs on documents.

If a document contains multiple datasets and a selector, which items are displayed in the selector depends whether the selector filters or slices the data. A selector provides interactivity, allowing each user to change how he sees the data. When a user clicks a selector, a selector can change the focus of a grid or graph report or dynamic text fields (a text field that is a reference to an object on a report) in a panel stack.

• A slicing selector shows only the items available on the target.

• A filtering selector shows all the items available in all the datasets.

For example, a selector on Category targets a Grid/Graph that displays only Books and Movies. A second dataset on the document is filtered for Books and Music, but is not used on the Grid/Graph.

• If the selector is filtered, the selector displays Books, Movies, and Music (all the categories available in all the datasets).

• If the selector is sliced, the selector displays Books and Movies (only the categories available on the target).

For a more detailed example of filtering vs. slicing selectors on a document with multiple datasets, see Selectors in an RS dashboard with multiple datasets; for general background information on selectors, see Providing interactivity to users: Selectors.

Related Topics

• Adding datasets to documents

• Changing datasets in a document

• Changing the grouping and sorting dataset for a document

• Removing datasets from documents

• Editing datasets from documents

• Using Intelligent Cubes as datasets

• Using subset reports as datasets

• Using MDX cube reports and Freeform SQL reports as datasets

• Displaying grouping elements that contain null values

• Removing sections that do not have metric data

• MicroStrategy reports