I have been thinking about the ad hoc querying using llblgen. I also ran accross a component that will take a datatable or a dataview and allow a user to select the appropriate fields from the datatable and then allow the user to create a query/filter against it. You can see and use the component here. http://www.cas-services.com/AHDemo/Default.aspx The demo is using the northwind db and its currently in beta. I must say I have no interest or affiliation with this person/company. The ui is just using the component so you can create your own ui to meet your needs.

That said, here is what I'm thinking. Using llblgen I can have an end user build the relations they want dynamically. Once the relations are created, I can then create a typed list from the relations which will return a datatable. Once the datatable is created, I could then use this component (mentioned above) to allow the user select the appropriate fields and create the necessary SQL statements to generate the report they want.

Does anyone out there see problems with this? Is there anyone who has created an ad-hoc query system the can provide some insight? Not sure how to dynamically build the the relaions so any help would be appreciated.

erichar11 wrote:

I have been thinking about the ad hoc querying using llblgen. I also ran accross a component that will take a datatable or a dataview and allow a user to select the appropriate fields from the datatable and then allow the user to create a query/filter against it. You can see and use the component here. http://www.cas-services.com/AHDemo/Default.aspx The demo is using the northwind db and its currently in beta. I must say I have no interest or affiliation with this person/company. The ui is just using the component so you can create your own ui to meet your needs.

That said, here is what I'm thinking. Using llblgen I can have an end user build the relations they want dynamically. Once the relations are created, I can then create a typed list from the relations which will return a datatable. Once the datatable is created, I could then use this component (mentioned above) to allow the user select the appropriate fields and create the necessary SQL statements to generate the report they want.

Does anyone out there see problems with this? Is there anyone who has created an ad-hoc query system the can provide some insight? Not sure how to dynamically build the the relaions so any help would be appreciated.

It's funny you bring this up. I've been in development of an ad-hoc query system for quite a bit now, so I've learned a few lessons . Additionally, just before I read your post, I posted here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1137

At any rate, I'm not using LLBLGen for the underlying infrastructure. Why? Two reasons: 1. You cannot have multiple joins from table A to table B. 2. LLBLGen Pro doesn't support aggregates

Now, both of these features are supposed to be coming out soon, that's why I posted above, but they weren't when I started so I was SOL.

The main problem, in my opinion is presenting the user with a different "layout" of fields, than what is represented by your database schema. This implies that you must present a pre-defined structure of entities and fields to the end-user such that the details of the db schema are hidden from them. For example, you don't want them to be confused by things like many-to-many intermediate tables.

Thus, you must predefine the relations for them and use them to create the query they really want, as opposed to them having to basically understand normalized database design to find their way around the schema. I had a great system in place, but found some drawbacks as I mentioned in the post above. I'm currently in the middle of improving the structure.

Now, all of this presupposes that your end users are not techinical in nature, as they are not in my case. If you have more "information worker" types, or semi-technical types using the system, you can give them much more flexibility and power, and you can basically present them with the schema itself and let them figure it out.

Anyway, if you have any questions, please contact me via e-mail (found in my profile). Good Luck.

Jeff...

Oh, by the way, even if you didn't care about aggregates, you'll want to consider the trade-off. You either need aggregates, or you'll need to restrict the user to 1-1 relationships as usually users won't know what to do with a resultset that contains fields from 3 different entities joined on anything but 1-1. If those types of relationships are absolutely necessary, then the way to resolve it is to set up grouping and aggregate on the appropriate fields.

Jeff, thanks for the reply. I would like to talk to you offline via email, but your profile has your email as not visible. You can send me your email via the email address in my profile or make yours visable. I must admitt I am no db expert so while what you say makes sense, I just can't seem to quite visualize it in the app I'm currently building. Also, I have never built a reporting app so your insights are very helpful. First, regarding aggregates, I would like this very much but at the moment it's not that important for me. The app won't be finished by the time the next version of llblgen is finished anyway, so I can wait. Secondly, I'm not sure I understand what you mean by "You cannot have multiple joins from table A to table B"? Are you talking about self joins?

So here is an example of what I'm trying to accomplish In the app I'm building I have a PageEntity. A PageEntity can contain multiple moduleEntites. Each module entity contains a set of fields which need to be reported on. The relationship between a page and a module are already predefined via configuration settings in an adminstration module.

