Adding a new parameter to a subreport

Each time we add a new parameter to a subreport and we link the main report's fields to be the value's to the parameters for the subreport, we get an error message stating that the parameter for the subreport has not been specified. If we physically REBOOT the computer and then reload the project, the error seems to clear up.

Is this a bug in Reporting Services or is there some other explaination.

Thanks for the information.

Actually the problem was that the developer added a parameter, but did not convert the value into an integer (cint). Sorry for the incorrect post.sql

adding a new key; planning for millions of records

Hello all,
i'm going to be adding a new column (1 byte) that will become a non-unique
indexed value into a table that i'm adding records into. i know for a fact
that this table will grow from the current thousands to millions of records
over the span of a few ws and have allocated the database file
accordingly.
since i already have the primary key as clustered is there something i can
do so that adding this key as part of insert operations will be done
efficiently? I'm adding tens of thousands of records a day. In a previous
incarnation of this database i had had problems with having to rebuild the
keys occasionally or insert operations would timeout. I know that part of
the problem was fragmentation of the database, but i'd like to give it a
'heads up' to prepare for a large B-tree for this key.
is there a knob that will control this when i create the index?
thanks,
johnJohn,
I'm assuming you're refering to index fragmentation for the 1 byte index.
Might try creating the index with a lower FILLFACTOR to help minimize
fragmentation.
HTH
Jerry
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:usOEf6$0FHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> i'm going to be adding a new column (1 byte) that will become a non-unique
> indexed value into a table that i'm adding records into. i know for a fact
> that this table will grow from the current thousands to millions of
> records
> over the span of a few ws and have allocated the database file
> accordingly.
> since i already have the primary key as clustered is there something i can
> do so that adding this key as part of insert operations will be done
> efficiently? I'm adding tens of thousands of records a day. In a previous
> incarnation of this database i had had problems with having to rebuild the
> keys occasionally or insert operations would timeout. I know that part of
> the problem was fragmentation of the database, but i'd like to give it a
> 'heads up' to prepare for a large B-tree for this key.
> is there a knob that will control this when i create the index?
> thanks,
> john
>sql

Adding a field whose value DEPENDS on other fields in table

Hi,
Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
another field for Status (a generic field) , which has value :
1.Open if the value of the field OPEN_STATUS=T ,
2.InProcess if the value of the field INPROCESS_STATUS=T
3.Completed if COMPLETED_STATUS=T.
Is there a way to do this ?...maybe by writing these if statements? How can
this be done? I know this is not a good desgin but already many applications
are using these 3 fields which I dont want to change... So is there any way
of creating this new field in the same table based on the criteria specified
above?
Thanks for any help.
--
pmudTry creating a calculated column.
alter table t
add status as case when OPEN_STATUS='T' then 'Open' when
INPROCESS_STATUS='T' then 'InProcess' when COMPLETED_STATUS='T' then
'Completed' else 'UNKNOWN' end
go
Are those columns exclusive?, because the expressions inside CASE will be
evaluated in the same order as they appear.
AMB
"pmud" wrote:

> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to ad
d
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How ca
n
> this be done? I know this is not a good desgin but already many applicatio
ns
> are using these 3 fields which I dont want to change... So is there any wa
y
> of creating this new field in the same table based on the criteria specifi
ed
> above?
> Thanks for any help.
> --
> pmud|||You might also consider changing the name of the table. then create a view
with the original table name and include the special column in the view...
This will insulate you and theprogrammers from future changes.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:9176C167-F9FB-48D4-AA84-6DA690D02B76@.microsoft.com...
> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to
> add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How
> can
> this be done? I know this is not a good desgin but already many
> applications
> are using these 3 fields which I dont want to change... So is there any
> way
> of creating this new field in the same table based on the criteria
> specified
> above?
> Thanks for any help.
> --
> pmud|||the solution using a caculated field is good if the field does not have to b
e
indexed or searched against (slow).
If the field has to be indexed or searched and the table has lots of rows,
consider using a trigger to calculate and store the value.
"pmud" wrote:

> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to ad
d
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How ca
n
> this be done? I know this is not a good desgin but already many applicatio
ns
> are using these 3 fields which I dont want to change... So is there any wa
y
> of creating this new field in the same table based on the criteria specifi
ed
> above?
> Thanks for any help.
> --
> pmud

