Ad Hoc View Editor

The Ad Hoc View Editor is an interactive tool that displays the data fields retrieved from your data sources. The effects of your changes are evident immediately, and you can adjust the display to highlight the most relevant and compelling aspects of your data.

With the Ad Hoc View Editor, you can switch between table, chart and crosstab views. The user interface for each view type includes options specific to the current view type in addition to the basic Ad Hoc user interface components shared across all views. Context menus are available in all view types.

Tables: are used to view values in the database and to summarize the values in columns
Charts: compare one or more measures across multiple sets of related fields
Crosstabs: aggregate data across multiple dimensions.

Getting Started

The Ad Hoc Editor is a browser-based, interactive tool for designing views and exploring your data:

As a designer, the Ad Hoc Editor lets you easily create and edit views and then use them to create reports. To create a view, select “Create” from the main menu, choose the available data set, and a view type. Then select the fields in your data source that should appear in your view. The Ad Hoc Editor’s interactive display of your results lets you evaluate your design without having to run the entire data set first. Finally, you can save the view, create one or more reports from it, and export it to several file formats.
As a data explorer, the Ad Hoc Editor provides the analysis option of filtering to help you recognize trends and outliers in your data. You can drill into specific details or analyze your data at a very high level.

The interesting trends and anomalies revealed by data exploration can lead you to create a view or report highlighting your findings. Conversely, while creating an Ad Hoc view, you may identify a trend that warrants further investigation. You can move seamlessly between the two activities—view creation and data exploration.

Ad Hoc View User Interface Components

The components of the Ad Hoc View Interface are illustrated and defined below:

Component	Description
Data Selection panel	The Data Selection panel shows the list of available fields, which can be added to any bar in the Layout Band, and measures, which are summarized values that cannot be added as groups. Typically, measures are created from numeric fields in the data sets, but in some circumstances, it makes sense to use string fields when summarized as a count. For example, you might want to display the number of unique employees called in a given quarter. Use the icon beside the set name to expand or collapse a set of fields or measures. To hide this panel, click the icon in the top left corner; click the same icon on the minimized panel to expand it.
Tool bar	The tool bar at the top of the canvas provides access to many of the Ad Hoc Editor’s functions, such as saving the view or creating a report from the view, undoing and redoing changes, and changing the view’s sort order.
Data mode menu	Click to select the amount of data displayed from the menu. Use Sample Data to design a view more quickly, or use Full Data to see all your data used in the same view. When you choose display mode, full data is displayed regardless of the selection shown in the editor.
View type menu	Click Chart, Table, or Crosstab to see your data in that type of view. Changes made in one type of view apply to the data displayed on another.
Title bar	The top portion of the canvas; click to add or edit the title of the view. To remove the title, point to the Properties menu and select Toggle the Title Bar.
Layout band	The layout band immediately below the tool bar has two boxes where you can drag and drop fields and measures from the Data Selection panel to add them to the canvas. You can change the order of the selected fields canvas by dragging them to a different location in the layout band. The boxes have different labels and functions, depending on the type of view; see the section for the individual view types for more information. To hide the layout band, point to the Properties menu and select Hide Layout Band.
Canvas	Occupying the middle of the editor, the canvas shows your data subject to the constraints you have created. This is also a sample of how your data will appear in any report you create from the view. To see the view without interface components, click for design mode.
Filters panel	This panel displays any filters defined for the view. You can set the filter values and see the resulting change in the canvas. To hide the filters panel, click the icon in the top left corner of the panel. Click the same icon on the minimized panel to expand it again. For more on applying filters, go here.
Filter controls	These menus let you change the display of filters, either collectively or individually. The menu on the filter panel lets you collapse all filters or remove all filters. The menu on each filter lets you view the filter operation, for example “is one of,” or delete the filter. To minimize the view of an individual filter, click the icon beside its name.

Groups

Groups allow you to create detailed data rows. For example, you can group Callouts together by Callout Type or Reason. If you group by Type, the Type names are then rearranged so that all Normal Callouts, for instance, are located under a “Normal” header row; Fill Shifts are together under a “Fill Shift” header row, and so on.

You can use multiple fields to make more specific nested groups. Only fields can be applied to a table as a group; measures cannot. Data is grouped in the table according to the order they have defined. You can change the order by dragging the groups into position if needed.

Ad Hoc Context Menus

Context menus appear when you right-click elements in the Ad Hoc Editor. Each menu offers options for the selected element. If a context menu blocks your view, close it by clicking anywhere outside the menu or by pressing Escape.

The figure “Sample Context Menus” shows the following examples:

Right-clicking a column in a table opens a context menu with options for that column’s settings.
Right-clicking a group value in a standard crosstab’s row or column lets you slice the crosstab.

Table column menu

Standard crosstab group menu

Summaries

Summaries show the result of a function applied to all data values.

In Ad Hoc table views, each field can display a single summary calculation. You can add a summary to a column by right-clicking it and selecting Add Summary from the context menu. The summary function is automatically applied to all groups in the table. Summaries appear at the bottom of each group, as well as at the bottom of the view. When a new group is added, it includes a summary for each column.
In crosstabs, each measure displays a summarized value. Summaries determine the values of the measures at the intersection of each row and column.
In charts, the type of chart determines whether measures are summarized. If summaries are used, they determine the size or location of the graphical elements that represent your data.

