Working with data using Longview Functions

The Longview Add-In for Office includes Longview Functions that allow you to query different types of data into worksheet cells and submit data from worksheet cells to the Longview data server repository.

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

• “Using Longview Functions”
• “Using multiple Longview Functions in a single cell”
• “Refreshing data from cells containing Longview Functions”
• “Submitting data using the Longview Add-In for Office”
• “Converting Longview Functions to static values”

Using Longview Functions

You can use Longview Functions in the Longview Add-In for Office to query different types of data from the data server repository. Longview Functions are useful when you want to query a variety of specific items from the data server repository (data for a specific attribute or symbol, for example), and you want to include titles, additional rows or columns, inserted graphics, or other customizations in your Microsoft Excel worksheet.

Consider the following example:

In the worksheet, LVCELL functions are used to query data for specific symbols, such as Total Revenues. In addition, as visible in the Formula Bar, the LVCELL functions reference other cells in the worksheet (in this case cell C8, which contains the time period). Additional customizations can be made to the worksheet without affecting the validity of the data returned from the data server repository.

For more information on the LVCELL function, see “LVCELL”.

Using multiple Longview Functions in a single cell

When you are working with Longview Functions in the Longview Add-In for Office, you can use certain Longview Functions multiple times in the same worksheet cell. Multiple Longview Functions can be used in the same cell in the following scenarios:

• Multiple Longview Functions that return numeric values can be used in formulas within a cell.

  For example:

  =SUM(LVCELL("Sym_dim1", "Sym_dim2", ...),LVCELL("Sym_dim1", "Sym_dim2", ...))

• Longview Functions that return numeric values can be used in place of function parameters that require values within other Longview Functions.

  For example:

  =LVSUBMIT("Sym_dim1", "Sym_dim2", ...,LVCELL("Sym_dim1", "Sym_dim2", ...))

  where the LVCELL function provides the value to be submitted to the data server repository for the LVSUBMIT function.

• Longview Functions that return symbol names can be used in place of function parameters that require symbol names within other Longview Functions.

  For example:

  =LVCELL("Sym_dim1", "Sym_dim2",LVCHILD("Dim", "Sym_name"), ...)

  where the LVCHILD function provides the symbol name required for the third dimension in the LVCELL function.

  Note: In any of the above examples, you can also use a reference to another cell location that contains the required Longview Function, instead of entering the additional Longview Functions directly in the formula.

The following Longview Functions cannot be used multiple times in the same cell, or used in place of parameters within other Longview Functions:

• LVLOCK
• LVSUBMIT

Using multiple LVCELL functions in a formula

When you are working with LVCELL functions in the Longview Add-In for Office, Longview recommends that you use one LVCELL function per worksheet cell. If you have a formula that requires multiple LVCELL functions, Longview recommends that you place each LVCELL function in a separate worksheet cell, and then use cell references to the cells that contain the LVCELL functions in the formula.

For example, if you are working with an IF formula that requires the following:

IF(LVCELLa<>0, LVCELLb, LVCELLc)

You can place LVCELLa in cell C2, LVCELLb in cell C3, and LVCELLc in cell C4, and use the following syntax for the IF formula:

IF(C2<>0, C3, C4)

Longview recommends that you do not use multiple LVCELL functions within the same cell.

