delenda.net - ephemeris

Wednesday, 2 September 2009

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

Tuesday, 1 September 2009

Eric Playedone

Usenet è ancora un posto interessante dove scoprire cose nuove oppure divertirsi in modo (relativamente) intelligente con l'inaspettato. Sul gruppo chitarristico della gerarchia it.* ieri c'è stato un thread liutistico-linguistico molto singolare. Lo spunto è stato fornito da questo video su Youtube:

Ovviamente si tratta di Jimmy Page, che viene intervistato per qualche minuto riguardo alle sue chitarre, in particolare la sua Les Paul. Comprendere bene quanto detto, anche per l'argomento piuttosto specialistico trattato non è proprio trivial ma ho voluto spenderci qualche minuto provando a fare una trascrizione. Ho capito fischi per fiaschi in un punto un po' biascicato, poco dopo però non sono riuscito ad afferare il nome e cognome di un certo chitarrista, evidentemente a me sconosciuto, mi sembrava che Page dicesse Harry o Erik Princes o Pleeces o qualcosa del genere. Ho provato anche a 'riavvolgere' il video più volte, ma niente di niente. Era il tizio della foto sopra, che negli anni '60 suonava soprattutto Gibson, poi si spostò più su chitarre di altro brand. La cosa divertente è che - giuro - meno di 72 ore fa ho strimpellato un pezzo preso da Total Guitar di Nov. 08 con Mr. Playedone on the cover. Metto la trascrizione corretta dal bravo 'ddguitar' qui sotto. La morale di tutto questo ? Devo esercitarmi di più con le ..scale.

I've noticed that the machines the tuners are Grovers, the seal Grovers, that wouldn't make them the original ones

JP Ah, no.. um i got this from Joe Walsh He insisted that I bought it and he was right

And it has been refinished and probably the reason he wanted to sell it is that it didn't feel the same to him when he got it back and my feeling is that it is a little hazy a long while ago but the sheer fact that all these holes haven't been filled in I've got a feeling that I would have changed

the machine heads the perception one so the Grover which I was more familiar with my lesp custom and they're more sensitive boy it fell down from my les paul days so here we go

So it was for tuning issues

JP Purely for that yeah You know three pieces like in Led Zeppelin you couldn't havea a slipping machine head or something

yeah i feel the same.. the only thing I noticed immediately is this.. push pull knob

JP right

ahem that way

JP yeah

and was that something that you had done

JP well I customized my number two lespaul which I gained as a real old vintage one too however that gave any combination of one or two but it's a little bit fuzzy because all of these pushpull and switches here but the reality of it was the thing that I found mooost important to me was the fact that you could reverse the phase on it reversing the phase you get you

get a closer approximation of the sound that peter green would get

oh yeah

JP and certainly BB King and when some of these came out with the pick ups that were out of phase and you know accidentally I don't know I set it on both of them and here we go

do you use specifically for the big sound or

JP oh yeah yeah I use em quite a bit it's just as simple as that you pull it out and you get them reversed pop it back in you get the full whack and everything

and this guitar got also a very very slim neck and probably people speculate as to whether it came that way or you had it done it is something that you had

JP I didn't have it done this guitar is sort of been it's one of these guitars was made to come my way because as I said Joe Walsh insisted that I bought it and he was right It came as it was the only thing that changed was that really in essence the tuning heads it already came with this scalloped neck

I've got to say that I inherited THIS OTHER guitar from Jeff Beck which was the

yardbirds guitar which was another BRAND (fender),I MIGHT ADD but the reality of

it is that it came to me and that had a very shallow neck as well it is as that came to me that way this came to me as well it always was so we need to go back in the day when ERIC PLAYED ONE, FOR INSTANCE it was always as knowna as a really user friendly guitar, THE LES PAUL.

a slow neck probably suits your style nicely then

JP well i played it then that's what it is

interesting so this guitar is essentialy is irrepleacable to you

JP oh absolutely

that's

JP it's my mistress my wife and the great thing is that it doesn't put you

out for alimony

hope it's not leaving

JP no it's not leaving

we wouldn't be very proud

JP it's gonna be having some son and daughters and the paternity ..ha ha