In general, you can change the summary function of any measure, with the exception of those that use the percent of total and percent of parent custom field functions.

By default, the module summarizes fields of each datatype as follows:

Datatype	Summary Function	Description
Numeric	Sum	Displays the sum of all values in the set.
Date	Count	Displays the total number of values in the set.
String	Count	Displays the number of values in the set.
Boolean	Count	Displays the number of values in the set.

Select from the following options to set a measure’s summary function in any type of view:

Option	Meaning	Available for
Average	Displays the average of all values in the set.	Numeric
Count All	Displays the number of rows in the set.	Boolean, Date, Numeric, and String
Distinct Count	Displays the number of unique values in the set.	Boolean, Date, Numeric, and String
Maximum	Displays the highest value in the set.	Numeric
Minimum	Displays the lowest value in the set.	Numeric
Sum	Displays the grand total for the set.	Numeric

For Table and Crosstab views, you can remove summaries:

For tables, remove a column’s summary by right-clicking the column or the summary itself, and selecting Remove Summary from the context menu.
For crosstabs, remove a column group’s summary by right-clicking the group label and selecting Delete Column Summary; remove a row group’s summary by clicking Delete Row Summary from the same context menu. These options are only available for the outermost group on either axis.

Setting the Data Format

You can set the format of data in tables and crosstabs. Click a row or column header and select Change Data Format from the context menu. In tables, the format is applied to all rows as well as the group- and view-level summaries. In crosstabs, the format is applied to the measures.

The options that appear in the menu are the formats available for objects of the given datatype. For example, for date datatypes it might list December 31, 2008, and 12/31/2008. By default, non-integer fields use the -1,234.56 data format; integers use -1234.

Calculated Fields & Measures

You can always create custom numeric fields in the editor, such as multiplying a field by an absolute number, subtracting one field from another, or displaying a field as a percent of a total for a column or group. You can add the fields to the view as you would any other numeric field.

As with any field, you can add a summary, change the data format, or change the display label of a custom field. You can also use custom fields in other custom fields to create more complex computed values.

To open the calculated fields dialog box for Ad Hoc views:

Create or open an Ad Hoc view.

Open the calculated fields dialog using one of these methods:

Click at the top right of either the Fields section or the Measures section of the Data Source Selection panel and select Create Calculated Field, from the context menu.
Right-click on an existing calculated field (shown by the icon ) or calculated measure (shown by the icon ) and select Edit. The dialog displays a text field for the name and two tabs, Formula Builder and Summary.

The Formula Builder

Component	Description
Field Name	The following are reserved words and cannot be used as field names: AND, And, and, IN, In, in, NOT, Not, not, OR, Or, or. Names containing these strings, such as "Not Available", can be used
Formula entry box	Shows the current formula for calculating your field or measure. You can edit the formula by typing directly in the panel. You can also add Fields, Measures, and Functions by double-clicking them. Click the buttons below the Formula field to add operators. Formulas must use the following syntax: Labels for fields and measures must be in double quotes (“): “attempt_id”, “Call End Time”. Text must be in single quotes (‘): ‘— ‘. Levels must be in single quotes (‘): ‘ColumnGroup’, ‘Total’.
Operator buttons	Click these buttons to insert the operator in the Formula entry box.
Fields and Measures	Lists all the fields and measures currently in your Ad Hoc view, including any calculated fields or measures you have already created.
Functions	Lists all the available functions you can use in your formula
Function Description panel	Gives a brief description of the function selected in the Functions list, if any. The sample inputs are intended to be as descriptive as possible.
Show arguments in formula checkbox	When this checkbox is selected, double-clicking a function name in the Functions list adds the full description to the Formula entry box; when the checkbox is not selected, double-clicking a function name adds only the function. For example, double-clicking Round adds Round("NumberFieldName", Integer) when the checkbox is selected, and adds Round() when the checkbox is not selected. If you select this checkbox, you can double-click on a string, such as NumberFieldName, and then replace it by double-clicking a name in the Fields and Measures list.
Validate button	Checks the formula for syntax errors, such as missing parentheses or quotes. Your calculated field or measure must be valid before you can create it. Syntax validation does not guarantee that your formula will give the results you want.

The Summary Tab

Summaries show a result applied to all data values. For example, for a numeric field such as total_calls, the summary value might be the sum of all the calls; for a text field such as Reason, the summary value might be the count of all callouts. The Summary tab lets you set the default summary function for your calculated field or measure.

Calculation list: Displays allowed summary functions for your calculated field or measure. The available options depend on the data type of the calculation. See Summary Calculations for more information.
Custom selection: Displays the same options available in the Formula Builder tab, including the Formula entry box, operator buttons, Fields and Measures list, Functions list, and Validate button. You use these options to build a formula for your custom summary. However, for summaries, you are limited to aggregate functions, that is, functions that operate on all the values in your field. For example, Sum and Mode are valid summary functions, because they use all available field values to get a result. Round is not a valid summary function, because it operates on a single value at a time.
Weighted Average: Displays a Weighted On drop-down list, which allows you to choose another field or measure to use as the weight for the average.

Articles in this section