Adding a field whose value DEPENDS on other fields in table

Hi,
Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
another field for Status (a generic field) , which has value :
1.Open if the value of the field OPEN_STATUS=T ,
2.InProcess if the value of the field INPROCESS_STATUS=T
3.Completed if COMPLETED_STATUS=T.
Is there a way to do this ?...maybe by writing these if statements? How can
this be done? I know this is not a good desgin but already many applications
are using these 3 fields which I dont want to change... So is there any way
of creating this new field in the same table based on the criteria specified
above?
Thanks for any help.
pmud
Try creating a calculated column.
alter table t
add status as case when OPEN_STATUS='T' then 'Open' when
INPROCESS_STATUS='T' then 'InProcess' when COMPLETED_STATUS='T' then
'Completed' else 'UNKNOWN' end
go
Are those columns exclusive?, because the expressions inside CASE will be
evaluated in the same order as they appear.
AMB
"pmud" wrote:

> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How can
> this be done? I know this is not a good desgin but already many applications
> are using these 3 fields which I dont want to change... So is there any way
> of creating this new field in the same table based on the criteria specified
> above?
> Thanks for any help.
> --
> pmud
|||You might also consider changing the name of the table. then create a view
with the original table name and include the special column in the view...
This will insulate you and theprogrammers from future changes.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:9176C167-F9FB-48D4-AA84-6DA690D02B76@.microsoft.com...
> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to
> add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How
> can
> this be done? I know this is not a good desgin but already many
> applications
> are using these 3 fields which I dont want to change... So is there any
> way
> of creating this new field in the same table based on the criteria
> specified
> above?
> Thanks for any help.
> --
> pmud
|||the solution using a caculated field is good if the field does not have to be
indexed or searched against (slow).
If the field has to be indexed or searched and the table has lots of rows,
consider using a trigger to calculate and store the value.
"pmud" wrote:

> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How can
> this be done? I know this is not a good desgin but already many applications
> are using these 3 fields which I dont want to change... So is there any way
> of creating this new field in the same table based on the criteria specified
> above?
> Thanks for any help.
> --
> pmud
sql

Adding a field whose value DEPENDS on other fields in table

Hi,
Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
another field for Status (a generic field) , which has value :
1.Open if the value of the field OPEN_STATUS=T ,
2.InProcess if the value of the field INPROCESS_STATUS=T
3.Completed if COMPLETED_STATUS=T.
Is there a way to do this ?...maybe by writing these if statements? How can
this be done? I know this is not a good desgin but already many applications
are using these 3 fields which I dont want to change... So is there any way
of creating this new field in the same table based on the criteria specified
above?
Thanks for any help.
--
pmudTry creating a calculated column.
alter table t
add status as case when OPEN_STATUS='T' then 'Open' when
INPROCESS_STATUS='T' then 'InProcess' when COMPLETED_STATUS='T' then
'Completed' else 'UNKNOWN' end
go
Are those columns exclusive?, because the expressions inside CASE will be
evaluated in the same order as they appear.
AMB
"pmud" wrote:
> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How can
> this be done? I know this is not a good desgin but already many applications
> are using these 3 fields which I dont want to change... So is there any way
> of creating this new field in the same table based on the criteria specified
> above?
> Thanks for any help.
> --
> pmud|||You might also consider changing the name of the table. then create a view
with the original table name and include the special column in the view...
This will insulate you and theprogrammers from future changes.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:9176C167-F9FB-48D4-AA84-6DA690D02B76@.microsoft.com...
> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to
> add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How
> can
> this be done? I know this is not a good desgin but already many
> applications
> are using these 3 fields which I dont want to change... So is there any
> way
> of creating this new field in the same table based on the criteria
> specified
> above?
> Thanks for any help.
> --
> pmud|||the solution using a caculated field is good if the field does not have to be
indexed or searched against (slow).
If the field has to be indexed or searched and the table has lots of rows,
consider using a trigger to calculate and store the value.
"pmud" wrote:
> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How can
> this be done? I know this is not a good desgin but already many applications
> are using these 3 fields which I dont want to change... So is there any way
> of creating this new field in the same table based on the criteria specified
> above?
> Thanks for any help.
> --
> pmud

