adjust x,y position of crystal report data field from VB

dear friend,

as you know, to adjust x,y position of any data field on Crystal report,
we just open CR and manually put and adjust the datafield...

But how if our VB application want to change this setting too ?
how to pass the x,y parameter of specific data field to crystal report, so crystal report will adjust automatically before it displays. ??

Thank you,If you use RDC to display or print the report, you can do it by changing the properties of the object.

Report1.Object1.Property1 = Value1

MyRpt.fldDate.Left = 1500
MyRpt.fldDate.Top = 250

Adhoc report with join tables on

Does anyone know if user has the capability to join two entities with
additional field which is not a predefined primary key or foreign key at the
design time? User want to run the report by join tables with certain fields
on the fly. I have not found a way to allow them to do that unless they have
Microsoft visual studio 2005 or SQL server business intelligence development
studio installed. Is SQL server reporting service a right candidate to serve
user's request? Does anyone know any other tool with this capability?
Thanks!1. You can do this if you define the sql statement dynamically and allow the
user parameter choices that provide the ability to specify the tables and
joins expressions. (for how to define the sql statement dynamically,
building it up as an expression, just treat the command as an expression
like you would the expression to display in a text box. IOW, start it with
an = sign and build up the string or invoke a code function)
However, I don't think it's really the best way.
2. Does the user have access and understanding to create views on the
server? It might be best to define the report based on a view, and have the
report basically remain ignorant of the joins and table information. The
report would have a single parameter -- the name of the view to invoke --
and would send that information to a stored procedure which would validate
that the view exists and has appropriate columns, and then run the view or
error-handle as described in choice #3 below.
3. If the user does not have that ability or access, I think I would build
this report to run a stored procedure, passing the parameter information as
described in #1, and have the stored procedure built and execute the sql. I
could do better validation in the stored procedure (for example, validate
that the tables and fields chosen by the user actually exist, assuming these
elements cannot be a dropdown in the report interface). I would have the
sproc send back a default data set of one record with every item showing
appropriate error text (or something) if I couldn't handle it another way.
Basically I think choice #2 is the right way to go here and if the user
doesn't have that ability and access I'm wondering whether that user should
be specifying this information at all...
>L<
"Daisy" <diyfan@.msnews.group.post> wrote in message
news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> Does anyone know if user has the capability to join two entities with
> additional field which is not a predefined primary key or foreign key at
> the
> design time? User want to run the report by join tables with certain
> fields
> on the fly. I have not found a way to allow them to do that unless they
> have
> Microsoft visual studio 2005 or SQL server business intelligence
> development
> studio installed. Is SQL server reporting service a right candidate to
> serve
> user's request? Does anyone know any other tool with this capability?
> Thanks!|||Thank you very much for the idea! I had built the model with report builder
to let user chose any fields from the tables given for the report. Now user
wanted to join the table not based on the primary key field that specified in
the design time. They wanted to join the table by certain non key fields at
the run time. I was stucked. I was only thinking use report builder model to
let user do this. And the report model could not change the key field (join
relation) on the fly. I have not found anything online or in MSDN library
telling me how to define the key at run time. As you suggested by using the
reprot designer and the sql stored procedure it is feasible to achieve the
task. Hope I did not misunderstood your post. If so please let me know.
Thanks!
"Lisa Slater Nicholls" wrote:
> 1. You can do this if you define the sql statement dynamically and allow the
> user parameter choices that provide the ability to specify the tables and
> joins expressions. (for how to define the sql statement dynamically,
> building it up as an expression, just treat the command as an expression
> like you would the expression to display in a text box. IOW, start it with
> an = sign and build up the string or invoke a code function)
> However, I don't think it's really the best way.
> 2. Does the user have access and understanding to create views on the
> server? It might be best to define the report based on a view, and have the
> report basically remain ignorant of the joins and table information. The
> report would have a single parameter -- the name of the view to invoke --
> and would send that information to a stored procedure which would validate
> that the view exists and has appropriate columns, and then run the view or
> error-handle as described in choice #3 below.
> 3. If the user does not have that ability or access, I think I would build
> this report to run a stored procedure, passing the parameter information as
> described in #1, and have the stored procedure built and execute the sql. I
> could do better validation in the stored procedure (for example, validate
> that the tables and fields chosen by the user actually exist, assuming these
> elements cannot be a dropdown in the report interface). I would have the
> sproc send back a default data set of one record with every item showing
> appropriate error text (or something) if I couldn't handle it another way.
> Basically I think choice #2 is the right way to go here and if the user
> doesn't have that ability and access I'm wondering whether that user should
> be specifying this information at all...
> >L<
>
> "Daisy" <diyfan@.msnews.group.post> wrote in message
> news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> > Does anyone know if user has the capability to join two entities with
> > additional field which is not a predefined primary key or foreign key at
> > the
> > design time? User want to run the report by join tables with certain
> > fields
> > on the fly. I have not found a way to allow them to do that unless they
> > have
> > Microsoft visual studio 2005 or SQL server business intelligence
> > development
> > studio installed. Is SQL server reporting service a right candidate to
> > serve
> > user's request? Does anyone know any other tool with this capability?
> >
> > Thanks!
>

Address field problem.

I have a number of text boxes (one on top of the other) to display the addresses in my report. I have Address_name, Address1, Address2, Address_city, Address_State, Address_PostalCode - Each with its own text box except for the city, state, and postal code for which I just combined the fields. However, I have many who do not have any data for Address2 and don't want an empty line in the address. Is there an expression I can use to display the next line or move/shift up the remaining parts of the address? Would the iif and isnothing expressions be helpful, and if so, how do I write it correctly?

If it is ok to put them all into one textbox, you could do this:

=Fields!Address_name.Value
& chr(10) & Fields!Address1.Value
& IIf(Len(Fields!Address2.Value) > 0, chr(10) & Fields!Address2.Value, "")
& chr(10) & Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value

If they have to have their own textbox and you just want to move it up if the Address2 is empty, then you could try something like this.

In the expression for the Address2 textbox:

=IIf(Len(Fields!Address2.Value) > 0, Fields!Address2.Value, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value)

Then, in the expression for the City/State/Postal Code:

=IIf(Len(Fields!Address2.Value) > 0, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value, "")

Hope this helps.

Jarret

|||

Thanks mate, I'll give it a try.!

What does "Len" do?

|||

Len gives you then length of the object you pass in. In this case, if the Address2 is NULL or an empty string, Len will return 0 and it will be skipped with the logic in the code.

Let me know if that fixes your issue.

Jarret

|||

I see, thanks for the info. I will try this in about an hour or so, I'll post back and give you an update.

Thanks again Jarret,

Bill

AddNew then getting Unique ID

