Frustrating Newbie Parameter Question

Is it possible to create a parameter where a NULL value returns ALL records
and entering a value returns the filtered data? I am filtering a date field
"ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end user a
choice of filtering the dataset with a paramater or leaving it blank and
returning records with all years.
There must be an easy way to do this. I am aware of placing an iif
statement in the dataset paramater
(i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
Parameters!Parameter1.Value)),
but if the parameter is empty, how will this return ALL records? I am
confused and would appreciate any help.
My query looks like this...
SELECT OrderDetail.*
FROM OrderDetail
WHERE (fkCategoryID = 4) AND (ConvYear = ?)
Thanks!Two points, unless you have to use a filter, don't (hard for me to tell if
you are using a filter or not). A filter first retrieves all the data
defined in the dataset and then filters it. So for instance with what you
have below, you should use that query as the definition for your dataset.
Also note that for a query parameter you use @.whateveryouwanttocallit if you
are going against SQL Server and ? if going against oledb or odbc datasourc.
OK, the trick here is to use like. Then for your parameters have an All that
returns a %. To test this out first try have your labels and values be a
list that you input on the report parameters screen (you can also use a
Union query to do this as well). What I like about this solution is you
don't have to use the generic query screen plus you don't lose having a
field list. Anyway, give it a try.
SELECT OrderDetail.* FROM OrderDetail WHERE (fkCategoryID =4) AND (ConvYear like ?)
Bruce L-C
"Brian" <bcovington@.hotmail.com> wrote in message
news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a parameter where a NULL value returns ALL
records
> and entering a value returns the filtered data? I am filtering a date
field
> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end user
a
> choice of filtering the dataset with a paramater or leaving it blank and
> returning records with all years.
> There must be an easy way to do this. I am aware of placing an iif
> statement in the dataset paramater
> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
> Parameters!Parameter1.Value)),
> but if the parameter is empty, how will this return ALL records? I am
> confused and would appreciate any help.
> My query looks like this...
> SELECT OrderDetail.*
> FROM OrderDetail
> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
> Thanks!
>
>
>
>|||This works great! Would this be a good solution if I wanted say 5 optional
parameters in a report, or is the LIKE clause too slow? I have about 20,000
records to search on, but the report isn't run very often.
Also, I have seen others discuss using dynamic SQL with iff statements to
obtain the same results. Would this possibly be a more efficient solution
than the LIKE clause?
Thanks,
Brian
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
> Two points, unless you have to use a filter, don't (hard for me to tell if
> you are using a filter or not). A filter first retrieves all the data
> defined in the dataset and then filters it. So for instance with what you
> have below, you should use that query as the definition for your dataset.
> Also note that for a query parameter you use @.whateveryouwanttocallit if
> you
> are going against SQL Server and ? if going against oledb or odbc
> datasourc.
> OK, the trick here is to use like. Then for your parameters have an All
> that
> returns a %. To test this out first try have your labels and values be a
> list that you input on the report parameters screen (you can also use a
> Union query to do this as well). What I like about this solution is you
> don't have to use the generic query screen plus you don't lose having a
> field list. Anyway, give it a try.
> SELECT OrderDetail.* FROM OrderDetail WHERE (fkCategoryID => 4) AND (ConvYear like ?)
> Bruce L-C
> "Brian" <bcovington@.hotmail.com> wrote in message
> news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
>> Is it possible to create a parameter where a NULL value returns ALL
> records
>> and entering a value returns the filtered data? I am filtering a date
> field
>> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end user
> a
>> choice of filtering the dataset with a paramater or leaving it blank and
>> returning records with all years.
>> There must be an easy way to do this. I am aware of placing an iif
>> statement in the dataset paramater
>> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
>> Parameters!Parameter1.Value)),
>> but if the parameter is empty, how will this return ALL records? I am
>> confused and would appreciate any help.
>> My query looks like this...
>> SELECT OrderDetail.*
>> FROM OrderDetail
>> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
>> Thanks!
>>
>>
>>
>|||I prefer the method I showed you because it is soooo much easier to develop,
you get the list of fields, you can use the normal query designer. Whether
it is slow or not depends on the database you are going against. If it is
SQL Server 2000 and you have 20,000 records it won't even breath hard.
20,000 is nothing. I suggest trying it this way, if performance is a problem
you can always go to dynamic sql.
Bruce L-C
"Brian" <bcovington@.hotmail.com> wrote in message
news:eb2du30nEHA.2024@.TK2MSFTNGP09.phx.gbl...
> This works great! Would this be a good solution if I wanted say 5
optional
> parameters in a report, or is the LIKE clause too slow? I have about
20,000
> records to search on, but the report isn't run very often.
> Also, I have seen others discuss using dynamic SQL with iff statements to
> obtain the same results. Would this possibly be a more efficient solution
> than the LIKE clause?
> Thanks,
> Brian
>
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
> > Two points, unless you have to use a filter, don't (hard for me to tell
if
> > you are using a filter or not). A filter first retrieves all the data
> > defined in the dataset and then filters it. So for instance with what
you
> > have below, you should use that query as the definition for your
dataset.
> >
> > Also note that for a query parameter you use @.whateveryouwanttocallit if
> > you
> > are going against SQL Server and ? if going against oledb or odbc
> > datasourc.
> >
> > OK, the trick here is to use like. Then for your parameters have an All
> > that
> > returns a %. To test this out first try have your labels and values be a
> > list that you input on the report parameters screen (you can also use a
> > Union query to do this as well). What I like about this solution is you
> > don't have to use the generic query screen plus you don't lose having a
> > field list. Anyway, give it a try.
> > SELECT OrderDetail.* FROM OrderDetail WHERE (fkCategoryID
=> > 4) AND (ConvYear like ?)
> >
> > Bruce L-C
> >
> > "Brian" <bcovington@.hotmail.com> wrote in message
> > news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
> >> Is it possible to create a parameter where a NULL value returns ALL
> > records
> >> and entering a value returns the filtered data? I am filtering a date
> > field
> >> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end
user
> > a
> >> choice of filtering the dataset with a paramater or leaving it blank
and
> >> returning records with all years.
> >>
> >> There must be an easy way to do this. I am aware of placing an iif
> >> statement in the dataset paramater
> >> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = " +
> >> Parameters!Parameter1.Value)),
> >> but if the parameter is empty, how will this return ALL records? I am
> >> confused and would appreciate any help.
> >>
> >> My query looks like this...
> >>
> >> SELECT OrderDetail.*
> >> FROM OrderDetail
> >> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
> >>
> >> Thanks!
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>|||Bruce,
Your time was very much appreciated. Thank you!
Brian
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ueS7sG1nEHA.3216@.tk2msftngp13.phx.gbl...
>I prefer the method I showed you because it is soooo much easier to
>develop,
> you get the list of fields, you can use the normal query designer. Whether
> it is slow or not depends on the database you are going against. If it is
> SQL Server 2000 and you have 20,000 records it won't even breath hard.
> 20,000 is nothing. I suggest trying it this way, if performance is a
> problem
> you can always go to dynamic sql.
> Bruce L-C
> "Brian" <bcovington@.hotmail.com> wrote in message
> news:eb2du30nEHA.2024@.TK2MSFTNGP09.phx.gbl...
>> This works great! Would this be a good solution if I wanted say 5
> optional
>> parameters in a report, or is the LIKE clause too slow? I have about
> 20,000
>> records to search on, but the report isn't run very often.
>> Also, I have seen others discuss using dynamic SQL with iff statements to
>> obtain the same results. Would this possibly be a more efficient
>> solution
>> than the LIKE clause?
>> Thanks,
>> Brian
>>
>>
>> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
>> > Two points, unless you have to use a filter, don't (hard for me to tell
> if
>> > you are using a filter or not). A filter first retrieves all the data
>> > defined in the dataset and then filters it. So for instance with what
> you
>> > have below, you should use that query as the definition for your
> dataset.
>> >
>> > Also note that for a query parameter you use @.whateveryouwanttocallit
>> > if
>> > you
>> > are going against SQL Server and ? if going against oledb or odbc
>> > datasourc.
>> >
>> > OK, the trick here is to use like. Then for your parameters have an All
>> > that
>> > returns a %. To test this out first try have your labels and values be
>> > a
>> > list that you input on the report parameters screen (you can also use a
>> > Union query to do this as well). What I like about this solution is you
>> > don't have to use the generic query screen plus you don't lose having a
>> > field list. Anyway, give it a try.
>> > SELECT OrderDetail.* FROM OrderDetail WHERE
>> > (fkCategoryID
> =>> > 4) AND (ConvYear like ?)
>> >
>> > Bruce L-C
>> >
>> > "Brian" <bcovington@.hotmail.com> wrote in message
>> > news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
>> >> Is it possible to create a parameter where a NULL value returns ALL
>> > records
>> >> and entering a value returns the filtered data? I am filtering a date
>> > field
>> >> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end
> user
>> > a
>> >> choice of filtering the dataset with a paramater or leaving it blank
> and
>> >> returning records with all years.
>> >>
>> >> There must be an easy way to do this. I am aware of placing an iif
>> >> statement in the dataset paramater
>> >> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear = "
>> >> +
>> >> Parameters!Parameter1.Value)),
>> >> but if the parameter is empty, how will this return ALL records? I am
>> >> confused and would appreciate any help.
>> >>
>> >> My query looks like this...
>> >>
>> >> SELECT OrderDetail.*
>> >> FROM OrderDetail
>> >> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
>> >>
>> >> Thanks!
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||We've used a statement in the where clause like :
where (ssn.Item_Number = IsNull(@.Stock_Code, ssn.Item_Number))
which returns all items if the sp parameter is null of uses the parameter
value to filter.
Julian
"Brian" <bcovington@.hotmail.com> wrote in message
news:uJyf4T1nEHA.132@.TK2MSFTNGP09.phx.gbl...
> Bruce,
> Your time was very much appreciated. Thank you!
> Brian
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:ueS7sG1nEHA.3216@.tk2msftngp13.phx.gbl...
> >I prefer the method I showed you because it is soooo much easier to
> >develop,
> > you get the list of fields, you can use the normal query designer.
Whether
> > it is slow or not depends on the database you are going against. If it
is
> > SQL Server 2000 and you have 20,000 records it won't even breath hard.
> > 20,000 is nothing. I suggest trying it this way, if performance is a
> > problem
> > you can always go to dynamic sql.
> >
> > Bruce L-C
> >
> > "Brian" <bcovington@.hotmail.com> wrote in message
> > news:eb2du30nEHA.2024@.TK2MSFTNGP09.phx.gbl...
> >> This works great! Would this be a good solution if I wanted say 5
> > optional
> >> parameters in a report, or is the LIKE clause too slow? I have about
> > 20,000
> >> records to search on, but the report isn't run very often.
> >>
> >> Also, I have seen others discuss using dynamic SQL with iff statements
to
> >> obtain the same results. Would this possibly be a more efficient
> >> solution
> >> than the LIKE clause?
> >>
> >> Thanks,
> >>
> >> Brian
> >>
> >>
> >>
> >>
> >> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:%23qKTNF0nEHA.1152@.TK2MSFTNGP10.phx.gbl...
> >> > Two points, unless you have to use a filter, don't (hard for me to
tell
> > if
> >> > you are using a filter or not). A filter first retrieves all the data
> >> > defined in the dataset and then filters it. So for instance with what
> > you
> >> > have below, you should use that query as the definition for your
> > dataset.
> >> >
> >> > Also note that for a query parameter you use @.whateveryouwanttocallit
> >> > if
> >> > you
> >> > are going against SQL Server and ? if going against oledb or odbc
> >> > datasourc.
> >> >
> >> > OK, the trick here is to use like. Then for your parameters have an
All
> >> > that
> >> > returns a %. To test this out first try have your labels and values
be
> >> > a
> >> > list that you input on the report parameters screen (you can also use
a
> >> > Union query to do this as well). What I like about this solution is
you
> >> > don't have to use the generic query screen plus you don't lose having
a
> >> > field list. Anyway, give it a try.
> >> > SELECT OrderDetail.* FROM OrderDetail WHERE
> >> > (fkCategoryID
> > => >> > 4) AND (ConvYear like ?)
> >> >
> >> > Bruce L-C
> >> >
> >> > "Brian" <bcovington@.hotmail.com> wrote in message
> >> > news:%23vYpB7znEHA.2024@.TK2MSFTNGP09.phx.gbl...
> >> >> Is it possible to create a parameter where a NULL value returns ALL
> >> > records
> >> >> and entering a value returns the filtered data? I am filtering a
date
> >> > field
> >> >> "ConvYear" (i.e. 2001, 2002, 2003,etc..) and I want to give the end
> > user
> >> > a
> >> >> choice of filtering the dataset with a paramater or leaving it blank
> > and
> >> >> returning records with all years.
> >> >>
> >> >> There must be an easy way to do this. I am aware of placing an iif
> >> >> statement in the dataset paramater
> >> >> (i.e. =iif(Parameters!Parameter1.Value = "",""," WHERE odConvYear ="
> >> >> +
> >> >> Parameters!Parameter1.Value)),
> >> >> but if the parameter is empty, how will this return ALL records? I
am
> >> >> confused and would appreciate any help.
> >> >>
> >> >> My query looks like this...
> >> >>
> >> >> SELECT OrderDetail.*
> >> >> FROM OrderDetail
> >> >> WHERE (fkCategoryID = 4) AND (ConvYear = ?)
> >> >>
> >> >> Thanks!
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Excellent tip! The visual designer messes up statements like:
(@.ItemNo Is Null OR ItemNumber = @.ItemNo)sql

