The requirement
Produce Reports from a Database or Data Warehouse on a daily basis, using parameterized queries.

The question is: what is the best tool for this kind of job?

The starting list contained :-
1) Crystal Reports 4.5 for Visual Studio 6
2) Crystal Reports for Visual Studio .NET 2003
3) Microsoft Access built-in reports

Unfortunately, it was not that easy and a major disappointment was that
Crystal Reports was the most inadequate of them all.

The sticky point seems to be a PARAMETERIZED query, or, better yet,
a couple of parameterized queries.
Both Crystal, and Access internal reports seem to have some problem with these.

Here are the details ...

The queries are parameterized by 3 variables - time zone, start date and end date.

Time Zone is a thing that goes into SELECT part,
(as in SELECT QueuedAt + TimeZone AS QueuedAtLocal), so one cannot set it up via
Filter property or something like this.

1. Crystal Reports for Visual Studio .NET 2003

This one was most disappointing. First of all, there is no "native" version,
only .NET version. This means, you need to isntall 18MB .NET framework
on the server just to print a report.

Second of all, when you build a report, it does not even show queries, only tables.
The only option seems to be to type in the SQL manually.

2. Microsoft Access reports (+VBScript).
The approach was to use VBSript to start Access, open the reportand print it.

The big problem was that when report is based on a parameterized query,
Access will always prompt for query parameters when you open the report.
So, I created a report on non-parameterized query, and changed SQL
in run-time (Report.RecordSource property). This requires full Access
to be installed on the server. Report viewer is not enough. This is because
RecordSource is design-time property, so the script basically changes
the report with every run.

3. Crystal Reports 4.5
This is relatively easy to use and understands queries.


Evaluation of a range of Enterprise Reporting Products.

The requirement is to produce fixed format reports (PDF's) through a
number of bespoke applications that extract data from a Database into Spreadsheets.

These reports contain a range of complex numerical calculates and
aggregations that are currently embedded into Excel.

All the calculations to be performed are within Microsoft Analysis Services
using calculated members and an external DLL, and this can be extracted using
MDX in the correct format.

Finally we need a report designer tool that allows grids / tables and charts
to be produced based on data from Analysis Services.

With MS Analytical Server and MDX an obvious option is to wait till Reporting
Services in SQL server 2000 will be available.

It will support both push reports (for example Mail based - "dumb reports" in
formats like adobe), and pull based client-server technology for
drill-down slice and dice etc.

End users can have very thin clients (XML) or OWC for moving the reports directly
into Excel as pivot-tables.

Reporting Services is aimed from simple to medium advanced users filling in
the very large gap in Microsoft's current reporting offerings on Analytical Server.

Crystal Reports combined with Crystal Analysis as a possible solution.

I realize that OLAP by definition is generally used for Ad-hoc, real-time reporting,
whereas we are looking to batch generate fixed format reports, but OLAP provides a
very rich set of statistical features that would be much harder to reproduce from
our source data.

Possibilities for Report Designer Tools for producing reports (some non-interactive,
fixed format) from OLAP data sources include ...



Crystal Reports is aimed at this sort of thing, but still seems not to handle OLAP
data sources very well.  Another similar product is WebFocus.

Alternatively, you could use an OLAP spreadsheet add-in to produce
well-formatted reports from Analysis Services, and distribute them as PDFs,
HTML pages or "deadsheets" (Excel spreadsheets without a live data
connection). The last approach is the easiest and also the most flexible as
it allows the recipients to re-use the data without re-keying.

A DIY approach is to write a tailored Data Layer to present the data to Crystal in a
more friendly format - (XML or ADO.NET) but this could add a great deal of complexity.

A Note on WebFocus

It comes from Information Builders Information Builders

It runs directly against 85 Database types with native data drivers and produces output reports in
Excel, Excel Pivot Table, PDF, html, XML, plain text and other formats.

It turns complex calculations directly into Excel formulas and it can provide drill-down directly into
operational data for more detailed reports.
It also supports Microsoft's Sharepoint Portal.
Intelligent Enterprise has published their review of WebFOCUS.

Oracle 9i OLAP
Oracle9i OLAP includes a powerful multidimensional calculation capability.
If it can be done with MDX, it can be done with 9i OLAP.

It is also good for this reporting requirement, which has just historically been quite
tricky to do with previous generation standalone OLAP Servers with proprietary APIs
designed for ad hoc queries.

This is because all the calculations available in 9iOLAP can be queried by any
Report Writer that can query an Oracle9i Database with simple SQL.
The calculations are declared as simple formulae or functions in the server
(using very simple syntax), and made to appear as if they are fully
calculated, fully solved columns in a relational view or table function.

The reporting tool simply SELECTs the columns required and formats them accordingly.
No fancy calculations required inside the query, just a simple SELECT.

Of course, Oracle Reports is one such product and provides output in PDF
form as you would expect.

The advantages here are many and various, but doing this the Oracle way
pushes the calculation functionality totally into the server engine, and
does not require the construction of tricky MDX (or even tricky SQL), so the
calculations are available to any SQL tool or application without modification.

You can hold your data in a secure and scalable Oracle database and avoid the
cost and complexity of transferring it and duplicating it in a standalone OLAP
Server (such as Express, Analysis Services etc), while having access to an
exceptionally powerful OLAP calculation engine, right inside the database.

Another DIY Solution
Maybe try combining XML for Analysis to retrieve the desired OLAP data and then
transform (XSLT) this into a nice friendly XML data source for Crystal Reports.

Although Crystals support for XML isn't amazing (better than it's OLAP support)
we can define very flexible and complex datasets via MDX.

Another Commercial Solution
O2O OLAP extends Microsoft Analysis Services (OLAP) and SQL Server with
some key capabilities for finance such as spreadsheet writeback, and is easy to use for business analysis.

O2OLAP provides a highly flexible, productive environment to enable finance specialists
to be independently productive without having to understand underlying complexities.