I have an auto-incremental field in my sql database table. After I add a
new record I need to get that ID. My below code adds the record with no
problems but the ID field I request always comes back empty. If I look in
the table the new record is there with the auto ID field.
hr = pConnection->Open(strCnn,"","",adConnectUnspecified);
hr= pRstPubInfo.CreateInstance(__uuidof(Recordset));
hr = pRstPubInfo->Open("messages",
_variant_t((IDispatch*)pConnection,true)
,
adOpenKeyset,adLockOptimistic,adCmdTable
);
pRstPubInfo->AddNew();
hr = pRstPubInfo->Fields->GetItem("message")->AppendChunk(varChunk);
hr = pRstPubInfo->Update();
_variant_t DBID = pRstPubInfo->Fields->Item["id"]->GetValue();
//DBID = EMPTY.Use a stored procedure, not add new. Optimistic recordsets and ad hoc SQL
are not optimal for performing inserts!
Anyway, then you could do this in one transaction and retrieve the output
variable:
CREATE PROCEDURE dbo.AddRow
@.value VARCHAR(32),
@.idOut INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT table(column) SELECT @.value;
SELECT @.idOut = SCOPE_IDENTITY();
END
GO
(Alternatively, you could use a scalar/resultset or, since it is an INT, you
could buck standard practice and use the return value.)
A
"Bob" <msgdev@.hotmail.com> wrote in message
news:%237C%23PnI4FHA.3000@.TK2MSFTNGP12.phx.gbl...
>I have an auto-incremental field in my sql database table. After I add a
>new record I need to get that ID. My below code adds the record with no
>problems but the ID field I request always comes back empty. If I look in
>the table the new record is there with the auto ID field.
>
> hr = pConnection->Open(strCnn,"","",adConnectUnspecified);
> hr= pRstPubInfo.CreateInstance(__uuidof(Recordset));
> hr = pRstPubInfo->Open("messages",
> _variant_t((IDispatch*)pConnection,true)
,
> adOpenKeyset,adLockOptimistic,adCmdTable
);
> pRstPubInfo->AddNew();
> hr = pRstPubInfo->Fields->GetItem("message")->AppendChunk(varChunk);
> hr = pRstPubInfo->Update();
> _variant_t DBID = pRstPubInfo->Fields->Item["id"]->GetValue();
> //DBID = EMPTY.
>|||I am adding a binary object to the database. It could be very large so I
thought using AddChunk would be better. Is there a way to add binary data
using stored procedures? Is there a way to add chunks? May be I am doing
this all wrong. Any help would be appreciated.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
> Use a stored procedure, not add new. Optimistic recordsets and ad hoc SQL
> are not optimal for performing inserts!
> Anyway, then you could do this in one transaction and retrieve the output
> variable:
> CREATE PROCEDURE dbo.AddRow
> @.value VARCHAR(32),
> @.idOut INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT table(column) SELECT @.value;
> SELECT @.idOut = SCOPE_IDENTITY();
> END
> GO
> (Alternatively, you could use a scalar/resultset or, since it is an INT,
> you could buck standard practice and use the return value.)
> A
>
> "Bob" <msgdev@.hotmail.com> wrote in message
> news:%237C%23PnI4FHA.3000@.TK2MSFTNGP12.phx.gbl...
>|||AppendChunk can be used for Parameter objects as well as Field objects.
Bob wrote:
> I am adding a binary object to the database. It could be very large
> so I thought using AddChunk would be better. Is there a way to add
> binary data using stored procedures? Is there a way to add chunks?
> May be I am doing this all wrong. Any help would be appreciated.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||CREATE PROCEDURE sp_InsertBLOB
@.ID int = NULL OUT,
@.BLOB image = NULL
AS
SET NOCOUNT ON
INSERT INTO BLOBTable ( BLOB ) VALUES( @.BLOB )
SELECT @.ID = SCOPE_IDENTITY()
END
GO
The strange thing to me is that you are worried about the efficiency of
sending a "large binary object" to the server, but you are willing to pull
down an entire table full of them just to perform an insert?
John
"Bob" wrote:

> I am adding a binary object to the database. It could be very large so I
> thought using AddChunk would be better. Is there a way to add binary data
> using stored procedures? Is there a way to add chunks? May be I am doing
> this all wrong. Any help would be appreciated.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
>
>

ADDING WITH COUNTING

Below is my query so far. There is a field in Winpayment call
transaction_amount that I would like to add up for use count statement if
possible. So what I am asking is there a way to for each count case add the
transaction amount and have a transaction total show for each count case
statement. For example, every exsistence in the first count statement would
be added up by using the transaction_amount and then have a field right afte
r
SHEETZ_MC_TAPPED_INSIDE say transactions total? Thanks for any help.
Use Winpayment
GO
SELECT S.card_acceptor_identification STORE,
COUNT(M.card_acceptor_identification) TOTAL,
COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '001' and pos_entry_mode
= '921' THEN 1 END) SHEETZ_MC_TAPPED_INSIDE,
COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '001' and pos_entry_mode
= '021' THEN 1 END) SHEETZ_MC_SWIPED_INSIDE,
COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '003' and pos_entry_mode
= '921' THEN 1 END) SHEETZ_MC_TAPPED_OUTSIDE,
COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '003' and pos_entry_mode
= '021' THEN 1 END) SHEETZ_MC_SWIPED_OUTSIDE
FROM Store S
Left Join financial_message M
On M.card_acceptor_identification = S.card_acceptor_identification
And settlement_batch_number = '961'
AND id_number_1 like '540168%'
Where len (S.card_acceptor_identification) = 4
GROUP BY S.card_acceptor_identificationI'm not following what you are trying to do. In addition to your query,
could you post the DDL and a few rows of sample data and something showing
your expected result?
--Brian
(Please reply to the newsgroups only.)
"tarheels4025" <tarheels4025@.discussions.microsoft.com> wrote in message
news:8C71D6DF-414C-4B77-95CD-2934AB2CA45D@.microsoft.com...
> Below is my query so far. There is a field in Winpayment call
> transaction_amount that I would like to add up for use count statement if
> possible. So what I am asking is there a way to for each count case add
> the
> transaction amount and have a transaction total show for each count case
> statement. For example, every exsistence in the first count statement
> would
> be added up by using the transaction_amount and then have a field right
> after
> SHEETZ_MC_TAPPED_INSIDE say transactions total? Thanks for any help.
>
> Use Winpayment
> GO
> SELECT S.card_acceptor_identification STORE,
> COUNT(M.card_acceptor_identification) TOTAL,
> COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '001' and
> pos_entry_mode
> = '921' THEN 1 END) SHEETZ_MC_TAPPED_INSIDE,
> COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '001' and
> pos_entry_mode
> = '021' THEN 1 END) SHEETZ_MC_SWIPED_INSIDE,
> COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '003' and
> pos_entry_mode
> = '921' THEN 1 END) SHEETZ_MC_TAPPED_OUTSIDE,
> COUNT(CASE WHEN id_code_1 = 'MC' and terminal_num = '003' and
> pos_entry_mode
> = '021' THEN 1 END) SHEETZ_MC_SWIPED_OUTSIDE
> FROM Store S
> Left Join financial_message M
> On M.card_acceptor_identification = S.card_acceptor_identification
> And settlement_batch_number = '961'
> AND id_number_1 like '540168%'
> Where len (S.card_acceptor_identification) = 4
> GROUP BY S.card_acceptor_identification

