For Geeks and Wonks...Help a brother out

[Kaos]

I need an ad hoc query generator that I can customize for one of my applications.  SQL.

We've been essentially building reports as they're requested them.  As we've grown the business that's become cumbersome.  One instance and you can write custom reports.  You can't for dozens.  It becomes all you do.

The data tables are extremely complicated and the few solutions we've looked into are either so disorganized that they're useless to end users or they fail to make the proper connections and spit out garbage data. 

Any suggestions on a product (open source or OTS)? 

[AUTiger1]

Out of my realm as a sysAdmin, but would something like MX Query Builder or SQLOrm be something that would help? 

[Kaos]

Out of my realm as a sysAdmin, but would something like MX Query Builder or SQLOrm be something that would help?

I will check them out.  Thanks. 

I've got roughly 100,000 end users right now for this one application.  Each has his/her own idea about what data they need to extract and how they want to see it.  We're fortunate in that each of those users has to report up a chain and we're only dealing with the highest level which filters out all those requests and dumps only the ones on us they deem pertinent. 

But it doesn't stop the squawking from the rank and file.  The problem with that is that when we go do a demo for Oklahoma, for instance, one of the lower level people there has talked to a lower level person in  Virginia and the word that's been passed across is "I can't get to the data I need." 

No, but you can get to the data your boss SAYS you need.   Doesn't matter, the word gets passed up and then I have to deal with it at the decision-making level. 

I'm just looking for something that will give them read only access to the database and the ability to create and store their own custom queries.  I want to be able to limit/control their access on my side because if I turn it into the Wild West they won't have any idea where data is stored, they'll write fucked up queries and we'll start getting the death knell of all complaints -- inaccurate data, can't be trusted. 

[Buzz Killington]

We had been using Crystal Reports for years and just last year started using Spreadsheet Server for most of our financial reporting from SAP.  We are on Oracle now, but it should work on any platform.  May be bigger than what you are looking for though.  Just read your last post...

http://www.glbsoft.com/

[AUTiger1]

I will check them out.  Thanks. 

Maybe they will.  Like I said, that is out of my realm.   I have heard of those two, but not exactly sure what they do.  I will ask around here at the office and see if anyone  knows of anything.

[Kaos]

One more thing...

Proficiency level of these end users. 

True story:

I was doing an implementation training with about 25 end users in the room.   I'm going through the basic processes to get logged on.  Simple stuff I think.  I'm getting them to set up their personal accounts in the system and I look in the back row and see a woman clearly struggling.  She's gasping in exasperation and beginning to flail about in her seat. 

That's not the kind of thing you need so the standard approach is to drift back there, find out what she's got going on and try to resolve it without shutting the whole thing down for five or ten minutes (because what happens then is some smartass who thinks he knows everything will forge ahead, fuck something up and then you have to stop again to fix his shit). 

So I get back there and she's still on the first block.  Hasn't typed a thing in.  Arms folded and fuming. 

I ask her if there's a problem with her machine and she says "her cursor won't stay put."  I ask her to show me.

She was sticking the mouse up to the screen and trying to click in the box. 

She's going to want to run her own reports....

[AUTiger1]

One more thing...

Proficiency level of these end users. 

True story:

I was doing an implementation training with about 25 end users in the room.   I'm going through the basic processes to get logged on.  Simple stuff I think.  I'm getting them to set up their personal accounts in the system and I look in the back row and see a woman clearly struggling.  She's gasping in exasperation and beginning to flail about in her seat. 

That's not the kind of thing you need so the standard approach is to drift back there, find out what she's got going on and try to resolve it without shutting the whole thing down for five or ten minutes (because what happens then is some smartass who thinks he knows everything will forge ahead, fuck something up and then you have to stop again to fix his shit). 

So I get back there and she's still on the first block.  Hasn't typed a thing in.  Arms folded and fuming. 

I ask her if there's a problem with her machine and she says "her cursor won't stay put."  I ask her to show me.

She was sticking the mouse up to the screen and trying to click in the box. 

She's going to want to run her own reports....

 

[Buzz Killington]

One more thing...

Proficiency level of these end users. 

True story:

I was doing an implementation training with about 25 end users in the room.   I'm going through the basic processes to get logged on.  Simple stuff I think.  I'm getting them to set up their personal accounts in the system and I look in the back row and see a woman clearly struggling.  She's gasping in exasperation and beginning to flail about in her seat. 

That's not the kind of thing you need so the standard approach is to drift back there, find out what she's got going on and try to resolve it without shutting the whole thing down for five or ten minutes (because what happens then is some smartass who thinks he knows everything will forge ahead, fuck something up and then you have to stop again to fix his shit). 

So I get back there and she's still on the first block.  Hasn't typed a thing in.  Arms folded and fuming. 

I ask her if there's a problem with her machine and she says "her cursor won't stay put."  I ask her to show me.

She was sticking the mouse up to the screen and trying to click in the box. 

She's going to want to run her own reports....

