Enduser based ad-hoc report writer?

Question

Enduser based ad-hoc report writer?

JBZ-189141

SSC Enthusiast

Points: 128
More actions
November 13, 2004 at 2:37 pm

#102270

Can anyone recommend an ad-hoc reporting tool that I can give to my end users to create their own reports?
I need something that will allow them to pick and choose fields for a report and will not require them to know data structure or how to write sql select statements.
I've done some searching on the web and have only found one program, Stonefield Query.
Anyone heard of this? Any other recommendations?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ian Goethert Say Hey Kid Points: 691 More actions · Answer 1

Crystal Reports (8.5) has a feature called Crystal Dictionaries that may help.

Note that Crystal was purchased by Business Objects earlier this year. I'm not sure if they plan to continue with the product line.

Hope this helps.

From the Crystal help file, Dictionary Overview:

"A dictionary is a structured and simplified view of data that you can create for some or all of the individuals in your organization that are using Crystal Reports.
Unlike some systems that force users to access data through a data distribution metalayer, dictionaries are optional components. Data can still be accessed directly by the user. Dictionaries simply provide all of the convenience without the restrictions.
Dictionaries allow you to:
design a single, dynamic view of all the data that is necessary to create reports and queries
organize the data and rename tables and fields to make it easier for users to understand the content and purpose of the data
create complex data-manipulation formulas that users can access without the need to understand formula concepts.
Note: The Formula Editor used in Crystal Dictionaries is not as complete as the one used in Crystal Reports.
Dictionaries reduce data misuse, loss, and damage. When a Dictionary is used to create a report, the only data used in the report is the data accessed through the Dictionary. You can not use a Dictionary and some other data source in the same report. Because Dictionaries are often used to impose data security, it would breach that security to allow unrestricted data access in a Dictionary report.
Note: You can include a subreport based on a different data source in a primary report based on a Dictionary.
Finally, Dictionaries provide an easy means of changing the underlying data set without changing the view of the data seen by users. You can change field and table names in the underlying data, for example. Then you remap the Dictionary to the new field and table names without changing the aliases assigned to the data. The users create their reports using the same data interface they've been using, never knowing what has changed under the surface.
Once created, the Dictionary acts as a filter, providing a view of complex data that is clear and easy for any user to understand."

Michael Roynan Old Hand Points: 357 More actions · Answer 2

Don't know if you sat in on the webcast from Microsoft a few weeks ago, but they plan to release a end-user ad-hoc tool for Reporting Services to allow end users to create their own reports. The tool does not require a .NET development tool as the current Reporting Services environment does; it is a stand alone tool. The user does not need to know the underlying data structures of the database or how to write queries; instead, 'business models' are defined base on the underlying structure and the reports are created off of that. The tool is supposed to be available with the RTM release of SQL 2005, I believe. That doesn't do you any good now, but it may be something to think about for the future.

Hope that helps.

JBZ-189141 SSC Enthusiast Points: 128 More actions · Answer 3

Thanks Ian. I found more info about Stonefield Query.

There are two parts to Stonefield Query Developer's Edition: the easy-to-use end-user reporting application (Stonefield Query) and the Configuration Utility that a technical person (developer, consultant, IT person, etc.) uses to customize Stonefield Query for the application's database.

You start by using the Configuration Utility to create a new Stonefield Query "project" (the set of data dictionary, configuration, and script files that make Stonefield Query specific for an application). Using the data dictionary "discovery" wizard, you can quickly load the data dictionary with the structures of your application's data. Stonefield Query can access pretty much any kind of database: SQL Server, Oracle, Access, DB2, MySQL, Visual FoxPro, dBase, Pervasive ... you name it. You then customize the data dictionary as necessary (for example, filling in descriptive captions for tables and fields).

Customizing the Data Dictionary

You can define virtual (also known as calculated) fields. For example, most order entry systems don't store the extended price for a line item because that value can be derived (unit price multiplied by quantity). However, the user may want to show the extended price on a report or even do a query on all items with an extended price greater than $50.00. You simply add a field to the data dictionary and specify that the calculation expression is unit price multiplied by quantity. As far as the user knows, it's just another field they can report on. You can even define "enumerated" fields, where the range of values for the field is a predefined list, or that a field from a related table should be display when the user selects the linking (foreign key) field.