views:

497

answers:

2

I don't have much experience managing reports, but I work on an application that works like this:

There is an interface for the users to select a report they want to view from a list of reports. This list is populated by a reports table in the database which holds info like the report name, the file name, and which parameters the report takes.

There are several common parameters in these reports -- they are ranges, or sorting -- so the user might pull up a report about all the widgets numbered 200 through 4000, grouped by Foo.

Right now a person develops a stored procedure, and the report file at the same time. They pass it off to me, and I have to deploy it by running the sproc script in production, and moving the .rpt file to the right directory. I then have to insert a record into the reports table with the name, filename, and parameters.

This is a logistical challenge because there's no great way to keep track of which reports have been deployed onto which systems (just because they are present, doesn't mean they are updated, there are 4 systems total that ideally should match), and there are several points of failure possible:

1) The params in the sproc don't match the params in the .rpt file 2) The params in the reports table don't match the params in the .rpt or sproc 3) The sproc is updated while the .rpt file isn't for whatever reason 4) What happens when a new report needs a parameter that hasn't been coded for in the params page?

It all boils down to the system not being dynamic enough. Like I said, I don't know about reporting, but I have a feeling that the guy doing the reports doesn't either. It seems like there must be a better way to keep the sproc and .rpt file in sync, and to dynamically ask for the parameters it needs through something like Reflection.

How is this normally handled?

A: 

I don't know about "normally", but our application binds the report up together with the stored procedure SQL and the configurable parameters in a single file. Our report generator application can then deserialize everything needed to run the report from a single package. The indexing required is limited to knowing a list of valid filenames and associated friendly report titles to display to the user.

This is done in C++/MFC using stock MFC serialization; I'm sure that as you say, with reflection and XML serialization an even slicker solution would be possible in a C#/.NET environment.

mwigdahl
+1  A: 

We simply use the default crystal parameter prompting engine. Otherwise, our reporting solution is basically the same as the one you described.

It doesn't make sense to put the parameters into a table. These can be retrieved by loading the ReportDocument and reading the parameters collection. Your prompting engine should look here and then generate the prompts. Otherwise, it's just too much to keep in sync.

dotjoe