02.05 Layout - Transactional Reports

02.05 Layout - Transactional Reports

Overview

In 02.03 Layout - Pivoting Across we saw that many reports, particularly in GL, have more than one dimension in the Across Axis, such as Year, Period and Measures. Later in the course we will build on this with hierarchies, calculations and planning.

But a great deal of day-to-day reporting is much simpler in shape. Users want to see the transactions themselves: which invoices were raised, which orders are open, what moved in and out of stock, and so on. We call these Transactional Reports.

Everything covered so far in this Layout section applies to them. Transactional reports are simply the simplest case of the same model.

Here is a video, or read below - don't forget the exercises at the bottom!

[Video placeholder] - New module so not yet filmed

The Shape of a Transactional Report

In a transactional report:

So each row of the report is (usually) a single transaction or transaction line, and each column to the right holds one of its values.

[Screenshot: A simple AR Invoice Lines report with the Down Axis and Across Axis highlighted]

You will find this shape in many areas of FastClose, including:

Step 1 - Choose the Right Starting Point

The single most important decision when building a transactional report is choosing the right Template to start from (via File > Home).

Templates differ in two key ways:

Not sure where to start? Ask the FastClose chatbot. Describe what you want to report on, for example "open sales order lines by customer and part", and it can often point you straight to the right template or solution.

Step 2 - Add and Remove Dimensions and Attributes

Templates often include more columns than you need, or fewer, or not quite the right ones. Adjust the Down Axis just as described in 02.01 Layout - Adding / Removing Columns:

Similarly, add or remove Measures by amending the Measures filter.

Dimensions and Attributes

As you browse the Field Explorer, you will notice that as well as dimensions there are Attributes: descriptive information hanging off a dimension, such as a Customer's Name, Territory or Credit Limit, or a Part's Description and Product Group. (We will look at the terminology more formally later, in 03.01 Fundamentals - Glossary of Terms.)

Attributes are extremely useful in transactional reports. For example, an AR Invoice Lines report showing Customer ID alone is hard to read, but adding the Customer Name attribute alongside makes it immediately useful.

Less Is Often More

Remember that every row in a transactional report is defined by the combination of everything in the Down Axis. This works in your favour when you want to summarise.

Removing the Invoice Number and Line columns from an AR Invoice Lines report, for example, leaves you with one row per Customer and Part, with all the invoice values added up automatically. In other words, removing detail columns is the quickest way to turn a detailed transactional listing into a summary report.

[Screenshot: before and after - invoice line detail vs summary by Customer and Part]

Step 3 - Add Subtotals

Transactional reports are often much easier to read with subtotals, for example a subtotal for each Customer on an AR Invoice Lines report, or for each Account on a GL Transactions report.

Subtotals are covered in more detail later, in 04.02 Subtotals & Grand Totals, but they are quick to add. Switch to the Insert tab and select Totals & Subtotals. Tick Grand Total for a total at the bottom, and select the dimensions on which a subtotal row should be injected each time the value changes.

Tip: Subtotals are injected each time the selected value changes as you read down the page, so make sure the report is sorted by the dimension you are subtotalling on, and that it sits to the left of the more detailed columns.

image

Step 4 - Filter Sensibly

Transactional data volumes can be large. A GL Transactions report with no filters could return every posting your business has ever made! So always filter to a sensible scope, typically:

The filtering techniques covered in 02.02 Layout - Filters, such as ranges, wildcards and inverted filters, all apply here.

Step 5 - Drill Through

Finally, transactional reports are often where users end up after drilling through from a summary report, for example from an account balance on a Trial Balance down to the GL postings that make it up, as we saw in 01 Getting Started - Templates & Drill Through.

Summary

Exercises

Now try the downloadable exercises below:

02.05 Layout - Transactional Reports - Exercises.pdf

Your feedback helps us do better, to fill in a short survey on this tutorial, click [here - survey link placeholder].

Powered By