Complex Reporting: Part 3 - Structure

So in part two I showed what was needed, so now how do we actually build this report. Well if your gut said to use a table or matrix then you would quickly hit a problem because what data needs to be shown in each field is not constant. A picture may help explain what I mean. If we look at our previous example:

 

 

 

Our fields would have to look like this:

 

 

So note how F1Q1Value appears in a different place in each row, try building that in a table or  matrix. You just can’t! Also now think about the requirement for sub totals, going horizontal is ok in concept but going vertical is tough because you are adding the values of a lot of 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 from the drop down and put into three text boxes. The next row, which is the header row for quarters could also be text boxes or the header row of a table since it doesn’t change, then we would need a table below that for the commodity names and values, but how do we get the values of the field to dynamically change based on the commodity? If you go back to part 1, you’ll remember I mentioned sub-reports, but I will get to that next time.