Views Pivot Grid Options


1. Introduction

 

When looking for the tools of analysing data in different ways without any coding, the pivot grid is the best solution.  With the drag-and-drop functionality, it allows Users to rearrange fields in the view dynamically.  This includes:

       Interchanging columns and rows on the fly

       Filtering and sorting items in different ways

       Collapsing and expanding data at different levels.

 

The main features of the Pivot Grid include:

 

Automatic summary calculation for each cell – There are 5 summary functions available: Sum, Count, Min, Max, and Average.

 

Display Multiple data fields – This feature allows you to calculate and display multiple summaries at the intersection of each column and row.

 

Multiple column and row fields – The values of column and row fields are listed along the top and right edges of the control and they identify column and row headers. The values of multiple column (row) fields are arranged into a hierarchy and can be expanded.

 

Automatic Total calculation – Totals display total summaries calculated for outer column and row fields.

 

Automatic Grand Total calculation – Grand Totals display overall summary totals calculated against all the columns and rows. They are displayed as the bottom-most rows or right-most (left-most) columns.

 

Filtering data – This feature enables you to limit which records are processed. A User can easily change the filter at runtime via the Filter Drop-down.

 

Automatic sorting of column and row field values in alphabetical order (for text data) and in order of magnitude (for numeric and date/time data). A User can change a field's sort order (from ascending to descending and vice versa) by clicking the field header.

 

Sorting by summary values – The Pivot Grid control enables column and row field values to be sorted by total summary values.

 

Customizable position of data field headers.

 

Export – to HTML, XML, XLS and TXT formats.

 

Cell selection and copying to clipboard – A User can select a range of cells with the mouse and then copy their contents by pressing the CTRL+C shortcut. The selected data is copied to the clipboard in the tab-separated format that is natively supported by Microsoft Excel, for instance.

 

Dragging fields between different areas – Users can freely drag field headers between different control areas. This gives them full control when analysing the data.

 

Built-in Customisation Form – Allows Users to temporarily hide fields and make them visible again.

 

 

2.  Basic View

 

A Pivot Grid in Ostendo has four major ‘working’ areas shown in the top of the display

 

Filter Band: A band across the top of the Grid in which you can display fields and their filtering options.  If you click on the drop-down contained within each field then you can specify the specific selection(s) to appear in the Pivot Grid.

 

Data Area: A small area immediately under the Filter Band in the left part of the screen.  These show the fields being calculated whose details are displayed in the main Pivot Grid

 

Column Area: An area immediately under the Filter Band to the right of the Data Area.  If you:

•

Click on the drop-down contained within each field name then you can specify the specific selection(s) of that name to appear in the Pivot Grid.

•

Click on the transparent arrow within each field name to sort the columns into ascending or descending order

 

Row Area: An area immediately under the Data Area.  If you:

•

Click on the drop-down contained within each field name then you can specify the specific selection(s) of that name to appear in the Pivot Grid.

•

Click on the transparent arrow within each field name to sort the rows into ascending or descending order

 

3.  Field Visibility

 

You can carry out the following activities that enable you to address the visibility of selected fields.

 

Individual Field visibility options:  If you ‘right mouse on any field in the upper ‘work area’ then the following options are available

•

Hide: Hides the selected field from the display

•

Order: Moves the selected field to:

o

Move to Beginning

o

Move to End

o

Move Right

o

Move Left

•

Hide or Show Field List: Hides or shows a panel in the bottom right of the screen for hiding and restoring multiple fields.  See next option

 

Multiple Field visibility options:  If you ‘right mouse outside of any field in the upper ‘work area’ then the following is displayed

•

Hide or Show Field List: Hides or shows a panel in the bottom right of the screen for hiding and restoring multiple fields.  Within this panel you can

o

Drag any shown field onto the Pivot Grid for use in the Grid

o

Drag any field from the Pivot Grid to this panel to hide the field

 

4.  Expanding, Collapsing, Grouping of Columns and Rows

 

You can click on the ‘Plus’ or ‘Minus’ buttons shown in the heading of fields to expand and collapse columns and rows that have nested columns and rows:

 

5.  Filtering

 

Data can be filtered against column, row and filter fields by clicking the filter button (Black downward facing triangle seen in the field header) and selecting items in the filter drop-down.

 

6.  Field Drill-Down

 