Adding two group sums

Hi guys,

I'm very new to Crystal reports and I've scoured the internet looking how to add two 'sums together in a field.

In group header 3 I'm trying to add the following two sums together

Sum ({WMLocnHandlingMediaConf.QtyStor}

and

Sum ({WMLocnHandlingMediaConf.QtyIn}

And I really don't have a clue on how to do, I am a novice on Crystal and this is probably the most complicated thing I've attempted to do.

If anyone can help it would be very much apperciated.

Many Thanks
NickTry inserting and configuring a Running Total Field|||Make sure the the two sums you have already created are physically on the report. (If you do not wanrt to show them just format/suppress).

Create a new formula. When looking for the two fields to sum in the formula, look into report fields. You should see the tweo previously created sums.

adding to text

If I have a varchar field, I can easily add to it. For instance
select 'Name: ' + fname as fname from Customers.

But what if I have a text field instead of varchar?

select 'Summary: ' + summary as Summary from Customers wont work at all.
Is there a way to accomplish this?TEXT columns really ought to be manipulated on the client, not the server. There are a number of reasons for this, most of which are design and performance issues.

If you really must manipulate a TEXT column on the server, you can use the UPDATETEXT (http://msdn2.microsoft.com/en-us/library/ms189466.aspx) statment, but I'll forewarn you that it is rather ugly.

You really ought to handle this on the client if you can't make the column a VARCHAR instead of a TEXT column.

-PatPsql

Adding times together

Hi guys,

I have a field in my DB called EventDate as a DateTime field,
therefore it holds both the date and time together like this:
'2004-10-14 08:42:57.000'.

I need to add together all the times in this column for a particular
date range (BETWEEN).

Any suggestions will be great.

Thanks
Sunny:)Sunny K (sunstarwu@.yahoo.com) writes:
> I have a field in my DB called EventDate as a DateTime field,
> therefore it holds both the date and time together like this:
> '2004-10-14 08:42:57.000'.
> I need to add together all the times in this column for a particular
> date range (BETWEEN).

If I take you by the word, it sounds like the answer is:

SELECT SUM(datefiff(ss, convert(char(8), EventDate, 112), EventDate)
FROM tbl
WHERE EventDate BETWEEN ... AND ...

But it looks a little funny.

A common advice for this type of query is that you post

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You certainly can use BETWEEN with the DATETIME datatype but if you are
querying values with times other than midnight it's often more convenient to
use use >= and < instead of BETWEEN. For example

This:

SELECT *
FROM YourTable
WHERE eventdate >= '20041014'
AND eventdate < '20041015'

Is equivalent to this:

SELECT *
FROM YourTable
WHERE eventdate
BETWEEN '2004-10-14T00:00:00.000'
AND '2004-10-14T23:59:59.997'

Hope that answers your question.

--
David Portas
SQL Server MVP
--|||> I need to add together all the times in this column

I missed that bit from my first post - maybe because I've no idea what it
means! Just what would you expect to be the result of, for example
'2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
you want to add up a DATETIME?

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xoudnd6FteLz-BTcRVn-1A@.giganews.com>...
> > I need to add together all the times in this column
> I missed that bit from my first post - maybe because I've no idea what it
> means! Just what would you expect to be the result of, for example
> '2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
> you want to add up a DATETIME?

Hi Dave

Thats for the reply, and admitly i was very vague in what i meant to
say. From your above example the time result of the two times would
give me 08:42:57.000, as the time added was 00:00:00.000.

Maybe this will help explain what i mean a bit better. Here is a few
typical lines from my table:

Name EventDate EventID
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
CTWIGG-MOBL 2004-11-03 15:46:11.000 6005

My 'exact' requirements are to SUM all the 6005 EventID times together
and SUM all the 6006 EventID times together then find the difference
between the two times. The dates in the column are of no use.

Ive been banging my head over how to do this for a few days now. Any
suggestions?|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xoudnd6FteLz-BTcRVn-1A@.giganews.com>...
> > I need to add together all the times in this column
> I missed that bit from my first post - maybe because I've no idea what it
> means! Just what would you expect to be the result of, for example
> '2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
> you want to add up a DATETIME?

Hi Dave

Thats for the reply, and admitly i was very vague in what i meant to
say. From your above example the time result of the two times would
give me 08:42:57.000, as the time added was 00:00:00.000.

Maybe this will help explain what i mean a bit better. Here is a few
typical lines from my table:

Name EventDate EventID
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
CTWIGG-MOBL 2004-11-03 15:46:11.000 6005

My 'exact' requirements are to SUM all the 6005 EventID times together
and SUM all the 6006 EventID times together then find the difference
between the two times. The dates in the column are of no use.

Ive been banging my head over how to do this for a few days now. Any
suggestions?|||Sunny K (sunstarwu@.yahoo.com) writes:
> Thats for the reply, and admitly i was very vague in what i meant to
> say. From your above example the time result of the two times would
> give me 08:42:57.000, as the time added was 00:00:00.000.
> Maybe this will help explain what i mean a bit better. Here is a few
> typical lines from my table:
> Name EventDate EventID
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
> CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
> CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
> CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
>
> My 'exact' requirements are to SUM all the 6005 EventID times together
> and SUM all the 6006 EventID times together then find the difference
> between the two times. The dates in the column are of no use.
> Ive been banging my head over how to do this for a few days now. Any
> suggestions?

I repeat from my previous post:

A common advice for this type of query is that you post

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

In this case, the part with the desired result is very important,
because I am not sure what result you are looking for, and I don't
feel like guessing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9597F073739C9Yazorman@.127.0.0.1>...
> Sunny K (sunstarwu@.yahoo.com) writes:
> > Thats for the reply, and admitly i was very vague in what i meant to
> > say. From your above example the time result of the two times would
> > give me 08:42:57.000, as the time added was 00:00:00.000.
> > Maybe this will help explain what i mean a bit better. Here is a few
> > typical lines from my table:
> > Name EventDate EventID
> > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> > CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
> > CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
> > CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
> > CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
> > My 'exact' requirements are to SUM all the 6005 EventID times together
> > and SUM all the 6006 EventID times together then find the difference
> > between the two times. The dates in the column are of no use.
> > Ive been banging my head over how to do this for a few days now. Any
> > suggestions?
> I repeat from my previous post:
> A common advice for this type of query is that you post
> o CREATE TABLE statement for your table.
> o INSERT statements with sample data.
> o The desired result, given the sample data.
> This make it easy to cut and paste and compose a tested solution.
> In this case, the part with the desired result is very important,
> because I am not sure what result you are looking for, and I don't
> feel like guessing.

Hi,

Here is the script to create the table with some sample data:

CREATE TABLE [dbo].[tbltemp23] (
[Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[EventDate] [datetime] NOT NULL ,
[EventID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:10:54.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:12:13.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:41:42.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:42:57.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:16:45.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:19:21.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:32:56.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:34:17.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:13:21.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:14:42.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:44:55.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:46:11.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:51:43.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:53:03.000',6005)

Now I need to work out the the total time of all the 6006 EventIDs
(the date is here is not needed) which should equal: 93:32:16 then the
the total time of all the 6005 EventIDs which equals: 93:42:44. Then
finally find the difference between the two times, which should equal:
00:10:28 in this case.

I hope this is enough information.

Thanks
Sunny|||Thanks for the DDL and data.

SQL Server doesn't have a timespan data type. The query below uses
1900-01-01 as the base date from which durations are calculated, ignoring
the date component of the table data. You can format the returned values
according to your reporting requirements.

SELECT
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6006) AS EventId6006Duration,
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6005) AS EventId6005Duration,
DATEADD(s,
DATEDIFF(s,
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6006),
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6005)),
'19000101'
) AS EventDurationDifference

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sunny K" <sunstarwu@.yahoo.com> wrote in message
news:1ecdad8f.0411080132.4d6627fe@.posting.google.c om...
> Erland Sommarskog <esquel@.sommarskog.se> wrote in message
> news:<Xns9597F073739C9Yazorman@.127.0.0.1>...
>> Sunny K (sunstarwu@.yahoo.com) writes:
>> > Thats for the reply, and admitly i was very vague in what i meant to
>> > say. From your above example the time result of the two times would
>> > give me 08:42:57.000, as the time added was 00:00:00.000.
>>> > Maybe this will help explain what i mean a bit better. Here is a few
>> > typical lines from my table:
>>> > Name EventDate EventID
>> > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
>> > CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
>> > CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
>> > CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
>> > CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
>>>> > My 'exact' requirements are to SUM all the 6005 EventID times together
>> > and SUM all the 6006 EventID times together then find the difference
>> > between the two times. The dates in the column are of no use.
>>> > Ive been banging my head over how to do this for a few days now. Any
>> > suggestions?
>>
>> I repeat from my previous post:
>>
>> A common advice for this type of query is that you post
>>
>> o CREATE TABLE statement for your table.
>> o INSERT statements with sample data.
>> o The desired result, given the sample data.
>>
>> This make it easy to cut and paste and compose a tested solution.
>>
>> In this case, the part with the desired result is very important,
>> because I am not sure what result you are looking for, and I don't
>> feel like guessing.
>
> Hi,
> Here is the script to create the table with some sample data:
>
> CREATE TABLE [dbo].[tbltemp23] (
> [Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [EventDate] [datetime] NOT NULL ,
> [EventID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:10:54.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:12:13.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:41:42.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:42:57.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:16:45.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:19:21.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:32:56.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:34:17.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:13:21.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:14:42.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:44:55.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:46:11.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:51:43.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:53:03.000',6005)
> Now I need to work out the the total time of all the 6006 EventIDs
> (the date is here is not needed) which should equal: 93:32:16 then the
> the total time of all the 6005 EventIDs which equals: 93:42:44. Then
> finally find the difference between the two times, which should equal:
> 00:10:28 in this case.
> I hope this is enough information.
> Thanks
> Sunny|||Sunny K (sunstarwu@.yahoo.com) writes:
> Now I need to work out the the total time of all the 6006 EventIDs
> (the date is here is not needed) which should equal: 93:32:16 then the
> the total time of all the 6005 EventIDs which equals: 93:42:44. Then
> finally find the difference between the two times, which should equal:
> 00:10:28 in this case.

To be honest, this still seem very strange to me. Sure, there is enough
information to write a solution, but somehow I wonder what is the real
problem.

Looking at your data, it seems that event 6006 means start and 6005
means end, and what you really are computing is the total duration of
all start-stop sequences. Given that, I wrote this query:

SELECT convert(char(8), dateadd(ss, SUM(diff), '19000101'), 108)
FROM (select diff = datediff(ss, a.EventDate,
(SELECT MIN(EventDate)
FROM tbltemp23 b
WHERE b.EventDate > a.EventDate
AND b.EventID = 6005))
FROM tbltemp23 a
WHERE a.EventID = 6006) AS c

Of course, this query breaks down if the 6006 and 6005 can come in
any order, but in that case I have no clue of what might be going on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What can I say you guys, you really know your stuff. Thanks for all
the help, its given me the exact results I've needed.

I will consider you guys when I face another problem:-P

Sunny

Adding Time In DateTime Field

Hi

I'm trying to add a time from a DateTime field to provide a total. Eg:

Field1

01/02/2007 01:00:00PM

01/03/2007 01:45:00PM

01/04/2007 03:00:00PM

I want to add the time so I get a total of 05:45. The total hours could go over 24. I know I can't Sum it. I've seen several examples of how to do this but can't make any of them work. Could someone please point me in the right direction?

Thanks

set @.d1='01/02/2007 01:00:00PM'

datepart(hh,@.d1) return : 1

DATEADD ( hour, datepart(hh,@.d1), YourDate ) add 1 hour to your data

|||

Please check your objective. All of the times you list are afternoon times. The sum of the time component for all of these is the 5:45 plus an additional 36 hours. If your answer is correct, there is more to it than just summing the time components.

Code Snippet

declare @.aTable table (field1 datetime)
insert into @.aTable
select '01/02/2007 01:00:00PM' union all
select '01/03/2007 01:45:00PM' union all
select '01/04/2007 03:00:00PM'

select datediff(day, 0, sumOfTime) as Days,
convert(varchar, sumOfTime, 114) as Time
from ( select cast(sum(cast(field1 as float)
-floor(cast(field1 as float)))as datetime)
as sumOfTime
from @.aTable
) x

/*
Days Time
--
1 17:44:59:997
*/

|||

From the nature of your question, and looking at your sample data, I assume that the sample data represents 'elapsed time' on a date. And that 01:45PM means 1 hour and 45 minutes elapsed time -NOT 13:45 o'clock.

To calculate the total 'elapsed time', it would have been so much easier if you were storing the StartDateTime and EndDateTime -then it would be relatively simple date arithematic.

If my assumptions are correct, AND you cannot re-engineer the data to collect Start/End datetime values, this will be a bit more effort.

Please confirm.

|||

Sorry, I should have been clearer. The time is just a time, the date is irrelevant. It's actually a travel time, so Arnie you're correct, it is an elapsed time. I'm purely interested in adding the hours together. So in the 3 lines of sample data they travelled for 1 hour, 1 hour 45 minutes and 3 hours. AM/PM is also irrelevant. Travel time will never go over 12 hours. So the total I want is 5:45.

I'm working with someone else's data and tables here, personally I wouldn't have used a datetime field for this data but that is what I have. I also agree it would be better to have a start and end time, but I don't.

One possible way could be to extract the time, convert it to minutes, add those minutes together and then convert it back to hours and minutes...possibly? Any ideas?

|||

YOu could use this function:

Code Snippet

CREATE FUNCTION dbo.TimeDiffInHoursAndMinutes
(
@.Firstdate DATETIME,
@.Seconddate DATETIME
)
/*
Function written by Jens K. Suessmeyer, 07/22/2007
http://www.sqlserver2005.de
*/
RETURNS VARCHAR(10)
AS
BEGIN

DECLARE @.FirstdateMinutes INT
DECLARE @.SeconddateMinutes INT

SELECT @.FirstdateMinutes = DATEPART (hh,@.Firstdate)*60 + DATEPART(mi,@.Firstdate)
SELECT @.SeconddateMinutes = DATEPART (hh,@.Seconddate)*60 + DATEPART(mi,@.Seconddate)

RETURN (
SELECT
CONVERT(VARCHAR(10), FLOOR(@.SeconddateMinutes-@.FirstdateMinutes) / 60) +
':' +
RIGHT('00' + CONVERT(VARCHAR(10), (@.SeconddateMinutes-@.FirstdateMinutes) - FLOOR((@.SeconddateMinutes-@.FirstdateMinutes) / 60)*60),2))
END;

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

This 'should' move you in the direction you want. (I've added another row to the sample data.)

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
TravelTime smalldatetime
)

INSERT INTO @.MyTable VALUES ( '01/02/2007 01:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/03/2007 01:45:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 03:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 01:45:00PM' )

SELECT
Hours = sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 2 ) AS int )) +
( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) / 60 ) ,
Mins = ( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) % 60 )
FROM @.MyTable

