Tuesday, 29 September 2009
Sul caso Polanski...
Monday, 21 September 2009
How to change the keyboard layout on the login screen in Windows (Can't enter my windows password anymore)
Well, you have to remember to use ALT + LEFT SHIFT to cycle through keyboard layouts. But you have to remember it, if you ever had known that in the first place. What a major user interface blunder. Today it took me three or four minutes to figure this out, but I have access to many computers, but think about a lone translator or tech writer stranded with only his notebook...
Sunday, 20 September 2009
Glissentar
Ieri sono stato a sentire il chitarrista Rocco Zifarelli (in un trio jazz insieme a Francis Lassus e a Linley Marthe, un bassista assolutamente stupefacente).
Notizie che danno la scossa...
Tuesday, 15 September 2009
It's happening in a London rabbinical college...
It's happening today in a famous progressive London rabbinical college, at the beginning of the new semester.
Friday, 11 September 2009
Una normale mattina in treno
Spigolature di cronaca fiorentina, II
dei figli. E tra tutti aleggia un clima di forte sfiducia: oltre il 57 per cento degli italiani ritiene che l'uscita dalla crisi non sarà breve, e che ci vorranno almeno un paio d'anni."
Sunday, 6 September 2009
Era un uomo semplice - Intervista a David Irving a El Mundo di Sabato 5 Settembre
Friday, 4 September 2009
Selling Hitler (again and again)
Wednesday, 2 September 2009
Waga Tomo Hitler
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