Reports Use one Model dynamically connected to several databases with identical schema

We have several archived databases with identical schema. Is it
possible to develop a single SQL Server 2005 report model such that
the user can generate reports by picking which database to connect to?
The report model are only based on the schema of one database. It is
not required to cross reference data from different archived
databases.
We have to support multiple users such that we cannot rename the
database while the report is generated.
Thanks in advance.
SimonEven if you manage to do for all databases, how to give an option of
selecting the database and binding to the model ? Not possible.
My suggestion is you can create the same model and copy that and change the
database it is pointing and the name. e.g
Data 2003, Data 2004, Data 2005 some thing like this.
When the user goes to the report model designer he can see all the model
available, let him select whichever he wants and creates a report.
Amarnath, MCTS.
"Simon" wrote:
> We have several archived databases with identical schema. Is it
> possible to develop a single SQL Server 2005 report model such that
> the user can generate reports by picking which database to connect to?
> The report model are only based on the schema of one database. It is
> not required to cross reference data from different archived
> databases.
> We have to support multiple users such that we cannot rename the
> database while the report is generated.
> Thanks in advance.
> Simon
>|||On Mar 16, 3:03 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
The issue is that the archives are not limited to yearly archive. The
user can create an archive at will.
I tried to use "Data Source Expressions" but failed because that data
source expression can not be used in a shared data source.
Simon
> Even if you manage to do for all databases, how to give an option of
> selecting the database and binding to the model ? Not possible.
> My suggestion is you can create the same model and copy that and change the
> database it is pointing and the name. e.g
> Data 2003, Data 2004, Data 2005 some thing like this.
> When the user goes to the report model designer he can see all the model
> available, let him select whichever he wants and creates a report.
> Amarnath, MCTS.
>
> "Simon" wrote:
> > We have several archived databases with identical schema. Is it
> > possible to develop a single SQL Server 2005 report model such that
> > the user can generate reports by picking which database to connect to?
> > The report model are only based on the schema of one database. It is
> > not required to cross reference data from different archived
> > databases.
> > We have to support multiple users such that we cannot rename the
> > database while the report is generated.
> > Thanks in advance.
> > Simon- Hide quoted text -
> - Show quoted text -|||Simon,
Infact you need to have report model for RB, it means the RModel should be
fixed with all possible links with tables, so that the user can select any
field. So RModel cannot be on the fly created by the user, unless you create
a custom code, which will be difficult, and I hope that should not be the
options, due to difficulty..
Amarnath, MCTS.
"Simon" wrote:
> On Mar 16, 3:03 am, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
> The issue is that the archives are not limited to yearly archive. The
> user can create an archive at will.
> I tried to use "Data Source Expressions" but failed because that data
> source expression can not be used in a shared data source.
>
> Simon
> > Even if you manage to do for all databases, how to give an option of
> > selecting the database and binding to the model ? Not possible.
> >
> > My suggestion is you can create the same model and copy that and change the
> > database it is pointing and the name. e.g
> > Data 2003, Data 2004, Data 2005 some thing like this.
> >
> > When the user goes to the report model designer he can see all the model
> > available, let him select whichever he wants and creates a report.
> >
> > Amarnath, MCTS.
> >
> >
> >
> > "Simon" wrote:
> > > We have several archived databases with identical schema. Is it
> > > possible to develop a single SQL Server 2005 report model such that
> > > the user can generate reports by picking which database to connect to?
> >
> > > The report model are only based on the schema of one database. It is
> > > not required to cross reference data from different archived
> > > databases.
> >
> > > We have to support multiple users such that we cannot rename the
> > > database while the report is generated.
> >
> > > Thanks in advance.
> > > Simon- Hide quoted text -
> >
> > - Show quoted text -
>
>