Hours Mins
-- --
7 30

|||Thank you so much Arnie that seems perfect. |||In addition and althrough already closed, I found something on my blog (didn′t know that I wrote such a thing yet :-) )

http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/31/Default.aspx

Jens K. Suessmeyer

http://www.sqlserver2005.de

Adding Time In DateTime Field

Hi

I'm trying to add a time from a DateTime field to provide a total. Eg:

Field1

01/02/2007 01:00:00PM

01/03/2007 01:45:00PM

01/04/2007 03:00:00PM

I want to add the time so I get a total of 05:45. The total hours could go over 24. I know I can't Sum it. I've seen several examples of how to do this but can't make any of them work. Could someone please point me in the right direction?

Thanks

set @.d1='01/02/2007 01:00:00PM'

datepart(hh,@.d1) return : 1

DATEADD ( hour, datepart(hh,@.d1), YourDate ) add 1 hour to your data

|||

Please check your objective. All of the times you list are afternoon times. The sum of the time component for all of these is the 5:45 plus an additional 36 hours. If your answer is correct, there is more to it than just summing the time components.

Code Snippet

declare @.aTable table (field1 datetime)
insert into @.aTable
select '01/02/2007 01:00:00PM' union all
select '01/03/2007 01:45:00PM' union all
select '01/04/2007 03:00:00PM'