Frequent errors in Application Event Viewer

Hi all,
I encounter many of this errors in Application event viewer regarding
SQL server 2005:
Type: Error
Date: 07/03/2008
Time: 8.54.24
Event: 19019
Source: MSSQL$INSTNEW
Category: Failover
User: N/A
Computer: NEW
Description:
[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
Type: Error
Date: 07/03/2008
Time: 8.54.24
Event: 19019
Source: MSSQL$INSTNEW
Category: Failover
User: N/A
Computer: NEW
Description:
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746;
message = [Microsoft][SQL Native Client]TCP Provider: An existing
connection was
forcibly closed by the remote host.
Type: Error
Date: 07/03/2008
Time: 8.54.24
Event: 19019
Source: MSSQL$INSTNEW
Category: Failover
User: N/A
Computer: NEW
Description:
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746;
message = [Microsoft][SQL Native Client]Communication link failure
Type: Error
Date: 07/03/2008
Time: 8.54.24
Event: 19019
Source: MSSQL$INSTNEW
Category: Failover
User: N/A
Computer: NEW
Description:
[sqsrvres] OnlineThread: QP is not online.
They occours every time in this order. 6-7 times a day.
I've searched but individually, they seem to be generics errors.
Could you help me?
Thanks in advance
TonyThat is the cluster service checking to see if the SQL Server is responsive.
Get enough of those in a row, and cluster serv tries to restart or failover
the sql group
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Tony_da_Roma" <killerloop@.post.com> wrote in message
news:ff950762-ed63-4c69-9e5e-f45a5386b2e4@.n58g2000hsf.googlegroups.com...
> Hi all,
> I encounter many of this errors in Application event viewer regarding
> SQL server 2005:
> Type: Error
> Date: 07/03/2008
> Time: 8.54.24
> Event: 19019
> Source: MSSQL$INSTNEW
> Category: Failover
> User: N/A
> Computer: NEW
> Description:
> [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
>
> Type: Error
> Date: 07/03/2008
> Time: 8.54.24
> Event: 19019
> Source: MSSQL$INSTNEW
> Category: Failover
> User: N/A
> Computer: NEW
> Description:
> [sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746;
> message = [Microsoft][SQL Native Client]TCP Provider: An existing
> connection was
> forcibly closed by the remote host.
>
> Type: Error
> Date: 07/03/2008
> Time: 8.54.24
> Event: 19019
> Source: MSSQL$INSTNEW
> Category: Failover
> User: N/A
> Computer: NEW
> Description:
> [sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746;
> message = [Microsoft][SQL Native Client]Communication link failure
>
> Type: Error
> Date: 07/03/2008
> Time: 8.54.24
> Event: 19019
> Source: MSSQL$INSTNEW
> Category: Failover
> User: N/A
> Computer: NEW
> Description:
> [sqsrvres] OnlineThread: QP is not online.
> They occours every time in this order. 6-7 times a day.
> I've searched but individually, they seem to be generics errors.
> Could you help me?
> Thanks in advance
> Tony

Freeze panes like excel

Is there a chance that columns used in sql reporting services can be freezed
as in excel work sheet.
Is it not implemented till date, if they implement when can we expect that
to happen for sql reporting services.
Freezin pane is one part they even want to freeze there own columns
dynamically.
Is there a chance in near future we have this provision, if we have it would
be great.
Any thought of when can we expect it.
Askin a lot :-)
NavinNavin,
There is a setting in RS that you can apply per object (matrix, table), in
which you can specifiy whether you want the header to remain visible when
scrolling. Look for this on the general tab. Name of the setting is "Header
should remain visible when scrolling".
(this is in RS2005)
regards,
Perry
"NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
news:9E12F763-4546-4C89-9347-C2E7F479E1B0@.microsoft.com...
> Is there a chance that columns used in sql reporting services can be
> freezed
> as in excel work sheet.
> Is it not implemented till date, if they implement when can we expect that
> to happen for sql reporting services.
> Freezin pane is one part they even want to freeze there own columns
> dynamically.
> Is there a chance in near future we have this provision, if we have it
> would
> be great.
> Any thought of when can we expect it.
> Askin a lot :-)
> Navin|||Freezing columns is big priority for us as well, and I've asked about
about it several times, but the feature is not available.
And I wouldn't expect it in a subsequent release, just based on the
technical issues to implement it, but maybe I'll be surprised.
NAVIN.D wrote:
> Is there a chance that columns used in sql reporting services can be freezed
> as in excel work sheet.
> Is it not implemented till date, if they implement when can we expect that
> to happen for sql reporting services.
> Freezin pane is one part they even want to freeze there own columns
> dynamically.
> Is there a chance in near future we have this provision, if we have it would
> be great.
> Any thought of when can we expect it.
> Askin a lot :-)
> Navin|||Seems certain part of my question solved, look forward for dynamic selection
as well, Thank for the info Perry
"Perry" wrote:
> Navin,
> There is a setting in RS that you can apply per object (matrix, table), in
> which you can specifiy whether you want the header to remain visible when
> scrolling. Look for this on the general tab. Name of the setting is "Header
> should remain visible when scrolling".
> (this is in RS2005)
> regards,
> Perry
>
> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
> news:9E12F763-4546-4C89-9347-C2E7F479E1B0@.microsoft.com...
> > Is there a chance that columns used in sql reporting services can be
> > freezed
> > as in excel work sheet.
> >
> > Is it not implemented till date, if they implement when can we expect that
> > to happen for sql reporting services.
> >
> > Freezin pane is one part they even want to freeze there own columns
> > dynamically.
> >
> > Is there a chance in near future we have this provision, if we have it
> > would
> > be great.
> >
> > Any thought of when can we expect it.
> >
> > Askin a lot :-)
> >
> > Navin
>
>|||unfortunatly it dont work the way i intended i want column to be scrolled as
well not only headers, supose i have 20 columns i want only two columns to be
freezed. and as i traversve through other columns the 2 columns which are
freezed still exists.
"Perry" wrote:
> Navin,
> There is a setting in RS that you can apply per object (matrix, table), in
> which you can specifiy whether you want the header to remain visible when
> scrolling. Look for this on the general tab. Name of the setting is "Header
> should remain visible when scrolling".
> (this is in RS2005)
> regards,
> Perry
>
> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
> news:9E12F763-4546-4C89-9347-C2E7F479E1B0@.microsoft.com...
> > Is there a chance that columns used in sql reporting services can be
> > freezed
> > as in excel work sheet.
> >
> > Is it not implemented till date, if they implement when can we expect that
> > to happen for sql reporting services.
> >
> > Freezin pane is one part they even want to freeze there own columns
> > dynamically.
> >
> > Is there a chance in near future we have this provision, if we have it
> > would
> > be great.
> >
> > Any thought of when can we expect it.
> >
> > Askin a lot :-)
> >
> > Navin
>
>|||Header scrolling dont work for matrix, only table header got that option.
"cowznofsky" wrote:
> Freezing columns is big priority for us as well, and I've asked about
> about it several times, but the feature is not available.
> And I wouldn't expect it in a subsequent release, just based on the
> technical issues to implement it, but maybe I'll be surprised.
> NAVIN.D wrote:
> > Is there a chance that columns used in sql reporting services can be freezed
> > as in excel work sheet.
> >
> > Is it not implemented till date, if they implement when can we expect that
> > to happen for sql reporting services.
> >
> > Freezin pane is one part they even want to freeze there own columns
> > dynamically.
> >
> > Is there a chance in near future we have this provision, if we have it would
> > be great.
> >
> > Any thought of when can we expect it.
> >
> > Askin a lot :-)
> >
> > Navin
>