Generated Report Model

I'm working on a software project that requires a reporting solution.
Of all the solutions we have looked at RS2005 seems to fit best with
our product, however, I had a few queries.
1. Our data is stored in a non-relation format but for reporting the
end user can select what data the wish to report on and a relational
table model is created. Because the table structure is not known until
this point, is it possible to create a Report Data Model in code so
that we can use the end user report designer.
2. Because of the JIT generation of the relational tables, the table
names are not known until the report is requested, is it possible to
pass the table names into the report as parameters? Is it possible to
use these parameters in th Report Data Model?
3. Is it possible to get column totals and group sub-totals in reports
designed in the Report Builder end user designer?
4. Is it (going to be) possible to design free flow reports in the
Report Builder rather than just table, matrix or chart reports?
Many thanks,
Adrian RitchieHi Adrian,
not knowing anything at all about your project...have you considered
using a separate data warehouse for the reporting solution and
propagating changes from the operational system to the BI System so
that you have access to a relational database for reporting...also,
your reporting and analysis needs are very likely to grow past simple
reporting, and I would think you are building in rather sever
limitations trying to write a JIT style application to generate tables
for reporting....I do not see how it could possibly scale as you must
JIT push data into tables when a report is requested rather than just
reading a table that is already there...'
Peter
www.peternolan.com|||Hi Peter,
I realise that my explaination of our system is rather vague but that
was as much as I could really write without breaching my NDA :(
Having a separate data warehouse sounds like a good idea and I will run
it past some of the other guys here but I fear may not be practical.
The reason the relational model is not generated until reports are
requested is that:
1. The data storage structure is flexible. We ship out a standard
structure but clients can modify and add to it as they see fit (with
appropriate security rights).
2. The data in the system is temporal. The end user can request a
report for any point in time for which there is data in the system.
The point in time that the user selects will determine what database
structure they get (as the structure may change over time).
The second problem may not be a problem at all, it's just that I
haven't thought of another solution for it, so any suggestions will be
greatfully received.
Adrian Ritchie|||Hi Adrian,
I understand the world of NDAs...no problems...
1. Changes to source data structures for applications rolled out is
always a problem but not a hard one to solve...
2. All data structures are 'temporal' it's just that most people don't
know it and think they don't change. They do and the analysis system
must take care of that...
I've done systems easily handling both 1 and 2......so I still think
that unless you have some really good reasons for building the
limitations into your reporting system that you very likely will do
taking a look at something separate would be a good idea...Like I said,
I know nothing about what you are doing...so these are just my
thoughts...
Peter
www.peternolan.com