If you use multiple LVCELL functions (for example,

=LVCELL(LVCELL(LVCELL(...))) or =IF(LVCELL(),LVCELL(),LVCELL())

you may need to refresh the worksheet multiple times to view the query data. Other unexpected behavior can occur.

Refreshing data from cells containing Longview Functions

You can refresh Longview Functions to view the most up-to-date data from the data server repository. You should refresh your worksheet or workbook in any of the following scenarios:

• When you open a workbook containing Longview Functions, the associated values are the same as when you last saved and closed the workbook. If you want to see the most up-to-date data from the data server repository, you can refresh the workbook.
• If you have had a worksheet containing function data open for a while, it is possible that another user has made changes in the Longview data server repository in the interim. You can refresh the worksheet to reflect any changes that other users have made to the data.
• When you enter an LVCELL or LVREFRESHDATETIME function into a worksheet cell, you must refresh the worksheet or workbook before you can see the query data in the worksheet cell.

Note: Longview recommends that you use one LVCELL function per work-sheet cell. If you use multiple Longview Functions within the same cell (for example, =LVCELL(LVCELL(LVCELL(...))) or =IF(LVCELL(),LVCELL(),LVCELL()), you may need to refresh the worksheet or workbook multiple times to view the query data.
For more information, see “Using multiple LVCELL functions in a formula”.

Refreshing function data in a worksheet

To see the most current data server repository information in worksheet cells containing Longview Functions, you must manually refresh those functions. You can also choose to refresh all function data in the open workbook.

Note: You can use the following procedure to refresh values in data query results if one of the Formulas options was specified for the As field when you ran the data query. For more information, see “Querying base data”.

To refresh all function data in a worksheet:

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

Refreshing function data 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 Longview Functions, you must manually refresh those functions. You can also choose to refresh function data in the active worksheet only.

Note: You can use the following procedure to refresh values in data query results if one of the Formulas options was specified for the As field when you ran the data query. For more information, see “Querying base data”.

To refresh all function data in a workbook:

1. Open the workbook containing function data 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:

   Field	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 workbook by making a single call to the data server for the entire workbook.

All cells containing functions in the workbook are refreshed.

Submitting data using the Longview Add-In for Office

You can use the Longview Add-In for Office to submit data from worksheet cells to the Longview data server repository. The LVLOCK and LVSUBMIT functions are used as a part of this process.

To apply and release locks in the Longview Add-In for Office, you must have Modify Data authorization. If you do not have Modify Data authorization, the Submit button is unavailable. In addition, to submit data in the Longview Add-In for Office, you must have Submit Data authorization for the Longview Add-In for Office. If you do not have Submit Data authorization, the Submit options (Submit Worksheet, Submit Workbook, or Submit Data) are unavailable. These authorizations are set in Longview Application Administrator.

For more information, see the Longview Application Administrator Guide or contact your System Administrator.

Submitting data using the Longview Add-In for Office consists of the following main steps:

1. Applying locks
2. Submitting data
3. Releasing locks

You can complete all steps of the data submission process at once by clicking the Submit button and selecting one of the Submit Worksheet or Submit Workbook options. For more information, see “Submitting data”.

You can also complete each of the steps manually.

For more information, see “Manually applying locks”, “Manually submitting data”, and “Manually releasing locks”.

Submitting data

You can use the Longview Add-In for Office to apply locks, submit data to the data server repository, and release locks with a single button-click.

When you select one of the Submit Worksheet or Submit Workbook options, the Longview Add-In for Office completes all the following steps, in order:

• Applies a lock as defined by each LVLOCK function in the active worksheet or workbook.
• Submits data for each LVSUBMIT function in the active worksheet or workbook.
• Releases the lock defined by each LVLOCK function in the active worksheet or workbook. Note that any locks applied manually before the Submit Worksheet or Submit Workbook option is selected are not released.

Note: If an error occurs at any point in the process, the system stops at that point and an error message displays. The system will attempt to release any locks that were applied in the first step. For example, if an error occurs when locks are being applied, the system returns an error message and does not proceed to submit data. Any locks that were successfully applied are released.

To submit data:

1. Open the workbook containing the LVLOCK functions that define the sections of the Longview data server repository to be locked and the LVSUBMIT functions that define the data values to be submitted to the Longview data server repository. For more information on the functions, see “LVLOCK” and “LVSUBMIT”.
   • LVLOCK: Ready to lock appears in each cell that contains an LVLOCK function.
   • LVSUBMIT: Ready to submit (Value) appears in each cell that contains an LVSUBMIT function, where Value is the value to be submitted to the data server repository.

   Note: You can use the Data Locks tool to view and (if you have authorization) delete any locks that other users may have applied to the data server repository. For more information, see “Accessing the Longview Add-In for Office”.

2. Click the Longview tab.
3. In the Data group, click Submit, and click one of the following options:

   Option	Description
   Submit Worksheet	If you select this option, the system applies a lock as defined by each LVLOCK function in the active worksheet, submits data for each LVSUBMIT function in the active worksheet, and then releases the lock defined by each LVLOCK function in the active worksheet.
   Submit Workbook	If you select this option, the system applies a lock as defined by each LVLOCK function in the active workbook, submits data for each LVSUBMIT function in the active workbook, and then releases the lock defined by each LVLOCK function in the active workbook.

   LVSUBMIT: Submitted (Value) appears in each cell that contains an LVSUBMIT function for which data was submitted successfully, where Value is the value that was submitted to the data server repository.

   Note: You can use the User Submissions tool to view the status of the batches created for your data submission process. For more information, see “Accessing the Longview Add-In for Office”.

   Manually applying locks

   Before you can manually submit data using the Longview Add-In for Office, you must apply locks for the sections of the data server repository that you plan to submit data to.

   To apply locks manually:

   1. Open the workbook containing the LVLOCK functions that define the sections of the Longview data server repository to be locked and the LVSUBMIT functions that define the data values to be submitted to the Longview data server repository. For more information on the functions, see “LVLOCK” and “LVSUBMIT”.
      • LVLOCK: Ready to lock appears in each cell that contains an LVLOCK function,
      • LVSUBMIT: Ready to submit (Value) appears in each cell that contains an LVSUBMIT function, where Value is the value to be submitted to the data server repository.

      Note: You can use the Data Locks tool to view and (if you have authorization) delete any locks that other users may have applied to the data server repository. For more information, see “Accessing the Longview Add-In for Office”.

   2. Click the Longview tab.
   3. In the Data group, click Submit, point to Manual Submission, and then click one of the following options:

      Option	Description
      Apply Locks (Worksheet)	If you select this option, the system applies a lock as defined by each LVLOCK function in the active worksheet.
      Apply Locks (Workbook)	If you select this option, the system applies a lock as defined by each LVLOCK function in the active workbook.

   4. The Results dialog opens.
   5. Review the information for the number of LVLOCK functions for which a lock was successfully acquired out of the total number of LVLOCK functions in the active worksheet or workbook (depending on the option selected in step 3).

      Note: The total number of LVLOCK functions in the active worksheet or workbook does not include LVLOCK functions that contain invalid parameters, or an invalid number of parameters.

   6. Click OK.
      • LVLOCK: Created lock LockID appears in each cell that contains an LVLOCK function for which a lock was acquired, where LockID is the system-assigned ID of the lock.
   7. Proceed to “Manually submitting data”.

Manually submitting data

After you have applied locks for the sections of the data server repository that you plan to submit data to, you can use the Longview Add-In for Office to manually submit data to the data server.

To submit data manually:

1. Open the workbook containing the LVLOCK functions that define the sections of the Longview data server repository to be locked and the LVSUBMIT functions that define the data values to be submitted to the Longview data server repository. For more information on the functions, see “LVLOCK” and “LVSUBMIT”.
2. Click the Longview tab.
3. If you have not already done so, apply the locks to the data server as described in “Manually applying locks”.
4. In the Data group, click Submit, point to Manual Submission, and then click one of the following options:

   Option	Description
   Submit Data (Worksheet)	If you select this option, the system submits data for each LVSUBMIT function in the active worksheet.
   Submit Data (Workbook)	If you select this option, the system submits data for each LVSUBMIT function in the active workbook.

5. The Results dialog opens.
6. Review the information for the total number of values submitted by LVSUBMIT functions in the active worksheet or workbook (depending on the option selected in step 4).

   Note: The total number of LVSUBMIT functions in the active worksheet or workbook does not include LVSUBMIT functions that contain invalid parameters, or an invalid number of parameters.

7. Click OK.
   • LVSUBMIT: Submitted (Value) appears in each cell that contains an LVSUBMIT function for which data was submitted successfully, where Value is the value that was submitted to the data server repository.

     Note: You can use the User Submissions tool to view the status of the batches created for your data submission process. For more information, see “Accessing the Longview Add-In for Office”.

8. Proceed to “Manually releasing locks”.

Manually releasing locks

After you have manually submitted data to the data server, you can use the Longview Add-In for Office to release the locks on the sections of the data server repository so that other users can work with the data.

To release locks manually:

1. Open the workbook containing the LVLOCK functions that define the sections of the Longview data server repository to be locked and the LVSUBMIT functions that define the data values to be submitted to the Longview data server repository. For more information on the functions, see “LVLOCK” and “LVSUBMIT”.
   • LVLOCK: Created lock LockID appears in each cell that contains an LVLOCK function for which a lock was acquired “Manually applying locks”, where LockID is the system-assigned ID of the lock.
2. Click the Longview tab.
3. In the Data group, click Submit, point to Manual Submission, and then click one of the following options:

   Option	Description
   Release Locks (Worksheet)	If you select this option, the system releases the lock defined by each LVLOCK function in the active worksheet.
   Release Locks (Workbook)	If you select this option, the system releases the lock defined by each LVLOCK function in the active workbook.

4. LVLOCK: Ready to lock appears in each cell that contains an LVLOCK function for which a lock was acquired successfully.

Converting Longview Functions to static values

You can use the Longview Add-In for Office to convert all cells in the active workbook containing Longview Functions to static data values. When you convert cells in the workbook to data values, you effectively remove all Longview Functions, References, and features from the workbook. You do not have to be connected to a Longview data server to convert Longview Functions to static values.

This functionality is useful if you want to provide a copy of the workbook to users who do not have the Longview Add-In for Office installed, or if you want to create a workbook that displays the data values in the Longview data server repository at a specific date and time.

Caution: If you want to continue working with Longview Functions or other Longview Add-In for Office features in the active workbook, make sure that you save the workbook before proceeding. This procedure cannot be undone.

To convert Longview Functions to static values:

1. Click the Longview Tab.
2. In the Data group, click Convert to Values. A confirmation dialog appears.
3. Click Convert. The active workbook now has the following properties:
   • All cells that contained Longview Functions are converted to static data values. This includes any Longview Functions created in data queries. For more information, see “Querying base data”.
   • Any queries in the workbook can no longer be rerun. In addition, data queries can no longer be reoriented or drilled into.
   • Cells that contained any functions other than Longview Functions are unchanged. However, worksheet cells that contained Longview Functions used in conjunction with other functions in the same worksheet cell are also converted to static data values. For example:

     Formula	Converted to...
     =AVERAGE(2,4,8)	Unchanged
     =LVCELL("Sym_dim1", "Sym_dim2", ...)	Static data value
     =AVERAGE(2,4,LVCELL("Sym_dim1", "Sym_dim2", ...))	Static data value
     =AVERAGE(2,4)&LVCELL("Sym_dim1", "Sym_dim2", ...)	Static data value
     =LVDIMNAME(AVERAGE(2,4))	Static data value

4. Save the workbook containing static data values with a new file name.