Querying data

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.

For information on analyzing data queries, see “Analyzing data query results”.

In this section, you can find information on the following topics:

• “Understanding data query output”
• “Querying base data”
• “Specifying format options for data queries”
• “Running a data query”
• “Rerunning a data query”
• “Refreshing data queries”
• “Saving and loading data query selections”

Understanding data query output

You can use the Longview Add-In for Office to query data from the data server repository and place it in a Microsoft Excel workbook. The results of a data query are divided into several main sections (displayed depending on the Format Options specified for the data query) and appear in the following layout:

Data query results in the Longview Add-In for Office are divided into the following main sections:

• The Query Information section
• The Query Title section
• The Query Data section
• The worksheet dimension symbol names (worksheet tabs)

Each section is separated from the others by a blank row. If none of the elements for the Query Information and Query Title sections display in the data query results, the Query Data section appears at the top left cell of the query results.

Note: If you manually modify any character string or data value in the data query results, or if you insert or delete a row or column within the range of the query results, you will not be able to rerun, reorient, analyze, or drill into that data query.

The Query Information section includes the following elements:

The Query Title section includes the following elements:

The Query Data section includes the following elements:

If duplicate symbols are specified in the Worksheet dimension (for example, when a data query is rerun), a new worksheet is created for each duplicate symbol. In each new worksheet, the symbol name is appended with (N), where N is a unique number corresponding to the number of duplicates created.

For example, if two new worksheets are created (for two duplicate symbols), the symbol names in the worksheet tabs appear as follows:

• SymName
• SymName (2)
• SymName (3)

Querying base data

You can use the Longview Add-In for Office to query data from the data server repository and place it in a Microsoft Excel workbook.

When you run a data query, you can specify whether to output Values or Longview Formulas in the data query results. You should select an output type depending on how you plan to use the data query results:

• Select one of the Values options for the As field if you do not plan to customize the data query results.
• Select one of the Formulas options for the As field if you want to customize the data query results (for example, by adding commentary, or an additional column, or by inserting Microsoft Excel functions) and you want to maintain your customizations when you refresh the data values in the query results. To refresh data values in customized data query results, use the Refresh Worksheet or Refresh Workbook buttons in the ribbon. For more information, see “Refreshing data queries”.

  Note: Some changes to data query results will prevent you from rerunning the data query or reorienting and drilling into the query results. For more information, see “Formatting query results”.

To query data:

1. Click the Longview tab.
2. In the Insert group, click Data Query. The Data 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. Complete the following fields:

   Field	Description
   Show	Specify the type of hierarchy data to display in the data query results using one of the following options from the drop-down list: All Data—To display all types of data. Leaf Data—To display data for leaf cells only. CTA Data—To display Cumulative Translation Adjustment (CTA) data only. This option displays data from CTA symbols containing data on the net gain or loss resulting from the data translation of another currency. Note: If you specify Leaf Data or CTA Data, you must specify either Values, Adjusted or Values, Unadjusted for As. The default value for this field is All Data.
   qwet…	Specify whether Longview Formulas are output and whether data that has been adjusted by journal entries displays in the data query results, using one of the following options from the drop-down list: Values, Adjusted—To output data values only, and display data that has been adjusted by journal entries. The time stamp for the query is output as a hard-coded string value. Values, Unadjusted—To output data values only, and display data that has not been adjusted by journal entries. The time stamp for the query is output as a hard-coded string value. Formulas, Adjusted, with Cell References—To create an LVCELL function for each data intersection in the query results, an LVDESC function for each symbol description in the Row, Column, and Fixed dimensions, and an LVREFRESHDATETIME function for the time stamp. Symbol names in each of the LVCELL and LVDESC functions are references to the cells that contain the required values for the data intersection. Note that the cell references also include absolute cell references (for example, $A$1), where the address of the cell remains the same, regardless of the position of the cell that contains the function. For more information, see “LVCELL”, “LVDESC, and “LVREFRESHDATETIME”. Data that has been adjusted by journal entries displays in the data query results. Note: If As is set to Formulas, Adjusted, with Cell References, and Show Fixed Symbols is set to Do not show in the Format Options page, cell references are output for symbols in the Row and Column dimensions only. All other symbol names in each of the LVCELL and LVDESC functions are hard coded as the required values for the data intersection. For more information on Format Options, see “Specifying format options for data queries”. Formulas, Adjusted, without Cell References—To create an LVCELL function for each data intersection in the query results, an LVDESC function for each symbol description in the Row, Column, and Fixed dimensions, and an LVREFRESHDATETIME function for the time stamp. Symbol names in each of the LVCELL and LVDESC functions are hard coded as the required values for the data intersection. For more information, see “LVCELL”, “LVDESC, and “LVREFRESHDATETIME”. Data that has been adjusted by journal entries displays in the data query results. The default value for this field is Values, Adjusted.
   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.

