I've read a few threads on folks thinking about/desigining/developing an ad hoc query tool. I'm in pretty dire need of a utility that will allow a user who can't even spell database to be able to pick and choose fields that they want (there's a lot of relationships that I'd like to hide from them as much as possible) and be able to export the results to an Excel or comma delimited file. There also needs to be pretty basic filters (equal to, greater than, etc).

The software that I wrote for this customer uses LLBLGen if that makes any difference.

I don't need reporting, just query ability. Can anyone provide any insight? Do you have such a tool? Would creating one in LLBLGen be relatively simple?

Thanks for any help.

billb wrote:

I've read a few threads on folks thinking about/desigining/developing an ad hoc query tool. I'm in pretty dire need of a utility that will allow a user who can't even spell database to be able to pick and choose fields that they want (there's a lot of relationships that I'd like to hide from them as much as possible) and be able to export the results to an Excel or comma delimited file. There also needs to be pretty basic filters (equal to, greater than, etc).

The software that I wrote for this customer uses LLBLGen if that makes any difference.

I don't need reporting, just query ability. Can anyone provide any insight? Do you have such a tool? Would creating one in LLBLGen be relatively simple?

Thanks for any help.

LLBLGen is perfectly suited for this using the new ResultsetFields collection and aggregate functions. You can construct any query necessary now (except those requiring SQL functions) using this functionality. The problem you'll have, however, is expressing the data structure to the user without exposing them to the vagaries of the relational model. Specifically, data returned from a query that requires the use of anything but one-to-one relationships will be confusing unless aggregate functions are applied.

Jeff...

That's what I was afraid of. The minute I speak of relations these people are going to say 'no way'. They are in no way prepared to deal with that level complexity. I suppose writing an interface to hide this as much as possible is where the money is.

jeffreygg wrote:

billb wrote:

I've read a few threads on folks thinking about/desigining/developing an ad hoc query tool. I'm in pretty dire need of a utility that will allow a user who can't even spell database to be able to pick and choose fields that they want (there's a lot of relationships that I'd like to hide from them as much as possible) and be able to export the results to an Excel or comma delimited file. There also needs to be pretty basic filters (equal to, greater than, etc).

The software that I wrote for this customer uses LLBLGen if that makes any difference.

I don't need reporting, just query ability. Can anyone provide any insight? Do you have such a tool? Would creating one in LLBLGen be relatively simple?

Thanks for any help.

LLBLGen is perfectly suited for this using the new ResultsetFields collection and aggregate functions. You can construct any query necessary now (except those requiring SQL functions) using this functionality. The problem you'll have, however, is expressing the data structure to the user without exposing them to the vagaries of the relational model. Specifically, data returned from a query that requires the use of anything but one-to-one relationships will be confusing unless aggregate functions are applied.

Jeff...

While it's (arguably) more work (and against our nature as programmers) you can provide the users with the illusion of an ad-hoc query system by working with them to create pre-written queries that represent the kind of business intelligence they want to get out of the system and allow them to modify the queries with different criteria. For the average end-user this is probably what they really want. The big problem will be if they want to cross-reference the results of one query with the other...

Jeff...

This may be what I have to do in this situation because like every other software project in the world, the budget is tight. I don't think they'll get the ability to cross-reference the results of queries and I'll never even mention it. I would, however, like to keep the tool as generic as possible for reuse. This may not be possible though.

jeffreygg wrote:

While it's (arguably) more work (and against our nature as programmers) you can provide the users with the illusion of an ad-hoc query system by working with them to create pre-written queries that represent the kind of business intelligence they want to get out of the system and allow them to modify the queries with different criteria. For the average end-user this is probably what they really want. The big problem will be if they want to cross-reference the results of one query with the other...

Jeff...

jeff, o guru of ad hoc reporting

Let's say for the moment that you have two entities

entity1 entity2

and a user can select various fields from the entities to ultimately create a resultset. The relationships between the two entities are already created via llblgen. How are your going about creating the relations between these entities dynamically, in other words, build the IRelationPredicateBucket dynamically? Lets forget about filtering and grouping for the moment.

By the way, are you using selfservicing or adapter model

Hmmm...I don't speak "Adapter-ese" as Adapter wasn't out when we started our current project (unforunately), so while I vaguely know what an IRelationPredicateBucket is, I really don't know how it's implemented. Self-Servicing allows you to specify the relations to use in a .GetMulti call by adding IEntityRelation objects to a RelationCollection.

Determining which relations to use and when is the problem. If you're presenting your query system to someone who understands the relational model, or to someone is able to grasp the concepts, this is a piece of cake. Just loop through the Relations available on each entity, create GUI objects that expose the details of the IEntityRelation object and let them pick which ones they want to use.

In my case, my project needed to expose data to end-users who would never want/need to learn about databases. Thus, instead of confusing them with relationships and 1:n this and m:n that, I chose to manually cook up objects that represented the underlying tables and pre-defined the relationships between the objects based on 1:1 relationships. If the user chose table A, they were presented with all tables {B...n} that were related to table A on a 1:1 basis.