Welcome to my world.
One week, I was filling in for the security guy, and I got a call from one of the HR "bigwigs."  Her password wouldn't work, so I reset it for her to something simple (abcd1234).  I told her to remember that the password was case-sensitive and hung up.  Two minutes later she calls back, saying that she is locked out now.  She's been typing the password, but I must not have set it right.  She says I have been typing c-a-s-e-s-e-n-s-i-t-i-v-e and it just won't work.
I hung up so I didn't laugh in her ear.

[GarMan]

I need an ad hoc query generator that I can customize for one of my applications.  SQL.

I would suggest one of the following... 
- Use Business Objects and Crystal Reports to develop a custom solution for them.  You would need to create a BO universe with "easy-to-understand" titles to data field mappings in the database.  (We callz that the semantic layer.)  The universe will give you the ability to simplify a data access tier to your database.  The end-users could then use Crystal Reports against the BO Universe to create whatever they want. 
- Use Informatica PowerAnalyzer to do something similar to above.  You'll find that PowerAnalyzer is a bit complicated, but wiff some training and some pre-built examples, you could get some super-users functional with this solution.  Actuate and Cognos have some analytic tools that may work as well. 
- Perhaps, the right way to do this is to create a real Business Intelligence solution.  You'd have to architect a data mart wiff a star/snowflake schema that can be populated from the core database periodically.  Then, configure one of the analytical tools mentioned above for use by the end-users.  If you design the data mart appropriately, they should be able to figure out how to get what they need.
- The poor man's solution is probably MS Access.  Point MS Access at the database and let them learn how to navigate to get what they want.  Basic MS Access training will help them, and they can learn to assemble more complex SQL over time wiff the Access GUI.  You might even want to create a data mart as mentioned above and only permit them access to that.  Also, their accounts would need to be locked down to read-only access with this solution as MS Access does permit updates to data. 

[AUChizad]

Sorry I'm just now seeing this.

A lot of good solutions mentioned, Crystal Reports probably being the best of those.

In my opinion, SQL Server Reporting Services (SSRS) is more versatile, more of a direct SQL-based way to build reports, and best of all is free with SQL Server.

Highly recommend going that route.

[Kaos]

Sorry I'm just now seeing this.

A lot of good solutions mentioned, Crystal Reports probably being the best of those.

In my opinion, SQL Server Reporting Services (SSRS) is more versatile, more of a direct SQL-based way to build reports, and best of all is free with SQL Server.

Highly recommend going that route.

We tried reporting services in 2006.  It was too complicated for the users to manage. 

I was hoping there was some plug and play thing I could buy that would make it easy. 

[AUChizad]

We tried reporting services in 2006.  It was too complicated for the users to manage. 

I was hoping there was some plug and play thing I could buy that would make it easy.

Well, yeah, for the users to manage. I figured you wanted to build a few reports but leave open several parameters that they can just plug in to do the "querying".

It will take some prep work on your end, but you can build the queries on the back end and just pop in some parameters for them to plug in to add to the WHERE clause.

Outside of maybe Access, I don't know of anything simpler that end user retards that pull shit like the mouse-to-screen scene you described can do to write queries. I mean, they can either write actual queries, or you can write some reports that let them just plug in the search parameters the want. And if that's the case, you want to build reports, be it with SSRS or Crystal, or whatever.

[Buzz Killington]

We tried reporting services in 2006.  It was too complicated for the users to manage. 

I was hoping there was some plug and play thing I could buy that would make it easy.

See my previous post.

OR

Business Objects might be a valid option.  What you need are a few "super-users" that can create the queries and just let the sheep run them on their own.

[CCTAU]

We tried reporting services in 2006.  It was too complicated for the users to manage. 

I was hoping there was some plug and play thing I could buy that would make it easy.

Uh yeah. With users like you described above, there may not be a "reporting for dummies" version available anywhere!

[GH2001]

I will check them out.  Thanks. 

I've got roughly 100,000 end users right now for this one application.  Each has his/her own idea about what data they need to extract and how they want to see it.  We're fortunate in that each of those users has to report up a chain and we're only dealing with the highest level which filters out all those requests and dumps only the ones on us they deem pertinent. 

But it doesn't stop the squawking from the rank and file.  The problem with that is that when we go do a demo for Oklahoma, for instance, one of the lower level people there has talked to a lower level person in  Virginia and the word that's been passed across is "I can't get to the data I need." 

No, but you can get to the data your boss SAYS you need.   Doesn't matter, the word gets passed up and then I have to deal with it at the decision-making level. 

I'm just looking for something that will give them read only access to the database and the ability to create and store their own custom queries.  I want to be able to limit/control their access on my side because if I turn it into the Wild West they won't have any idea where data is stored, they'll write fucked up queries and we'll start getting the death knell of all complaints -- inaccurate data, can't be trusted.

This may be more big picture solution than you are looking for but have you ever considered getting Business Objects? It can pretty much take care of anything data related now.