Monday, March 19, 2012
I want separate data sources for 1 RDL file
Here's my situation:
I have a single RDL file that describes a certain report.
I have different databases on different computers with the same tables
but different data in each table.
I need people to go to each computer, run the same RDL file, but have
it show the data that is specific to their database.
For instance, there is a single .rdl file which describes a personnel
report, and i want people at site A to only see info about site A's
personnel, and people at site B to only see info about site B's
personnel.
Is this possible? If so, is there any advice you can give me to help do
this? Thank you.
Kevin Green
Software Engineer
Pragmatech SoftwareOn each computer have an rds file specifying the connection properties for
that computer.
"KevinGreen24@.gmail.com" wrote:
> Hello all,
> Here's my situation:
> I have a single RDL file that describes a certain report.
> I have different databases on different computers with the same tables
> but different data in each table.
> I need people to go to each computer, run the same RDL file, but have
> it show the data that is specific to their database.
> For instance, there is a single .rdl file which describes a personnel
> report, and i want people at site A to only see info about site A's
> personnel, and people at site B to only see info about site B's
> personnel.
> Is this possible? If so, is there any advice you can give me to help do
> this? Thank you.
> Kevin Green
> Software Engineer
> Pragmatech Software
>|||Well we're working of a SaaS model, so every end user is running a thin
client, so we're not installing anything on the end user's computer.
magendo_man wrote:
> On each computer have an rds file specifying the connection properties for
> that computer.
> "KevinGreen24@.gmail.com" wrote:
> > Hello all,
> >
> > Here's my situation:
> >
> > I have a single RDL file that describes a certain report.
> >
> > I have different databases on different computers with the same tables
> > but different data in each table.
> >
> > I need people to go to each computer, run the same RDL file, but have
> > it show the data that is specific to their database.
> >
> > For instance, there is a single .rdl file which describes a personnel
> > report, and i want people at site A to only see info about site A's
> > personnel, and people at site B to only see info about site B's
> > personnel.
> >
> > Is this possible? If so, is there any advice you can give me to help do
> > this? Thank you.
> >
> > Kevin Green
> > Software Engineer
> > Pragmatech Software
> >
> >|||Are the databases on two different SQL servers? How do the users run the
report? From Report Manager, or embedded in a web page?
If they are running the reports from Report Manager on different servers
then you need to set up data sources (i.e. rds files) on each, with the same
name, but pointing to the site-specific database.
"KevinGreen24@.gmail.com" wrote:
> Well we're working of a SaaS model, so every end user is running a thin
> client, so we're not installing anything on the end user's computer.
> magendo_man wrote:
> > On each computer have an rds file specifying the connection properties for
> > that computer.
> >
> > "KevinGreen24@.gmail.com" wrote:
> >
> > > Hello all,
> > >
> > > Here's my situation:
> > >
> > > I have a single RDL file that describes a certain report.
> > >
> > > I have different databases on different computers with the same tables
> > > but different data in each table.
> > >
> > > I need people to go to each computer, run the same RDL file, but have
> > > it show the data that is specific to their database.
> > >
> > > For instance, there is a single .rdl file which describes a personnel
> > > report, and i want people at site A to only see info about site A's
> > > personnel, and people at site B to only see info about site B's
> > > personnel.
> > >
> > > Is this possible? If so, is there any advice you can give me to help do
> > > this? Thank you.
> > >
> > > Kevin Green
> > > Software Engineer
> > > Pragmatech Software
> > >
> > >
>|||Also in RS 2005 you can dynamically change the datasource based on the user.
I haven't done this but it is one of the capabilities.
Bruce Loehle-Conger
"magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
news:BCD91F31-A84B-40D2-8B00-2FB36C53CD52@.microsoft.com...
> Are the databases on two different SQL servers? How do the users run the
> report? From Report Manager, or embedded in a web page?
> If they are running the reports from Report Manager on different servers
> then you need to set up data sources (i.e. rds files) on each, with the
> same
> name, but pointing to the site-specific database.
> "KevinGreen24@.gmail.com" wrote:
>> Well we're working of a SaaS model, so every end user is running a thin
>> client, so we're not installing anything on the end user's computer.
>> magendo_man wrote:
>> > On each computer have an rds file specifying the connection properties
>> > for
>> > that computer.
>> >
>> > "KevinGreen24@.gmail.com" wrote:
>> >
>> > > Hello all,
>> > >
>> > > Here's my situation:
>> > >
>> > > I have a single RDL file that describes a certain report.
>> > >
>> > > I have different databases on different computers with the same
>> > > tables
>> > > but different data in each table.
>> > >
>> > > I need people to go to each computer, run the same RDL file, but have
>> > > it show the data that is specific to their database.
>> > >
>> > > For instance, there is a single .rdl file which describes a personnel
>> > > report, and i want people at site A to only see info about site A's
>> > > personnel, and people at site B to only see info about site B's
>> > > personnel.
>> > >
>> > > Is this possible? If so, is there any advice you can give me to help
>> > > do
>> > > this? Thank you.
>> > >
>> > > Kevin Green
>> > > Software Engineer
>> > > Pragmatech Software
>> > >
>> > >
>>|||We host 1 report server which each client browser will access. We host
1 database server which has databases which hold the information for
each company that uses our product. So basically we host the data for
each company and we host the report server.
Each client browser will connect to the report server, and at that time
the appropriate company database will be accessed and the information
retrieved, along with the common .rdl file, and the appropriate report
rendered.
So there's 1 .rdl file which describes the form of the report, and
there are N databases for the N companies that use the product, which
hold the data. I need the 1 .rdl file to be able to display information
from any of the N databases that we have, based on the company that's
trying to access the report.
Does this make sense?
magendo_man wrote:
> Are the databases on two different SQL servers? How do the users run the
> report? From Report Manager, or embedded in a web page?
> If they are running the reports from Report Manager on different servers
> then you need to set up data sources (i.e. rds files) on each, with the same
> name, but pointing to the site-specific database.
> "KevinGreen24@.gmail.com" wrote:
> > Well we're working of a SaaS model, so every end user is running a thin
> > client, so we're not installing anything on the end user's computer.
> >
> > magendo_man wrote:
> > > On each computer have an rds file specifying the connection properties for
> > > that computer.
> > >
> > > "KevinGreen24@.gmail.com" wrote:
> > >
> > > > Hello all,
> > > >
> > > > Here's my situation:
> > > >
> > > > I have a single RDL file that describes a certain report.
> > > >
> > > > I have different databases on different computers with the same tables
> > > > but different data in each table.
> > > >
> > > > I need people to go to each computer, run the same RDL file, but have
> > > > it show the data that is specific to their database.
> > > >
> > > > For instance, there is a single .rdl file which describes a personnel
> > > > report, and i want people at site A to only see info about site A's
> > > > personnel, and people at site B to only see info about site B's
> > > > personnel.
> > > >
> > > > Is this possible? If so, is there any advice you can give me to help do
> > > > this? Thank you.
> > > >
> > > > Kevin Green
> > > > Software Engineer
> > > > Pragmatech Software
> > > >
> > > >
> >
> >|||That's exactly what i need, but i can't figure out how!
It also has to be secure of course.
Bruce L-C [MVP] wrote:
> Also in RS 2005 you can dynamically change the datasource based on the user.
> I haven't done this but it is one of the capabilities.
> Bruce Loehle-Conger
> "magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
> news:BCD91F31-A84B-40D2-8B00-2FB36C53CD52@.microsoft.com...
> > Are the databases on two different SQL servers? How do the users run the
> > report? From Report Manager, or embedded in a web page?
> >
> > If they are running the reports from Report Manager on different servers
> > then you need to set up data sources (i.e. rds files) on each, with the
> > same
> > name, but pointing to the site-specific database.
> >
> > "KevinGreen24@.gmail.com" wrote:
> >
> >> Well we're working of a SaaS model, so every end user is running a thin
> >> client, so we're not installing anything on the end user's computer.
> >>
> >> magendo_man wrote:
> >> > On each computer have an rds file specifying the connection properties
> >> > for
> >> > that computer.
> >> >
> >> > "KevinGreen24@.gmail.com" wrote:
> >> >
> >> > > Hello all,
> >> > >
> >> > > Here's my situation:
> >> > >
> >> > > I have a single RDL file that describes a certain report.
> >> > >
> >> > > I have different databases on different computers with the same
> >> > > tables
> >> > > but different data in each table.
> >> > >
> >> > > I need people to go to each computer, run the same RDL file, but have
> >> > > it show the data that is specific to their database.
> >> > >
> >> > > For instance, there is a single .rdl file which describes a personnel
> >> > > report, and i want people at site A to only see info about site A's
> >> > > personnel, and people at site B to only see info about site B's
> >> > > personnel.
> >> > >
> >> > > Is this possible? If so, is there any advice you can give me to help
> >> > > do
> >> > > this? Thank you.
> >> > >
> >> > > Kevin Green
> >> > > Software Engineer
> >> > > Pragmatech Software
> >> > >
> >> > >
> >>
> >>|||The below is from help. Note that they show using a parameter but you could
also use the result of a query that uses the global variable for user (I
believe it is User!Username). Have a table that provides the mapping from
user to database.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
>>>>>>>>
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/4d8f0ae1-102b-4b3d-9155-fa584c962c9e.htm
Data Source Expressions
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
a.. Design the report using a static connection string. A static
connection string refers to a connection string that is not set through an
expression (for example, when you follow the steps for creating a
report-specific or shared data source, you are defining a static connection
string). Using a static connection string allows you to connect to the data
source in Report Designer so that you can get the query results you need to
create the report.
b.. When defining the data source connection, do not use a shared data
source. You cannot use a data source expression in a shared data source. You
must define a report-specific data source for the report.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. Add a report parameter to specify a data source. For parameter values,
you can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
e.. Be sure that the list of data sources share the same database schema.
All report design begins with schema information. If there is a mismatch
between the schema used to define the report and the actual schema used by
the report at run time, the report might not run.
f.. Before publishing the report, replace the static connection string
with an expression. Wait until you are finished designing the report before
you replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
>>>>>>>>>
<KevinGreen24@.gmail.com> wrote in message
news:1153261522.566759.269720@.h48g2000cwc.googlegroups.com...
> That's exactly what i need, but i can't figure out how!
> It also has to be secure of course.
> Bruce L-C [MVP] wrote:
>> Also in RS 2005 you can dynamically change the datasource based on the
>> user.
>> I haven't done this but it is one of the capabilities.
>> Bruce Loehle-Conger
>> "magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
>> news:BCD91F31-A84B-40D2-8B00-2FB36C53CD52@.microsoft.com...
>> > Are the databases on two different SQL servers? How do the users run
>> > the
>> > report? From Report Manager, or embedded in a web page?
>> >
>> > If they are running the reports from Report Manager on different
>> > servers
>> > then you need to set up data sources (i.e. rds files) on each, with the
>> > same
>> > name, but pointing to the site-specific database.
>> >
>> > "KevinGreen24@.gmail.com" wrote:
>> >
>> >> Well we're working of a SaaS model, so every end user is running a
>> >> thin
>> >> client, so we're not installing anything on the end user's computer.
>> >>
>> >> magendo_man wrote:
>> >> > On each computer have an rds file specifying the connection
>> >> > properties
>> >> > for
>> >> > that computer.
>> >> >
>> >> > "KevinGreen24@.gmail.com" wrote:
>> >> >
>> >> > > Hello all,
>> >> > >
>> >> > > Here's my situation:
>> >> > >
>> >> > > I have a single RDL file that describes a certain report.
>> >> > >
>> >> > > I have different databases on different computers with the same
>> >> > > tables
>> >> > > but different data in each table.
>> >> > >
>> >> > > I need people to go to each computer, run the same RDL file, but
>> >> > > have
>> >> > > it show the data that is specific to their database.
>> >> > >
>> >> > > For instance, there is a single .rdl file which describes a
>> >> > > personnel
>> >> > > report, and i want people at site A to only see info about site
>> >> > > A's
>> >> > > personnel, and people at site B to only see info about site B's
>> >> > > personnel.
>> >> > >
>> >> > > Is this possible? If so, is there any advice you can give me to
>> >> > > help
>> >> > > do
>> >> > > this? Thank you.
>> >> > >
>> >> > > Kevin Green
>> >> > > Software Engineer
>> >> > > Pragmatech Software
>> >> > >
>> >> > >
>> >>
>> >>
>|||Right. That's similar what I did to solve the problem this morning:
1. Create the master .rdl file that has a hidden parameter called
"DbName"
2. Make the connection string in the data source dynamic:
="Data Source=(local);Initial Catalog=" & Parameters!DbName.Value
3. Create a linked report(s)
4. Using the report manager, set the default value of DbName according
to the company that will own each linked report.
This way, the parameter can be set for the report based on the company
it belongs to, and it's also secure because role-based security can be
set up at the linked report level.
Bruce L-C [MVP] wrote:
> The below is from help. Note that they show using a parameter but you could
> also use the result of a query that uses the global variable for user (I
> believe it is User!Username). Have a table that provides the mapping from
> user to database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> >>>>>>>>
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/4d8f0ae1-102b-4b3d-9155-fa584c962c9e.htm
> Data Source Expressions
> Data source expressions are processed at run time or when a report is
> previewed. The expression must be written in Visual Basic. Use the following
> guidelines when defining a data source expression:
> a.. Design the report using a static connection string. A static
> connection string refers to a connection string that is not set through an
> expression (for example, when you follow the steps for creating a
> report-specific or shared data source, you are defining a static connection
> string). Using a static connection string allows you to connect to the data
> source in Report Designer so that you can get the query results you need to
> create the report.
>
> b.. When defining the data source connection, do not use a shared data
> source. You cannot use a data source expression in a shared data source. You
> must define a report-specific data source for the report.
>
> c.. Specify credentials separately from the connection string. You can use
> stored credentials, prompted credentials, or integrated security.
>
> d.. Add a report parameter to specify a data source. For parameter values,
> you can either provide a static list of available values (in this case, the
> available values should be data sources you can use with the report) or
> define a query that retrieves a list of data sources at run time.
>
> e.. Be sure that the list of data sources share the same database schema.
> All report design begins with schema information. If there is a mismatch
> between the schema used to define the report and the actual schema used by
> the report at run time, the report might not run.
>
> f.. Before publishing the report, replace the static connection string
> with an expression. Wait until you are finished designing the report before
> you replace the static connection string with an expression. Once you use an
> expression, you cannot execute the query in Report Designer. Furthermore,
> the field list in the Datasets window and the Parameters list will not
> update automatically.
> >>>>>>>>>
> <KevinGreen24@.gmail.com> wrote in message
> news:1153261522.566759.269720@.h48g2000cwc.googlegroups.com...
> > That's exactly what i need, but i can't figure out how!
> >
> > It also has to be secure of course.
> >
> > Bruce L-C [MVP] wrote:
> >> Also in RS 2005 you can dynamically change the datasource based on the
> >> user.
> >>
> >> I haven't done this but it is one of the capabilities.
> >>
> >> Bruce Loehle-Conger
> >>
> >> "magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
> >> news:BCD91F31-A84B-40D2-8B00-2FB36C53CD52@.microsoft.com...
> >> > Are the databases on two different SQL servers? How do the users run
> >> > the
> >> > report? From Report Manager, or embedded in a web page?
> >> >
> >> > If they are running the reports from Report Manager on different
> >> > servers
> >> > then you need to set up data sources (i.e. rds files) on each, with the
> >> > same
> >> > name, but pointing to the site-specific database.
> >> >
> >> > "KevinGreen24@.gmail.com" wrote:
> >> >
> >> >> Well we're working of a SaaS model, so every end user is running a
> >> >> thin
> >> >> client, so we're not installing anything on the end user's computer.
> >> >>
> >> >> magendo_man wrote:
> >> >> > On each computer have an rds file specifying the connection
> >> >> > properties
> >> >> > for
> >> >> > that computer.
> >> >> >
> >> >> > "KevinGreen24@.gmail.com" wrote:
> >> >> >
> >> >> > > Hello all,
> >> >> > >
> >> >> > > Here's my situation:
> >> >> > >
> >> >> > > I have a single RDL file that describes a certain report.
> >> >> > >
> >> >> > > I have different databases on different computers with the same
> >> >> > > tables
> >> >> > > but different data in each table.
> >> >> > >
> >> >> > > I need people to go to each computer, run the same RDL file, but
> >> >> > > have
> >> >> > > it show the data that is specific to their database.
> >> >> > >
> >> >> > > For instance, there is a single .rdl file which describes a
> >> >> > > personnel
> >> >> > > report, and i want people at site A to only see info about site
> >> >> > > A's
> >> >> > > personnel, and people at site B to only see info about site B's
> >> >> > > personnel.
> >> >> > >
> >> >> > > Is this possible? If so, is there any advice you can give me to
> >> >> > > help
> >> >> > > do
> >> >> > > this? Thank you.
> >> >> > >
> >> >> > > Kevin Green
> >> >> > > Software Engineer
> >> >> > > Pragmatech Software
> >> >> > >
> >> >> > >
> >> >>
> >> >>
> >
I want know SQl Server...
gratuitous? If it is not, can gratuitously be used under certain conditions?
Which is the approach cost of acquisition) of supported data (Relationary?
Guided objects) (logical and physical structures of storage, structures of
memory).
Thanks for all...
The HOME of SQL.
www.microsoft.com/sql
Its not free You need to buy a licenses see how to buy:
http://www.microsoft.com/sql/howtobuy/default.asp
Technical Information:
http://www.microsoft.com/sql/techinfo/default.asp
Free version called MSDE but has limitations
http://www.microsoft.com/downloads/d...displaylang=en
Anything else?
I want know SQl Server...
s
gratuitous? If it is not, can gratuitously be used under certain conditions
?
Which is the approach cost of acquisition) of supported data (Relationary?
Guided objects) (logical and physical structures of storage, structures of
memory).
Thanks for all...The HOME of SQL.
www.microsoft.com/sql
Its not free You need to buy a licenses see how to buy:
http://www.microsoft.com/sql/howtobuy/default.asp
Technical Information:
http://www.microsoft.com/sql/techinfo/default.asp
Free version called MSDE but has limitations
http://www.microsoft.com/downloads/...&displaylang=en
Anything else?
I want know SQl Server...
gratuitous? If it is not, can gratuitously be used under certain conditions?
Which is the approach cost of acquisition) of supported data (Relationary?
Guided objects) (logical and physical structures of storage, structures of
memory).
Thanks for all...The HOME of SQL.
www.microsoft.com/sql
It´s not free You need to buy a licenses see how to buy:
http://www.microsoft.com/sql/howtobuy/default.asp
Technical Information:
http://www.microsoft.com/sql/techinfo/default.asp
Free version called MSDE but has limitations
http://www.microsoft.com/downloads/details.aspx?FamilyID=413744D1-A0BC-479F-BAFA-E4B278EB9147&displaylang=en
Anything else?
i want a certain field to be sequentially increased
I want to setup one of the fields in a table so it increments
sequentially(int data type). i.e the first record should be record 1
and the second one should be 2 and so on. This field will also be the
key field. I am new to SQL and don't know how to do this.
I am using SQL server 2000.
Thanks for the help in advance.
-S>> I want to setup one of the fields [sic] in a table so it increments
sequentially(INTEGER data type). i.e the first record [sic] should be
record 1 [sic] and the second one should be 2 and so on. This field
[sic]will also be the key field [sic]. I am new to SQL and don't know
how to do this. <<
Very new. Rows are not records; columns are not fields; keys are not
physical record numbers. You missed the most basic concept of the
RDBMS model. A key is a subset of the attributes (columns) which are
unique and not null for each entity (row) in the table. It is never a
"magic number for everything" generated by the computer which has no
meaning in the reality you are trying to model.
You actually have to think and work hard to design a database. Get a
book on data modeling and read it.|||Take a look at IDENTITY (Property) in "SQL Server Books Online".
"Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
news:a3007893.0311071034.54617b6b@.posting.google.c om...
> Hi,
> I want to setup one of the fields in a table so it increments
> sequentially(int data type). i.e the first record should be record 1
> and the second one should be 2 and so on. This field will also be the
> key field. I am new to SQL and don't know how to do this.
> I am using SQL server 2000.
> Thanks for the help in advance.
> -S|||thanks for the 40. I hear SQL FOR SMARTIES is pretty good. Any thoughts??
joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0311071358.4db9e6b8@.posting.google.com>...
> >> I want to setup one of the fields [sic] in a table so it increments
> sequentially(INTEGER data type). i.e the first record [sic] should be
> record 1 [sic] and the second one should be 2 and so on. This field
> [sic]will also be the key field [sic]. I am new to SQL and don't know
> how to do this. <<
> Very new. Rows are not records; columns are not fields; keys are not
> physical record numbers. You missed the most basic concept of the
> RDBMS model. A key is a subset of the attributes (columns) which are
> unique and not null for each entity (row) in the table. It is never a
> "magic number for everything" generated by the computer which has no
> meaning in the reality you are trying to model.
> You actually have to think and work hard to design a database. Get a
> book on data modeling and read it.|||>> thanks for the 40. I hear SQL FOR SMARTIES is pretty good. Any
thoughts?? <<
I like it a lot -- especially when the royalty check arrives :)
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dont listen to Celko - his is too pr0 :p
go with the identity - but beware some of its behaviour, and protect
it carefully, it is not terribly robust and can totally shaft your
data relationships if you use it and let it get damaged.
"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vqofb5e8rl3k97@.corp.supernews.com>...
> Take a look at IDENTITY (Property) in "SQL Server Books Online".
> "Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
> news:a3007893.0311071034.54617b6b@.posting.google.c om...
> > Hi,
> > I want to setup one of the fields in a table so it increments
> > sequentially(int data type). i.e the first record should be record 1
> > and the second one should be 2 and so on. This field will also be the
> > key field. I am new to SQL and don't know how to do this.
> > I am using SQL server 2000.
> > Thanks for the help in advance.
> > -S|||Wang,
I do agree with you. These are all features that help programmers to develop
more efficient applications. It all depends on how good you are, if you are
a terrible programmer then it doesn't matter whether you use IDENTITY or
not, you will end up with a piece of crap anyway. A professionals is someone
who can take advantage of these features and use them wisely to help
developing their creative ideas without making a mess.
Shervin
"WangKhar" <Wangkhar@.yahoo.com> wrote in message
news:bb269444.0311110220.539cf94d@.posting.google.c om...
> Dont listen to Celko - his is too pr0 :p
> go with the identity - but beware some of its behaviour, and protect
> it carefully, it is not terribly robust and can totally shaft your
> data relationships if you use it and let it get damaged.
>
>
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:<vqofb5e8rl3k97@.corp.supernews.com>...
> > Take a look at IDENTITY (Property) in "SQL Server Books Online".
> > "Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
> > news:a3007893.0311071034.54617b6b@.posting.google.c om...
> > > Hi,
> > > I want to setup one of the fields in a table so it increments
> > > sequentially(int data type). i.e the first record should be record 1
> > > and the second one should be 2 and so on. This field will also be the
> > > key field. I am new to SQL and don't know how to do this.
> > > I am using SQL server 2000.
> > > Thanks for the help in advance.
> > > > -S|||how would you protect the behavior of IDENTITY?
Shervin and wangkhar thanks for the help.
Celko, Don't expect any royalty from this beginner!!
Wangkhar@.yahoo.com (WangKhar) wrote in message news:<bb269444.0311110220.539cf94d@.posting.google.com>...
> Dont listen to Celko - his is too pr0 :p
> go with the identity - but beware some of its behaviour, and protect
> it carefully, it is not terribly robust and can totally shaft your
> data relationships if you use it and let it get damaged.
>
>
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vqofb5e8rl3k97@.corp.supernews.com>...
> > Take a look at IDENTITY (Property) in "SQL Server Books Online".
> > "Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
> > news:a3007893.0311071034.54617b6b@.posting.google.c om...
> > > Hi,
> > > I want to setup one of the fields in a table so it increments
> > > sequentially(int data type). i.e the first record should be record 1
> > > and the second one should be 2 and so on. This field will also be the
> > > key field. I am new to SQL and don't know how to do this.
> > > I am using SQL server 2000.
> > > Thanks for the help in advance.
> > > > -S|||>> Celko, Don't expect any royalty from this beginner! <<
What I used to get from beginners was consulting gigs, to clean up the
mess they made when their company has problems. Those jobs pay MUCH
better than royalties :)
Using IDENTITY as a primary key is a sign that there is no data model,
only an imitation of a sequential file system. Since this "magic,
all-purpose, one-size-fits-all" pseudo-identifier exists only as a
result of the physical state of a particular piece of hardware at a
particular time as read by the current release of a particular database
product, how do you verify that an entity has such a number in the
reality you are modeling?
You will see newbies who design tables like this:
CREATE Drivers
(driver_id IDENTITY (1,1) NOT NULL PRIMARY KEY,
ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),
vin CHAR(17) NOT NULL REFERENCES Motorpool(vin));
Now input data and submit the same row a thousand times, a million
times. Your data integrity is trashed. The natural key was this:
CREATE Drivers
(ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),
vin CHAR(17) NOT NULL REFERENCES Motorpool(vin),
PRIMARY KEY (ssn, vin));
If you want to enforce a rule that a car can have one driver:
CREATE Drivers
(ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),
vin CHAR(17) NOT NULL PRIMARY KEY
REFERENCES Motorpool(vin));
Now you are REALLY thinking about relations and keys instead of 1950's
sequential record numbering. Go further and add DRI:
CREATE Drivers
(ssn CHAR(9) NOT NULL
REFERENCES Personnel(ssn)
ON DELETE CASACADE
ON UPDATE CASACADE,
vin CHAR(17) NOT NULL PRIMARY KEY
REFERENCES Motorpool(vin)
ON DELETE CASACADE
ON UPDATE CASACADE,
PRIMARY KEY (ssn, vin));
Adding an IDENTITY column to either of these tables as a candidate key
would be dangerously redundant; one query uses the IDENTITY and another
uses the real key, and like a man with two watches, you are never sure
what time it is.
Researching the CHECK constraints you need for a VIN or SSN will take a
few days -- but newbies only want "quick and magic answers that solve
all the problems" and do not bother doing the real work.
Finally, an appeal to authority, with a quote from Dr. Codd: "..Database
users may cause the system to generate or delete a surrogate, but they
have no control over its value, nor is its value ever displayed to them
..."(Dr. Codd in ACM TODS, pp 409-410) and Codd, E. (1979), Extending
the database relational model to capture more meaning. ACM Transactions
on Database Systems, 4(4). pp. 397-434.
This means that a surrogate ought to act like an index; created by the
user, managed by the system and NEVER seen by a user. That means never
used in queries, DRI or anything else that a user does.
Codd also wrote the following:
"There are three difficulties in employing user-controlled keys as
permanent surrogates for entities.
(1) The actual values of user-controlled keys are determined by users
and must therefore be subject to change by them (e.g. if two companies
merge, the two employee databases might be combined with the result that
some or all of the serial numbers might be changed.).
(2) Two relations may have user-controlled keys defined on distinct
domains (e.g. one uses social security, while the other uses employee
serial numbers) and yet the entities denoted are the same.
(3) It may be necessary to carry information about an entity either
before it has been assigned a user-controlled key value or after it has
ceased to have one (e.g. and applicant for a job and a retiree).
These difficulties have the important consequence that an equi-join on
common key values may not yield the same result as a join on common
entities. A solution - proposed in part [4] and more fully in [14] - is
to introduce entity domains which contain system-assigned surrogates.
Database users may cause the system to generate or delete a surrogate,
but they have no control over its value, nor is its value ever displayed
to them...." (Codd in ACM TODS, pp 409-410).
References
Codd, E. (1979), Extending the database relational model to capture more
meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||It's not always consecutive. So if it's important to you just forget
IDENTITY.
Identity is a means just like many other things in SQL, if you chose the
right tool for your problem you won't regret it. It's not a silver bullet
but it will help you if you use it in the right place.
Good luck,
Shervin
"Sumanth Suri" <dangerousminds_17@.yahoo.com> wrote in message
news:2e121621.0311111136.5c401fd@.posting.google.co m...
> how would you protect the behavior of IDENTITY?
> Shervin and wangkhar thanks for the help.
> Celko, Don't expect any royalty from this beginner!!
> Wangkhar@.yahoo.com (WangKhar) wrote in message
news:<bb269444.0311110220.539cf94d@.posting.google.com>...
> > Dont listen to Celko - his is too pr0 :p
> > go with the identity - but beware some of its behaviour, and protect
> > it carefully, it is not terribly robust and can totally shaft your
> > data relationships if you use it and let it get damaged.
> > "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:<vqofb5e8rl3k97@.corp.supernews.com>...
> > > Take a look at IDENTITY (Property) in "SQL Server Books Online".
> > > > "Sumanth Suri" <sumant_suri@.hotmail.com> wrote in message
> > > news:a3007893.0311071034.54617b6b@.posting.google.c om...
> > > > Hi,
> > > > I want to setup one of the fields in a table so it increments
> > > > sequentially(int data type). i.e the first record should be record 1
> > > > and the second one should be 2 and so on. This field will also be
the
> > > > key field. I am new to SQL and don't know how to do this.
> > > > I am using SQL server 2000.
> > > > Thanks for the help in advance.
> > > > > > -S
Wednesday, March 7, 2012
I need to find total for rows with a certain value: how?
and 0 for off, just those two values) and a date column which is a timestamp
containing a date and time. There are other columns in this table but they
are unimportant in this discussion.
A process inserts rows into this table about every second or so, depending
on the state of an object.
It has been requested of me that I prepare a report. Among other items,
this report must show the total time an object is turned on. In other
words, for every row that is set to 1, I need to add up the time and let the
users know how long that item was set to on for a given period of time, say
24 hours i.e. how long was that object in an on state during the last 24
hours.
I consider myself to be pretty handy at SQL but I must confess I'm at a loss
to come up with a query for this.
Any ideas and examples would be greatly appreciated?
Thanks in advance,
Chris SmithOh I neglected one important piece...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTime
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LofDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Where On.OnFlag = 1
And Off.OnFlag = 0
Group By ObjectID
"someone" wrote:
> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||jeez, typo... And this produces total Millliseconds, Change the ms inside
the datediff function to whatever you want it to produce if milliseconds is
not what you want:
sec, minute, hour, day, etc...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTimeMs
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LogDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Where On.OnFlag = 1
And Off.OnFlag = 0
Group By ObjectID
"someone" wrote:
> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||I assume that the table also contains an ObjecID - to identofy WHICH Object
is being Tuened On Or OFF...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTime
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LofDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Group By ObjectID
"someone" wrote:
> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||It might be helpful for you to provide sample data, especially about
this timestamp field. When a row is inserted with the on/off flag set
to ON, what value is in the timestamp column? The way I see it, there
are 3 options: -
1. The time the object entered the ON state only
2. The time the object left the ON state only
3. Both the time the objected entered the ON state + the time it left
or the duration it was in this state.
The 3rd option is easy because all you need to do is sum the duration
(or calculate it from the Left - Entered Time and sum) for each state
in a given time period.
The 1st and 2nd options are a bit tough since you have to form a sort
of JOIN to find the related state from which the object toggled from or
to. e.g. if I store the time the object entered the ON state, to find
out how long it stayed in this state, I need to find the immediate NEXT
time it entered the OFF state. Likewise, if it is the end time that is
stored, I need to find the immediate PREVIOUS time it left the OFF
state.
Another thing you will have to consider, though this has to do with
data integrity, how do u ensure no overlaps occur in the records (such
that your data says at a given point in time, the object was both ON
and OFF)? Do you allow time gaps in which you can't tell whether the
object was ON or OFF? if you don't how do u ensure there are no gaps?
If you do, how do you interpret the time where there are no records?
Please clarify...|||It might be helpful for you to provide sample data, especially about
this timestamp field. When a row is inserted with the on/off flag set
to ON, what value is in the timestamp column? The way I see it, there
are 3 options: -
1. The time the object entered the ON state only
2. The time the object left the ON state only
3. Both the time the objected entered the ON state + the time it left
or the duration it was in this state.
The 3rd option is easy because all you need to do is sum the duration
(or calculate it from the Left - Entered Time and sum) for each state
in a given time period.
The 1st and 2nd options are a bit tough since you have to form a sort
of join to find the related state from which the object toggled from or
to. e.g. if I store the time the object entered the ON state, to find
out how long it stayed in this state, I need to find the NEXT time it
entered the OFF state. Likewise, if it is the end time that is stored,
I need to find the PREVIOUS time it left the OFF state.
Another thing you will have to consider, though this has to do with
integrity, how do u ensure no overlaps occur in the records? Do you
allow time gaps in which you can't tell whether the object was ON or
OFF? if you don't how do u ensure there are no gaps? If you do, how do
you interpret the time where there are no records?
Please clarify...|||Hi Sienko,
I'll try to answer your questions as best as I can.
First of all, it should be noted this isn't a typical database. This is
actually a database accessed through MS SQL 2000 called InSQL. InSQL allows
you to store real time data more efficiently than SQL Server while allowing
for more transactions per second and storing the data in a manner that
allows for smaller files on the harddrive than what SQL Server itself would
allow. InSQL itself is actually made up of extension tables to SQL Server
2000. Along with this, InSQL provides other things such as providing a
discreete data type which allows you to store values that tells you if a
device is on or off where 1=on/open and 0=off/close. This probably isn't
important for you to know but it might be. For the most part, T-SQL is
still valid so any solution you can help me find should still work.
A typical row will include a column for a tagname, a discreete value column
(contains a value of 1 or 0, that is all as far as I know of), and a
timestamp which is just a datetime column (this column is equal to the time
the discreete value was retrieved from a device, not the time the row was
created). There are other columns but they aren't important to this
discussion as far as I know of. An example row might look like:
tagname_here -- discreete value -- datetime
There are rows inserted for each tag about every second or so. We'll say a
row is inserted each second to keep this simple. The rows will be the same,
just the fact the discreete value is different along with the time. For
example, a valve maybe open at the time a sensor takes a reading so this
data is retrieved (the state of the valve along with the datetime the
reading was taken) and inserted into a table (discreetehistory is the name
of the table I think, we'll use that for the sake of this conversation). As
long as the valve is open, a row will be inserted into this table where that
row will have a 1 in the discreete column and the datetime that value was
taken. When the valve is closed, rows will be inserted into the table where
the discreete value is now 0 and it has a datetime with it. Again, as long
as the valve is closed, rows will be inserted into the table with a
discreete value of 0 along with it's datetime. In other words, we have a
process that is looking at equipment 24/7/365. This process determines the
state of devices and inserts rows into a table every second or so.
Everytime a row is written, that row represents the state of the device and
a datetime is stored in that row to let us know when the sensor reading was
taken.
I don't think the table will allow null values nor do I believe a null value
will ever occur but am not certain. There is a transition period between
opening and closing valves. After all, a valve doesn't instantly open or
close. Depending on the size of size valve, it might take 1 to 5 seconds to
switch states. But, I don't believe this transition state is being
recorded. I wish I was at work answering this. I could tell you for sure
then.
Again, the problem is I need a query where I can report how long a valve was
left open in the last two hours. In other words, for a time from 12:00PM to
2:00 PM, the valve might be open at first but close at 12:30. Then, the
valve might open at 12:40. Then, the valve might be close at 1:00PM and
then open again at 1:10 PM. In other words, the valve was opened and
closeded multiple times during this two hour block. In total, the valve was
open 1hr and 40 minutes during this two hour block with the valve being
closed for 20 minutes. I need a query that will read this table and
everytime the valve is open which is represented with a discreete value of 1
(where 1 = open), then it will sum up all the times and report this to me.
I thought I was pretty good with T-SQL but I've never done anything like
this before when it comes to time. I must admit I don't have the slightest
idea how to proceed with this one.
Any thoughts would be greatly appreciated. If you need more information,
please let me know.
Thanks for trying to help me out. I apologize for taking a while to respond
to you but I was busy and simply forgot about this.
Thanks again!
Chris Smith
"sienko" <sienko@.gmail.com> wrote in message
news:1112174062.024672.131620@.g14g2000cwa.googlegroups.com...
> It might be helpful for you to provide sample data, especially about
> this timestamp field. When a row is inserted with the on/off flag set
> to ON, what value is in the timestamp column? The way I see it, there
> are 3 options: -
> 1. The time the object entered the ON state only
> 2. The time the object left the ON state only
> 3. Both the time the objected entered the ON state + the time it left
> or the duration it was in this state.
> The 3rd option is easy because all you need to do is sum the duration
> (or calculate it from the Left - Entered Time and sum) for each state
> in a given time period.
> The 1st and 2nd options are a bit tough since you have to form a sort
> of JOIN to find the related state from which the object toggled from or
> to. e.g. if I store the time the object entered the ON state, to find
> out how long it stayed in this state, I need to find the immediate NEXT
> time it entered the OFF state. Likewise, if it is the end time that is
> stored, I need to find the immediate PREVIOUS time it left the OFF
> state.
> Another thing you will have to consider, though this has to do with
> data integrity, how do u ensure no overlaps occur in the records (such
> that your data says at a given point in time, the object was both ON
> and OFF)? Do you allow time gaps in which you can't tell whether the
> object was ON or OFF? if you don't how do u ensure there are no gaps?
> If you do, how do you interpret the time where there are no records?
> Please clarify...
>|||You usually model time as durations, so you would have
CREATE TABLE Events
(event_id CHAR(10) NOT NULL,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_time DATETIME, -- null means current
. );|||On Sat, 2 Apr 2005 12:49:01 -0500, someone wrote:
-- Crosspost to non-existant group removed --
(snip description)
>I thought I was pretty good with T-SQL but I've never done anything like
>this before when it comes to time. I must admit I don't have the slightest
>idea how to proceed with this one.
>Any thoughts would be greatly appreciated. If you need more information,
>please let me know.
Hi Chris,
I think the best way to approach this problem is to start with a view to
reduce the load of imported data to just the relevant things: periods
without status change, with beginning and ending datetime.
CREATE VIEW Periods
AS
SELECT start.Status,
start.TheDatetime AS StartDT,
MAX(sameperiod.TheDatetime) AS EndDT
FROM RawData AS start
INNER JOIN RawData AS sameperiod
ON sameperiod.Status = start.Status
AND sameperiod.TheDatetime >= start.TheDatetime
AND NOT EXISTS
(SELECT *
FROM RawDate AS beetween -- deliberate misspelling: reserved word
WHERE beetween.TheDatetime > start.TheDatetime
AND beetween.TheDatetime < sameperiod.TheDatetime
AND beetween.Status <> start.Status)
WHERE NOT EXISTS
(SELECT *
FROM RawDate AS previous
WHERE previous.TheDatetime =
(SELECT MAX(TheDatetime)
FROM RawData
WHERE TheDatetime < start.TheDatetime)
AND previous.Status = start.Status)
GROUP BY start.Status, start.TheDatetime
With this query, your report becomes easy:
DECLARE @.StartReport smalldatetime
DECLARE @.EndReport smalldatetime
SET @.StartReport = '2005-04-02T12:00:00'
SET @.EndReport = '2005-04-02T14:00:00'
SELECT SUM(DATEDIFF(minute,
CASE WHEN StartDT < @.StartReport
THEN @.StartReport ELSE StartDT END,
CASE WHEN EndDT < @.EndReport
THEN @.EndReport ELSE EndDT END))
FROM Periods
WHERE StartDT < @.EndReport
END EndDT > @.BeginReport
I was not able to test the query and view above, since you have not
posted the CREATE TABLE and INSERT statements needed to create a test
database on my server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The thing is I'm not creating a table with multiple columns for dates. A
table is already present with one datecolumn only. I need to find all rows
within a timespan that have a value equal to 1 in the discreete value
column. Once I find all the rows, I need to add them all somehow such that
it will tell me the total time all the rows were set to a 1 in this column.
Any ideas on that?
Thanks!
Chris Smith
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112464694.412138.243320@.o13g2000cwo.googlegroups.com...
> You usually model time as durations, so you would have
> CREATE TABLE Events
> (event_id CHAR(10) NOT NULL,
> start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> end_time DATETIME, -- null means current
> .. );
>
Sunday, February 19, 2012
I need help with Analysis Services
I have a problem with the caculation of the dimensions in the Analysis
Services and I need help.
In a certain dimension I make some calculations with the "personalized member formulas". Some cells are modified after this calculations. After this, in another dimension, other calculations are made using again the "personalized member formulas". With this operations some cells that were modified in the previous dimension should be modified again, but they aren't. Is there a way to fix the sequence of the calculation of the dimensions? Do they get calculated at the same time or some get done before others? Once one of this cells are modified using those formulas is it possible to modify them again? In the Hyperion's Essbase software ther is an option to calculate some dimensions before others. Does Analysis Services has that option or something similar?.
I hope you can help me. Thank you very much.Are you using third party OLAP and if not what is the level of service pack on Analysis server?|||We are you using service pack 3 on Analysis server and the Microsoft tools for Analysis Services. (SQL Server 2000 SP3 + Windows 2000 Server + SQL Server Enterprise Manager; and Analysis Services SP3 + Analysis Manager)