Use the Analysis Grid
This document introduces Logi Info end-users to the Analysis Grid. It has its own user interface that allows you manipulate data, create charts, change table layouts, and much more, at runtime. Topics include:
- About the Analysis Grid
- Selecting Data
- Adding Formula Columns
- Filtering Rows
- Showing, Hiding, and Moving Columns
- Sorting Rows
- Grouping Rows
- Aggregating Data
- Controlling Paging
- Formatting Data
- Creating Charts and Gauges
- Pivoting and Summarizing Data
- Exporting Data
- Adding Analysis Grid Charts to Dashboards
Information for developers is available in our Developing with the Analysis Grid document.
The Analysis Grid user interface consists of separate panels for controls, tables, and visualizations. At runtime, you can manipulate the controls, creating data analyses and visualizations on the fly.
In the example Analysis Grid shown above, both a table and a chart are displayed.
As you can see in the examples above, the Analysis Grid can look quite different depending on the Theme being used. The examples in this document use the Signal Theme.
The Analysis Grid consists of a set of panels. The Controls Panel, at the top, includes a set of tabs or buttons that allow you to manipulate the data and visualizations at runtime. The visibility of specific controls is configured by the application developer and so you may or may not see the full set shown here. When a tab or button is clicked, a configuration panel for that feature appears, as shown above. Clicking it again hides the panel.
Visualization Panels contain either a data table, crosstab table, or chart. Panels can be collapsed or expanded using the "+" and "-" icons. You can also rearrange their order by clicking with your mouse near the top of a panel, and dragging it up or down. Panels containing tables may display an icon for exporting them to Excel, CSV, or PDF. The availability of the different types of exports is under developer control, so those available to you may vary.
Clicking a panel's Gear icon will display a configuration area for the specific visualization. The Table configuration area, shown above, let's users control a variety of table features.
Table column headers contain a variety of features. They can be dragged to rearrange the column order or to adjust column widths. When column header text is clicked, drop-down menus provide sorting, filter, formatting, grouping, and other features, as shown above. The availability of each feature can be restricted during development.
If your application has been configured for it, you'll see Undo and Redo icons, circled above, beside Add Crosstab. These allow you to undo and redo your recent actions. Now, let's get started using the Analysis Grid.
Depending on how your application has been configured, you may or may not see this feature. Skip this section if you do not see a "Data" tab or button at the top of your Analysis Grid. Click the "Data" tab or button to open its panel:
The first thing you'll need to do is select the data table or view you want to work with. The data source tables and views available to you have been determined by the application developer. Your application may be configured for multiple data sources; if so, it will look like the example shown above, right. If that case, you'll need to select a data source first, then a table or view.
As soon as you select data, a data table will appear showing it. A set of column selection checkboxes will also appear, as shown above. You can un-check the boxes for columns you don't want to include in your analysis work. If it's visible, click Apply Column Selection to update the table, otherwise the table may update automatically.
Your application may be configured to allow you to "join" different data tables. If so, you'll see additional data selection lists, as shown above left. By default, items like Orders - Customers indicate an Inner Join, while items like Orders Order Details indicate a Left Outer Join. However, these designations can be customized by your application's developer and other Join types may be available. What's a "join"? A join combines two sets of data to produce a single dataset. Different types of joins produce different results. For example, an Inner Join selects all rows from both tables as long as there is a match between a column in both tables. A Left Outer Join selects all rows from the first table and adds rows from the second table that match a specified column value.
When you select data that joins tables, you'll see a color-coding scheme applied to the table that indicates where data came from. In the example shown above, table columns with a blue border came from the original table "Orders" while columns with a red border came from the selected join. Once you've selected data, all of the other tabs or buttons at the top of the Analysis Grid become enabled. Click the Data tab or button to hide the data selection controls.
Click the Formula tab or button to use the feature that allows you to add calculated columns to the data:
New columns are added at the right side of the table but can be relocated by dragging them. Here's how to use this feature:
- Help constructing a formula is available via the Formula Help button.
- Enter the Name for the column that will be added to the table.
- Insert column names, functions, and operators into the Formula box by selecting them here.
- And/or enter the formula by typing it into the Formula box. Column names should be enclosed within square brackets [ ] and typical math operator symbols, such as + - * / should be used. You can always edit or delete anything in this space.
You can enter formulas that don't contain data columns.
- Specify the Data Type for the new column.
- Specify a Display Format. Formatting options include numeric and date formats. Click Add to create the new column and refresh the table.
- As Formula Columns are created, they're added to the Formula Columns list. Use the adjacent Replace button and Remove (Trashcan) icon to manage the list. Columns that have been added are now included in the list of available columns (3) for use in other formulae.
Note that, to prevent creation of unmanageable tables, numeric type Formula columns are not available for use in a Crosstab table as the Header Values Column or the Label Values Column. Click the Formula tab or button to hide the controls when done.
Click the Filter tab or button to use the feature that lets you remove table rows that don't meet your criteria:
Here's how to use this feature:
- Select the Filter Column containing the values to be compared.
As shown above, you'll see that the options are grouped and color-coded to make it easier for you to identify them. If you created any Formula columns, they'll be in there, too.
- Set the filtering criteria by selecting a Comparison operator from the list.
Comparison operators include = , <, >, < =, > =, Not =, Starts With, Contains, Not Contains, and Does Not Start With. If the Filter Column is a date, then Date Range is available and some other options are not. The Starts With and Contains operators are useful for finding values at the beginning or within text data. The Not Contains and Does Not Start With operators work in the opposite manner.
Comparison operators In List and Not In List allow comparison against a comma-separated list of values you enter in the Value text box.
Depending on the comparison chosen, additional input controls may be displayed, for example, for date ranges. Or you may see a browse button that lets you select values for comparison from a pop-up list of choices.
- Enter a comparison Value. Wildcard characters (*, %) are not allowed in these values. Click Add. Rows that don't meet these criteria will be removed from the table.
- As filters are created, they're added to the filter list. Use the adjacent Replace button and Remove (Trashcan) icon to manage the list.
If you add multiple filters, only rows that meet all the conditions will be retained (an "And" situation). Clicking the And link in the Filters list, shown above, changes it to an Or link, so rows that meet any of the conditions will be retained. A set of four arrow icons will also appear by the Remove (Trashcan) icon. These can be used to re-order the precedence of the filters or to group them together in various arrangements using parentheses.
Once filters are configured, you can use the Gear icon to collapse the Filter configuration area, as shown above. Checkboxes and filter descriptions will remain visible in the area. Uncheck a checkbox to disable a filter. If you click the description text, simple controls will appear, allowing you to change the filter value.
Filtering by Dates
If the Filter Column selected is a datetype column, the interface presents different value controls:
You may choose to filter on a Specific Date and either type it in or select it from a pop-up calendar. Or, as shown above, right, you can filter using a Sliding Date value and select from a long list of relative dates (Last Week End, Last Month Start, 90 Days Ago, etc.)
If the Comparison option Range is chosen, as shown above, different value controls for Starting and Ending dates are displayed. These can be used in a variety of combinations. Click the Filter tab or button to hide the panel.
You've selected which data columns to include in your working dataset but you may not want to see them all. The Columns feature in the Table configuration area, which can be displayed by clicking the Gear icon or by clicking any table column header, controls column display:
As shown above, you can remove a column from the table by un-checking it. The (All) checkbox makes working with lots of columns easier, and you'll need to click OK to refresh the table with any changes. Click the Gear icon again to hide the configuration area.
You can also rearrange the order, and change the widths, of table columns using two "drag handles" that appear when you hover your mouse over a column header, as shown above.
This feature allows you to set the sort order of the table column data. You can display the Sort configuration area by clicking the Gear icon and then the Sort item, or by clicking any table column header and selecting a Sort option from the pop-up menu.
Here's how to use this feature.
- Select a data column to sort on:
You'll see that the available columns are grouped and color-coded to make it easier for you to identify them. If you created any Formula Columns, they'll be in there, too.
- Select a sorting orderdirection - ascending or descending. Click Add to add the sort and refresh the table. The table will immediately be updated with the new sort. Repeat as desired for additional sorting.
- As Sort Order columns are created, they're added to the list of sorts. Use the adjacent Replace button and Remove (Trashcan) icon to manage the list.
- You can also sort a column directly, by clicking its column header and selecting the sort in the menu that appears.
Click the Gear icon to hide the configuration area. The table rows will be re-displayed in the sort order created.
If your application has been configured for it, an arrow, shown circled above, will appear beside the column header text to indicate that a sort is in effect and to show its order/direction.
Once a column has been sorted, the column header menu also allows you to change the order or remove the sorting, as shown above.
The Group feature in the Table configuration area, which can be displayed by clicking the Gear icon or by clicking any table column header, lets you group table rows:
Here's how to use this feature:
- In the configuration area, select the Grouping Column for the first level of grouping from the list of columns. Depending in the column's data type, additional input controls may be displayed. Click Add to group the data and refresh the table. Repeat as desired to create sub-groups.
- As groups and sub-groups are created, they're added to the Groups list. Use the adjacent Remove button and Replace (Trashcan) icon to manage the list.
- The Exclude Detail Rows checkbox can be used to hide the rows that have been grouped, "collapsing" each group into a single row in the table.
- Grouping and un-grouping can be also be accomplished by clicking a column header in the table and then selecting the desired options from the context menu.
Click the Gear icon to hide the configuration area.
The Aggregate feature in the Table configuration area, which can be displayed by clicking the Gear icon or by clicking any table column header, lets you calculate totals, averages, and other aggregations:
Here's how to use this feature:
- Select the Data Column to be aggregated from the column list.
- Select the Aggregate Function. Options include: Sum, Average, Standard Deviation, Count, Distinct Count, Minimum, and Maximum. Click Add to aggregate the data and refresh the table. The formatting of the aggregation will match that of the column.
- As aggregates are created, they're added to the Aggregates list. Use the adjacent Replace button and Remove (Trashcan) icon to manage the list.
- Aggregate results appear in an extra table row that can be positioned at the top or bottom of the table, as shown above. If Grouping is in effect, aggregate values will also appear at each grouping level in the table.
- Aggregating can be also be accomplished by clicking a column header in the table and then selecting the desired options from the context menu.
Columns with Null values are excluded by default from aggregations. However, your Analysis Grid may be configured to include them; check with the developer of your application for details. Click the Gear icon to hide the configuration area.
Aggregate Awareness: Selecting the Order of Operations
If you're going to aggregate a Formula column (created by executing a calculation), the "order of operations" may be important. For example, should the Analysis Grid do the calculation first, then apply the aggregation, or apply the aggregation and then do the calculation? The choice can result in completely different results. The Analysis Grid includes "aggregate awareness" and, upon detecting this situation, will prompt you to choose the desired order of operations. Here's how it works:
In the example above, the TotalValue column is a Formula column, created using the formula [Quantity] * [UnitPrice].
We'd like to know the average TotalValue, so we click the TotalValue column header and select AggregateAverage, as shown above.
Because the Analysis Grid knows that TotalValue is a Formula column, it will display the modal dialog box shown above, allowing us to decide which order of operations to use. This level of control ensures that you'll get the aggregation you expected, and is especially useful when working with complex formulas, such as per capita and pro rata calculations, with multiple columns.
Click the Table's Gear icon and then the Paging option item to control the pagination of Analysis Grid tables:
Here's what you need to do:
- Choose a paging option:
- Show all rows will display all of the data at once in the table. Caution: Selecting all rows can result in a length delay while data is retrieved.
- Show paging
will display a fixed number of rows per page and display the paging controls.
These settings affect all tables simultaneously. Click the Gear icon to hide the configuration area.
You can format the appearance of the data in the Table by clicking a table column header and selecting Format and then the desired option:
These options allow you to apply, or remove, a variety of standard formats. More information about these formats is available in our document Formatting Data. Note that selecting No Format will clear previously-selected numeric or color formatting but not alignment.
The Percent of Total format applies a calculation that determines each data value's percentage of the summed values and displays it as a percent. Two special format options insert visualizations right into the column:
Selecting the Bar Gauges option will produce a horizontal bar gauge within the column, as shown above. The actual data value will appear in a tooltip if you hover your mouse over the gauge. Selecting the Cell Colors option will display the column value using an imbedded Cell Color Slider, as shown above. You can drag the slider in the column header to customize the color ranges.
The Cell Colors feature has been changed to allow you to apply conditional colors:
Its previous functionality is now represented by a new Format menu item, Cell Color Slider, as shown above.
The menu's Cell Colors item now displays the pop-up dialog box shown above, where you can select data value ranges and cell background colors.
Click the Add Chart tab or button to use the feature that lets you create charts and gauges. A separate Chart panel with its own configuration area will be displayed:
Here's how to use this feature (note that some controls shown above only appear for specific data and chart types):
- Click the option item for the type of chart to be created.
- Assuming a Bar chart was selected, select the Label Column. This provides data for the X-axis of the chart. If the column selected is a date-type column, an interval control (Year, Quarter, Month, Day) will be displayed.
For text type columns, you can select the Label data sort order, A-Z or Z-A, in a sort control.
- Select the Data Column (Y-axis data, the "height" of each
bar). You'll see that the options are grouped and color-coded to make
it easier for you to identify them. If you created any Formula columns,
they'll be in there, too. You can also choose to show the actual data
values on the chart.
- Select a Data Aggregation function. Options include: Sum, Average, Standard Deviation, Count, Distinct Count, Minimum, and Maximum. Columns with Null values are excluded by default from aggregations.
- Select whether or not to show the actual data values as labels within the chart.
- The Additional Column specifies a second data series that will be charted along with the Data Column values for comparison. Depending on the chart type, other controls will appear for use configuring a second series, including aggregation options and charting types.
- Data Forecasting is available for Bar, Line, Curved Line, and Scatter Plot (v12.2) charts. See the section below for more information.
- Relevance allows you to tune the data to be shown. Options include Automatic, Rank, and Percentage.
- Bar Orientation allows you to choose whether the bars are vertical or horizontal arrangements for Bar charts.
Depending on how your application is configured, you may see an "OK" or "Update" button that applies all of your selection changes to the chart at once. Otherwise, your chart will be redrawn immediately as you make individual changes.
Hide the chart configuration area by clicking the Gear icon, or delete the chart completely by clicking its Remove (Trashcan) icon. Charts are displayed in their own panels, so you can expand and collapse them using their "+" and "-" icons.
You can also rearrange the order of chart and table panels by clicking with your mouse near the top of a panel, and dragging it up or down. Charts will automatically include Quicktips, which appear when you hover your mouse over a data value, as shown above. In addition, "resizing handles" (circled above) will appear when the mouse is over the chart, allowing you to resize the chart by dragging them. Unlike the chart shown above, Bar charts that are not time-oriented will automatically be shown in a horizontal format. This allows greater clarity in reading the "X-axis" label text.
Data Forecasting, if included by your application developer, is available for Bar, Line, Curved Line, and (in v12.2+) Scatter charts.
If it's available, extra controls for it will appear in the Chart configuration panel, as shown above.
Data forecasting is the process of generating values based on events that have not yet occurred. "Prediction" is a similar but more general term. Forecasting refers to formal, statistical methods that use time series, cross-sectional, or longitudinal data to produce predicted data. Typically, forecasts are displayed most effectively on charts. > Forecasting analysis options, which may vary by chart, include:
- Time Series (Time Series Decomposition), consisting of data in a natural, time-related order with a strong interval, where the Label Column data is of DateTime-type and the Data Column is a number.
- Regression, using one of several regression analysis functions. Regression analysis is recommended when the focus is on a relationship between a dependent value and one or more independent values. Available analysis functions include:
- Linear - used to calculate predictive values based on a trend line.
- used when attempting to predict an output of a system based on
previous outputs. The estimation technique used is based on "Burg's"
- Exponential, Logarithmic, Polynomial, or Power - non-linear types used to display the relationship between dependent and independent variables as a curvilinear function, which may provide more accuracy than a linear regression.
More information about forecasting in Logi applications can be found in our Forecasting Methodologies document.
Click the Add Crosstab tab or button to use the feature that lets you create a crosstab (also known as a "pivot") table:
Here's how to use this feature:
- Select the Header Values Column, whose values will be shown horizontally, as column headers, across the top of the crosstab table. Note that, to prevent unmanageably wide tables, numeric columns in the original data and numeric Formula columns are not available for use here. Additional controls may appear depending on the data type of the selected column.
- Select the Label Values Column, whose values will be shown vertically, in the left-most column of each row. Note that, to prevent unmanageably long tables, numeric columns in the original data and numeric Formula columns are not available for use here.
- Select the Aggregate Values Column, whose values will be aggregated to produce the contents for the rest of the table cells.
- Select the Aggregate Function to be applied to the column selected in Step 3. Options include Sum, Average, Standard Deviation, Count and Distinct Count, Minimum (v12.2), and Maximum (v12.2).
- Select a Summary Function to display a summary result.
- If you application has been configured for this, check the Compare Label Columns checkbox to cause the difference between column values to be displayed, along with a cell shading indicator.
Also, depending on the application configuration, you may see an OK button like the one shown above that applies all of your selection changes to the table as a batch. Otherwise, your table will be updated immediately when you make individual changes. The Summary Function feature, introduced in v12.1, and the Label Column Comparison feature provide interesting ways to analyze the data:
As shown above, summarizing the data totals the rows and columns, inserting a row and column to display the results. Comparing Label columns can produce color spectrum backgrounds or directional arrows, and can display the value differences as values or percentages, in the cells. Hide the crosstab configuration area by clicking the Gear icon, or delete the table entirely by clicking the Remove (Trashcan) icon. Crosstab tables are displayed in their own panels, so you can expand and collapse them using their "+" and "-" icons. You can also rearrange the order of chart and table panels by clicking with your mouse near the top of a panel, and dragging it up or down.
Analysis Grid tables include three Export functions, controlled by the Export icon:
Depending on your application's configuration, you may see some, all, or none of the Export options shown above. They allow you to export the table's data, as follows:
Excel - The data is exported into an .xlsx or .xls file, as raw data. The file can be viewed in Excel (if installed on your computer) or can be saved to your file system. Table column headers are exported into the first row of the Excel worksheet, as shown above, and numbers are exported as text. Depending on your application's configuration, the data may be formatted and specific worksheet column widths may be set.
CSV - The data is exported into a .csv file, as raw data. The file can be viewed in Notepad (or any text editor) and in Excel (if installed on your computer) or can be saved to your file system. Table column headers are exported into the first row. All fields are enclosed in double-quotes and separated by commas.
PDF - An image of the table is exported into a temporary .pdf file. This file can be viewed in your browser using the Adobe Acrobat plug-in, similar plug-ins, or, in some cases, native browser technology. Viewers usually let you save the export as a file, if desired, or print it. Table headers will be displayed at the top of each PDF page.
Export Row Limit
When working with very large data sets, exports may not be practical. To prevent unacceptably long exports, your application may have been configured with a limit on the number of rows that can be exported. If the number of rows in your Table (across all pages) exceeds this limit, the export controls will be disabled. If this is the case, you may be able to reduce the number of rows in the Table and re-enabled the export feature by adding one or more Filters on the data.
The Analysis Grid includes an optional feature that lets you create a table or chart in one report, then add that chart as a new panel in an existing Dashboard in another report.
If your application has been configured for this feature, Analysis Grid tables and charts will display an Add To Dashboard link, as shown above. When you click the link you'll be prompted for the Panel Title and a description for display in the Dashboard Configuration Page. The new table or chart panel thereafter appears in the Dashboard Configuration Page. You can insert multiple tables and charts into a Dashboard using this technique. For more information about Dashboards, see our document Introducing the Dashboard.