4. Specify the active dimensions for your query:

   Dimension	Description
   Row	Data for the specified symbol or symbols in the Row dimension displays in rows on the left side of the worksheet.
   Column	Data for the specified symbol or symbols in the Column dimension displays in columns across the top of the worksheet.
   Worksheet	Data for the specified symbol or symbols in the Worksheet dimension displays in separate worksheets, one for each symbol.

5. For each dimension in your system, specify the following symbol specifications:

   Note: Certain combinations of Symbols, Spec, and Level are restricted because they will not return a valid set of results. If you attempt to use one of these combinations, either the combination will be restricted (for example, if you specify Leaf for Spec, the Level field is unavailable), or you will receive an error message.

   Specification	Description
   Symbols	Type a symbol name or click the symbol selector button to search the hierarchy for the selected dimension. For more information, see “Using the Symbol Selector”. If you specify more than one Root symbol for the Row, Column, or Worksheet dimensions, you cannot change the orientation of that dimension to a fixed dimension unless you delete the additional symbols. Note: Default values for the data query symbols for each dimension are set by the UGPDDimNQueryDefault user attributes, where DimN is an integer corresponding to the order of the dimension as it appears in the Longview data server repository. For more information on attributes, see the Longview Application Administrator Guide.
   Spec	This field is unavailable for symbols that are not in the Row, Column, or Worksheet dimensions. Specify the type of symbol to query, using one of the following options from the drop-down list: All—To query all symbol types. Leaf—To query only leaf symbols. Parent—To query only parent symbols. Root and Parent—To query only root and parent symbols. The default value for this field is All.
   Level	This field is unavailable for symbols that are not in the Row, Column, or Worksheet dimensions. Type the number of levels of descendants of the selected symbol to appear in the results. You can select up to 99 levels of symbol detail. If you type 0, only the symbol you selected will appear. To include the selected symbol and one level of symbol detail below it, type 1. If you select 99 levels of symbol detail, all levels of symbol detail are included in the results.

   Note: Large queries take longer to complete than smaller queries. Be as specific as possible with the data intersection you are querying.

6. For the dimensions you specified as the Row, Column, and Worksheet dimensions in step 5, you can specify additional symbols to query. To add additional symbols to query for a dimension, complete the following steps:
   1. Select the applicable dimension row.
   2. Click Symbol. A new row appears.
   3. For the new symbol, specify the symbol specifications, as described in step 5.
   4. Repeat step a to step c for each additional symbol that you want to include in your data query.

   Note: To delete additional symbols that you have added, select the row containing the symbol and click Delete. You can also change the order in which the symbols will appear in the query results by selecting the row containing the symbol and clicking Move Up or Move Down.

7. Do one of the following:
   • To specify the format options for your data query, proceed to “Specifying format options for data queries”.
   • To run the data query with the default format options, proceed to “Running a data query”.

   Note: You may also quickly populate the Data Query dialog with query selections that you have previously saved by using the Load Query button. For more information, see ”Saving and loading data query selections”.

Specifying format options for data queries

Before you run a data query, 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 query output”.

To specify format options:

1. Click Format Options in the left pane of the Data Query dialog. The Format Options page opens.
   
2. In the General section, complete the following fields:

