Complex Reporting: Part 3 - Structure

Published on 02 August 2008 • Estimated reading time: 1 minutes

So in part two, I showed what was needed. Now, how do we actually build this report?

Well, if your gut tells you to use a table or matrix, you’d quickly hit a problem: the data required in each field isn’t constant. A picture might help explain what I mean. If we look at our previous example:

Our fields would need to look like this:

Notice how F1Q1Value appears in a different place in each row. Try building that in a table or matrix—you just can’t.

Also, consider the requirement for subtotals. Going horizontal is fine in concept, but vertical is tough because you’re adding values from many different fields together.

So how do you go about building this?

Well, the financial year indicators at the top could just be calculated from the value in the dropdown and placed into three text boxes. The next row—the header row for quarters—could also be text boxes or the header row of a table, since it doesn’t change. Then, you’d need a table below that for commodity names and values. But how do you get the field values to dynamically change based on the commodity?

If you go back to part 1, you’ll remember I mentioned sub-reports—but I’ll cover that next time.