select datediff(day, 0, sumOfTime) as Days,
convert(varchar, sumOfTime, 114) as Time
from ( select cast(sum(cast(field1 as float)
-floor(cast(field1 as float)))as datetime)
as sumOfTime
from @.aTable
) x

/*
Days Time
--
1 17:44:59:997
*/

|||

From the nature of your question, and looking at your sample data, I assume that the sample data represents 'elapsed time' on a date. And that 01:45PM means 1 hour and 45 minutes elapsed time -NOT 13:45 o'clock.

To calculate the total 'elapsed time', it would have been so much easier if you were storing the StartDateTime and EndDateTime -then it would be relatively simple date arithematic.

If my assumptions are correct, AND you cannot re-engineer the data to collect Start/End datetime values, this will be a bit more effort.

Please confirm.

|||

Sorry, I should have been clearer. The time is just a time, the date is irrelevant. It's actually a travel time, so Arnie you're correct, it is an elapsed time. I'm purely interested in adding the hours together. So in the 3 lines of sample data they travelled for 1 hour, 1 hour 45 minutes and 3 hours. AM/PM is also irrelevant. Travel time will never go over 12 hours. So the total I want is 5:45.

I'm working with someone else's data and tables here, personally I wouldn't have used a datetime field for this data but that is what I have. I also agree it would be better to have a start and end time, but I don't.

One possible way could be to extract the time, convert it to minutes, add those minutes together and then convert it back to hours and minutes...possibly? Any ideas?

|||

YOu could use this function:

Code Snippet

CREATE FUNCTION dbo.TimeDiffInHoursAndMinutes
(
@.Firstdate DATETIME,
@.Seconddate DATETIME
)
/*
Function written by Jens K. Suessmeyer, 07/22/2007
http://www.sqlserver2005.de
*/
RETURNS VARCHAR(10)
AS
BEGIN

DECLARE @.FirstdateMinutes INT
DECLARE @.SeconddateMinutes INT

SELECT @.FirstdateMinutes = DATEPART (hh,@.Firstdate)*60 + DATEPART(mi,@.Firstdate)
SELECT @.SeconddateMinutes = DATEPART (hh,@.Seconddate)*60 + DATEPART(mi,@.Seconddate)

RETURN (
SELECT
CONVERT(VARCHAR(10), FLOOR(@.SeconddateMinutes-@.FirstdateMinutes) / 60) +
':' +
RIGHT('00' + CONVERT(VARCHAR(10), (@.SeconddateMinutes-@.FirstdateMinutes) - FLOOR((@.SeconddateMinutes-@.FirstdateMinutes) / 60)*60),2))
END;

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

This 'should' move you in the direction you want. (I've added another row to the sample data.)

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
TravelTime smalldatetime
)

INSERT INTO @.MyTable VALUES ( '01/02/2007 01:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/03/2007 01:45:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 03:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 01:45:00PM' )

SELECT
Hours = sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 2 ) AS int )) +
( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) / 60 ) ,
Mins = ( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) % 60 )
FROM @.MyTable

Hours Mins
-- --
7 30

|||Thank you so much Arnie that seems perfect. |||In addition and althrough already closed, I found something on my blog (didn′t know that I wrote such a thing yet :-) )

http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/31/Default.aspx

Jens K. Suessmeyer

http://www.sqlserver2005.de

adding security to a linked table

Can someone tell me how to go about adding security to a field in a linked table so that only certain people can access and modify the information? Thank you in advance to anybody who can help me out here!

Use a VIEW, actually 2 views. One for the hoi poloi, and the other for the chosen few. That is the 'best' option.

However, you can use column level permissions in the linked table if necessary. (Something about your question leads me to believe that you are using Access, in which case, my response may be totally 'full of beans'.)

