Get Months Rolling with the Financial Trend Analysis Report in Sage 300c – Part 3

If you need to brush up on some of the concepts covered in this post, please see past Ledger Lines articles for Using Dynamic Statement Titles Part 1 and Part 2 as well as a post on Using Helper Rows. Let’s get started with the rolling 12-month report. For our report to work, it needs to know the starting period for the […]


If you need to brush up on some of the concepts covered in this post, please see past Ledger Lines articles for Using Dynamic Statement Titles Part 1 and Part 2 as well as a post on Using Helper Rows.

Let’s get started with the rolling 12-month report.

For our report to work, it needs to know the starting period for the rolling 12 months.  I found a spot just above the GL account selection to request this information.

Next, we need to think about how we can make this dynamic.  To do this, we need to know what part of the existing formulas are static.  Let’s have a look at the first data field in the grid for January of the current year.

We can see that since the column headings never change, the field name for the data can be hardcoded.  If you feel energetic, you can try to chase down the rest of the formula to see what it’s doing, but the field name is the only part of the formula that we need for this exercise. 

Now that we know what we want to replace, we need to figure out how to replace it.  To do this, I inserted several helper rows to pre-calculate the values that I needed (see our website for “Using Helper Rows” for more information on using helper rows).  I implemented 3 new rows to build what I need.  (There was a previous hidden row, I just left it alone).

Let’s breakdown why each of these rows are needed.

Row 15 is used to calculate the Period for the column.  The first value in column D simply references our Starting Period field that we previously created.  To determine the previous period and handle the wrap from period one to period 12, use the formula below and copy it across.

Remember, the original report hard coded the field name, we need to dynamically build it.  There are four things that we know about the field names;

  1. All the fields end in a 2-digit period
  2. The field names that we want, start with one of “ACTUAL”, “LASTYR” or “LAST2YR”
  3. The first field of the Current row will always start with “ACTUAL”
  4. The first row of the Prior row will always start with “LASTYR”

Let’s start with D16.  We know it starts with “ACTUAL” and we need to add the 2-digit value for the period.  We can accomplish this using “&” to concatenate values and the TEXT function to structure our period to 2-digits. See the formula below.

That was easy!  Now we need to make the values to the right dynamic (see our website for “Using Dynamic Statement Titles” for more information on creating dynamic values).  We can use the same logic that we used in our formula to identify the period number and incorporate it into our concatenate formula so it looks like this;

Do the same thing for row 17 but start with “LASTYR” and switch to “LAST2YR”

You’ll want to change your Month titles to make them dynamic as well.  The existing formula hard coded the period as it would never change.  Just change the period to draw from our helper field.

Now that we have all our helper rows finished, we can change the main formula that grabs the values for the GL account that we select.  We need to drop our newly calculated field name into the existing formula.  There is one snag that we must work around when we concatenate the sheet name to the field; which we can get around using the INDIRECT function (nothing a Google search can’t find!).

Once you set you proper anchors, you can copy this formula all the way across and down to the next row!

Give it a try and change the starting period to see your rolling 12 – month report in action!  Once you’re done be sure to hide your helper rows before saving the template.  Congratulations, this one was more of an intermediate solution, so you are on your way to becoming a Sage Intelligence Reporting expert!

Up Next