The Old Joel on Software Forum: Part 4 (of 5)

Whats the deal with Reports

Help me fill a cognitive gap.

Why are report software the big deal it seems to be? I always assumed that its just a matter of making overviews or statistics from a database. Is there more to it? Does it get tremedously complex on the upper end of the scale?

The reason Im asking is that I noticed an job add looking for crystal reports experts.

Ive never bothered with that kind of stuff. Ive always worked on pretty small time stuff though. To me it always appeared easier to build a RTF or HTML document on the fly when the client want something to print or send around in emails.

So, have I missedunderstood something about this concept?

Eric Debois
Thursday, May 13, 2004

If a client didn't have you how would someone make
a report? That's what report writers are for.

son of parnas
Thursday, May 13, 2004

All the reports I do boil down to a few SQL queries, but the complexity arises from two things: all the different permutations of the reports, and people need a *ton* of reports.

Because of all of the different permutations of reports, you end up trying to write software to do it generically. Which makes it much more difficult, because now you need to support different orderings, groupings, sub-orderings, sub-groupings, sums of these sub groups, on different columns and column orders, custom titles and colors, create a new report just like the last one but reverse sorted (required if there are lots of sub-groupings, and they don't want to do it themselves), a new report like the last but add his middle initial, another just like the reports for year-by-year, but for all of the data we have (which might be incomplete because of a transition to a new system 10 years ago) and only where the lead has a first name of "john", etc etc etc ad absurdum.

It sounds like its not that hard, but there are just so many edge cases and possible toggles to add to the system, it gets complex quickly.

Add to this the fact that generally the customers need the reports quickly and often -- especially when they get a couple of them, it just whets their appetite -- that you can't just create them as-needed. They need a system that will spit out the needed reports at the click of a button.

Each individual report isn't hard, a system that can handle most any report needed with the click of a button is.

And don't even start me on people modifying the generated report and wondering why the database didn't get updated too.

-Andrew, Someone who spent a good part of last year setting up a home-grown Excel VBA reporting system for an Oracle database.

Andrew Hurst
Thursday, May 13, 2004

"Someone who spent a good part of last year setting up a home-grown Excel VBA reporting system for an Oracle database."

Thought I should clarify this: If I had to do it again I would probably choose something off-the-shelf. This system started from a couple Excel spreadsheets that generate reports as an example to 10 spreadsheets that generate 40+ different reports, and more are still needed.

Andrew Hurst
Thursday, May 13, 2004

From what I've heard the people in charge of the database don't have much of a clue but rather than admit that, they say they need new software that will help them do their job better. Place I'm at takes the SQL guys like 3 months to just create the SQL statements for reports and then our guy spends 3 days on formatting the report. You've got all kinds of issues when making the display (hierarchies). I haven't been involved in this but the guys who are say the above (yes, they say 3 months). From what I've seen a lot of people pretend to know whateveryone is talking about. On occassion I've tried to understand but when I pry everyone gets upset and so things just clunk and junk and sometimes it just goes right. Really crazy imho. Luckily I only have to work with them on rare occassions.

must remain anonymous
Thursday, May 13, 2004

Interesting.

I still feel that something fishy is going on here though. I guess I feel that SQL is already as simple as it gets, and trying to GUI-fy it can only make it harder. But then, I guess leaving point-and-click territory is out of the question for most of the users of these apps.

Eric Debois
Thursday, May 13, 2004

Reporting software does simplify creating and printing reports.

Yes, at the bottom of it all is an SQL statement (or some other source of data), but laying out the data on a sheet of paper in an intelligent format and printing it -- is another thing.

Imagine if you had to generate reports by starting from scratch with the Windows GDI. It would be more work than using Crystal Reports. (Some may argue vice-versa.)

While I have seen, used and coded report generators by hand, it is still more productive to use a ready made tool.

So becoming proficient with a reporting tool is definitely a plus... at least until you see an ad for a 'Windows GDI report writer...'

* - It is interesting to note that in COBOL and RPG reports are often generated and laid out in code. This is an exercise I think everyone should take part in because it teaches a valuable lesson in hierarchical design and coding. (In fact it was often the case that, when I was in school, most students would write complex RPG III/IV code to output reports when in fact it was a simple matter of sorting the records and using a hierarchical design.)

Software Craftsman
Thursday, May 13, 2004

Eric Debois,

Reporting software is useful in automating alot of stuff such as grouping, totalling and headings.

They are also often (not always) considerably faster, and don't require knowledge of bottlenecks in MS Word or some other technology (see http://word.mvps.org/FAQs/TblsFldsFms/FastTables.htm for example).

Also, they often generate a page at a time - so you can view Page 1 while Page 2040 is still being generated. This is hard to do in a MS Word based system (for example).

Seeya
Matthew

Anon
Thursday, May 13, 2004

In my experience the typical Crystal reports/Access jockey is someone who has a whole lot of domain knowledge and just enough DB smarts to know where the data is and how to get it out. Stepping from the checkboxes to UNIONs, LEFT OUTER JOINs etc is not usually an option.

As for the GUIfication, these reports are often subject to the "over the shoulder" analysis where the boss will say something along the lines of "so which are our slowest processes at the moment....the ones defined this year, not last year." In cases like these it's often easier to drop a filter on the existing data set and click a column header than modify a long SQL query.

Most of these sites are too small for a dedicated IT support person, let alone one with DB programming skills.

Motown (AU)
Thursday, May 13, 2004

Eric, it's an example of a funny trend that's common in IT. Dumb people think some simple task is the work of experts, and so advertise for such. The specification of "Crystal Reports" is a giveaway.

In truth, any SQL or DB person could do the job, and probably better than the person they end up hiring. They would probably reject a lot of good people on the grounds they haven't used Crystal Reports, which is not really relevant.

Inside Job
Thursday, May 13, 2004

"They would probably reject a lot of good people on the grounds they haven't used Crystal Reports, which is not really relevant."

Have you created and deployed intricate reports in Crystal?

Philo

Philo
Thursday, May 13, 2004

Yes.

Inside Job
Thursday, May 13, 2004

Personally, I think Crytal Reports is probably the wrong tool for the job of quick, constantly changing, on the fly reports. Maybe I just had a bad experience with it, but I seem to remember having to spend a lot more time formatting stuff than I felt was justified.

We're using Cognos now, and while it too has its flaws (cost being one of them), it does make one off reports easy to do. Of course, it does crash on my machine from time to time, and it has its own set of things that should be easier to do.

We still use Crystal Reports for when the layout and presentation has to be "just so" though, but that's generally in the case of an on going report that will be viewed by lots of people. We also still do the occasional green screen print out too.

Steve Barbour
Friday, May 14, 2004

i run a small team (4 people total) - our main function is to develop and deploy reports (mostly crystal). i agree that the most important attribute of a crystal report developer is a good knowledge of sql. imo all of the joining, aggregation, etc. should be done on the db. the reporting front end should only be responsible for formatting, ordering, etc.
i also agree that crystal is the wrong tool for constantly changing, on the fly reports. an olap tool (cognos, bo, etc) is better suited.
i would expect a 'crystal expert' to have experience with things like: different ways of connecting to datasources, different ways of passing criteria to datasources, the api, the pros and cons of subreports, etc. in an enterprise deployment, i would also expect experience developing & supporting some type of report launching / distribution system and the operational issues that go with that.

b walton
Friday, May 14, 2004

Ummm odd. Place I'm at is using cognos to replace current system. Had I been in charge I would leave it pretty much as is. DB and domain people work up the SQL needed to produce data, programmer then converts it to xml, designer then chooses template/xslt for output format. We currently just receive comma delimited data and output to html but I doubt the guy who does this uses templates. Even now most people I know handcraft html in their code (never mind thoughts of pdf/rtf/etc). One programmer was extolling virtues of cognos to me and for every item he mentioned I could suggest a method to do it where we have complete control, if we wanted to. Our problem was not so much time/work as much as architectural foresight, we just though of the quick way to format the data.

me
Friday, May 14, 2004