|||I am using Access 2002 on the front end, sorry I thought I had mentioned that.|||Can someone please help explain how to go about doing this? I also need to modify field properties on the access end of a linked table. I applied the field property changes I wanted to the table in SQL server 2005, but am at a loss when changing them in access. I keep getting a message saying I can't save changes to a linked table. Is there a way for the changes made to the table in SQL to cascade down to the same table in access? Otherwise, how should I go about doing this? Thanks in advance!|||Can someone provide where to find more information about adding column level permissions in a linked table? I am using SQL server 2005 as a back end and Access 2002 as a front end. I added a new column to an existing table and now I want to add permissions to only this new column. Thank you much!|||

I still think that the best solution is to create an updatable VIEW (WITH VIEW_METADATA) that does not include the column in question. Those that should not access the column are provided permissions for the view, those that need access to the column are provided access to another VIEW that includes the column, or to the underlaying table.

For column level permissions, the specific Topic in Books Online is: GRANT Object Permissions (Transact-SQL). However, I recommend serious consideration of the VIEW option -it will be so much easier to maintain.

You might also find this thread useful. Or this thread.

adding security to a linked table

Can someone tell me how to go about adding security to a field in a linked table so that only certain people can access and modify the information? Thank you in advance to anybody who can help me out here!

Use a VIEW, actually 2 views. One for the hoi poloi, and the other for the chosen few. That is the 'best' option.

However, you can use column level permissions in the linked table if necessary. (Something about your question leads me to believe that you are using Access, in which case, my response may be totally 'full of beans'.)

|||I am using Access 2002 on the front end, sorry I thought I had mentioned that.|||Can someone please help explain how to go about doing this? I also need to modify field properties on the access end of a linked table. I applied the field property changes I wanted to the table in SQL server 2005, but am at a loss when changing them in access. I keep getting a message saying I can't save changes to a linked table. Is there a way for the changes made to the table in SQL to cascade down to the same table in access? Otherwise, how should I go about doing this? Thanks in advance!|||Can someone provide where to find more information about adding column level permissions in a linked table? I am using SQL server 2005 as a back end and Access 2002 as a front end. I added a new column to an existing table and now I want to add permissions to only this new column. Thank you much!|||

I still think that the best solution is to create an updatable VIEW (WITH VIEW_METADATA) that does not include the column in question. Those that should not access the column are provided permissions for the view, those that need access to the column are provided access to another VIEW that includes the column, or to the underlaying table.

For column level permissions, the specific Topic in Books Online is:GRANT Object Permissions (Transact-SQL). However, I recommend serious consideration of the VIEW option -it will be so much easier to maintain.

You might also find this thread useful. Or this thread.

Adding RunningTotal field from runtime

Hi!

I have a problem adding RunningTotal field from runtime...

Here is what i allready done:

Adding gruop:
.AddGroup 0, .unFKonto.Field, crGCAnyValue, crAscendingOrder

Adding unBoundField in GroupHeader section(lSectH)

Dim crKonto As FieldObject
Set crKonto = crReport.Sections(lSectH).AddUnboundFieldObject(crStringField, 200, 20)
With crKonto
.Font.Name = "Arial Narrow"
.SetUnboundFieldSource "{ado.Konto}"
.width = 1000
End With

All that works great but now i need to add RunningTotal in GruopFooter section
How to do that?

Here is what i allready done:
Dim crRTtest As RunningTotalFieldDefinition

Set crRTtest = .RunningTotalFields.Add("Sum of Konto")
crRTtest.SummaryType = iSum

Well, how to add crRTtest to section 5 after setting all needed properties?

regardsDone!

Dim crRTtest As RunningTotalFieldDefinition
Dim crBla As FieldObject
With crReport
Set crRTtest = .RunningTotalFields.Add("Sum of sss")

crRTtest.SummaryType = crSTSum
crRTtest.SetNoEvaluateCondition

crRTtest.SetResetConditionField .unFKonto.Field
crRTtest.SetSummarizedField .DokPrice.Field
Set crBla = .Sections(lSecF).AddFieldObject(crRTtest, 200, 20)
End With

Spent 3 days on that...
It was confusing to me that CR have AddBlobObject, AddSummaryFieldObject and so on but no AddRunningTotalFieldbject.

p.s.
Do you know where to find good help for developer on CR v8?
Developr.hlp is cunfusing and not very helpfull

Adding Report Filter on Float field

I am adding a filter on a float datatype field in my dataset.
Example:
=Fields!SharePrice.Value > 50
When running the report, I get the following error:
"...the processing of filter for the data set 'dataset' cannot be performed.
The comparision failed. Please check the data type returned by the data
expression."
I've tried casting the field as decimal with mixed results. Does anyone
know why this error occurs?You'd have to explicitly cast the return value using CSng() function.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am adding a filter on a float datatype field in my dataset.
> Example:
> =Fields!SharePrice.Value > 50
> When running the report, I get the following error:
> "...the processing of filter for the data set 'dataset' cannot be
performed.
> The comparision failed. Please check the data type returned by the data
> expression."
> I've tried casting the field as decimal with mixed results. Does anyone
> know why this error occurs?
>|||Try this:
Filter expression: =CDbl(Fields!SharePrice.Value)
Operator: >
Filter value: =CDbl(50)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am adding a filter on a float datatype field in my dataset.
> Example:
> =Fields!SharePrice.Value > 50
> When running the report, I get the following error:
> "...the processing of filter for the data set 'dataset' cannot be
performed.
> The comparision failed. Please check the data type returned by the data
> expression."
> I've tried casting the field as decimal with mixed results. Does anyone
> know why this error occurs?
>|||Thanks. That did the trick.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eNogP1WWEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Try this:
> Filter expression: =CDbl(Fields!SharePrice.Value)
> Operator: >
> Filter value: =CDbl(50)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
> news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > I am adding a filter on a float datatype field in my dataset.
> >
> > Example:
> >
> > =Fields!SharePrice.Value > 50
> >
> > When running the report, I get the following error:
> >
> > "...the processing of filter for the data set 'dataset' cannot be
> performed.
> > The comparision failed. Please check the data type returned by the data
> > expression."
> >
> > I've tried casting the field as decimal with mixed results. Does anyone
> > know why this error occurs?
> >
> >
>

Adding Records in SQL Server Express 2005

When adding records containing a date field in SQL Server 2005 Express I get an errorInput string was not in a correct format. Do datefields have to be converted when adding or editing into strings?

I usually insert dates into SQL in String format. SQL can convert them by himself. BUT at least in our environment you have to create the date string in "MM.DD.YYYY" format so if you are trying "DD.MM.YYYY" It propably wont work though I know this could be solved with localization somewhere, somehow :)
|||

I do have it in string format. Any Ideas? My code excerpt

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:Web2005ConnectionString1 %>"

ProviderName="<%$ ConnectionStrings:Web2005ConnectionString1.ProviderName %>"

SelectCommand="SELECT * FROM [People] WHERE ([id] = @.id)"