Adding a default value to a table

I'm new to SQL server and am in the process of converting an Access Db to SQL.

What I'd like to happen is for the current date and an Autonumber generated as soon as the user enters a value in another field. This was fairly straightforward in Access. When I try to set the default in design table (inSQL), the date() is not available. After a little research I came up with CREATE DEFAULT and Bind column commands, Global variables, etc.

I have a couple of questions for anyone that might know:

Is there an easier way than this to create default values in a table? AND,

Would I also have to bind columns for Table lookups? For example, if I wanted a price to be returned after the product ID was entered (Automatic in Access if relationship/form setup properly), would I have to bind the column? Or would it happen automatically?you can use getdate() or current_timestamp instead of date()

for "lookup" feature of access you can create calculated field.

Adding A default value in a column in a declare statment

Hi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

ThanksHi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

Thanks

would help us if u could post the ddl for the table|||what is the dataytpe of [Sales Rep Code]?
Off the cuff, it looks like your IND value is supposed to be a string.
Your example would be OK if you use 'IND' as [Sales Rep Code] - with the single quotes around IND.|||hi,

The datatype for SalesRep code is not defined as its in a view and I am creating the column [Sales Rep Code] with the default value of IND,

e.g

Select 'IND' AS [Sales Rep Code] from test would create a [Sales Rep Code] populated by IND for each row.

I have tried the 'IND' and it appears that it is incorrect syntax, this is beacuse it is closing the string value of the declare statment at the fist ' but I dont have a way around this.

Any Ideas|||Hi thanks for everyones help finally got it working but I still have one problem in that it is over 4000 carachters long and the @.test has a maximum of 4000 carachters is there any way around this.

Thanks

Adding "All" value to parameter field

Hi,
I just want to know if is it possible to add a "ALL" value to the drop down
list of a parameter field?
I can't seem to manually add the "ALL" value after I specify that the
parameter's values are taken from a query.
For example, I have a parameter, "Country" and its values are taken from a
query. Now I have the country values populated. How do I add the ALL value
from here?
Thanks,Use a union in the original query to add a dummy 'All' record to your
primary result set. I've used that on many similar occasions.
Julian Bowker
Marble Steps Systems
"et_ck" <etck@.discussions.microsoft.com> wrote in message
news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
> Hi,
> I just want to know if is it possible to add a "ALL" value to the drop
> down
> list of a parameter field?
> I can't seem to manually add the "ALL" value after I specify that the
> parameter's values are taken from a query.
> For example, I have a parameter, "Country" and its values are taken from a
> query. Now I have the country values populated. How do I add the ALL value
> from here?
> Thanks,|||Can we take this question one step farther? Adding a UNION will make the
word "ALL" appear in the parameter drop down list, but what technique do you
use in the report dataset to implement "ALL" in the WHERE clause?
Is there a better technique than coding:
WHERE (country = @.country OR @.country = 'ALL')
I find that I loose the ability to render the query in the GUI if I use
complex AND/OR combinations in parenthesis.
Thanks in advance.
Randy Howie
"Julian Bowker" wrote:
> Use a union in the original query to add a dummy 'All' record to your
> primary result set. I've used that on many similar occasions.
> Julian Bowker
> Marble Steps Systems
> "et_ck" <etck@.discussions.microsoft.com> wrote in message
> news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
> > Hi,
> >
> > I just want to know if is it possible to add a "ALL" value to the drop
> > down
> > list of a parameter field?
> >
> > I can't seem to manually add the "ALL" value after I specify that the
> > parameter's values are taken from a query.
> >
> > For example, I have a parameter, "Country" and its values are taken from a
> > query. Now I have the country values populated. How do I add the ALL value
> > from here?
> >
> > Thanks,
>
>|||That technique is much better than just about any other that I have seen.
Saying that you might prefer to use like and have the value be the wild card
for the database you are going against (for instance % if SQL Server). I.e.
Where (country like @.country)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Randy Howie" <Randy Howie@.discussions.microsoft.com> wrote in message
news:E849778B-9215-42B2-AC0D-702E0101B6C2@.microsoft.com...
> Can we take this question one step farther? Adding a UNION will make the
> word "ALL" appear in the parameter drop down list, but what technique do
> you
> use in the report dataset to implement "ALL" in the WHERE clause?
> Is there a better technique than coding:
> WHERE (country = @.country OR @.country = 'ALL')
> I find that I loose the ability to render the query in the GUI if I use
> complex AND/OR combinations in parenthesis.
> Thanks in advance.
> Randy Howie
> "Julian Bowker" wrote:
>> Use a union in the original query to add a dummy 'All' record to your
>> primary result set. I've used that on many similar occasions.
>> Julian Bowker
>> Marble Steps Systems
>> "et_ck" <etck@.discussions.microsoft.com> wrote in message
>> news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
>> > Hi,
>> >
>> > I just want to know if is it possible to add a "ALL" value to the drop
>> > down
>> > list of a parameter field?
>> >
>> > I can't seem to manually add the "ALL" value after I specify that the
>> > parameter's values are taken from a query.
>> >
>> > For example, I have a parameter, "Country" and its values are taken
>> > from a
>> > query. Now I have the country values populated. How do I add the ALL
>> > value
>> > from here?
>> >
>> > Thanks,
>>|||Hi Julian,
Thanks for the help.
"Julian Bowker" wrote:
> Use a union in the original query to add a dummy 'All' record to your
> primary result set. I've used that on many similar occasions.
> Julian Bowker
> Marble Steps Systems
> "et_ck" <etck@.discussions.microsoft.com> wrote in message
> news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
> > Hi,
> >
> > I just want to know if is it possible to add a "ALL" value to the drop
> > down
> > list of a parameter field?
> >
> > I can't seem to manually add the "ALL" value after I specify that the
> > parameter's values are taken from a query.
> >
> > For example, I have a parameter, "Country" and its values are taken from a
> > query. Now I have the country values populated. How do I add the ALL value
> > from here?
> >
> > Thanks,
>
>sql