Field	Description
Title Text	Specify the text for the data query title. If you leave this field blank, no title row is included in the data query results.
Subtitle Text	Specify the text for the data query subtitle. If you leave this field blank, no subtitle row is included in the data query results.
Show Fixed Symbols	Specify whether the Fixed dimension symbols display in the data query results, using one of the following options from the drop-down list: Note: The functionality for this option includes the Worksheet dimension symbol that is included in the Query Information section. The Worksheet dimension symbols in the worksheet tabs are unaffected. Do not show—Fixed dimension symbols do not display in the data query results. Note: If Show Fixed Symbols is set to Do not show, and the data query is set up to output Longview Formulas with cell references, cell references are output for symbols in the Row and Column dimensions only. All other symbol names in each of the LVCELL and LVDESC functions are hard coded as the required values for the data intersection. Top—Fixed dimension symbols display in the Query Information section above the data query results. The default value for this field is Do not show.
Show Time Stamp	Specify whether the time stamp displays in the data query results, using one of the following options from the drop-down list: Do not show—The time stamp does not display in the data query results. Top—The time stamp displays in the Query Information section above the data query results. The default value for this field is Do not show.
Show Symbol Descriptions	Select this field to show symbol descriptions for symbols in all dimensions in the data query results. Clear this field to show only symbol names for symbols in all dimensions in the data query results. This field is selected by default.

3. In the Row Headers section, complete the following fields:

   Field	Description
   Symbol Name Column Width	Specify how the width of the Symbol Name column (the column containing the Row dimension symbol names) is determined, using one of the following options from the drop-down list: AutoFit—To automatically fit the width for the Symbol Name column to the longest symbol name. This does not include the Worksheet and Fixed dimension symbol names. Specify—To specify a custom width for the Symbol Name column. The default value for this field is AutoFit.
   Specify Symbol Name Column Width	This field is available only if Symbol Name Column Width is set to Specify. Type a number for the width of the Symbol Name column in characters. Type a number from 0 through 255, including decimals. The default value for this field is 22.71.
   Symbol Description Column Width	Specify how the width of the Symbol Description column (the column containing the Row dimension symbol descriptions) is determined, using one of the following options from the drop-down list: AutoFit—To automatically fit the width for the Symbol Description column to the longest symbol description. This does not include the Worksheet and Fixed dimension symbol descriptions. Specify—To specify a custom width for the Symbol Description column. The default value for this field is AutoFit.
   Specify Symbol Description Column Width	This field is available only if Symbol Description Column Width is set to Specify. Type a number for the width of the Symbol Description column in characters. Type a number from 0 through 255, including decimals.

4. In the Column Headers section, complete the following fields:

   Field	Description
   Column Width	Specify how the width of the Data columns (the columns containing the Column dimension symbol names and descriptions) is determined, using one of the following options from the drop-down list: AutoFit—To automatically fit the width for the Data columns to the symbol name, symbol description, or data value, whichever is longer. Specify—To specify a custom width for the Data columns. The default value for this field is Specify.
   Specify Column Width	This field is available only if Column Width is set to Specify. Type a number for the width of the Data columns in characters. Type a number from 0 through 255, including decimals. The default value for this field is 13.57.

5. In the Data section, complete the following fields:

   Note: Custom formatting as specified in the data section overrides the formatting applied by Longview cell styles. For more information, see “Formatting query results”.

   Field	Description
   Apply Custom Formatting	Select this field to apply custom formatting to the data values in the data query 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	This field is available only if Apply Custom Formatting is selected. Select this field to use your regional separator (as set in your system settings) as the thousands separator in the symbol data. For example, if your region is set to United States and your regional separator is a comma, the symbol data appears as follows: “1,000,000”. Clear this field to display the symbol data without thousands separators; for example, “1000000”. This field is selected by default.
   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)
   Exclude Zeros	Specify whether zero data will be suppressed, using one of the following options from the drop-down list: None —No rows or columns are suppressed. Rows — Rows where all the data returned are values of “zero”. Columns — Columns where all the data returned are values of “zero”. Rows and Columns — Rows and Columns where all the data returned are values of “zero”. The default value for this field is None.

6. Proceed to “Running a data query”.

Running a data query

When you are finished specifying the data and format options for the query, you can run the data query.

To run a data query:

1. Do one of the following:
   • If you are querying data in a workbook that does not contain existing data queries, click Run Query. The results of the data query appear in Microsoft Excel, with your specified orientation, symbols, and formats. Proceed to step 3.
   • If you are querying data in a workbook that contains existing data queries, and any symbols that you specified for the Worksheet dimension are duplicated in the Worksheet dimension of the existing data queries, click Run Query, and proceed to the next step.
