AdHoc reporting against SQL 2K w/meta data?

Okay... We have a SQL2K database that has about 500 tables or so. It is normalized to a reasonable level and enforces all relationships with PK/FKs, not triggers. Hence, for a database-minded person it is fairly easy to read (as easy as a 500+ table database can be!).

Our users need adhoc query capabilities. Our report writer is simply overwhelmed. He doesn't need to be spending time writing a report that is intended to be run once.

I expect the best alternative would be to use some sort of adhoc reporting tool that is based off meta data. We (the DBAs) could be responsible for maintaining the meta data and STILL have a manhour savings over developing all these reports.

Here's the catch... We are on a TIGHT budget (aerospace industry is still reeling a bit). Is anyone using a product or aware of a product that might be just the ticket for us? We have been investigating a product by LogiXML called LGX AdHoc (http://www.logixml.com/products/AdHoc/adhoc.htm). Looks promising. Anyone use or familar with it?Can you describe what it does (I didn't find their eval)? I am using ActiveReport from DataDynamics, but it does require some knowledge and time to spend designing reports.sql

adding up fields

My problem right now is I have three layers in my sheet.
Objective level
measure level
region level
facility level
The facility level is not a problem, just a Field!Facility.Value, it's a
straight pull from the db.
Region is fine, it's a sum(Field!Facility.Value)
Measure is a bit tricky, but it's fine - (Sum(Field!PassFail.Value)/Sum(Field!
TotalFacility))*Field!MeasureScore.Value
My problem now is that when you add all the measures, they should equal the
Objective score. So say I have 2 measures that work out to
.61
.24
my objective score should be .85
The problem is, I can't sum the measure scores because I can't Sum an
aggregate function.
So, I changed one of my views so that I could calculate a measure score in
the datafield and just pull it to the spreadsheet. The problem is, when I
Sum these fields, it works for adding up ALL of the objectives, not for each
objective. And when I put in more than once facility, it adds up the scores
for all of the objectives for all of the facilities.
My guess is that I am grouping incorrectly in the view. I'm pulling a bunch
of columns into my view and grouping on them. Is it possible to pull a
column into a select statement without grouping by it? I am grouping by
region, facility, objective and measure where I think I should just be
grouping by objective. When I try to just group by one column, I get errors
all over the place.
thanks for any help you can give.Okay, so I have four levels and not three:) Shows just how confused and
frustrated I am :)
ravensensei wrote:
>My problem right now is I have three layers in my sheet.
>Objective level
>measure level
>region level
>facility level
>The facility level is not a problem, just a Field!Facility.Value, it's a
>straight pull from the db.
>Region is fine, it's a sum(Field!Facility.Value)
>Measure is a bit tricky, but it's fine - (Sum(Field!PassFail.Value)/Sum(Field!
>TotalFacility))*Field!MeasureScore.Value
>My problem now is that when you add all the measures, they should equal the
>Objective score. So say I have 2 measures that work out to
>.61
>.24
>my objective score should be .85
>The problem is, I can't sum the measure scores because I can't Sum an
>aggregate function.
>So, I changed one of my views so that I could calculate a measure score in
>the datafield and just pull it to the spreadsheet. The problem is, when I
>Sum these fields, it works for adding up ALL of the objectives, not for each
>objective. And when I put in more than once facility, it adds up the scores
>for all of the objectives for all of the facilities.
>My guess is that I am grouping incorrectly in the view. I'm pulling a bunch
>of columns into my view and grouping on them. Is it possible to pull a
>column into a select statement without grouping by it? I am grouping by
>region, facility, objective and measure where I think I should just be
>grouping by objective. When I try to just group by one column, I get errors
>all over the place.
>thanks for any help you can give.