add with null

I have two int fields of a table: Count1 and Count2
I select with the sql:
Select Count1+Count2 as CountSum form myTable
When one filed have a value and the other with null, the CountSum will be
null.
I wnat to treate the null value as 0 int, So I want to 2+ null=2
How can I do?ad wrote:
> I have two int fields of a table: Count1 and Count2
> I select with the sql:
> Select Count1+Count2 as CountSum form myTable
>
> When one filed have a value and the other with null, the CountSum will be
> null.
> I wnat to treate the null value as 0 int, So I want to 2+ null=2
> How can I do?
>
SELECT COALESCE(Count1, 0) + COALESCE(Count2, 0)
Tracy McKibben
MCDBA
http://www.realsqlguy.com

add with null

I have two int fields of a table: Count1 and Count2
I select with the sql:
Select Count1+Count2 as CountSum form myTable
When one filed have a value and the other with null, the CountSum will be
null.
I wnat to treate the null value as 0 int, So I want to 2+ null=2
How can I do?ad wrote:
> I have two int fields of a table: Count1 and Count2
> I select with the sql:
> Select Count1+Count2 as CountSum form myTable
>
> When one filed have a value and the other with null, the CountSum will be
> null.
> I wnat to treate the null value as 0 int, So I want to 2+ null=2
> How can I do?
>
SELECT COALESCE(Count1, 0) + COALESCE(Count2, 0)
Tracy McKibben
MCDBA
http://www.realsqlguy.com

add values with select

Hi

Thnks for the time

I need to insert some values and select a value from another table to insert

insertinto products values(10,'proname','desc',(select modelfrom Products))

How can this be done.

Try:

insert into products(c1, ..., cn)

select 10, 'proname', 'desc', model

from dbo.products

go

AMB

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

you can do it the other way around

Code Snippet

insertinto products

select 10,'proname','desc', Model

from Products

|||

Hi

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Put the result of the "select" statement into a variable and use the variable.

declare @.model varchar(25)

set @.modele = (select model from products where producti = @.productid)

insert into products values(10, 'proname', 'desc', @.model)

go

AMB

add time to datetime value and split into date and time

Hi

i have the following situation. in my database i have a datetime field (dd/mm/yy hh:mms) and i also have a field timezone.

the timezone field has values in minutes that i should add to my datetime field so i have the actual time.

afterwards i split the datetime into date and time.