2. In the dialog that appears, do one of the following:
   • Click Create to create a new worksheet or worksheets for the data query results.
   • Click Reuse to reuse the existing worksheet or worksheets for the new data query results. The existing data query results are overwritten.

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

3. Click the File tab.
4. Click Save.
5. 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 query

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

For information on refreshing queries that contain Formulas, see “Refreshing data queries”.

When you open a workbook containing data 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 queries.

If you create a new worksheet or worksheets for the rerun data query results, and symbols in the Worksheet dimension of the rerun data query are duplicates of the symbols in the Worksheet dimension of the existing data query, symbol names in the worksheet tabs of the rerun data query results are appended with (N), where N is a unique number corresponding to the number of duplicates created.

For example, if two new worksheets are created (for two duplicate symbols), the symbol names in the worksheet tabs appear as follows:

• SymName
• SymName (2)
• SymName (3)

To rerun a data query:

1. Right-click any cell in the results of the data query that you want to rerun and select Rerun Query. The Data Query dialog opens, with the Data Options page displayed.
2. Make any changes to the data query as necessary.

   Note: If you specify more than one symbol for the Row, Column, or Worksheet dimensions, you cannot change the orientation of that dimension to a fixed dimension unless you delete the additional symbols.

If you did not make any changes to the data query, or if any symbols in the Worksheet dimension of the modified data query are duplicates of the symbols in the Worksheet dimension of the existing data query, a dialog appears prompting you to create or reuse existing worksheet(s).

1. Click Create to create a new worksheet or worksheets for the rerun data query results.
2. Click Reuse to reuse the existing worksheet or worksheets for the rerun data query results. The existing data query results are overwritten.

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

Refreshing data queries

When you open a workbook containing a data query, the associated values are 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 can manually refresh your data query.

Refreshing data queries in a worksheet

To see the most current data server repository information for a data query in a worksheet, you must manually refresh that data query. You can also choose to refresh all data query data in the open workbook.

For more information, see “Refreshing data queries in a workbook”.

This functionality is available for data queries only if one of the Formulas options was specified for the As field when you ran the data query. If one of the Values options was specified for the As field, you can refresh the data by rerunning the data query.

For more information, see “Querying base data” and “Rerunning a data query”.

To refresh a data query in a worksheet:

1. Open the worksheet containing the data query to refresh.
2. Click the Longview tab.
3. In the Data group, click Refresh Worksheet. All cells containing query data in the worksheet are refreshed.

Refreshing data queries in a workbook

In Microsoft Excel, a workbook consists of one or more worksheets. Each worksheet appears as a tab at the bottom of the workbook. A Microsoft Excel workbook corresponds to a single .xls or .xlsx file.

To see the most current data server repository information in all workbook cells containing data queries, you must manually refresh those queries. You can also choose to refresh data queries in the active worksheet only.

For more information, see Refreshing data queries in a worksheet.

This functionality is available for data queries only if one of the Formulas options was specified for the As field when you ran the data query. If one of the Values options was specified for the As field, you can refresh the data by rerunning the data query.

For more information, see “Querying base data” and “Rerunning a data query”.

To refresh all data queries in a workbook:

1. Open the workbook containing the data queries to refresh.
2. Click the Longview tab.
3. In the Data group, click the arrow beside Refresh Workbook, and click one of the following options:

   Option	Description
   By Cycling Through All Worksheets	If you select this option, the system refreshes the workbook by making a call to the data server for each worksheet in the workbook. Note: Longview recommends that you use this option if you are refreshing a large amount of data or using a slower network.
   With a Single Database Retrieval	If you select this option, the system refreshes the Database Retrieval workbook by making a single call to the data server for the entire workbook.

   All cells containing data queries in the workbook are refreshed.

Saving and loading data query selections

You may save the query selections that you have selected in the Data Options and Format Options tabs of the Data Query dialog. This will enable you to quickly prepopulate the Data Query dialog in the existing 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 data query selections

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

To save data query selections:

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

Loading data query selections

You may quickly pre-populate the Data Query dialog with selections from a saved query.

To load data query selections:

1. Click the Longview tab.
2. In the Insert group, click Data Query. The Data 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 Query dialog are populated with the query selections from the selected file.
7. You may modify these selections before running the query.