Querying Data Table

Once you are connected to the Longview data server, you are ready to work with the data contained within it. This chapter explains how to use the Longview Add-In for Office to query data table.

Understanding Data Table output

You can use the Longview Add-In for Office to query data table from the data server repository and output it to a Microsoft Excel worksheet. This output format can be conveniently used in a Microsoft Excel Table or a PivotTable, allowing you to further organize, filter, analyze, and summarize the data.

The query results are displayed in a tabular format, with each selected data table column appearing as a separate worksheet column and each record displayed as a row.

You can configure the data query table to your preferred settings in the Data table query dialog. You can also save your preferred settings and use them to re-run the data table query.

Querying Data Table Data

To query data table:

1. Click the Longview tab in Microsoft excel.
2. In the Insert group, click Data Table Query. The Data Table Query dialog opens with the Data Options page in view.

   Note: You must be connected to the data server to run a data extract.

3. In the Data Table list, select the Data Table you want to query from the Data Table drop list. The available columns for the selected data table are displayed.



4. Complete the following field:

Field	Description
Cell Location	Type the location of the top left cell of the query results. Keep in mind that the query results will overwrite any data in the worksheet cells. If you must place multiple queries in a single worksheet, Longview recommends that you ensure that the query results do not overlap.

5. Configure the query by selecting columns and specifying decimals:

   Field	Description
   Name	Displays the available columns for the selected data table. Select one or more columns to include in the query.
   Type	Displays the data type for each column (for example, String, Number, Date, Boolean, File, or UserList).
   Decimals	Optionally specify the number of decimals to display for Number Type columns. The default value is 0. Valid values are between 0 and 9.

Specifying Format options for Data Table Query

Before you run a data table, you can specify how the results of the query are formatted in the Microsoft Excel worksheet. For more information on the layout of the query results, see Understanding data table output.

To specify format options:

1. Click Format Options in the left pane of the Data Table Query dialog. The Format Options page opens.

   

2. In the Number Formatting section, complete the following fields:

   Field	Description
   Apply Custom Formatting	Select this field to apply custom formatting to the data values in the data extract results. This field is cleared by default.
   Decimal Places	This field is available only if Apply Custom Formatting is selected. Type an integer from 0 through 9 for the number of decimal places to include in the symbol data. The default value for this field is 0.
   Use Thousand Separator	Select this field to display numeric values with thousand separators based on your system's regional settings. This improves readability of large numbers in the extract output. Default is unchecked.
   Negative Numbers	This field is available only if Apply Custom Formatting is selected. Specify how negative numbers in the symbol data display in the data query results using one of the following options from the drop-down list: -1234.00 — Negative numbers are preceded by a minus sign. 1234.00 — Negative numbers display in red. -1234.00 — Negative numbers are preceded by a minus sign and display in red. (1234.00) — Negative numbers are enclosed in parentheses. (1234.00) — Negative numbers are enclosed in parentheses and display in red. The default value for this field is (1234.00)

3. In the Symbol and Dimension Formatting section, complete the following fields:

   Field	Description
   Display Format	Specifies how Symbol and Dimension values are displayed in the query results. Select one of the following options: Name – Displays the object name only. Description – Displays the object description only. Name and Description – Displays both the object name and description.

4. Proceed to Running a Data Table Query.

Running a Data Table Query

When you are finished specifying the data and format options for the Data Table, you can run the Query.

To run a data table query:

1. Click Run Query.
2. Click the File tab.
3. Click Save.
4. If you are saving the workbook for the first time, the 'Save As' dialog opens. Enter a name for the workbook and click Save.

Rerunning a Data Table Query

You can use the Longview Add-In for Office to rerun an existing data table. Rerunning a data table query refreshes data values in the query and allows you to modify the query as necessary.

When you open a workbook containing data table queries, the associated values are not updated to show the latest values in the Longview data server repository. The data will be exactly the same as when you last saved and closed the workbook. To see the most up-to-date data from the data server repository, you must rerun your data table queries.

To rerun a data table queries:

1. Right-click any cell in the results of the data table query that you want to rerun and select Rerun Query. The Data Table Query dialog opens, with the Data Options page displayed.
2. Make any changes to the data table query as necessary.
3. Clear or select the 'Clear previous results' checkbox.
• Selected (default): Removes all existing query results and formatting before displaying the new results.
• Cleared: Retains existing query results and formatting, overlaying the new results on top of the existing data.

4. Click Run Query.

Note: Any existing data and formatting (font, borders, or alignment for example) is retained when you reuse the existing worksheet or worksheets with the Clear previous results check box not selected. This includes formatting automatically applied to the existing data table query results.

Saving and loading Query selections

You may save the query selections that you have selected in the Data Options and Format Options tabs of the Data Table Query dialog. This will enable you to quickly prepopulate the Data Table Query dialog in the current session or within a future session.

The query selections are saved to a file. You can name and save the file in a local or network drive. This enables you to give the query a meaningful name and the ability to share your query with others.

Saving Query selections

To save your query selections, you must first specify the selections you wish to save in the Data Table Query dialog. For more information, see Querying Data Table Data.

To save data table query selections:

1. Complete the fields in the Data table query dialog.
2. Click Save Query.
3. The Save As dialog opens. Enter a name for the query and click Save.

Loading Query selections

You may quickly prepopulate the Data Table Query dialog with selections from a saved query.

To load data table query selections:

1. Click the Longview tab.
2. In the Insert group, click Data Table Query. The Data Table Query dialog opens with the Data Options page in view.

   Note: You must be connected to the data server to run a data query.

3. Click Load Query.
4. The Open dialog appears.
5. Select the query file that you wish to load and click Open.
6. The fields of the Data Table Query dialog are populated with the query selections from the selected file.
7. You may modify these selections before running the query.