Working with Data Items in a Report :: SAS(R) Visual Analytics 6.2: User's Guide

About Data Items

Each data source in the designer includes one or more standard data items. You decide which data items to use to define a query for each report object. 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.

For information about filtering data items, see Working with Data Item Filters in a Report.

Data Items That Are Available in the Designer
Data Item	Icon	Description
Aggregate Measure		A data item that represents special predefined operations, like distinct count, percentage of totals, percentage of subtotals, or frequency percent. Or the user can define their own aggregated measure calculations. Aggregate measures can be used in only some report objects. They cannot be used in filters, controls, spark lines, or time series graphs. Percentage of subtotal items (including row total, row subtotal, column total, and column subtotal) can be used in only in crosstabs. Some aggregate measure calculations cannot be used in a detail rank.
Calculated	, , or	A data item that is calculated from existing data items by using an expression. For example, you could create a calculated data item called Profit, which is created by using this expression: `[Revenue] — [Cost]`, where Revenue and Cost are measures in a data source. Calculated dates and times are treated as categories with distinct values being governed by the date or time format that you have chosen. Numeric calculated items can be treated as measures (with an aggregation type such as Sum, which is applied to each distinct category combination). Or, you can change numeric calculated items into category data items with distinct values being governed by the number of decimal places in the numeric format.
Category		A data item whose distinct values are used to group and aggregate measures. There are five types of categories: alphanumeric, date, datetime, time, and numeric. 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. Alphanumeric categories sort lexically. Date, datetime, time, and numeric categories are sorted by their underlying numeric values. Category data items can also be numeric. A category data item sorts differently than an alphanumeric data item. Numeric category data items sort by number. Note: If you change a measure to a category, then it uses this category icon. The icon indicates a filtered category data item. The icon indicates a user-defined format category data item. User-defined format categories can be based on underlying numeric or character data.
Date and Time		A category data item whose distinct values are used to group and aggregate measures. There are three types of date categories: date, datetime, and time. Examples of date, datetime, and time categories are Order Year, Date of Sale, and Delivery Time.
Geography		A category data item whose values are mapped to geographical locations or regions. Geography data items can be used in reports to show your data on a geographic map. For example, a geography data item can identify geographic information that is specific to your organization (for example, sales regions, warehouse locations, oil platforms, and so on). The icon indicates a filtered geography data item.
Hierarchy		A data item whose values are arranged with more general information at the top and more specific information at the bottom. The first level in the hierarchy is the root level. For example, you might have a Time hierarchy, which includes the Year (the root level), the Quarter, and then the Month. You can also have geographic hierarchies.
Measure		A data item whose values can be used in computations. These values are numeric. Examples of measures include Sales Revenue, Units Sold, and Salary. The designer assigns a default aggregation method to every measure. Almost all measures are assigned sum. You can change the aggregation method. The icon indicates a filtered measure data item.

Note: Report objects that are imported from SAS Visual Analytics Explorer (the explorer) use either live or on-demand data. Therefore, you can update the properties and styles for these report objects in the designer, but you cannot change the data assigned to them.

Select Data Items

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

On the Data tab in the left pane, 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, click , which displays the Add Data Source window. 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. Click Yes in the confirmation message that is displayed.

Note: For list tables only, you can press the Ctrl key to select multiple data items, and then drag and drop them onto the table in the report canvas.
Select an existing report object that uses the same data source name or add a new report object to the section.
Drag and drop a report object onto the report canvas. Alternatively, you can right-click on a data item and select Add Data Item to <ReportObject>, where <ReportObject> is the name of the report object in the report. (For example, List Table 1, Bar Chart 1, and so on.)

Note: For list tables only, you can press the Ctrl key to select multiple data items, and then drag and drop them onto the report canvas.
If the selected data item can replace a current data item in the report object or if the selected data item is valid for multiple data roles, then select the data assignment from the Assign <DataItemName> as window. A data assignment that is marked with a red asterisk (*) is required before the query can be run.

Here is an example of the Assign <DataItemName> as window when a data item called Product Line is being dropped onto a bar chart.

Assign as Window
(Optional) To see more information about a data item, select it in the list. The Name, Role, Format, and Aggregation are displayed in the data item table below the list of data items. For a category data item with a user-defined format that has an underlying numeric value, you can specify Sort Options.

Here is an example of the details for a data item called Engine Size:

Details about a Selected Measure Data Item

Rename Data Items

You can rename data items in the data source using the Data tab.

To rename data items:

On the Data tab, right-click on a data item, and then select Rename Data Item. The Rename Data Item window is displayed.

Rename Data Item Window
Enter a new name. The name cannot be used by another data item in the same data source.
Click OK.

Alternatively, you can use the data item table at the bottom of the Data tab to rename a data item. For the Name property, enter a new name for Value.

Search for Data Items

If your data source contains many data items, you can search for particular data items using the Data tab.

To search for data items:

Enter the name of a data item in the search field on the Data tab. The field is located above the list of data items.

