|
"Mid-level" tools question.
I caught myself in a bit of a bind this week, having to generate a report at the last minute. I was going to use Excel to display the data, but the level of text and queries involved made me lean toward Access. Ten, I was going to use Access, but I couldn't get it to generate a report using multiple tables/queries and I never could quite figure out the Expression Builder. I ultimately kludged together a little ASP/VBScript page that read from my table and generated an HTML-esque report. I say all this to set the tone for a couple of questions:
1. Did I do the right thing? It works and the boss seems pleased, so in that regard it works. However, was an active server page for something as basic as a report over kill?
2. My report pulled data from multiple tables, used several bits of parsing and math to tally numbers up, and required a bit of adaptability given that this single report was basically a template for several departments. Were Access and Excel (both 2000) improper tools for this job?
3. Thinking of Access and Excel, powerful as they are, as low-end Office tools and thinking of MS-SQL or Oracle Servers as a high-end tools, are there any mid-level tools that would have made my reporting easier? Or are things like VBScript and Perl the mid-level tools I'm thinking of?
I'm curious if there's a hierarchy or a layering scheme to office software. I'm curious if there are any other packages, aside from Office and Corel suites, out there that I should be learning to use and develop for. Any thoughts or opinions are welcome. Having to write a whole script for one report feels like overkill, so I'm wondering if I could have gone a better route. Thanks!
Andrew Burton
Friday, October 24, 2003
Access is a good enough tool.
You can create a query that brings your data from many tables into one that is easier to export/display. The wizard is not too bad.
You can also create a report that pulls data from one or many tables and/or queries. Again, the wizard normally generates a good starting point.
Spend a couple of hours reading up on access forms, queries and reports, and for most non-trivial reporting requirements, you will not need to touch a line of code.
Tapiwa
Friday, October 24, 2003
The only problem that comes into my mind is the worry that your boss will think that you can just "whip something together" for any reporting he might need... I hope for your sake it doesn't turn that way.
Greg Hurlman (www.squaretwo.net)
Friday, October 24, 2003
My own excel-centric philosophy is this: whatever tool you used to create the report, the report is more valuable if you can hand it to your boss in Excel format than in HTML format. Why? Because it's so easy for him to resort, pivot, do ad-hoc "what-ifs", sum things up differently, etc.
Joel Spolsky
Friday, October 24, 2003
Tapiwa: Duly noted. I will keep investigating Access. As much fun as coding scripts/queries/reports from scratch is... Heh. It'd be nice not to have to do it.
Greg: I actually do hope they think that, to some degree, it's how I landed this gig. :)
Joel: Agreed. The way I did it this time is sort of static, but all the data is in an Access database. If they ever wanted anything more complex (and gave me more time), it wouldn't be too hard to transfer (I believe).
I'd have probably gone with Excel if I knew how to run SQL queries through it.
Andrew Burton
Friday, October 24, 2003
Joel's right. Create the query in Access, and then export to Excel, an dsend the boss the Excel file as the report.
Access reports are a mess to format. You want to use them only when you are going to be creating exactly the same report every week or every month.
For a one off report create the query in Access and then just click the toolbar and export to Excel. You then have an independent file you, and your boss, can play with to your heart's content.
Excel's great for producing reports and analyzing data. Just don't use it as a data storage medium for anything more important than the lunch break shopping list.
Stephen Jones
Friday, October 24, 2003
I use Excel to generate about 30+ different reports off of our Oracle database. I just wrote a bunch of macro's that pull a few key's ( year, category, directorate, etc) out of a few specific cells (marked well) in one main workbook. It fires on the click of a button, and pulls the needed data from oracle using OLE, spits it into the page, formats, and saves.
It actually works very well, but the only thing that I don't like is creating a new report, just so they can have an extra column or a different sub-sort. I'm thinking of writing a general report-making tool in VB, and having it open Excel and making the report itself.
The biggest problem I see coming up with our setup here, is the (commonly asked) question "Can I just edit this data, and then have you re-import it?" My usual answer is "its possible, but there is a better way." They've asked enough I tell them its not possible and i'll make them another way to do it. Too much freedom in Excel for importing the data.
The other biggest problem is library management. If everything is stored in macro's, its hard to have a common set of libraries, even though there is about 15 functions shared between each of these reports. Did I mention this was 30 reports in 6 workbooks?
Or we might use Oracle Discoverer, but thats a whole 'nother can of worms.
Andrew Hurst
Friday, October 24, 2003
I think the comments about if the layout of the report does not change, then ms-access is a good choice.
The other thing is that the access report writer is very nice in that you can use sub-reports for the “many” side. While you can write some sql and do a join to the many side, sub-reports are much easier. You thus don’t have to fiddle and try and hide duplicate rows that get returned for each parent record.
Since each record in a join does return repeating fields for the parent record, then you have to start clicking the “hide” duplicates button in access. Often, you can also just use sorting and groping, and move the repeating fields out from the detail group to the report grouping you make. If you use sub-reports, then you don’t have any repeating fields from the parent record...and don’t even have to do any of the above, or even use SQL joins to get the child reocrds.
Totals for those groupings is easy, but the learning curve to start using code in access reports is a bit tricky until you get the hang of it.
The real question is how much the report will need to change over time? Often if you give the users just a few options to select things like date ranges, and location etc, then you can cover just about 90% of the requests for that report. And, as long as the user can supply some basic parameters to the reports then you are probably ok. If things like year etc. are hard coded, then you need to move that stuff out.
For most reports in ms-access, I give the user a form to “launch” the report. Here is some screen shots of what I mean:
http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html
Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn
Albert D. Kallal
Sunday, October 26, 2003
That link did not come out right...
http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html
Albert D. Kallal
Sunday, October 26, 2003
 Recent Topics
Fog Creek Home
|
