Tom_W posted on Tuesday, September 15, 2009

Hi All

I know a lot of the reporting tools allow reports to be based on object collections rather than database tables, but does anyone know of a component that allows users to do Access/Enterprise Manager style ad-hoc query building from objects (rather than the underlying db tables)?

I'm talking about this kind of thing; http://msdn.microsoft.com/en-us/beginner/bb308906.vwd-3-1_big.gif

I've had a good scrub through ComponentSource etc but to no avail, which kind of surprises me.

The reason for this is two fold:

We want to allow users to do ad-hoc querying and ideally don't want to replicate all of the calculated totals baked into the business objects on the DB too (for example we don't want users to have to do the hard work of adding up payments against invoice lines to find out what's paid/owing on an invoice, when the Invoice classes already know these values)
We don't want to expose the DB on one of our apps to the users (21 CFR 11 compliance etc) particularly as the business objects already know all the rules about which user should be able to see which fields.

Any suggestions gratefully received!

Jack replied on Tuesday, September 15, 2009

you didn't say for what platform - but Aspose - Adhoc is out there for ASP and I believe the DataDynamics products might do something as well (web/win)

JonnyBee replied on Tuesday, September 15, 2009

Hi,

Yes, I'd give DataDynamics and ActiveReport my vote too. Used it some years ago and great product with developer only license - royaltyfree distribution.

Easy-to-Use End-User Reporting Tools

ActiveReports allows you to host the report designer in your application and distribute it royalty free. It also allows end-users to edit reports and to save and load report layouts. In addition, it allows you to monitor and control the design environment and customize the report's look and feel to meet the needs of your end users.

/jonnybee

Tom_W replied on Wednesday, September 16, 2009

Hi Both

Many thanks, should have said this is for Winforms.

I had a look at the Aspose control, but the VB.Net version doesn't support querying multiple tables (or in our case multiple collections).

I had a look at ActiveReports but I couldn't see a query builder tool.

I'm wondering if I may be better getting the ReadOnly list classes to output a datatable/dataset version of their contents and then using these as the basis of the queries. That might open up the option of a few more controls.

activescott replied on Thursday, September 24, 2009

With ActiveReports we don't include a query builder for objects. However, ActiveReports does allow binding directly to object/IList based datasources with it's IListDataSource. When you install ActiveReports a sample named IList Binding. A more complete description of the samples are available in a readme.html file within the sample folder. You can find more information about using ActiveReports in a completely "unbound mode" at http://www.datadynamics.com/Help/ActiveReports6/arWLKGroupOnUnboundFields.html

Our Data Dynamics Reports product may also be interesting to you. It also doesn't include a query builder feature exactly, but you -the developer- can define the fields available in the underlying object data source at design time (see http://www.datadynamics.com/Help/ddReports/ddrdconDatasetFieldsTab.htmlfor more information). Then you're end users can select which fields appear in the report using the built-in UI as shown in the following two links: http://www.datadynamics.com/Help/ddReports/ddrconFieldSelection.html

For information on getting the data into the report see the "Object Provider" section at the following link: http://www.datadynamics.com/Help/ddReports/ddrconDataSetAndObjectDataSource.html Visit our support forums at www.datadynamics.com if you have more questions.

Hope this helps!

Scott Willeke

GrapeCity / Data Dynamics

Tom_W replied on Thursday, September 24, 2009

Hi Scott,

Thanks for that, it looks interesting. Do either of those solutions allow the user to define 'joins' at all? (I.e. to join across object collections rather than db tables?)

activescott replied on Monday, October 05, 2009

We are considering some features around this scenario but at this time we don't have a way to define a join between collections of objects. I'd suggest you load your data into a DataSet or you can setup a hierarchy in your classes so that you can use the nested property syntax for referencing nested objects in properties of the outer object as shown in the example column of the following page: http://www.datadynamics.com/Help/ddReports/ddrdconDatasetFieldsTab.html

On the other hand maybe a sophisticated query builder (or a custom one) is more appropriate for your needs.