For any calculated field in the main body of the Pivot Grid you can ‘double click’ on the field to drill down and display the records that are the source of the displayed summary information.

 

7.  Keyboard Shortcuts

 

The keyboard shortcuts and mouse operations listed in the table below can be used by the User to move focus between cells.

•

Clicking a cell: Focuses the clicked cell.

•

Pressing the Up, Down, Left or Right arrow keys: Focuses a corresponding adjacent cell.

•

Pressing the Page Down key: Moves row focus one page down preserving the column focus.

•

Pressing the Page Up key: Moves row focus one page up preserving the column focus.

•

Pressing the Home key: Focuses the first cell within the current row.

•

Pressing the End key: Focuses the last cell within the current row.

•

Pressing the Ctrl+Home key: Focuses the first cell within the first row.

•

Pressing the Ctrl+End key: Focuses the last cell within the last row.

 

 

8. Re-Ordering Fields

 

The following operations can be performed by Users to rearrange fields:

•

Drag a field header and drop it at a new position within the same or another header area.

•

Select an option from the "Order" submenu in the field header context menu.

•

Double-click a field header within the customisation form or selecting a field header and clicking the form's Add To button.

 

9. Resizing Columns

 

The following operations can be performed by Users to resize columns:

•

Drag the right edge of a column header to change the width of the current and relative columns.

•

If you Double-click the right edge of a column header then it applies best fit to the current and relative columns

 

10.  Selecting Cells

 

The Pivot Grid control allows Users to select multiple data cells using the mouse or the available keyboard shortcuts. The data that is displayed by the selected cells can be copied to the clipboard and pasted into other applications (e.g. MS Excel, MS Word).

Pressing the Shift+Arrow key combination enables Users to select a continuous range of data cells. When such actions are carried out any previous cell selection is cleared.

Pressing the Ctrl+A key combination will select all data cells within the data area.

 

11. Copying Selected Records to the Clipboard

 

Pressing the Ctrl+C or Ctrl+Ins key combination will copy the selected data cells to the clipboard as text.

 

12.  Sorting

 

The values of column fields and row fields are always displayed in ascending or descending order.  You can alternate between ascending and descending by simply clicking on the field header.

 

13.  Output Options

 

Print: You can output the current view to your screen prior to printing or print directly to a selected printer.

 

Email: The drop-down list provides the following options

PDF - Generates a Portable Document Format (PDF) document. This file can then printed and/or attached to an email

XLS or XLSX - Generates a spreadsheet from the displayed information and then opens up the Emailing software on your PC with this spreadsheet an attachment

HTML - Generates an HTML format and displays this in the ‘body’ of the generated email

XML - Generates an XML document, which can be called and displayed as a standard Web form.  It also creates an XSL document showing the source document from the displayed information.  The program then opens up the Emailing routine on your PC with the XML and XSL documents attached.

CSV - Generates an CSV (Comma Separated Value) document from the displayed information and then opens up the Emailing software on your PC with this CSV document as an attachment

 

Export: The drop-down list provides the following options

PDF - Generates a Portable Document Format (PDF) document.   This file can then printed and/or attached to an email

XLS or XLSX -  If this is selected then a panel will appear for you to save the current displayed data as an Excel Spreadsheet.  You may then call up Excel and manipulate the data as required.

HTML - If this is selected then a panel will appear for you to save the current displayed data as an HTML file.  This file can then be called and displayed as a standard Web form using your Browser.

XML - If this is selected then a panel will appear for you to save the current displayed data as an XML file.  Two files are created.   

An XML document that can be called and displayed as a standard Web form using your Browser.


An XSL document showing the source document

This file can then be called and displayed as a standard Web form using your Browser.

CSV - If this is selected then a panel will appear for you to save the current displayed data as a Comma Separated Value file.  This can be used as an import format to external systems. You may also call up the CSV file in Excel (File Type = .csv) and manipulate the data as required.

 

14.  Multi-View Option

 

Using the same selection criteria you can save and recall have multiple Views.  Three buttons are available on the top toolbar:

 

Save: Click on this button and enter the name that you are giving this specific View and then click the OK button

 

Restore: Click on this button to display a list of the saved Views.  Selecting one of these will restore that View.

 

Delete: Click on this button to display a list of the saved Views.  Selecting one of these will Delete that View.from the list