Linking sheets

Linking sheets in the OSR Report Designer cannot be done in the same manner as in a regular workbook. To link sheets in the Report Designer, formulas are required because of the complexities of expanding groups and references between worksheets. This section will explain the steps for setting up a simple linked sheet. The concept can be applied to design more complex reports.

Link sheets

In this example, two identical sheets will be created, but the second sheet will reference cells from the first sheet.

1. Create a simple report with these items:

   • From the General Ledger folder, insert department, account, description (under the Account dimension), and amount into cells B8 through E8.
   • Drag the Period dimension into cell E6
   • Drag the Year to date function from the Period Functions module into cell E3.

   The report should look like the figure below shown below.

   NOTE: Make sure that a group is created on row 8 and column E.

   

2. Right-click the worksheet and select OneStop Reporting > Copy.

3. Create a new worksheet.

4. Right-click on the new sheet tab and select OneStop Reporting > Paste as new.

   

   Now, you must create reference names on the first sheet.

5. Access the first sheet (Sheet 1) and highlight cells E6 and F6. In the name box, rename this set of cells Period2.

   

   **NOTE:**The reason for selecting an extra cell to the right is because the group in column E contains an expanding group that will result in an unknown number of columns. Selecting the extra cell ensures that all columns created by the expansion are included in the reference. The same would apply if an expanding group existed in the row group. However, for an expanding group on a row, the extra cell selected should be one cell below.

6. Access the copied version of the report in Sheet1(1). Delete the contents of cell E6 and link the period from the original sheet by entering the formula =INDIRECT(“Sheet1!Period2”).

You can now execute the report and the sheets will look identical because the Period dimension in the second sheet is referencing the Period dimension in the original sheet.