InsertCommand="INSERT INTO [People] ([LastName], [FirstName], [Price], [LogDate]) VALUES (@.LastName, @.FirstName, @.Price, @.LogDate)"UpdateCommand="UPDATE [People] SET [LastName] = @.LastName, [FirstName] = @.FirstName, [Price] = @.Price, [LogDate] = @.LogDate WHERE [id] = @.id"DeleteCommand="DELETE FROM [People] WHERE [id] = @.id">

<SelectParameters>

<asp:ControlParameterControlID="GridView1"Name="id"PropertyName="SelectedValue"

Type="Int64"/>

</SelectParameters>

<UpdateParameters>

<asp:ParameterName="LastName"Type="String"/>

<asp:ParameterName="FirstName"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="LogDate"Type="DateTime"/>

<asp:ParameterName="id"Type="Int64"/>

</UpdateParameters>

<InsertParameters>

<asp:ParameterName="LastName"Type="String"/>

<asp:ParameterName="FirstName"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="LogDate"Type="DateTime"/>

</InsertParameters>

Adding or subtracting

I'm in trouble. I have a table with some fields, one of them is an amount of money.
One field indicates whether the amount is negetive or positive. This fields has only two possible values, 'N' and 'R'. 'N' means the amount is negative and 'R' means the amount is positive.

The problem I have is that I have to sum these amounts to give a total for each customer and report them as

customer1 total amount1
customer2 total amount2

I don't know how to make a query to bring these results. I know that sum would bring me the total amount for each customer but I don't know how to specify which fields are supposed to be negative so that it subtracts them instead of adding them.

By the way I'm not allowed to change the structure of the table.

Could you help me try to create this query?you need to use a CASE stmt. check out BOL for some samples.

hth|||Could you give me an example through this thread please. I have no access to other internet pages. I can only access this one.

I tried something like this but it didn't work out

select r.numpolso, r.numofic,
case r.tdocumen
when 'N' then sum (r.prmtotrb*'-1')
when 'R' then sum (r.prmtotrb)
end as r.prmtotrb
from trecibos r
where r.numpolso = '1010' and r.ramo = '90' and r.subramo = '14' and r.numofic = '001'
group by r.numofic, r.numpolso, r.prmtotrb

I got a syntax error msg

could you give me an example or another way of doing this?

thanks in advance

Adding new field in a table which being used for replication

