Reporting solution without the reports?!
-
Having a little bit of a hard time with something over here & am wondering if any of you might have some advice. We've got a data warehouse in Oracle, and a handful of other databases in SQL Server. We don't have any real reporting solution in place. I have a lot of scheduled reports that I've made DTS packages out of - a DTS package will grab the data, put it into Excel, save it to the network, and optionally email it.
The problem is all the on-demand reports. I'll get requests to pull a certain piece of info for an end-user and will either type up a query in query analyzer (hitting linked tables & local tables), or sometimes just run a query in Access. I've got SQL scripts and Access queries both saved for the different requests. I'll take the results, copy & paste to Excel, and email. Usually they want something a bit different than the last time, so the modifications to the query will typically need to be made..... Sometimes one report will lead to more questions & prompt another report & another, etc.
I need to get this out of my hands and into the hands of the end-users so that they can pull their own reports and I can focus on more interesting things. I downloaded an eval of Crystal Reports, but i don't think a traditional report designer is going to be our answer. With that, not only do I have to make the query, but I now need to worry about formatting a report nicely on a page, rather than just spitting the raw data out to Excel.
I guess I'm wondering if there's any kind of system sorta like Reporting Services or Crystal Reports that would let you basically have a repository of queries, and allow a user to run them on-demand, fill in parameters, maybe be able to schedule them, and possibly even define their own queries to an extent... Not a full-blown report writer where I have to worry about fonts and alignment and all that junk, but something that would just dump a query into Excel. And, I'd have to be able to pull from different data sources at the same time.... Maybe something web-based that I could put on the Intranet - sorta like the "Crystal Reports Server" product I was looking at in terms of being able to schedule, control access, cache reports, etc...
Maybe I just need to create an Access DB with a bunch of linked tables, create some basic queries, give some brief lessons on how to use the existing queries and make new ones, on what joins to what, etc, and let them go to town. Seems scary - that database will quickly become a mess and grow out of control. But, the alternatives are: 1) giving them crystal reports, access to a bunch of relevant tables/views and letting them go to town; or 2) making a ton of reports myself in reporting services or crystal & formatting them pretty. Neither seem very appealing. Can end users (department heads and the like) really even grasp report writing on their own, or is this a pipe dream?
Not sure if my questions are clear, but if anybody's "been there, done that" and has some words of wisdom they could bestow upon me, it would be greatly appreciated!! This whole "reporting" thing has always been somewhat of a black hole. It's good enough for me to just copy & paste query results to excel & email that out - I'd like to keep it that simple if possible.... Maybe I need to hire a report writer.....
Thanks in advance, and sorry if the post is a little long-winded!! Jeez, it's a freakin' enormous post now that I look at it. Sorry!!
-
I guess it depends on how much time you have, and how important this is to your company. Everyone wants some Ad-Hoc Reporting tool that allows the user to do anything and everything they want.
You can attain this to some level by buying a huge application that allows this. But this costs a ton of money.
Or you can build your own, that is driven entirely off metadata. But this will take a ton of your time. and Cost alot of money.
The tool you speak of is query analyzer. Unfortunatelly there is significant training involved in getting the end users up to what they need.
Unfortunatelly I don't have an answer for you, but your probably on the right track by using access, you should probably make a set of Views that incorporate the basic queries the users want, and allow them to select the columns, and where clauses using access.
That way you don't have to teach them all the complex joins.
Ray
-
We had a similar issue and (because the end users are smart) I gave them DTS via enterprise manager and select only permissions. I let them know they couldn't do this during peak processing times and since then (After a little monitoring initially) all's been well - They use the query builder in DTS to create the process.
-
Regardless of how you do it you won't get out of the query building process, however you can get out of it with some programming. Our internal OMS was written in VB 6 (being converted to .NET soon) and it has an inquiry screen using a simple grid. I created a bunch of queries and allow the user to pick from a list which one to use. They can rearrange columns and save their order. The can limit records by filtering on any field in the collection. They can also export that information to excel to create pivot tables, charts, etc. Best of all the only thing I have to do now is create new queries when needed, but many of the requests are just subsets of the existing queries which the end user can take care of.
I would imagine it could take a few days to build a similar screen but you'll get that time back in next to know time and end users might actually be happy.
As far as a reports, the next version of reporting services will have Report Builder which may allow the users to build the formatted reports but again you'll probably need to provide the queries.
Good Luck!
If the phone doesn't ring...It's me.
-
I support a SQL datawarehouse with similar requests. I do have a PowerBuilder front end that allows end users to run queries and export to execl. However, the more advanced power users extract data via Excel Pivot Tables and or MS Access.
This way they can drill to thier own reports.
Also, the database is de-normalized to help assist them in not having to make more complex data joins and such.
Perhaps you could develop some de-normalized views which can be used?
-
Yeah, I'm leaning towards setting up some views and giving access in access. The hard part is we've got this Oracle server which is our data warehouse and has most of the info, but we have no control over that, it's provided by a 3rd party. We've also got a bunch of internal data on SQL servers that we need to link up to. I was thinking about setting up a local SQL database and having views in there that go out to the linked Oracle server, but the performance is terrible because it's bringing back all the records and then filtering on the front-end..... It'd be nice if I could make stored procedures that grab only what's needed from the Oracle tables, but alas, Access can't link up to a stored proc.
Nothing's easy, I guess, eh? This is why it'd be great if there were something like Crystal but that isn't really a report-writer, just a query-builder that allows exporting to Excel, has security settings, and can be scheduled..... Oh well.
I think I'm going to create a daily job to transfer a handful of the important Oracle tables to a SQL database, and create denormalized views in there linking between the oracle tables and the local tables.... Make an access app, link those views up, throw some queries in there and call it a day. I can use trusted security from the access app to the sql server, and set up permissions that way...
Not too thrilled with that solution, but I think building a custom front-end will be too big of a project. This should at least get some of it out of my hands - we shall see!
-
Have you tried setting up parameterized queries in EXCEL? From what you write, this seems to be the best solution.
1. Create a stored procedure with parameters.
2. grant permissions on the stored procedure.
3. In excel, from the menu bar Data - external data - new database query. (This will open MS Query, if it has been installed)
4. in the SQL window, type in your procedure and its parameters.
For example: Let's say you have a sproc called GetProductSales which takes the parameter of a product ID and a date. you would run this in a QA window as
GetProductSales 542, '05/01/2005' and this would return the information on sales of this product.
In MS Query in Excel, the syntax would by {call GetProductSales; 1 ((?),(?)}
Whenever this excel data is refreshed, the user will be asked to enter the parameters.
Good Luck!
-
You noted that you were toying with the idea using SSRS. Had you considered going this route and providing the end users access to build their own reports using the Report Builder?
EDIT: Never mind, you're on 2000....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply