Complex report question

[AUGuy]

I've got a large amount of reports that I generate on a monthly basis. As it currently stands, it is clunky, large, and takes a while to process. It's actually about 90 reports, each Report makes a page in the actual report I present to management. It's a portfolio report that shows asset counts/balances/totals for each region/division/asset status. Most reports have a coinciding pie chart/graph representing the numerical data at the bottom.

Currently this is calculated through a myriad of queries for each region/division/status. For instance, the report that shows Count, Balance, and Percentage of Portfolio has a query for each chart and a subreport to show the amounts/totals below.

I'm investigating a rebuild of this database. All of the data that is pulled into the reports originates in a single table that is an export from our primary database. It's basically the entire portfolio. It is a increasingly large table with a date stamp field at the end to show which month each line corresponds to and is currently 246,000 records.

this database is not multi-user, I'm really the only person who uses it. It doesn't have to have a fancy front end or anything like that. I'm mainly interested in different ways of reducing the number of reports/queries needed to build this 90 page monstrocity.

Any suggestions?

 

[spikepl]

What is the issue prompting you to a rewrite? Speed? Maintenance/changes?

 

[AUGuy]

It's cumbersome to produce and make changes to a report if it's required. I'm concerned that should someone else need to take this over it could be a nightmare for them. I know it currently like the back of my hand, but it's not exactly simple to understand all the moving parts at first glance.

For instance, say they'd like to see a different calculation for a slide that shows historical trend by division, i have to go to each report for each division and make the change. There's really only about 18 unique report configurations, the massive number comes from each division having their own reports/queries to produce those 18 reports.

There are some speed concerns that could arise later down the road if the business need to have these reports remains another year or so (we'd be well over 500k records in the primary table by then). But it's not a huge concern at the moment.

 

[spikepl]

Ok ..

There's really only about 18 unique report configurations, the massive number comes from each division having their own reports/queries to produce those 18 reports.

So how do the above differ? Same type of data, same treatment of data, but only the "owner" of the report differs? If so, then this seems to, at least partially, answer your question, doesn't it?

 

[AUGuy]

The data queried is specific to the division, so you'd have for instance a Managed Portfolio report that has all 8 or so divisions totaled and then the same report for each individual division.

I guess i'm not following what you're inferring as far as being partially the answer. The treatment of the data varies with division/region/etc. But yes, it all originates from the same point (initially).

 

[spikepl]

So if the treatment varies with division/region/"etc", and IF the output also is different, how do you intend to go from dedicated queries/reports for each case to something else? I.e. if there is NOTHING in common in the treatment, then the only change you can bring about is a pile of code that adopts some template-like query/report to the task at hand (instead of your current dedicated solutions) for each destination. And that can get messy too.

If there are any common parts, then that is the area to look into.

I am not sure what you expect in the line of miracles, since you know the app, and we don't

 

[AUGuy]

Nope, you're basically spot on. I just didn't know if there was something I was completely missing by doing it the way I currently was (that would also be less complicated). My other line of thinking was using code to do it, but I quickly realized that while the query pane would look much cleaner and smaller, it'd be along the lines of putting lipstick on a pig!

That's kind of what I figured, but I thought i'd might as well ask to see if someone could provide the said miracle

Thanks!

 

[spikepl]

Just hang in here - maybe someone else gets a brain wave.