Hi dear friends,
I'm trying to add a new field in a table and in my desired
position (OrdinalPosition), but I can not and I get the
following error message. As this table is involved in
replication I get this error message:
('tblTransPayments' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot drop the table 'dbo.tblTransPayments' because it is
being used for replication.)
It should be noticed that I have lots of data in this
table and I'm not going to drop the table.
I can easily add my new field at the end of the column's
list but not as 21th column which I want to.
Thanks in advance.
If you really need the new column at a specific position, you'll have to drop the subscription, drop the publication, add the column then recreate the publication and subscription and initialize. You might want to do a nosync initialization to avoid the c
ost of the snapshot, but in this case you'll have to add the column onto the subscriber(s) table manually and if you are doing transactional replication, you'll need to create the scripts and apply them manually.
(No doubt you've seen this in other threads, but referring to columns by position rather than by name is generally seen as being a restrictive practice.)
HTH,
Paul Ibison
|||Hi -
Please check SQL BOL for "sp_repladdcolumn" and "sp_repldropcolumn".
Thanks
-Surajit
"Mattew" <anonymous@.discussions.microsoft.com> wrote in message
news:19d4101c44d7b$6507c260$a101280a@.phx.gbl...
> Hi dear friends,
> I'm trying to add a new field in a table and in my desired
> position (OrdinalPosition), but I can not and I get the
> following error message. As this table is involved in
> replication I get this error message:
> ('tblTransPayments' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> Cannot drop the table 'dbo.tblTransPayments' because it is
> being used for replication.)
> It should be noticed that I have lots of data in this
> table and I'm not going to drop the table.
> I can easily add my new field at the end of the column's
> list but not as 21th column which I want to.
> Thanks in advance.
|||Surajit,
this won't give the ability to specify the position, and Matthew doesn't
want the column created at the end.
Regards,
Paul Ibison

Adding new field in a table which being used for replication

Hi dear friends,
I'm trying to add a new field in a table and in my desired
position (OrdinalPosition), but I can not and I get the
following error message. As this table is involved in
replication I get this error message:
('tblTransPayments' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot drop the table 'dbo.tblTransPayments' because it is
being used for replication.)
It should be noticed that I have lots of data in this
table and I'm not going to drop the table.
I can easily add my new field at the end of the column's
list but not as 21th column which I want to.
Thanks in advance.If you really need the new column at a specific position, you'll have to dro
p the subscription, drop the publication, add the column then recreate the p
ublication and subscription and initialize. You might want to do a nosync in
itialization to avoid the c
ost of the snapshot, but in this case you'll have to add the column onto the
subscriber(s) table manually and if you are doing transactional replication
, you'll need to create the scripts and apply them manually.
(No doubt you've seen this in other threads, but referring to columns by pos
ition rather than by name is generally seen as being a restrictive practice.
)
HTH,
Paul Ibison|||Hi -
Please check SQL BOL for "sp_repladdcolumn" and "sp_repldropcolumn".
Thanks
-Surajit
"Mattew" <anonymous@.discussions.microsoft.com> wrote in message
news:19d4101c44d7b$6507c260$a101280a@.phx
.gbl...
> Hi dear friends,
> I'm trying to add a new field in a table and in my desired
> position (OrdinalPosition), but I can not and I get the
> following error message. As this table is involved in
> replication I get this error message:
> ('tblTransPayments' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> Cannot drop the table 'dbo.tblTransPayments' because it is
> being used for replication.)
> It should be noticed that I have lots of data in this
> table and I'm not going to drop the table.
> I can easily add my new field at the end of the column's
> list but not as 21th column which I want to.
> Thanks in advance.|||Surajit,
this won't give the ability to specify the position, and Matthew doesn't
want the column created at the end.
Regards,
Paul Ibison

Adding new field in a table which being used for replication

Hi dear friends,
I'm trying to add a new field in a table and in my desired
position (OrdinalPosition), but I can not and I get the
following error message. As this table is involved in
replication I get this error message:
('tblTransPayments' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot drop the table 'dbo.tblTransPayments' because it is
being used for replication.)
It should be noticed that I have lots of data in this
table and I'm not going to drop the table.
I can easily add my new field at the end of the column's
list but not as 21th column which I want to.
Thanks in advance.Hi -
Please check SQL BOL for "sp_repladdcolumn" and "sp_repldropcolumn".
Thanks
-Surajit
"Mattew" <anonymous@.discussions.microsoft.com> wrote in message
news:19d4101c44d7b$6507c260$a101280a@.phx.gbl...
> Hi dear friends,
> I'm trying to add a new field in a table and in my desired
> position (OrdinalPosition), but I can not and I get the
> following error message. As this table is involved in
> replication I get this error message:
> ('tblTransPayments' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> Cannot drop the table 'dbo.tblTransPayments' because it is
> being used for replication.)
> It should be noticed that I have lots of data in this
> table and I'm not going to drop the table.
> I can easily add my new field at the end of the column's
> list but not as 21th column which I want to.
> Thanks in advance.|||Surajit,
this won't give the ability to specify the position, and Matthew doesn't
want the column created at the end.
Regards,
Paul Ibison

Adding new column to Flat File Source Connection

What is the best way to deal with a flat file source when you need to add a new column? This happens constantly in our Data Warehouse, another field gets added to one of the files to be imported, as users want more data items. When I originally set the file up in Connection Managers, I used Suggest File Types, and then many adjustments made to data types and lengths on the Advanced Tab because Suggest File Types goofs a lot even if you say to use 1000 rows. I have been using the Advanced Tab revisions to minimize the Derived Column entries. The file is importing nightly. Now I have new fields added to this file, and when I open the Connection Manager for the file, it does not recognize the new columns in the file unless I click Reset Fields. If I click Reset Fields, it wipes out all the Advanced Tab revisions! If I don't click Reset Fields, it doesn't seem to recognize that the new fields are in the file?

Is it a waste of time to make Advanced Tab type and length changes? Is it a better strategy to just use Suggest Types, and not change anything, and take whatever you get and set up more Derived Column entries? How did the designers intend for file changes to be handled?

Or is there an easy way to add new fields to this import that I am overlooking? I am finding it MUCH more laborious to set up or to modify a file load in SSIS than in DTS. In DTS, I just Edit the transformation, and add the field to the Source and Destination lists, and I'm good to go. My boss isn't understanding why a "better" version is taking so much more work!

thanks,

Holly

Ah, well, I have some sympathy for you, but as soon as you said Data Warehouse, I lost that sympathy. The flat file source CANNOT change. SSIS isn't meant to be a be-all/do-all application. There are many downstream metadata components that would surely break if the flat file source could somehow magically understand the new columns. The whole point of SSIS (and many die-hard DTS people will balk at this) is to enforce strict-adherence to the way connections are built. Any changes require developer effort to ensure that the changes are handled appropriately. (No guessing!)

Suggest data types is good for a starting point, but you'll still need to go through and make sure that they are correct. The suggest feature does not scan all rows.

The more important question is why are you (the maintainers of the Data Warehouse) allowing for a constantly changing file to be introduced into the system? There should be some sort of production control in place.|||Exactly for the reasons Phil says, I don't use the "flat file" connection in SSIS for imports unless really, really needed.

If it is a standard delimited file, I use a temporary table in the same field structure as the source and the field names for the destination. Use Bulk Insert or BCP to insert into the temp table, then use SSIS to transform/move the data to the ultimate destination. In most cases use
INSERT INTO dest SELECT * from temptable. In other cases, I have to write a dynamic sql statement to read and populate the matching field names between the two tables and insert them.

That way if a new field is added, all you have to do is change the temp table and the dest table. Nothing in SSIS needs to change.

Yes, I know it is slower. But the ease of changing far outweights the added time it takes.|||

Sorry, I obviously did not explain clearly. The files are not "constantly changing."

Business users submit requests for data warehouse additions. Our data warehouse has expanded constantly over the years. New tables, new fields in existing tables, etc. When these requests are approved, the changes have to be implemented. If the data comes from the mainframe, and if it is logically part of an existing download, the programmer adds the new field/s to the appropriate download creating the extract file. Then the data warehouse administrator has to modify the package to import the new data fields. As businesses change, new regulations, new situations, new data is needed.

I don't mean that one night, suddenly, with no warning, there are new fields in a file. But I'd love to have a dollar for every field I have added to an existing DTS import over the years.

We use DTS for our ETL. I am now converting it to SSIS, but only in the Test environment. So far I have mostly been creating the SSIS packages, but since it takes a while, and other work goes on, I am coming to situations where I have to modify existing SSIS packages that I have already converted, because I have changed the DTS packages they are converting from.

I am beginning to think that I took the wrong strategy to change types and lengths on Advanced Tab, because I can't see any way of adding another field or two, without wiping out all that work. That was the purpose of my question. What are others in this situation using as their approach.

Thank you for your reply and I would be glad to hear other opinions.

Holly

|||

Tom,

Yes, I can see your point. The ideal thing would just be to let the import file define the file structure of that temporary or prep or stage table, where ever you want to dump the data. I am importing, in most cases, into a "prep" table from the text file. However, the approach to setting up the data structure of the prep tables was defined in 1999 and 2000, and the stored procedures that load from them are expecting that structure, and there are hundreds of tables, and I really don't have time to change them and then change the stored procs to deal with the new situation..... It seems to be an example of, if you set up a new ETL using SSIS it would be done very differently from an ETL that was set up under older versions. The conversion requires fixing something, and it's sort of a question of what you decide to fix.

It is interesting to hear the different ways people do things.

Holly

|||

I think a general "best practice" kind of approach to loading files is to always load them to a staging table. The goal of this initial load is that it should contain virtually no logic - it should be so simple that it virtually never fails.

A more drastic way of doing this is to use a "generic loader" setup. This consists of one staging table with enough varchar columns (max size) to match the number of columns in your widest table, plus a "target_table_name" column. You can create a generic file import process that will load every record in every file to this structure, setting the target table column differently for each load. You will have to maintain a mapping of your generic column names to the specific column names of the target table, or you can double up on your columns and add a "Column name" column for each column.

You can change datatypes, etc. when loading the targets from the generic table. This makes it a bit easier to recover from errors due to datatype issues, which is especially common when loading user-maintained Excel files, for example.

Obviously there are some drawbacks to this approach, like the mapping confusion it can create, as well as the fact that your loads tend to get pretty linear in execution. If you try to parallel load a setup like this the table gets thrashed too much and performance suffers. However, these tradeoffs do lead to a lot more flexibility and you have fewer ETL objects to maintain, though the maintenance may be more difficult in some cases.

There are always tradeoffs, but thought I'd throw the idea it out in case it might fit in your situation.

|||Holly,

I know it is a bunch of work to do to reimplement, I too have hundreds of flat files to import. I started this approach a long time ago because I could see this was going to be a problem.

I shortened part of my process in my description. I actually have a stored proc importing the flat files into a temp (staging) database with the table names the same as the flat files. I use a loop for each table, import each flat file (tablename.txt) into the temp table and process the tables from "import" database into their ultimate destination.

In your case, if you take my approach you could move them into the stage tables in a stage database, and then insert into your existing "prep" tables for the rest of the processing by the stored proc.

Neither SSIS or DTS are pretty when it comes to changing file structures for importing. Especially when all you usually need to do is map txtfile.field123 to table.field123.

Good luck|||You may try DataDefractor. It handles input data change gracefully.

Regards,
Ivan