Using Data Items in a Query

About Data Items

Each data source includes one or more standard data items. You decide which data items to use to define a query for a report section. You can use all the data items in the data source or a subset of data items. Each data item is classified as either a category or a measure.

Category

A data item whose distinct values are used to group and aggregate measures. There are four types of categories: alphanumeric, date, timestamp, and time. Alphanumeric categories can be made up of all letters, all digits, or a combination of the two. Categories that have values that are all digits might be physically stored as character or numeric data. The data type affects how values are handled in relation to some functionality, such as filtering, sorting, and formatting.

Examples of alphanumeric categories include data items such as Product ID, Country, Employee Number, and Employee Name.

Date and Time date time icon

A data item whose distinct values are used to group and aggregate measures. There are three types of date categories: date, timestamp, and time.

Examples of date, timestamp, and time categories are Order Year, Date of Sale, and Delivery Time.

Measure

A data item whose values can be used in computations. Usually, these values are numeric. Examples of measures include Sales Revenue, Units Sold, and Salary.

Every measure has a default aggregation method, which is specified by the data source that contains it. In some cases, you can change the method. However, if you use a measure as part of a custom data item, then the measure is always calculated by using the default aggregation method.

Note: Report objects that are imported from the explorer interface use embedded data. Therefore, you have a very limited ability to make changes to these data items in the designer interface.

Select Data Items

To select data items to use in the query for the current report section:

On the Data tab in the left panel, click the down arrow to display a list of available data sources. Select a data source and the Data tab is populated with a list of all of the data items that are in the data source.

If the data source that you want is not in the list, select Add another data source, which displays the Add Data Source dialog box. Select the data source that you want, and then click Add. The Data tab is populated with a list of all of the data items that are in the data source.

If you do not want to use the data source that you originally selected, click Remove data source. A message box asks you to confirm that you want to remove the data source. Click Yes.
(Optional) To see more information about a particular data item, select it in the list. The Name, Role, Format, and Aggregation are displayed in the table below the list of data items. Here is an example of the details for a data item called Engine Size:

Details about a Selected Measure Data Item

Modifying Data Item Properties

Rename a Data Item

To rename a data item:

Select a data item on the Data tab.
In the table, select the existing name for the data item and then enter a new name. Your change is saved automatically.

Modify a Data Item’s Role

To modify a data item’s role:

Select a data item on the Data tab.
In the table, select the existing role name for the data item. In the drop-down menu, select either Measure or Category. Select one and your change is saved automatically.

Modify the Format of a Measure Data Item

To change the format of a measure data item:

Select a measure data item in the Data tab.
In the table, select the existing format. A list is displayed with the Format type, Width, and Decimals. Make your selections. A sample of your selection (or selections) is displayed under the list. Here is an example of the list:

A List of the Format Type, Width, and Decimals for a Data Item
Click OK to save your changes.

Modify How a Measure Is Aggregated

To change the aggregation method of a measure in a data source:

Select a measure data item on the Data tab.
In the table, select the existing aggregation. A drop-down list is displayed with aggregations. Sum, Average, Minimum, and Maximum are examples of aggregations. Select one, and your change is saved automatically.

Note: You need to understand your data, because some aggregation methods are not always appropriate. For example, an average of an average is not valid.