Using Crystal Reports with variable height bands and grids

Let’s say you have (an already ordered) data structured this way. Year, Month, ID1, ID2 are primary keys, i.e., for one combination of ID1/ID2 and month of a particular year, there is one record of data. Not for EVERY month in one year, as you can see from the example…

Year	Month	ID1	ID2	Description
2009	1	30	11	Foo Bar Baz
2009	2	30	11	Foo Bar Baz Zam
2009	3	30	11	Foo Bar Baz
2009	4	30	11	Foo Bar Baz
2009	5	30	11	Foo Bar
2009	6	30	11	Foo Bar Baz
2009	7	30	11	Foo Bar Baz
2009	8	30	11	Foo Bar Baz
2009	9	30	11	Foo Bar Baz Zam
2009	10	30	11	Foo Bar Baz
2009	11	30	11	Foo Bar
2009	12	30	11	Foo Bar Baz
2009	3	30	12	Foo Bar Baz
2009	4	30	12	Foo Bar Baz
2009	6	30	12	Foo
2009	9	30	12	Foo Bar Baz
2009	12	30	12	Foo Bar Baz
2009	5	30	13	Foo Bar Baz
2009	6	30	13	Foo Bar Baz Zam
2009	7	30	13	Foo Bar Baz

You want to create a report (using Crystal Reports >= X, since you are a masochist) like this.

	Year 2009
	Contract	30 12
	January
	February
	March			Foo Bar Baz
	April			Foo Bar Baz
	May
	June			Foo
	July
	August
	September			Foo Bar Baz
	October
	November
	December			Foo Bar Baz
	Contract	30 13
	January
	February

That is, all months of the year must be listed in a fixed grid, even if there is no record for every mont

Normally I would create a simple report grouped by Year, ID1-ID2. The Report would use at least two Group Headers, one for the year, the other for the ‘Contract’ ID1 ID2. The Detail section would store the actual data, using a formula to translate the month number to the month name, using boxes and line to build a table grid etc.

The problem is the missing months: using this straightforward method only the months with actual data would be rendered, like this…

	Year 2009
	Contract	30 12
	March			Foo Bar Baz
	April			Foo Bar Baz
	June			Foo
	September			Foo Bar Baz
	December			Foo Bar Baz

One solution is to create some form of database view/stored procedure to fill in the missing months with blank records, and generate the report over the new data source. Creating a stored procedure is not trivial and would probably require using a cursor. Let’s say you want to stick to the original data and solve the problem only with CR. There are probably many solutions, but this is what I’ve found after a bit of experimentation. The first step is to create in the Group Header section a blank grid with all the months.

	Year [Year]
	Contract	[ID]
	January
	February
	March
	April
	May
	June
	July
	August
	September
	October
	November
	December

The trick is to set the Underlay Following Sections flag in the properties of this Group Header. With Underlay Following Sections (it’s not present in every CR release, only the recent ones) the sections coming after this are rendered over the first section, overwriting over it. Of course only with this trick the data is printed on the grid... in the wrong position, because of the headway beetwen the ‘Year’ text and the first line of the table, and because not every month is there... You get something like this:

				Foo Bar Baz
	Year 2009			Foo Bar Baz
				Foo
	Contract	30 12		Foo Bar Baz
				Foo Bar Baz
	January
	February
	March
	April
	May
	June
	July
	August
	September
	October

I also need a way to implement this ‘algorithm’

• The first month of data must be rendered adding a blank space over it, with the same height as the first band in the group header, from the top border to the start of the month grid.
• Every month of data, including the first, must rendered adding a blank space taking into account the missing months.

One idea is to use shared variables. I put a formula in the group header, initializing a shared variables (a variable whose value is kept between band renderings) with a value for the ‘last’ month rendered, initially 0, and a flag (again initially 0) to track the rendering of the ‘first’ month.

In the detail section, the one rendering the single month data, another formula should manipulate the band to change, somehow, its height. There is not a property that I know of in CR allowing for direct modification of a band’s height. Bands are automatically resized by CR according to the amount of data contained. One common trick is to use a text object with the ‘Can Grow’ flag set and a formula. If the formula fills the text object with blank characters and if the object is positioned and sized in the right way with the right font, font size etc, it is actually possible to ‘shape’ the detail band heights in order to let them ‘fall’ in the right place when they are rendered ‘over’ the preceding group section (because of the Underlay following sections). To simplify things in my case I created a secondary detail band only with this text object, initially set to a minimum height.

The first formula, to be positioned in the group header is…

shared numbervar lastmonth;

lastmonth :=0;

shared numbervar monthflag;

monthflag :=0;

The second formula, be positioned in the in the ‘can grow’ text object in the detail section is...

shared numbervar lastmonth;

shared numbervar monthflag;

numbervar mp;

numbervar fl;

fl := IIf( ({table.Month} =1) or (({table.Month} >1) and (monthflag = 0)),1,0);

monthflag := 1;

mp := lastmonth ;

lastmonth := {table.Month} ;

mp := ( {table.Month} - mp);

mp := Iif(mp = 1 and fl =0,0,mp);

if (fl =1) then

replicatestring('#', 4 + mp ) //4 is the ‘headway’

else

replicatestring('#', mp )

The second formula is a bit tricky, the two local variables are needed because of the way CR shared variables persists between band rendering. Replicatestring generates a string of ‘#’, using it on a small text object with the can grow flag set, makes it grow in a columnar fashion. The font color is set to white. One could also generate CRLFs of course, but I found that in my case adjusting the heights is easier this way.

That’s it: on a relatively large amount of data this hack doesn’t seem to slow much the creation of report. I suspect that a stored procedure using a cursor would have performed much worse