dynamic reporting in an asp.net app -- how to?

[Ray Porter]

Hi all,
I'm in the process of re-implementing and ISAPI -based web app as an asp.net
app. The application allows the user to enter a number of parameters, some
of which are optional, then queries the database and returns the data in an
Excel sheet. The database is Oracle 10G. So I need to be able to collect
user entered data at runtime, build my SQL statement on the fly, then
generate a report based on that SQL statement. All of the examples I've
found with Crystal and MS Reports show a dataset created at design time.

Is there a step-by-step instruction set somewhere for doing this all at
runtime, based on dynamically created datasets? I don't mind designing the
report at design time but I need to be able to actually populate the dataset
based on a dynamically created SQL statement.

Thanks,
=================================
Ray Porter
Information Systems
Applications Development Manager
Division of University Advancement
University of North Carolina at Chapel Hill
Phone: (919) 259-9389
Fax: (919) 843-3314
Pager: (919) 216-4218

(e-mail address removed)
http://www.unc.edu/~dragon

Meddle not in the affairs of dragons for thou
art crunchy and taste good with ketchup

 

[Patrice]

My understanding is that you need to return aribtrary data using the Excel
format ? Crystal and RS are rather devoted to making reports on known data.

If you have simple needs you could create the file directly (as an XML or
HTML file that Excel would read).
With RS you could create a report and then run this report (report
description uses an XML based format).

Or elaborate a bit. For now I'm not sure why you could build the report at
design time (so the report would used always the same columns) but not the
SQL part (this is just because of the criteria ?)

 

[Ray Porter]

Or elaborate a bit. For now I'm not sure why you could build the report at
design time (so the report would used always the same columns) but not the
SQL part (this is just because of the criteria ?)

Yes, Patrice. The columns in the SELECT statement are static. The problem
is that the WHERE clause will vary depending on what the user enters on the
web page.

This is a fairly common scenario that I've dealt with in other programming
environments so I'm sure there's some way to do it in .Net world -- I'm just
too new to .Net to know the best approach yet.

Thanks,
Ray

 

[Patrice]

Ok so from a general point of view and in the worst case it could be done at
the SQL side :

http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

Now if using Reporting Services and the selection is quite simple it could
be not necessary. A parameter can be multivalued and taken from another
SELECT. The "select all" option collects all values so in all cases using an
IN clause is enough :

http://msdn.microsoft.com/en-us/library/aa337292.aspx

Don't know about Crystal (we stopped at 9.5) but likely similar...