Search Field in the Designer
(Optional) Click to collapse the list of data item groupings or click to expand the list of data item groupings.
Click to clear the search term and display all of the data items in the data source.

Sort Data Items on the Data Tab

To sort data items on the Data tab, click options icon

, and then select Sort Items

Ascending By Name or Sort Items

Descending By Name. The data items are sorted on the Data tab within each grouping. The default sort is Ascending By Name.

For information about sorting data values in report objects, see Sorting Data Items in Reports.

Group Data Items on the Data Tab

To group data items on the Data tab, click options icon

, and then select one of the following:

Group Items By First Letter
Group Items By Data Type
Group Items By Role
Group Items By Format
Group Items By Aggregation

The data items are grouped on the Data tab. The default is grouping By Role.

Duplicate Data Items

Duplicating measure data items in the designer enables you to see the aggregations of a data item (Sum, Average, Minimum, Maximum, and Count) side by side in a table. You can duplicate a numeric measure if you want to use it as a category to group other values in some tables or graphs. If you save a report with duplicate data items, then those data items are available when you edit the report the next time.

You can duplicate a calculated data item to make a variation of a calculation. For example, you might make similar calculations involving miles per gallon for a vehicle, but you create one calculation using MPG (City) and another using MPG (Highway). You can duplicate any data item if you want to use it with more than one format in your report. For example, you might change Month to Year for a date data item.

To duplicate a data item:

On the Data tab in the left pane, right-click the data item that you want to duplicate.

Duplicate Data Item Menu Selection
Select Duplicate Data Item. All of the properties of the original data item are copied to the duplicate data item. The duplicate data item appears in the list of data items on the Data tab. For example, if the original data item name is Engine Size, then the duplicate data item is displayed as Engine Size (1). If you choose to duplicate the same data item again, then it is displayed as Engine Size (2).
(Optional) Change the name, format, or aggregation for the duplicate item (or items).
(Optional) Edit the calculation for a calculated data item or aggregate measure.
(Optional) Change the sort options for a category data item with a user-defined format that is based on an underlying numeric value.
(Optional) Change the role for the data item. For example, a numeric data item that has been duplicated and is not yet assigned to a report object can be a category or a measure.
(Optional) Rename the duplicate data item (or data items).

Derive a Distinct Count for a Category Data Item

A distinct count query is useful in many ways. For example, you might want to know the number of distinct products that were purchased during a specific time period. Or, you might want to know which products have the most customers or which products have the most customers in a particular geographic region. You can derive a distinct count for category data items only.

To derive a distinct count data item:

On the Data tab in the left pane, right-click the category data item that you want to use for the distinct count.
Select Derive Distinct Count.

Derive Distinct Count Menu Selection

The distinct count data item appears in the list of data items with a name that is derived from the original name. For example, if the original data item name is Date, then the distinct count category data item is displayed as Date (Distinct Count). The icon identifies the new distinct count category data item on the Data tab.

Derive a Percentage of Total for a Measure

You can derive the percentage of total for a measure as a new aggregated measure in the designer. The aggregated measure does not contain data values in itself, but when it is used in a report object, it displays the percentage of the total value for the measure on which it is based.

Note: The percentage of total is relative to the subset of data that is selected by your filters and ranks.

For example, you might derive the percentage of total for a measure that contains revenue values. If you create a bar chart of the aggregated measure and a category that contains product lines, then the bar chart shows the percentage of total revenue for each product line.

To derive a percentage of total from a measure data item:

On the Data tab in the left pane, right-click on the measure data item that you want to use for the percentage of total.
Select Derive Percent of Total.

Derive Percent of Total Menu Selection

The percentage of total measure data item appears in the list of aggregated data items with a name that is derived from the original name. For example, if the original measure data item name is Revenue, then the percentage of total measure data item is displayed as Revenue (Percent of Total). The icon identifies the new percentage of total measure data item on the Data tab.

Create Geography Data Items

A geography data item can be useful if your data contains values that are mapped to geographical locations or regions. For example, a geography data item can identify geographic information that is specific to your organization (for example, sales regions, warehouse locations, oil platforms, and so on).

If you change a numeric measure to a geography data item, then it automatically becomes a category data item.

Starting with the 6.2 release, you do not have to use a custom geography data item. You can use the geography items that are available in SAS Visual Analytics. These geography data items are Countries, US States, US States Abbreviated, US ZIP Codes, and Custom. To access these geography data items, select the Data tab in the left pane. Right-click the data item that you want to use for the geography data item, and then select Geography.

Note: Calculated data items cannot be turned into geography data items.

To create a custom geography data item:

On the Data tab in the left pane, right-click the data item that you want to use for the geography data item. Select Geography, and then select Custom. The Geography window is displayed.

Geography Window
Select a measure for the Latitude. You can also enter the first letter of the name of the latitude column to quickly search for it in the drop-down menu.
Select a measure for the Longitude. You can also enter the first letter of the name of the longitude column to quickly search for it in the drop-down menu.
Select a Coordinate space. The choices are World Geodetic System (WGS84), Web Mercator, and British National Grid (OSGB36). The default is World Geodetic System (WGS84).
Click OK. The icon identifies the new geography data item on the Data tab.

