Linking External Worksheets In FRx

FRx provides an extend functionality to your financial reporting with linking Microsoft Excel or Lotus 1-2-3 files for reporting. Using linking option in FRx You can import data from one of these programs and combine the same with your data from general ledger.
For example, you can import budget figures from a worksheet and then create a report that compares actual figures from the general ledger to the budget figures in worksheet. The other example would be linking the unit sold information from the excel to FRx and doing some analysis for unit sold with Gross profit.

Method of Linking External Worksheet

1-    Combined Worksheet & 

2-    Separate worksheet link.

1-   Combined Worksheet:

Using the combined method data can be referred in FRx using simple cell references in the same link column as your general ledger account codes. It is also the easiest method of linking External worksheet.
By its Name itself, using this method means some rows in a given column of your report contain general ledger information while other rows in that same column contain worksheet data. Combined method is preferred when there is a requirement of importing limited data (such as headcount, units sold).
Step 1 – Link The Row With External Worksheet:

- Open row format In FRx Report Designer

- Select Open Link menu or click the Link Form icon in the toolbar to display the Links dialog box.

- In the Link Type box, change General Ledger to GL + Worksheet.

- Accept the FRx Report Designer default value in the Link Name box, or if you would like to use your own link naming system, type a new name. 

- In the Worksheet File Name box, click the arrow.  

- In the Worksheet File dialog box, type or select the worksheet to use and click Open.  

Note:

If you type a file without a full path, FRx Report Designer uses the path defined for the default IO_Data directory in the Company Information(FRx >> Company >> Information >> Import & Export Path) dialog box. The file name appears by default in the Link to GL + Worksheet header description.

- In the Optional Description box, you can type a description of the link for the reference.

-  Click Save.

- Click Close.

Step 2 – Reference a Worksheet Cells in FRx Row Linking:

Here is the sample excel sheet which was linked in above step.

- In the row format, Type the following code in the link to General Ledger + WKS column ( H)

@WKS ( B=B5)

@WKS = A predefined code of FRx which instruct the row to look into External worksheet for data.

B= Column Layout Reference.

B5= Cell reference of External worksheet.

- Save the row and column layout.

- Create a report catalog with the newly created row & column layout.

Step 3 – Generate the Report:

- Here is the resultant report by GL & External worksheet linking.

 

2-   Separate Worksheet Method:

You can use a separate worksheet link method to access multiple rows and columns of worksheet data and associate one or more external worksheets with one or more reporting units in the reporting tree. You create a special column in the row format that links to the worksheet cell addresses, and then add the worksheet file names to the reporting tree.

Note: Linking to spreadsheets that have identical layouts reduces the number of link columns required in the row format and reduces maintenance time.

Step 1– Link the row with external Worksheet  or many external sheets.

- Open row format

- Select Open Link menu or click the Link Form icon in the toolbar to display the Links dialog box.

- In the Link Type window, Click New Button and then change link type to Worksheet.

- Worksheet path will be disabled, it will be defined in Reporting Tree and details will be referred in column layout in next steps.

- Click Save.

- Click Close.

Step 2 – Reference a Worksheet Cells in FRx Row Linking:

- Here is the sample excel sheet which we will be linking

- Type the following in external worksheet link column ( i), which is worksheet column

B5   ( B5= Cell reference of External worksheet)

Step 3 – Setup Column Layout For Worksheet linking

- Open exiting or setup a new column layout.

- Add a new column with Type “WKS”, apart from WKS column you can also add additional Column for any calculation such as Variance calculation with Actual Vs Budget.

Step 4 – Setup Reporting Tree

- Open reporting Tree setup window to setup a new Reporting Tree.

- Select the company code, Type the unit code & Description.

- Click on Tree Menu and select tree form( FRx >> Reporting Tree >> Tree >> View Form )

Select the followings.

Row Format Name    = Row format which we have setup in step 1 above.

GL Row Link             = Name Of GL Link column in the row format to pull GL values.

Wks Link                 = Worksheet Link Column name from The row format

Wks File Name         = Location of External worksheet

Note:

If you type a file without a full path, FRx Report Designer uses the path defined for the default IO_Data directory in the Company Information(FRx >> Company >> Information >> Import & Export Path) dialog box. The file name appears by default in the Link to GL + Worksheet header description.

- Save the reporting tree.

Step 5 – Generate the Report:

- Setup a new report catalog with the Row, Column layout and reporting tree we have created above.

- Mark the check box, under Building Block Row format “Use Row Format and worksheet links information From Reporting Tree”

- Generate the report, Here is the resultant report by GL & External worksheet linking.

I Hope this post is helpful.