Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
yonabout
Posting Yak Master
112 Posts |
 Posted - 2006-05-30 : 08:40:22
|
Hi, I'm looking for an alternative method for doing something. I suppose what I really want is to know if anyones tried this before, and if they have if they've got any advice or information they can point me at. Here's the background..I developed an application a year or so ago to allow users to create their own reports from data held on an administration system. The Admin system came with no built in report writer. The vast majority of the users are computer illiterate, hardly any of them know how to use windows explorer (despite training), so just giving them query analyser training or even MS query wasn't really an option.So, the application we came up with allows them to drag data from the admin system, and run it with simple criteria attached. The other advantage is that it has a similar look and feel to the admin system, so it fits in with what they're used to.There's a backend SQL database for the report writer, which controls which fields from the admin system database are displayed, and how they are grouped for the user to select.There's a table which controls the joins, so that when a user picks all the fields they want to report on, the report writer knows how to join the admin system tables together.There are a bunch of other tables which contain criteria fields for each report, ordering information etc. etc.So the idea is, the user builds their report in the front end. The back end holds all the bits that make up an SQL statement, so at run time, the reporting application can build an SQL query, run it against the admin system and dump the results wherever they need to be dumped.In practice, it's running fine, but as it's been running for about a year, it does seem fairly limited, and difficult to include the flexibility that is often required for the reports. For instance it's easy to write (or generate) a query to select a bunch of fields, with joins and criteria, but if a user needs a report that is going to require data selected from a joined aliased table or something, then I end up having to write a specific query for that user, then shoe horning it into the front end. A couple of days ago a user wanted to write a report to pick up all the people who don't have a historic entry at a date. My application looks like it allows you to do that, but the SQL it generates is along the lines ofwhere effdate <> '01/01/2005' which returns everyone, because all their other historic entries aren't equal to the date. To return the results that they want - i.e. people who don't have a historic entry at that specific date, I selected people who had records which were not in a group of people having records at that date.Anyway, this is just one example where it doesn't seem flexible enough to give the users what they want, so any advice would be cool.Cheers,Yonabout |
|
|
|
|
|
|
|
SQL reporting for chimps.