Modify Data Item Properties

Rename a Data Item

To rename a data item:

Select a data item on the Data tab.
In the data item table, select the existing name for the data item, and then enter a new name. The name cannot be used by another data item in the same data source. Your change is saved automatically.

Modify a Data Item’s Role

You can modify a data item’s role. For example, you might want to modify a measure data item to be a category data item. A data item's role cannot be changed if that data item is in use in the report. However, duplicating the data item allows the new data item to have a different role.

Note: You cannot change the role for the frequency data item or the frequency percent data item. You cannot change the role for an aggregate measure.

Tip

For geography data items, use the context menu instead of the drop-down menu to change the role.

To modify a data item’s role:

Select a data item on the Data tab.
In the data item table, select the existing role name for the data item.
Click to open the drop-down menu. Select Measure or Category. Your change is saved automatically.

Note: Category data items cannot be converted into measure data items.

Note: Calculated data items cannot be converted into geography data items.

Modify the Format of a Numeric Measure Data Item or a Date, Datetime, or Time Data Item

You can modify the format of a numeric measure data item or a date, datetime, or time data item. You can also modify the format of a data item with a user-defined format, as long as the user-defined format is based on an underlying numeric value.

Note: You cannot modify the format of a data item that is being used in a filter or as part of a calculated or aggregated measure.

To change the format:

Select a data item on the Data tab.
In the data item table, select the existing format. A list is displayed with the Format type, Width, and Decimals (for numeric data items). Make your selections. A sample of your selection is displayed under the list.

Note: There are different format variations available for some format types for date, datetime, and time data items. Select the format variation based on the sample value displayed in the Format drop-down list.

Here is an example of the list for a numeric data item:

Available Formats for a Numeric Data Item

Here is an example of the list for a date data item:

Available Formats for a Date Data Item

Note: The Reset to Default option is displayed only if the format has been changed from the default.

Note: The Reset to Default option is available for user-defined format data items after they have been modified to a standard numeric format, as long as the data item is still a category data item.
Click OK to save your changes.

Modify User-Defined Formats

In the designer, user-defined formats that are defined in the SAS LASR Analytic Server are applied to the results, even though the Format property of the data item does not display the name of the applied user-defined format. You can change the format for an underlying numeric data item, but you cannot change the format for an underlying character-based data item. If you change the numeric format, you can restore the user-defined format by selecting Reset to Default.

The Format property of the data item does not display the name of a character-to-character user-defined format, but it does display the base name for a numeric-to-character user-defined format.

The

icon identifies a category data item with an active user-defined format on the Data tab.

Modify How a Measure Is Aggregated

You can change the aggregation method for a measure in a data source using the Data tab or a report object in the canvas.

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.

To change the aggregation method using the Data tab:

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

Note: All report objects in the report that uses this data item are affected by this change unless you have selected a local aggregation override.

Available Aggregations for a Data Item

To change the aggregation method when you are working with a report object in the canvas:

Choose one of the following:
- For a list table, right-click on a measure header in the report object.
  
  Aggregation Menu Items for a List Table
- For a crosstab, right-click on the header row with the measure.
- For a graph, right-click on the measure name hotspot. Or, you can right-click on a measure data item name on the Roles tab.
  
  Aggregation Menu Items for a Graph
Select Aggregationaggregation name, where aggregation name is Sum, Average, Minimum, Maximum, or Count.

Your change is saved automatically.

Note: This is a local override for only this report object. It does not affect the default aggregation for this data item in other report objects.

Modify the Sort Options for a Category Data Item

Using the data item table on the Data tab, you can change the Sort Options for a category data item with a user-defined format that is based on an underlying numeric value. The sort options are the following:

Formatted

Uses the formatted character output of the user-defined format and sorts lexically. (For example, the names of the months in the year would sort as April, August, December, February, and so on.) Formatted is the default.

Unformatted

Use the underlying numeric value and sorts numerically. (For example, if the underlying numeric value for January is 1, for February is 2, for March is 3, and for April is 4, then the months of the year would sort as January, February, March, April, and so on.)

Delete Data Items

You can delete data items that you have created in the designer (for example, calculated data items or duplicated data items) so that they no longer appear on the Data tab in the left pane.

You cannot delete a data item that is inside a hierarchy if it reduces the hierarchy to a single level. You cannot delete a data item if it is the last or only reference to a column in the original data source. You cannot delete a data item if it is used in a calculated data item, aggregate measure, or a geography data item.

Tip

You can hide a data item that you cannot delete by right-clicking on the data item name in the Data tab, and then selecting Hide Data Item. For more information, see Showing or Hiding Data Items.

To delete a data item (or data items):

On the Data tab in the left pane, right-click the data item that you want to delete.

Delete Data Item Menu Selection
Select Delete Data Item.
Click Yes in the confirmation message that is displayed. The data item is removed from the list of data items, as well as from any report objects, filters, or ranks that were using it.