How to show a quarter-to-date balance on an FRx report using the Effective Dates feature

Entry Type: Informational

Product: FRx

Application: FRx Desktop   

Version Reported: 6.7

Subject:

How to create an FRx report to show a quarter-to-date balance

Possible Resolutions:

Two options exist to create a rolling quarter-to-date report in Microsoft FRx. One method uses the effective dates feature to define different columns for each quarter. The second method uses a single column layout with conditional print controls to achieve the same result.

Option 1:
Using the column effective dates:

1. Create a column to use for each quarter:
   1. Q1 column layout design:

      DESC	GL
      	Actual
      	Base
      	1 to Base
      	Cur

   2. Q2 column layout design:

      DESC	GL
      	Actual
      	Base
      	4 to Base
      	Cur

   3. Q3 column layout design:

      DESC	GL
      	Actual
      	Base
      	7 to Base
      	Cur

   4. Q4 column layout design:

      DESC	GL
      	Actual
      	Base
      	10 to Base
      	Cur

2. Create the row format as needed.
3. Create a catalog, and select the row format created in the steps above.
4. In the Catalog window, select the Effective dates check box (in the Column Layout section) to open the Effective Dates window.
5. Select the Use Period Numbers option.
6. In the first row, select the column layout to be used for Q1. In the Date Period field, enter 1
7. In the second row, select the column layout to be used for Q2. In the Date Period field, enter 4
8. Repeat the steps above for quarter 3 and 4 column layouts. In the Date Period field, enter 7 and 10 respectively.

Option 2:
Single column layout:

This option involves maintaining only one column layout. To create a rolling quarter report with only one column layout, create the following column layout:

There are specific design features used in this column layout to enable rolling quarters:

1. • Notice that columns B, C, and D are the columns that retrieve the data for the current quarter, but they have an NP print control to prevent display.

   • Columns E through P calculate the quarter-to-date balance depending on the period that the report is generated for. However, only the correct column displays because of the P = B print control. This control requires that the period the report is generated for matches the Period Code of the column for the column to display.