page A: Module 1 Module 2 Module 3

Now, what I'm trying to accomplish is to let the user create a report against the fields in module 1 and the fields in module 2. So a user would select module 1 and module 2 to be reported against (or any combination of the modules). All the fields in those 2 modules would ultimately be combined into a datatable (a flat data structure) which would be used as the basis for the component I mentioned, so uses can select the appropriate fields they want to report on, filter and so on. So what are the issues for using a typed list that combines fields from multiple entities) at runtime to return a datatable. I think thats what I'm not understanding.

Just to outline the process I'm thinking about from a user perspective. It would probably be a wizard.

1. select report data (user selects llglen entities associated with a page and a typed list would be returned to the adhoc component)
2. Select columns (this is done from the adhoc component rather than llblgen)
3. order columns // maybe able to combine with select columns
4. select additional criteria (select, where, orderby, etc based on selected columns
5. output to a datagrid

Thanks for all your insight. It's much appreciated

erichar11 wrote:

Jeff, thanks for the reply. I would like to talk to you offline via email

no offline chat people like me are lurking trying to learn stuff...put it all here

erichar11 wrote:

Jeff, thanks for the reply. I would like to talk to you offline via email, but your profile has your email as not visible. You can send me your email via the email address in my profile or make yours visable. I must admitt I am no db expert so while what you say makes sense, I just can't seem to quite visualize it in the app I'm currently building. Also, I have never built a reporting app so your insights are very helpful. First, regarding aggregates, I would like this very much but at the moment it's not that important for me. The app won't be finished by the time the next version of llblgen is finished anyway, so I can wait. Secondly, I'm not sure I understand what you mean by "You cannot have multiple joins from table A to table B"? Are you talking about self joins?

That's one aspect of it. It also refers to:

"Train" Table
  StartLocationID -> FK to "Location" Table
  EndLocationID -> FK to "Location" Table

So here is an example of what I'm trying to accomplish In the app I'm building I have a PageEntity. A PageEntity can contain multiple moduleEntites. Each module entity contains a set of fields which need to be reported on. The relationship between a page and a module are already predefined via configuration settings in an adminstration module.

page A: Module 1 Module 2 Module 3

Now, what I'm trying to accomplish is to let the user create a report against the fields in module 1 and the fields in module 2. So a user would select module 1 and module 2 to be reported against (or any combination of the modules). All the fields in those 2 modules would ultimately be combined into a datatable (a flat data structure) which would be used as the basis for the component I mentioned, so uses can select the appropriate fields they want to report on, filter and so on. So what are the issues for using a typed list that combines fields from multiple entities) at runtime to return a datatable. I think thats what I'm not understanding.

If you could post the DDL for the tables (the CREATE TABLE SQL statements) that would be helpful. However, assuming the the tables selected by the user are related to each other in a 1:1 manner, I think you'd be OK. Otherwise, you'll get multiple rows for each combination of tables. Hope that makes sense. Post the DDL and we'll take a look.

Thanks for all your insight. It's much appreciated

Glad to help.

BTW, I've unhidden my e-mail address if anyone's interested in spamming me.

Thanks Jeff, for your help. Heres the create table stuff. I only included what I thought would be beneficial. If you need more, let me know.

CREATE TABLE [dbo].[PageType] ( [PageType Id] [int] IDENTITY (0, 1) NOT NULL , [PageTypeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO

CREATE TABLE [dbo].[PageType Module] ( [PageType Id] [int] NOT NULL , [Module Id] [int] NOT NULL ) ON [PRIMARY] GO

CREATE TABLE [dbo].[Module] ( [Module Id] [int] IDENTITY (0, 1) NOT NULL , [ModuleTitle] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PanelName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ModuleType Id] [int] NOT NULL , [Company Id] [int] NOT NULL ) ON [PRIMARY] GO

CREATE TABLE [dbo].[ModuleType] ( [ModuleType Id] [int] IDENTITY (1, 1) NOT NULL , [ModuleTypeName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Source] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO

Hope this helps, please note that the actual module (or modules types have not been added to the db yet). They are sitting in another test project.

PageType has modules which is related through PageTypeModules Table (m:n). The goal is to allow a user to select multiple modules and create a flat data structure of all the fields in the modules selected.

Jeff, I'm also confused by the statement "assuming the the tables selected by the user are related to each other in a 1:1 manner". Sorry not up on my db lingo Please help.

erichar11 wrote:

Jeff, I'm also confused by the statement "assuming the the tables selected by the user are related to each other in a 1:1 manner". Sorry not up on my db lingo Please help.

Thanks for the DDL. OK, so as I see it you're looking for a query something like this:

SELECT Module.* 
FROM PageTypeModule
JOIN PageType ON PageTypeModule.PageTypeID = PageType.PageTypeID
JOIN Module ON PageTypeModule.ModuleID = Module.ModuleID
WHERE
PageTypeModule.PageTypeID = blah AND
PageTypeModule.ModuleID IN (blah1,blah2,blah3)

The problem with this query is that you're going to get 1 row for EACH module, not 1 row for the PageType with columns for each field in each module. What you're looking for is a crosstab, and you're limited to using stored procedures to generate that (or construct it in code).

That's what I was referring to when I said "assuming that the tables selected by the user are related to each other in a 1:1 manner". If the tables in the FROM clause were related in a one to one manner, they would construct a single row with fields from each table in the row.

However, since your tables are m:n, it has to spit out multiple rows representing 1 row for each combination of PageType and Module.

The solution to this is GROUP BY and aggregate functions. You group on the PageType, then sum or average the aggregatable (is that a word) columns, and eliminate all of the other ones. That's the only way you'll be able to reduce the dataset to one row.

Jeff...

By the way, I have some links to some crosstab generating stored procedures, but they're not fun...I'm using a couple in my app right now.

Awsome, thanks for the great explination. That's exactly what I needed and I have a much greater understanding of what the issues are. Looks like aggregates are more important than I thought. Go aggregates in llblgenPro. I think I'm going to wait for the next version before I start to really pickup the ad hoc reporting stuff. I can afford to do this since the project is for myself.

Anyways, did you get a chance to take a look at the component I mentioned in my initial message. Any thoughts. If I can eventually get llblgenPro to return the correct flat datatable, I could then just drop this component in to handle the ui portion, of course with some ui changes.

Thanks for the help, it was excellent.

erichar11 wrote:

Awsome, thanks for the great explination. That's exactly what I needed and I have a much greater understanding of what the issues are. Looks like aggregates are more important than I thought. Go aggregates in llblgenPro. I think I'm going to wait for the next version before I start to really pickup the ad hoc reporting stuff. I can afford to do this since the project is for myself.

Anyways, did you get a chance to take a look at the component I mentioned in my initial message. Any thoughts. If I can eventually get llblgenPro to return the correct flat datatable, I could then just drop this component in to handle the ui portion, of course with some ui changes.

Thanks for the help, it was excellent.

No, problem . As you plan it out, just remember that there's a critical difference between the information the user wants and the way they want to see it versus the way it's stored in the database. There's an implied requirement that you present the user with a list of tables/entities and fields in the way that they expect to see it, figure out how it relates to your schema, and then translate that requirement into SQL.It's harder than you think because often times it introduces aggregates and grouping in places where the user hasn't explicitly asked for it, such as your case. Anyway, it's harder than I thought. . I ended up limiting the user 1:1 relations for the first phase - which gives them plenty of information to report on - while I figure out how to make it easy to give them access to all of the relations in the db and still present it to them in a manner that they would understand.

Jeff...

<Edit> Oops, forgot to answer your question about the component. I'll check it out, but if its only requirement is that you give it a "flattened" resultset, you're pretty good to go. I used ActiveReports for the "presentation" of the data - they include an end-user report designer that is really nice. The demo isn't even crippled - just a banner at the bottom of each report - so you really get to have a good feel for the product.

Anyway, the report designer is nice because all you have to do is create a listbox populated with fieldnames (and the dataset, of course) and the designer takes care of drag-drop functionality, and everything. Really smooth.

jeff, with respect to ActiveReports is the reports designer a web based designer or does it require a user to have the reports designer on the desktop. I haven't looked at the product in a while, but I think its the latter. If that is the case, I don't think it will work as one of the requirements is to be completely web based.

Hey...I'm pretty sure it's a WinForms only thing. I know they have a web viewer, but I don't think they have a web designer...you might want to check though.

Jeff...