April 15, 2008 at 12:18 pm
I'm part of a team working on an interface to build user-generated queries for reporting. The user can pick tables and columns from those tables and the interface generates a query that then returns a datagrid showing the results. My question is: is there a way for us to store the user-generated query in the DB in case it is a query the user wishes to reuse on a periodic basis, perhaps in a table? Any suggestions on this one? TIA...
Brad
April 15, 2008 at 12:23 pm
Query text can pretty easily be stored as varchar(max) in a table. Perhaps with a user ID column and a last-used datetime.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2008 at 12:31 pm
Where it gets tricky is do you want to have the ability for the users to parameterize those queries? How do you handle parameters?
I know I can be a bit aof a broken record on questions like this one, but this is exactly why MS has the report builder in SSRS. You define the report model and the users create reports (queries) based on the report model with WYSIWYG design. Have you considered this?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2008 at 12:40 pm
Well, I'd love to say it would be that easy, but this solution has to be portable. Right now the development database is in SQL Server 2005, but the DB is being ported to Oracle 10g, so...trying to keep it generic?
And no, there aren't parameters available to them - just looking at the table names and column names. Program logic assists them in ensuring they have picked joinable tables, and drop downs allow them to do thins like "APN = 123456789"...
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply