Boolean parameter default issue on report server

Hi Folks,
I have a report with a boolean parameter(Show Reversed Invoices). I default
it to 'No' and this works in the BIDS environment just as i want with a drop
down menu, when deployed to the reportserver it renders as True/False radio
buttons however, the False radio button is not automatically checked. What am
I missing in order to have this happen?
Thanks for your help.On Nov 3, 2:07 pm, Stevie <Ste...@.discussions.microsoft.com> wrote:
> Hi Folks,
> I have a report with a boolean parameter(Show Reversed Invoices). I default
> it to 'No' and this works in the BIDS environment just as i want with a drop
> down menu, when deployed to the reportserver it renders as True/False radio
> buttons however, the False radio button is not automatically checked. What am
> I missing in order to have this happen?
> Thanks for your help.
When you say you set the default to 'No', do you mean 'False'? If not,
you will want to change the default value to False. If so, you may
want to check the Service Pack level of SQL Server on the Report
Server, it may not be the latest and you may need to get the latest
service pack for your version of SQL Server (if SQL 2005, SP2). Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique,
Thanks for responding. I just spoke to my manager about the service pack
level and he said we're at SP1+ and that we're going to SP2 soon. Once we get
there I'll let you know the results thereafter.
Best regards
"EMartinez" wrote:
> On Nov 3, 2:07 pm, Stevie <Ste...@.discussions.microsoft.com> wrote:
> > Hi Folks,
> >
> > I have a report with a boolean parameter(Show Reversed Invoices). I default
> > it to 'No' and this works in the BIDS environment just as i want with a drop
> > down menu, when deployed to the reportserver it renders as True/False radio
> > buttons however, the False radio button is not automatically checked. What am
> > I missing in order to have this happen?
> >
> > Thanks for your help.
>
> When you say you set the default to 'No', do you mean 'False'? If not,
> you will want to change the default value to False. If so, you may
> want to check the Service Pack level of SQL Server on the Report
> Server, it may not be the latest and you may need to get the latest
> service pack for your version of SQL Server (if SQL 2005, SP2). Hope
> this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Nov 5, 9:05 am, Stevie <Ste...@.discussions.microsoft.com> wrote:
> Hi Enrique,
> Thanks for responding. I just spoke to my manager about the service pack
> level and he said we're at SP1+ and that we're going to SP2 soon. Once we get
> there I'll let you know the results thereafter.
> Best regards
> "EMartinez" wrote:
> > On Nov 3, 2:07 pm, Stevie <Ste...@.discussions.microsoft.com> wrote:
> > > Hi Folks,
> > > I have a report with a boolean parameter(Show Reversed Invoices). I default
> > > it to 'No' and this works in the BIDS environment just as i want with a drop
> > > down menu, when deployed to the reportserver it renders as True/False radio
> > > buttons however, the False radio button is not automatically checked. What am
> > > I missing in order to have this happen?
> > > Thanks for your help.
> > When you say you set the default to 'No', do you mean 'False'? If not,
> > you will want to change the default value to False. If so, you may
> > want to check the Service Pack level of SQL Server on the Report
> > Server, it may not be the latest and you may need to get the latest
> > service pack for your version of SQL Server (if SQL 2005, SP2). Hope
> > this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||It doesn't seem to be a Service Pack problem. I encountered the same exact
problem on SQL 2000 SP4. Oddly enough, my report had two boolean parameters,
the first of which defaulted correctly on the published Reporting Services
site, and the second did not (however, both worked fine in the Visual Studio
developer environment).
I was able to workaround the problem by renaming the parameter. At that
point, everything started working fine.
"EMartinez" wrote:
> On Nov 5, 9:05 am, Stevie <Ste...@.discussions.microsoft.com> wrote:
> > Hi Enrique,
> >
> > Thanks for responding. I just spoke to my manager about the service pack
> > level and he said we're at SP1+ and that we're going to SP2 soon. Once we get
> > there I'll let you know the results thereafter.
> >
> > Best regards
> >
> > "EMartinez" wrote:
> > > On Nov 3, 2:07 pm, Stevie <Ste...@.discussions.microsoft.com> wrote:
> > > > Hi Folks,
> >
> > > > I have a report with a boolean parameter(Show Reversed Invoices). I default
> > > > it to 'No' and this works in the BIDS environment just as i want with a drop
> > > > down menu, when deployed to the reportserver it renders as True/False radio
> > > > buttons however, the False radio button is not automatically checked. What am
> > > > I missing in order to have this happen?
> >
> > > > Thanks for your help.
> >
> > > When you say you set the default to 'No', do you mean 'False'? If not,
> > > you will want to change the default value to False. If so, you may
> > > want to check the Service Pack level of SQL Server on the Report
> > > Server, it may not be the latest and you may need to get the latest
> > > service pack for your version of SQL Server (if SQL 2005, SP2). Hope
> > > this helps.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. Software Consultant
>
> You're welcome. Let me know if I can be of further assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptest
If you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest
|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>
|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:
> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> >
> > Hi,
> >
> > I'm doing dynamic queries. When I try to insert blank or null date data
> > into a table, I'm getting the default value of '01-01-2000' or
> > '01-01'1900'
> > respectively. What I really want it to do is insert a null into the
> > table.
> >
> > I created some sample code below. Can anyone help?
> >
> > create table #mytemptest
> > (testdate smalldatetime null
> > )
> >
> > SET CONCAT_NULL_YIELDS_NULL OFF
> >
> > declare @.query as varchar(1000)
> > declare @.inputdate as smalldatetime
> >
> > select @.inputdate=''
> > select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select @.inputdate=null
> > select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select * from #mytemptest
> > drop table #mytemptest
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10
), 'NULL') + ''''

bit data type

I run a simple select statement to a table that selects
data. One of the column's data type is "bit" size "1" set
to default to ((-1)).
Here is the problem, when I run a select statement in
query analyzer, all data from that column is "1". When I
run the same query and use DTS to export the data to a
text file, all the data from that column is "true". What
does that mean and how can I get the data to just be "1"?
Please help.
Thanks. John.
The valid values of a BIT column are 0, 1 and NULL, so there's no good
reason to default the column to -1. The -1 will be implicitly cast to 1,
which is one of the peculiarities of the BIT datatype.
DTS assumes that you want BIT columns to be treated as Boolean values and
chooses to export them as the strings "True" and "False". You can avoid this
by casting the value as an INTEGER in your transformation. Alternatively,
choose another numeric type for the column instead of BIT.
David Portas
SQL Server MVP
|||Thank you very much. That makes sense.
John

>--Original Message--
>The valid values of a BIT column are 0, 1 and NULL, so
there's no good
>reason to default the column to -1. The -1 will be
implicitly cast to 1,
>which is one of the peculiarities of the BIT datatype.
>DTS assumes that you want BIT columns to be treated as
Boolean values and
>chooses to export them as the strings "True" and "False".
You can avoid this
>by casting the value as an INTEGER in your
transformation. Alternatively,
>choose another numeric type for the column instead of BIT.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>
|||Hi
VB: -1 = True
All other languages: +1 = True.
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:18ba01c47be5$ba2b8cc0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thank you very much. That makes sense.
> John
>
> there's no good
> implicitly cast to 1,
> Boolean values and
> You can avoid this
> transformation. Alternatively,

bit data type

I run a simple select statement to a table that selects
data. One of the column's data type is "bit" size "1" set
to default to ((-1)).
Here is the problem, when I run a select statement in
query analyzer, all data from that column is "1". When I
run the same query and use DTS to export the data to a
text file, all the data from that column is "true". What
does that mean and how can I get the data to just be "1"?
Please help.
Thanks. John.The valid values of a BIT column are 0, 1 and NULL, so there's no good
reason to default the column to -1. The -1 will be implicitly cast to 1,
which is one of the peculiarities of the BIT datatype.
DTS assumes that you want BIT columns to be treated as Boolean values and
chooses to export them as the strings "True" and "False". You can avoid this
by casting the value as an INTEGER in your transformation. Alternatively,
choose another numeric type for the column instead of BIT.
--
David Portas
SQL Server MVP
--|||Thank you very much. That makes sense.
John
>--Original Message--
>The valid values of a BIT column are 0, 1 and NULL, so
there's no good
>reason to default the column to -1. The -1 will be
implicitly cast to 1,
>which is one of the peculiarities of the BIT datatype.
>DTS assumes that you want BIT columns to be treated as
Boolean values and
>chooses to export them as the strings "True" and "False".
You can avoid this
>by casting the value as an INTEGER in your
transformation. Alternatively,
>choose another numeric type for the column instead of BIT.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>|||Hi
VB: -1 = True
All other languages: +1 = True.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:18ba01c47be5$ba2b8cc0$a601280a@.phx.gbl...
> Thank you very much. That makes sense.
> John
>
> >--Original Message--
> >The valid values of a BIT column are 0, 1 and NULL, so
> there's no good
> >reason to default the column to -1. The -1 will be
> implicitly cast to 1,
> >which is one of the peculiarities of the BIT datatype.
> >
> >DTS assumes that you want BIT columns to be treated as
> Boolean values and
> >chooses to export them as the strings "True" and "False".
> You can avoid this
> >by casting the value as an INTEGER in your
> transformation. Alternatively,
> >choose another numeric type for the column instead of BIT.
> >
> >--
> >David Portas
> >SQL Server MVP
> >--
> >
> >
> >.
> >