the last part i can accomplish (CONVERT (varchar, datetime, 103) as DATEVALUE and CONVERT (varchar, DATETIME, 108) as TIMEVALUE).

could anybody tell me how i can add the timezone value (in minutes) to my datetime value ?

i do all the calculations in my datasource (sql).

Thanks

V.

at the end i found it myself

this is how i solved it.

to add the timezone value (in minutes) to my datetime i used following :

DATEADD(minute, TimeZone, DATETIMEVALUE) AS ACTUALDATETIME

this expression will add the timezone value (example 60) to my current date time value (12/06/2007 08:00:00) and store it in

actualdatetime value (12/06/2007 09:00:00).

from then on it's easy to extract date and time from the result.

CONVERT(varchar, (DATEADD(m,TimeZone,DATETIME)), 103) AS DATEVAL for the DATE

CONVERT(varchar, (DATEADD(m,TimeZone,DATETIME)), 108) AS TIMEVAL for the time.

i thank myself for my research .. lol

Greetings to all

Add spaces to a string value in a cell

Hi,

I am trying to add spaces to a string value in a cell but when I run the report the value is trimmed.
Any ideas?
Here is the expression I am using:
=Fields!strVal.Value & " "

Thanks,
Igor

Igor,

Try using a calculated field on your dataset, In the expression, use =" number of spaces wanted" save field name like Space5. You can then use this field in your expressions.

Ham

|||I tried and it still comes out trimmed.|||

Igor,

Can you wrap a LEN around your expression? I used this to verify that the spaces where actually there. If the spaces are there then you made have some other issue.

Ham

|||the Len function returns the correct number of characters (including the empty spaces) but the spaces just don't show up.|||

Igor,

What type of formatting are you trying to accomplish? Are you trying to align text, columns?

Ham

|||I am trying to move some of the values a little to the left but still keeping them right-justified.|||
Igor,
You can try a couple of different things (what you have should work, but if not I can only suggest these)....

=Cstr(Fields!strVal.Value) & Space(5)
or
=Cstr(Fields!strVal.Value) + Space(5)

In Data you could try Cast or Convert on the field to force it to a certain number of characters as well.|||

Igor,

Why not use your "Left-Alignment" properties on your textbox?

Ham

|||

Hello Igor,

Can you try something like this in your field's expression:

=Fields!FIELD1.Value & StrDup(5, " ")

Hope this helps.

Jarret

Add Spaces before text

Hi,

I want to add some extra spaces before a text value. I'm using the below expression

=IIF(Fields!ParentLevel.Value=2or Fields!ParentLevel.Value=1,Space(4) & UCase(Fields!Business_Unit_Name.Value),(Fields!Business_Unit_Name.Value))

Spaces are added when I preveiw the report in VS.NET IDE. However, If I deploy the report on Report Server and view the report in Browser (IE7) I'm not able to see extra spaces that I'm seeing in preview.

Is there anything I'm missing here?

Quick help is highly appreciated.

Thanks and Regards,
Chakra

Hi,
This will more than likely be a html render "issue" as opposed to your code.
Instead of inserting spaces try inserting 4 non-breaking spaces...

add seconds to time.

Hello how would i add 5 seconds to this value in a select statement?
2005-02-16 04:12:44.000Use DATEADD function.
declare @.d datetime
set @.d = '2005-02-16 04:12:44.000'
select dateadd(second, 5, @.d)
go
AMB
"Fab" wrote:

> Hello how would i add 5 seconds to this value in a select statement?
> 2005-02-16 04:12:44.000
>
>

Add one more Value

How can I add one more Value besides "Hireback", I need to add "Hireback3".
I tried using (OR) and could not get it to work.
=iif(Sum(iif(Fields!Workday_Type.Value="Hireback",1,0)) > 0,
Sum(iif(Fields!Workday_Type.Value="Hireback",1,0)),"")
Thank you
FrankOn Jan 11, 9:51 am, Frank <Fr...@.discussions.microsoft.com> wrote:
> How can I add one more Value besides "Hireback", I need to add "Hireback3".
> I tried using (OR) and could not get it to work.
> =iif(Sum(iif(Fields!Workday_Type.Value="Hireback",1,0)) > 0,
> Sum(iif(Fields!Workday_Type.Value="Hireback",1,0)),"")
> Thank you
> Frank
This expression should work.
=switch(Sum(iif(Fields!Workday_Type.Value="Hireback",1,0)) > 0,
Sum(iif(Fields!Workday_Type.Value="Hireback",1,0)), Sum(iif(Fields!
Workday_Type.Value="Hireback3",1,0)) > 0, Sum(iif(Fields!
Workday_Type.Value="Hireback3",1,0)), true, "")
Regards,
Enrique Martinez
Sr. Software Consultant

