Complex Subsummary Reporting

[jsword]

Hello All!

 

I am having trouble building a complex subsummary report efficiently for a real estate database. The report layout is simple: I basically have a layout with 18 columns, all years, 2000-2017. The layout only has subsummary parts, no body parts. The subsummary part I am using to display data is sorted by a field called Micromarket (basically a geographical area). I want to display the number of units built in each year for each Micromarket. I have the report working perfectly, but I had to add many fields to my property table to accomplish this. The fields are as follows:

 

Field Name: Number_of_Units_2000: Calculation: If ( (Year_Built)=2000;Number_of_Units ) and a corresponding Summary field that is the "Total Of" Number_of_Units_2000.

 

I have done this for each year on the report, so I have made 18 Calculation fields, and 18 corresponding Summary fields. I then put each Summary field in its corresponding column on the layout, and for each Micromarket, I am able to display the number of units added in a given year.

 

Is there a more efficient way to do this? I have tried performing a different find for each year within a script, then populating a Merge Variable with the result, but the Merge Variable doesn't behave the same way on the layout as a Summary field does.

 

Any help would be appreciated!

[comment]

The "proper" way to do this is vertically, i.e. show 18 rows for each area, instead of 18 columns.

 

To force a horizontal display (i.e. columns) in a  more efficient way that what you have now, you could use a single repeating calculation field (result is Number) =

Case (
Extend ( Year_Built ) = 1999 + Get (CalculationRepetitionNumber) ;
Extend ( Number_of_Units )
)

Set the number of repetitions to 18, then define a summary field to summarize the above calculation field and set it to summarize repetitions individually.

[jsword]

Great, thank you for the help! I will try to use this now and let you know how I fare.