How to link to an external spreadsheet in FRx

Entry Type: Informational

Product: n/a

Application: FRx

Version Reported: All

Subject:
How to link to an external spreadsheet in FRx

Possible Resolution:
Within FRx, there are two methods for "linking" to worksheets:

A Combined Method which is a single link to both General Ledger and the spreadsheet within the row format; or a Separate Method which is single or multiple links in the row, requiring the use of a reporting tree.

COMBINED METHOD	SEPARATE METHOD
ROW	TREE
Uses a GL+Worksheet Link	Uses an External Worksheet Link
Path to Worksheet in Row Format	Path to Worksheet(s) in Reporting Tree
Link only to one spreadsheet	Link to multiple spreadsheets
Commonly used to fill rows of data	Commonly used to fill columns of data
Syntax=@WKS before cell reference	Syntax=cell reference only
No Reporting Tree required	Reporting Tree required
Steps:	Steps:
Create the Building Blocks.	Create the Building Blocks.
In the Row Format, Open the Link  Window.	In the Row Format, Open the Link Window.
Click on the "Link Type" drop-down.	Click on the "Link Type" drop-down.
Change to GL+Worksheet link	Click on New, to add additional link columns.
Select a path to the appropriate worksheet in the "Worksheet File Name".	Change the link type to External Worksheet.
Click on Save and Close.	Complete the optional description field to help you choose the correct link from the tree.
In this Combined Link, type in the appropriate cell reference, using the @WKS syntax.	Click on Save and Close after adding the number of links desired, one for each unique set of cell references.
Use /CPO or /RPO after the cell references, if desired.	Type the appropriate cell references in each link to Worksheet column.  Do not use @WKS.
	Use  /CPO or /RPO after the cell references, if desired.
	In the Column Layout, choose the column Type WKS.
	In the Reporting Tree, column I, choose the appropriate Row Format.  You must enter a Row Format for each unit of the tree.
	In the Reporting Tree, column T, click on the drop-down box and complete the path to the appropriate spreadsheet.
	In the Reporting Tree, column W, click on the drop-down box and choose the link with the proper cell reference for each unit in the tree.
	In the Catalog, check the box "Use Row Format(s) and Worksheet Links from Reporting Tree".

Optional Row Modifiers /CPO and /RPO

There is an option of hard coding the cell reference within the Row Format, or alternatively using a row modifier that will allow FRx to "count" across columns or down rows of the worksheet.

To count across, use /CPO to pull the proper cell of data into the finished report.  Always start on Column to the left of period 1.

To count down, use /RPO, as with /CPO, the Base Period (Report Date) determines how many rows drown from the starting point.  The starting point, or anchor, will be one row above period 1.

Examples:

In a Combined Link:

@WKS B=A1 /CPO -- this would read the cell A2 as period 1, A3 as period 2, etc.

In a Separate Link with a single WKS column:

A1/CPO

In a Separate Link with  multiple WKS columns:

(B=A1, C=A2)/CPO

In this scenario, both B and C are WKS columns and the row determines which spreadsheet data applies to which columns.

Important Reminder when Using Links:

• Combined Links (GL+Worksheet) may link to a single workstation (or tab in a workbook).
• When linking to tabs in a workbook, the field name must be enclosed in square brackets, with the tab name following; i.e. [workbook.xls]tab name.
• Spreadsheet data may only be placed in GL or WKS Type columns.  If data needs to be presented in a CALC or other Type column, then use a row CAL to move the data to the appropriate column.
• For Separate Links be certain to check the "Use Row Format(s) and Worksheet Links from Reporting Tree" box on the building blocks tab of the Catalog.