Unfortunately, this (obviously) reduces the level of data that the end-user can retrieve, but for us it was sufficient for now and close to the limit of what's possible with an ad-hoc query system for those not technically inclined. In the future I will extend the functionality to include 1:n/n:1 relationships with a system that will aggregate fields to eliminate the "duplication" resulting from tables joined in anything but a 1:1 fashion.

If you have the choice, I would opt for a pre-defined set of queries like I mentioned in the previous post, and just let the users move within that structure. They'll feel safer that way and I believe your usability will go way up.

Hope that isn't too confusing.

Jeff...

Thanks Jeff, I understand exactly what your saying. I too will have users who care nothing about the database and 1:M, M:N relatations, etc. But in my case, lets say I have 20 different entities one can report against in any combination, are you suggesting that one builds these relationships manually? Thats tuff as there are so many different combinations that a user could have. So if a user wants to report against 3 entities(1,4 20) the only relations I need are between those combinations of entities. This is why I was asking whether and/or how you build the relations for these three entities. Ideally I can build those relations in the background dynamically and not have the user have to select which relations to use.

For example

User is presented with a list of entities to be reported on User selects 1 or more entities User is then presented with each entity they have selected and the fields associated with that entity User selects the fields they want to report on from each entity

User selects generate report (I'm not concerned with aggregates and filter just yet, just trying to get basics down first) At this point the relations betweeen the entities are created

I hope I'm making sense here. Is this a logical process to follow for a reporting infrastructure.

erichar11 wrote:

Thanks Jeff, I understand exactly what your saying. I too will have users who care nothing about the database and 1:M, M:N relatations, etc. But in my case, lets say I have 20 different entities one can report against in any combination, are you suggesting that one builds these relationships manually?

No, not manually, but you've got 2 choices here.

1. Present the tables to the user and ask them to select how to get between all of the tables

2. Present each "instance" of each table that is represented by a relation. For example, If Table A has 2 relationships to Table B, then show Table B twice, once for each relationship - then label each appropriately.

Either way, as I mentioned in the previous post, you can build/show the information to the end user by iterating through the available relations given you in the EntityClasses (I'm speaking about Self-Servicing, I'm not sure what the equivalent would be in Adapter) and presenting them as either for option #1 above a GUIRelation object, or #2 as a GUITableInstance object.

Thats tuff as there are so many different combinations that a user could have. So if a user wants to report against 3 entities(1,4 20) the only relations I need are between those combinations of entities. This is why I was asking whether and/or how you build the relations for these three entities. Ideally I can build those relations in the background dynamically and not have the user have to select which relations to use.

Unless you have no tables that relate to each other through more than one foreign key - and can guarantee that will never happen - you won't be able to build the relationships automatically. The user must choose. However, #2 above is probably the easiest way to do that. Example:

I work with "Train" entities. Trains have a start location and an end location. So:

Table Train
Column #1 TrainID
Column #2 StartLocationID --FK to Location
Column #3 EndLocationID --FK to Location

For your type of user I would recommend showing the following tables

Table Trains
Table StartLocations
Table EndLocations

Thus, the user selects which table "instance" he wants and you have your relationship.

For example

User is presented with a list of entities to be reported on User selects 1 or more entities User is then presented with each entity they have selected and the fields associated with that entity User selects the fields they want to report on from each entity

User selects generate report (I'm not concerned with aggregates and filter just yet, just trying to get basics down first) At this point the relations betweeen the entities are created

I hope I'm making sense here. Is this a logical process to follow for a reporting infrastructure.

Yea, you've got it. The difference is that for advanced users I would show each Table and require them to choose the relationship, set the table alias, field alias, etc, for normal users, I would show a table instance for each table represented by a relation.

Of course, honestly, I have to keep going back to pre-building queries and allowing them to change the criteria. Just depends on how much flexibility they truly need.

Jeff...

Thanks once again. hmmm I think I see your approach now, so if I was building a wizard the user would do the following:

User selects the first entity Based on the entity selected, show the related entities user selects the related entities, so on and so forth

Is this correct?

Crap, I really need to see what this would look like from a UI perspective You have given me something to think about, Thanks.

Eric

billb wrote:

I've read a few threads on folks thinking about/desigining/developing an ad hoc query tool. I'm in pretty dire need of a utility that will allow a user who can't even spell database to be able to pick and choose fields that they want (there's a lot of relationships that I'd like to hide from them as much as possible) and be able to export the results to an Excel or comma delimited file. There also needs to be pretty basic filters (equal to, greater than, etc).

The software that I wrote for this customer uses LLBLGen if that makes any difference.

I don't need reporting, just query ability. Can anyone provide any insight? Do you have such a tool? Would creating one in LLBLGen be relatively simple?

Thanks for any help.

Perhaps this message can help you: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=332#1464

It's a setup for a generic search form (winforms), it builds the search for entities of a certain type. Perhaps you can use it to get started

Just as a followup, we found a tool that will work really well given the situation. Microsoft Access. Not sure why we didn't think of this before. Perhaps this is why I had such difficulty finding an ad hoc query tool.