Wednesday, March 21, 2012
Reports Use one Model dynamically connected to several databases with identical schema
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 -
>
>
Friday, March 9, 2012
Reportmodel data incorrect
Hi,
I have a very weird and frustrating problem at a customer. Since two months we are busy developing a report model, so users are able to create their own reports using the reportbuilder.
Suddenly the 1:n relations seem to be screwed! If I have a simple relation between entities, like a customer can have optional multiple invoices and a invoice has always one customer..
When in reportbuilder I select the customername, invoicenumber and #Invoices, then I expect there is only one invoice per invoicenumber. (When I select this in a query myself it is the truth...) But, the reportbuilder gives 2 as a result. This suddenly happens to all my 1:n roles.
It's not only in the #invoices fields, even when i select invoice amount this amount is being multiplied by 2.
I opened a backup of my project from one month ago, with the same relation in it and try to make the same report and it gave me the right results. But ofcourse I don't want to waste a month works, so I need to get this model working!
HELP!!!!
Thanks in advance
Julian
My fault... If i selected the relation entity and tested the results I would have seen each relation is mentioned twice.Monday, February 20, 2012
Reporting Services/BI Model processing
this time we are most interested in the Business Intelligence functionality
included in SS2K5. While we were testing this product we had everything all
on the same box...SS, Reporting Services, web tools...everything. Now, we are
scaling out the project and are trying to determine where the processing of
the reports and BI models takes place. Is it on the SQL Server box or on the
Reporting Services box?
Thanks in advance!!
--
ManuelRS is a aps.net application. It uses SQL Server databases for its object and
metadata storage. It stores reporting definitions, subscriptions, etc.
Rendering occurs in RAM. So, a big report can definitely really drag on the
server. Then, if your data is also coming from SQL Server then SQL Server DB
engine has the work to retrieve the data. This is totally separate from the
work that RS is doing.
When RS is put on its own server you still need to have a SQL Server license
for the box. It doesn't matter where the RS databases reside (ReportServer
and ReportServerTempDB). My advice is, unless you are doing a web farm,
install the SQL Server database for the metadata storage on the box that
will run RS. Then keep the data you are reporting off of on a second box. In
that way, the temp data storage, the report definitions, etc are close at
hand. And, a big report won't slow down your database server which contains
your data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mlg1906" <mlg1906@.discussions.microsoft.com> wrote in message
news:256AEE4E-AC81-4388-A20B-67247EE7EA59@.microsoft.com...
> My company is in the process of migrating to SQL Reporting Services 2005.
> At
> this time we are most interested in the Business Intelligence
> functionality
> included in SS2K5. While we were testing this product we had everything
> all
> on the same box...SS, Reporting Services, web tools...everything. Now, we
> are
> scaling out the project and are trying to determine where the processing
> of
> the reports and BI models takes place. Is it on the SQL Server box or on
> the
> Reporting Services box?
> Thanks in advance!!
> --
> Manuel
>|||We're a lot clearer on what our approach will be. Thanks a million!
--
Manuel
"Bruce L-C [MVP]" wrote:
> RS is a aps.net application. It uses SQL Server databases for its object and
> metadata storage. It stores reporting definitions, subscriptions, etc.
> Rendering occurs in RAM. So, a big report can definitely really drag on the
> server. Then, if your data is also coming from SQL Server then SQL Server DB
> engine has the work to retrieve the data. This is totally separate from the
> work that RS is doing.
> When RS is put on its own server you still need to have a SQL Server license
> for the box. It doesn't matter where the RS databases reside (ReportServer
> and ReportServerTempDB). My advice is, unless you are doing a web farm,
> install the SQL Server database for the metadata storage on the box that
> will run RS. Then keep the data you are reporting off of on a second box. In
> that way, the temp data storage, the report definitions, etc are close at
> hand. And, a big report won't slow down your database server which contains
> your data.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "mlg1906" <mlg1906@.discussions.microsoft.com> wrote in message
> news:256AEE4E-AC81-4388-A20B-67247EE7EA59@.microsoft.com...
> > My company is in the process of migrating to SQL Reporting Services 2005.
> > At
> > this time we are most interested in the Business Intelligence
> > functionality
> > included in SS2K5. While we were testing this product we had everything
> > all
> > on the same box...SS, Reporting Services, web tools...everything. Now, we
> > are
> > scaling out the project and are trying to determine where the processing
> > of
> > the reports and BI models takes place. Is it on the SQL Server box or on
> > the
> > Reporting Services box?
> >
> > Thanks in advance!!
> > --
> > Manuel
> >
>
>