Tornadic Crystal Migration
By Tim Dexter-Oracle on May 21, 2009
Spent a great couple of days in Oklahoma City last week with the good folks of the Oklahoma Department of transport. When I was asked to go over there a few months back, I agreed on the condition that I got to see a tornado. I came sooo close its not even funny.
I arrived to weather forecasts of huge storms rolling over the city that night, being a weather geek there was just a little excitement brewing. There had been a tornado out in countryside NW of the city earlier on in the day. There were some great cloud formations (apologies, the iPhone camera does not do it justice)
Had some dinner and then decided to take a walk in the sweltering 80 degree heat at about 10pm. Got downstairs just in time to catch one of the best lightening storms I have ever seen, not much hitting the ground but at times it was like daylight with about 5 strikes per second (according to the excited weather dude on the TV) Then came the rain, boy did it rain, after about 10mins there was a river rushing down the road ... all very exciting. Then the tornado sirens started, looked about, everyone else was still watching the storm ... fair enough, no one else is bothered I'll stay out. Rather stupidly, I then rang my wife to tell her about the storm and the sirens and how cool it all was. 'Get back inside, stay away from the windows and get in the bath' was her pleading recommendation. Hmmmm ... not sure about the bath, Im sure the hotel would have us down in the basement if they were that worried, watched as the storm moved over us and then back to the room for some serious weather channel watching. Im convinced Im going to end up in a home when Im older, sitting in front of the weather channel and shouting incoherently at the talking heads. The local channels were more enjoyable anyway, with the weather dudes searching for 'hooks' and 'rotations' on the leading edge of the storm. they all had storm chasers out in the dark around town reporting that they might have just seen a 'wall cloud' and the 'base appeared to be lowering but it was dark so they were not quite sure' All good stuff for a weather geek, took a snap of the TV for posterity, I was under some of that purple you can see.
Thankfully no touch downs in built up areas, a few roofs blown off but no serious damage and no one injured. It was a good night!
Why was I in Oklahoma? To give the DOT folks some experience of BIP, nothing hands on but we dug into the tool and walked around the standalone server. The DOT folks are big Crystal users so we decided to have a go at taking one of their reports and migrating it. There is a pretty nice paper on this on the BIP web site.
Its a completely manual process but we worked through the paper and I added some tweaks here and there to hopefully seed things up a bit and we found some gotchas along the way that I wanted to share.
The report we took a look at seemed pretty innocuous on first sight, I was surprised, I thought they would give me a tough one. I should have been more wary of Corbi's wry smile as she opened the report :0)
There was only a single query in the report which we copied and pasted into a BIP report, created LOVs and parameters. So far so good, the report ran and generated data. We eventually noticed a problem, here's a gotcha for ya. We were getting a repeating element in the output. i.e. NAME. The problem was that in the Crystal Report the query was selecting
Crystal discerns between the two as it keeps the table name associated with the column. Once BIP renders the data it ignores the table name so we get repeated element names. Not a huge problem, but when you load the data into the Template Builder it only shows the first instance on the element. An easy but somewhat annoying work around would be to provide aliases to the columns to make the element names distinct. We made a change to two columns and moved on.
Next the layout, the paper talks about building the layout, I wanted a to take some advantage of the Crystal layout. We needed an RTF output or at least a .doc format that we could re-save as RTF. Crystal offers an export functionality, there are two RTF options, be sure to use the 'editable' RTF option. Once it generates you will get the layout for the layout, its then a case of replacing data with fields using the template builder and you're off to the races.
Here's where things got a little tough, the Crystal report was using a subtle command 'while reading records' to get past a small problem in the schema ... how I got around that, I'll save for tomorrow.