How to compare current and prior year numbers using a sub query
Recipients will often interpret numbers on a report by comparing them against the prior year or month. For example, comparing current year and prior year sales to determine if sales have increased or declined.
Using sales and expense data across two years, we would like to create an online report showing current year sales and expenses as well as the prior year amounts for each appended to the right:
Before we get started
This how-to guide is using data as shown in the screenshot above which has been loaded into the following managed table:
If you need help to create a managed table or to import data from text / Excel / MS Access file into the managed table, then please refer to below help articles to create a managed table, and import data into the managed table.
- How to create a managed table
- How to import data from an excel file into managed table
- How to import data from text file into managed table
- How to import data from access file into managed table
Starting in step one of the report creation wizard:
1. Drag the Account Description and Amount data fields into the columns sections – This is what we want the report to show
2. Drag the Reporting Month data fields into the filters section – We want users to be able to select the reporting period
3. Under the report options, display data as “Table Only”, the output will be “Colum Based” and the Analysis will be “No Drill” and include “Sub-Queries” (Important)
Note: Enabling “Sub Queries” is essential for this report. It will allow us to “append” the prior year amounts beside the current year amounts.
4.Click the “+ Sub Query” button in the top right hand corner of the “Columns” section:
Choose “Append” as the type and click “Submit”.
5.Ensure that the Append tab is selected and add the same columns and filters as per step 1 and 2:
What we have done is in-effect, created two reports of the same structure side-by-side, one that will be used for the current year, the other for the prior year (we will use the filters to do this).
The appended report will be used to show the prior year’s amounts. In order to do this, we must ensure that the master and appended reports “talk” to one another by joining the two on a matching field – the account description.
6.Click the “Refresh” button in the “Joins” section found in the “Append Sub Query Properties” section:
This will refresh the drop-down list shown above with column values in both the Master and Sub Query.
7.Configure the join to be where the Account Description field in the master report is equal to the Account Description field in the sub query report and click “Add Join”:
8.Click on “2” to move on to the next step of the report wizard. We will now configure the filter for this report:
We would like a user to select the reporting period for the report. The Current Year will show the selected reporting period whilst the prior year (append sub query) will show the reporting period minus one year.
9.The “Master Query” tab will include the Reporting Month filter as a “User Input” by default. Select the “Append” tab and link the reporting month filter in this tab to the “Master Query” tab as follows:
The Reporting Month filter for the appended column will be equal to the reporting month for the master column (input by the user) less one year.
10.Click on “3” to load the report then select the latest reporting period from the filter list and click on the “>” button:
Note: In the screenshot above, the filter input format has been changed to a drop-down list and the format of the table such as the column headings and row shading have been updated.