Add New Record into table

Hi , All
I default value in textbox for show now date and I used to method

txtDate.Text = Now.ToShortDateString

It's have problem when I want to add new record in table

I get error message "The statement has been terminated. String or binary data would be truncated. "

and I set type in database is char length 10 ,I can't set type is datetime because in SQL Server fix type datetime

It's have length 8 but 8 length it's not enough for default value
Please help me
Thanks
Nisarat

First use DateTime.Now.ToShortDateString
Secondly use 'yyyy-MM-dd' eg. '2005-06-01'(notice the single quote ' ) format while inserting data.
|||

Hi, dinuj

I set type of field date is datetime and set default value "dd/mm/yyyy" but I get error message "Error validating the default for column "datekey""
Thanks
Nisarat

|||Do mean to say that you are trying to set a default date for the coloumn datekey
|||

Hi,Dinuj

yes ,I trying set type of field DateKey is DateTime type but ,in SQLServer it's 8 char only and I will trying to defaulf value in this field but It's can not
Thanks
Nisarat

|||

Nisarat wrote:

Hi,Dinuj

yes ,I trying set type of field DateKey isDateTime type but ,in SQLServer it's 8 char only and I willtrying to defaulf value in this field but It's can not
Thanks
Nisarat

Is your DateKey column is of DateTime type? DateTime is 8 bytes widenot 8 chars. Are you using Enterprise Manager ? If yes then clickon Default value and set the default date to whatever you want in thisformat 'yyyyMMdd' . For example for today '20050606'

Add Line for Series Group Total to chart

Have trend chart with lines for monthly costs for individual assets. I need
to add another line for the total each month. When I add a new Value to sum
the costs, I get duplicate lines on the chart. What am I doing wrong?
TIA
DeanIn your new Value set the following value:
= sum(Fields!Yourfield.Value, "DATASETNAME")
Hope this helps.
"Dean" <deanl144@.hotmail.com.nospam> escribió en el mensaje
news:uv2uapZEIHA.3548@.TK2MSFTNGP06.phx.gbl...
> Have trend chart with lines for monthly costs for individual assets. I
> need to add another line for the total each month. When I add a new Value
> to sum the costs, I get duplicate lines on the chart. What am I doing
> wrong?
> TIA
> Dean
>

Add leading zero to field value via SP

Hi All,

I want to add a leading zero to a field based on a param that I create on the fly in my stored proc. I have a @.month which is created from my datetime param @.date.

@.Month needs to be char(2) but if the month is inputted as '04' I get '4 ' in the table (note the space after 4)

How can I add a leading zero to this field?
Set @.Year = right('0',1)year(@.Date) is spitting it's toys out.

Thanks,
Brett

prepend a '0' char to the front of it and take RIGHT('0'+yourString,2)

Add Datafile

I have a need to add a secondary datafile. My question pertains to the
FIlegroups tab in Enterprise Manager. Do I need to change the value of the
Primary Filegroup 'Files' from 1 to 2? From researching, I believe that a
database can only have one primary file.
Hi
I prefer to do such things by QA not by EM
CREATE DATABASE databasename
GO
ALTER DATABASE databasename SET RECOVERY FULL
ALTER DATABASE databasename ADD FILEGROUP new_customers
ALTER DATABASE databasename ADD FILEGROUP sales
GO
ALTER DATABASE databasenameADD FILE
(NAME=databasename_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE databasename
ADD FILE
(NAME='databasename_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:560E20AD-0955-44A6-95AF-6C853151F478@.microsoft.com...
>I have a need to add a secondary datafile. My question pertains to the
> FIlegroups tab in Enterprise Manager. Do I need to change the value of
> the
> Primary Filegroup 'Files' from 1 to 2? From researching, I believe that a
> database can only have one primary file.