Conditionally showing/hiding a parameter

I have a report that in most cases takes 4 parameters. In a few special cases, though, it takes 5 parameters. I have my code modified enough such that it can handle this exception, however I cannot figure out how to conditionally show the 5th parameter based on the 4th parameter. Is this possible?

how to conditionally show the 5th parameter based on the 4th parameter. Is this possible?

Yes, this is possible. Lets say your 5th parameter is driven off of a query, then you can pass what ever is selected in the 4th parameter as an input to the query. Also in the report parameter your 5th parameter should be below the 4th parameter meaning the order of the parameters is important.

Let me know if you would want me to elaborate this further.

|||

You're describing how to change the available values of the 5th parameter based on the choice made for the 4th parameter. I think the original question is it possible to show or hide the 5th parameter based on the choice made for the 4th parameter.

I have never found a way to conditionally hide parameters and don't believe it's possible. If I'm wrong then I'd love to hear how to do it. When we've encountered cases like this we've had to just change the values of the 5th parameter (as Techquest has described) to have only a single item available such as "n/a" and then defaulted to that value.

I'd also love to hear if there's a way to conditionally change the label for parameters. I don't believe this is possible either but there's a lot of smart people out there and it would be great if someone could show me that I'm wrong.

tia

-bruce

|||Bruce, that's what I wanted to do.

I haven't been working with SSRS for too long, I wonder if it's possible to write a script or extension to allow more flexibility in the parameters.

I like the N/A idea though, that'll work if I can't figure out anything new.

Thanks,

Erik

Conditionally load Drop downs in Parameter toolbar

In trying to incorporate business rules into my SSRS report, I need to be
able to conditionally load drop downs based upon what the selects for other
drop downs.
Can anyone tell me how? Example:
DropDown1 = Country
DropDown2 = State/Region
How Can i leave DropDown2 empty until they select from DropDown1?
Thanks.Hi JrMcG,
Thank you for your posting!
Based on my experience, you could do the following step to get the
Parameters related.
1. Create a dataset and add a Report Parameter named Country.
2. Create another dataset named States and use the parameter in the query
text. For example:
select State from tbl_Region where Country = @.Country
3. Create a new Report Patameter named State and in the Available values,
you need to use From query, and choose the dataset States, Value filed and
Label filed use State.
Then, in the preview, you could see the Parameter State could not get the
value untill you specify the value of Country.
Please try the above steps and let me know the result. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi JrMcG,
Have you got any chance to check this issue? Please let me know if you need
any help, thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||You are looking for a "Cascading Parameter" Report; there is a good
example in the sample set for SSRS 2005.
Dennis Graham
JrMcG wrote:
> In trying to incorporate business rules into my SSRS report, I need to be
> able to conditionally load drop downs based upon what the selects for other
> drop downs.
> Can anyone tell me how? Example:
> DropDown1 = Country
> DropDown2 = State/Region
> How Can i leave DropDown2 empty until they select from DropDown1?
> Thanks.|||My subject is very closeley tied to this one so i hope it's OK if I post
here...
I did the same thing but also added an 'all' option in my dataset. Selecting
'all' and a single option works but when selecting multi values the report
breaks. What can i do in my WHERE claus to get this working. Without it the
Bussiness Rules are useless.
"Wei Lu [MSFT]" wrote:
> Hi JrMcG,
> Have you got any chance to check this issue? Please let me know if you need
> any help, thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Conditionally hidden groups

Hi,

I have conditionially visible groups that are show/hide based on a report parameter. The problem is that I also want to have a document label on this group. When the group is hidden a blank entry appears in the doument map rather that no entry at all. Is this a bug or is there some work around. Thanks.

This worked for me.

I have two report parameters, Group1 and Group2, and the user selects what field they want to group on. One of the options for Group2 is "None", meaning that they do not want to have a second grouping for the report. In this case, I hide the group header and footer rows, and set the document map label to Nothing.

Edit the group, and set the document map label to something like:

=IIF (Parameters!Group2.Value = "None", Nothing, "My Document Map Label for Group2")

cheers,

Helen

|||Thanks for the reply, I'll give it a whirl.

Conditionally hidden groups

Hi,

I have conditionially visible groups that are show/hide based on a report parameter. The problem is that I also want to have a document label on this group. When the group is hidden a blank entry appears in the doument map rather that no entry at all. Is this a bug or is there some work around. Thanks.

This worked for me.

I have two report parameters, Group1 and Group2, and the user selects what field they want to group on. One of the options for Group2 is "None", meaning that they do not want to have a second grouping for the report. In this case, I hide the group header and footer rows, and set the document map label to Nothing.

Edit the group, and set the document map label to something like:

=IIF (Parameters!Group2.Value = "None", Nothing, "My Document Map Label for Group2")

cheers,

Helen

|||Thanks for the reply, I'll give it a whirl.

Conditionally hidden groups

Hi,

I have conditionially visible groups that are show/hide based on a report parameter. The problem is that I also want to have a document label on this group. When the group is hidden a blank entry appears in the doument map rather that no entry at all. Is this a bug or is there some work around. Thanks.

This worked for me.

I have two report parameters, Group1 and Group2, and the user selects what field they want to group on. One of the options for Group2 is "None", meaning that they do not want to have a second grouping for the report. In this case, I hide the group header and footer rows, and set the document map label to Nothing.

Edit the group, and set the document map label to something like:

=IIF (Parameters!Group2.Value = "None", Nothing, "My Document Map Label for Group2")

cheers,

Helen

|||Thanks for the reply, I'll give it a whirl.

Conditionally end a report

I am new to CR so this might be a really stupid question. CR version 10.

I want to end the report after X number of records based on a parameter field. Specifically I want to list customers in order of total sales but specify how many customers to print out. Like, Top 100 Customers by Sales.

I know I could print all records to the screen then choose to only print X number of pages but that's not possible if the report goes right to the printer.

Thank you.Type TOP into Crystal's online help (index) and see what you get.l

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.
Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDynvarchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:

> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:
> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:

> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50))
)'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50))
)'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.sqlsql

Conditional Where using a Parameter

How do I construct a select with a conditional where:

If DefaultWH is not blank I want to add a "AND part".

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company)

CASE WHEN

@.DefaultWH' <> ' ' THEN

AND (@.DefaultWH = whseid)

END

Here is something that I have done. Rather than use a blank, set it to null and use an isnull.

Example:

If @.DefaultWh = ''
Begin
Set @.DefaultWh = NULL
End

Select Distinct Name
from Warehouse
Where (Cono = @.Company)
and isnull(@.DefaultWH, WhseID) = WhseID

Hope that helps!

BobP

|||

Try :

SELECT DISTINCT Name
FROM Warehouse
WHERE ((Cono = @.Company) and (@.DefaultWH='')) or ((Cono = @.Company) and (@.DefaultWH=whseid))

Hope it work's...

|||

Neith of the examples worked. I need to have the query run in 1 of 2 formats

Parameter 1 = select company

Parameter 2 = select DefaultWH

Parameter 3 =

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company) < === from Parameter 1

or

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company) AND (@.DefaultWH = whseid) <===From Parameter 1 and 2

I need to be able to drop or include the "AND part" based on the value of Parameter 2

Parameter 1 runs and I pick a company#

Parameter 2 runs and it returns DefafultWH as a blank (access to all warehouses) or a value (limited to that warehouse) based on the Company# enter for Parameter 1.

If Parameter 2 is blank then run Parameter 3 -- Select without the AND

If Parameter 2 is not blank then run Parameter 3 -- Select with the AND part

It just cant be that hard to to. I just have not been that this that long to figure it out.

|||

My example would work in both scenarios.

This is what it does:

The ISNULL syntax returns the value of @.DefaultWH if it is NOT null, and the value of WhseID if it is. So, If parameter2 is blank, change it to null, and the AND clause will look like this:

Parameter1 = 'ABC Company'

Parameter2 = blank (Unselected)

Select Distinct Name
from Warehouse
Where Cono = 'ABC Company'
and WhseID = WhseID

So this effectively removes the AND. The only caveat to this: If WhseID can be NULL, then you would need to add one more statement.

Select Distinct Name
From Warehouse
Where Cono = @.Company
And (WhseID = isnull(@.DefaultWH,WhseID) or WhseID is null)

You have to add the "WhseID is null" clause because null does not "=" null, it only IS null.

Feel free to email me at bobp1339
at
yahoo

BobP

|||

Where does the code go

If I insert on the generic query screeen is says that @.DefaultWH is not declared or defined

If I insert the following into the generic code screen a

=Code.GetSQL()

and then insert the code in the Report code section I get errors there to.

Conditional where clause, depending on parameter

I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a store
d
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.

Conditional where clause, depending on parameter

I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a stored
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.

Conditional Where

I have a stored proc that accepts several parameter, one being
relocation_id. If it is passed in I want to add it to the Where clase
AND rd.relocateID = @.relocation_id
Ive been trying to avoid dynamic sql and thought I could do something with
COALESCE but I cant seem to get same results for total if I left off the
AND
So if I run my query with the AND included from above I get 238,473 records.
If I do:
AND rd.relocateID = COALESCE(@.relocation_id, rd.relocateID) I get
207566. At this point @.relocation_id is NULL because it wasnt past in.
Im not even sure this is possible or are there other paths?
Thanks!is rd.relocateID nullable?|||The below takes advantage of @.relocation_id being NULL if it is not passed t
o
the stored procedure. If you have a default value, you will need to modify
accordingly.
SELECT [SomeColumns] FROM [SomeTable]
WHERE [OtherConditions]
AND CASE
WHEN @.relocation_id IS NOT NULL AND rd.relocateid = @.relocation_id THEN 1
WHEN @.relocation_id IS NULL THEN 1
ELSE 0
END = 1
"Brian" wrote:

> I have a stored proc that accepts several parameter, one being
> relocation_id. If it is passed in I want to add it to the Where clase
> AND rd.relocateID = @.relocation_id
> Ive been trying to avoid dynamic sql and thought I could do something with
> COALESCE but I cant seem to get same results for total if I left off the
> AND
> So if I run my query with the AND included from above I get 238,473 record
s.
> If I do:
> AND rd.relocateID = COALESCE(@.relocation_id, rd.relocateID) I get
> 207566. At this point @.relocation_id is NULL because it wasnt past in.
> Im not even sure this is possible or are there other paths?
> Thanks!
>
>
>|||That worked perfect. Thanks. One follow up and not this is possible
I am taking the query that was in a Coldfusion page and making it an SP and
it was a conditional join
<cfif len(arg.degreeID)>LEFT OUTER JOIN resume_education ed ON ( rd.userID =
ed.userID )</cfif>
So if on the search page someone picked a Degree we would then need to join
in that table as well. Is the thinking here you should
just always make the join and then have the where clause be conditional like
below.
Thanks again!
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:6DAC0EEE-E8F0-4549-9A22-E3CD91D671E4@.microsoft.com...
> The below takes advantage of @.relocation_id being NULL if it is not passed
> to
> the stored procedure. If you have a default value, you will need to modify
> accordingly.
> SELECT [SomeColumns] FROM [SomeTable]
> WHERE [OtherConditions]
> AND CASE
> WHEN @.relocation_id IS NOT NULL AND rd.relocateid = @.relocation_id THEN 1
> WHEN @.relocation_id IS NULL THEN 1
> ELSE 0
> END = 1
>
> --
>
> "Brian" wrote:
>

COnditional visibility of a field in SSRS 2005

Hi,

I have a report in which there is a field called "Returned Qty" and there is a parameter called Show Qty now I want this field returned Qty to only appear if this show Qty parameter is set to yes. Can Anyone suggest me how to do this?

I guess It could be done by using the properties option and writing some expression. I appreciate the response,

Thanks,

Rashi

Hello Rashi,

Try putting this in the Hidden property of your textbox:

=IIf(Parameters!ShowQty.Value = "Yes", "False", "True")

Or, you can just replace the value directly in the textbox, using this expression:

=IIf(Parameters!ShowQty.Value = "Yes", Fields!ReturnedQty.Value, "")

Hope this helps.

Jarret

|||

Hello Jarret,

Thanks for the solution, I am sure this should work but somehow it gives me error both ways. I wrote the first expression under the text properties of the box by checking the expression option but it dint work. I wrote the same expression under the hidden properties too but it too dint work. The second expression display field but the filed contains "Error".

I am sure the expresison is correct but I am not using it under correct text box may be.

My aim is not to display the field at all if showQty is set to No.

Pls let me know if I am commiting any mistake while doing this

Thanks again for your help,

Rashi

|||

Hi Rashi,

Try false and true without the quotes ""

=IIf(Parameters!ShowQty.Value = "Yes", False, True)

Thanks,

Panna

|||

Yep, I tried this and it gives me an error " input string was not in correct format". No matter whatever expression do I write I am getting the same error message.

Any idea what it is?

|||

Sorry, yes, you will need to remove the " " from around the false and true. Right click and select Properties on your textbox that will hold the returned quantity. Select the Visibility tab, then select 'Expression:' from the 'Initial visibility:' section. Enter this as the expression here:

=IIf(Parameters!ShowQty.Value = "Yes", False, True)

The other way to do this is to replace the value with nothing in the textbox where it actually shows the value. Right click on the textbox that will hold the returned quantity and select 'Expression:'. Enter this and hit ok.

=IIf(Parameters!ShowQty.Value = "Yes", Fields!ReturnedQty.Value, "")

But, you said this shows Error when you did it, so can you try this instead?

=IIf(Parameters!ShowQty.Value = "Yes", Fields!ReturnedQty.Value, nothing)

Jarret

|||

When referencing parameters you can either look at the "Value" or the "Label".

=IIf(Parameters!ShowQty.Label = "Yes", False, True)

=IIf(Parameters!ShowQty.Value = False, False, True)

Either way should work!

GiveMeABreak. . .

|||I have been doing both ways n the error message is still the same no matter what I try it keeps giving: " input string not in correct format". If I remove all the expression it does build and shows me the returned qty firld but not with the expression values|||

Can you post the expression exactly as you have it in your textbox and in your hidden property?

Also, how do you have your parameter setup?

Jarret

|||

Thank you all, it has been fixed now.

I used the one below

IIf(Parameters!ShowQty.Value = False, False, True)

The mistake was in refrencing the parameters, otherwise its perfect!

Thanks again. :)

Rgds,

Rashi

sqlsql

Conditional visibility

I need to pass a parameter to determine the visibility of a remark. If the
parameter defines it to be visible, I also need to toggle the visibility of
the remark when the user expands or collapses the containing group. I do
not want the visibility turned on when the parameter wants it always
invisible.
If the Visibility.ToggleItem was based upon an expression, I believe I could
easily do this. Does anyone know how or when this could be done?Have you try the Iif function ?
= Iif(MyParameter.Value = "ViewNotes", True, False)
So set this formula as the visibility formula.
"Walter Lundgren" <lundgren_w@.crane.navy.mil> a écrit dans le message de
news:ekQ2AxhXEHA.1684@.tk2msftngp13.phx.gbl...
> I need to pass a parameter to determine the visibility of a remark. If
the
> parameter defines it to be visible, I also need to toggle the visibility
of
> the remark when the user expands or collapses the containing group. I do
> not want the visibility turned on when the parameter wants it always
> invisible.
> If the Visibility.ToggleItem was based upon an expression, I believe I
could
> easily do this. Does anyone know how or when this could be done?
>|||Thanks, but I also need to consider the need for a drilldown hide/unhide
feature. The ToggleItem, initial visibility, and InitialToggleState are
properties that support drilldown.
The ToggleItem property is hardwired to consider only the collapsed/expanded
state of the controls on the Dropdown list. If I could use an expression to
set ToggleItem, the reported "remarks" would not need to be tied to the
state of the collapsed/expanded control.
"Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
news:%23QDQI7hXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> Have you try the Iif function ?
> = Iif(MyParameter.Value = "ViewNotes", True, False)
> So set this formula as the visibility formula.
>
> "Walter Lundgren" <lundgren_w@.crane.navy.mil> a écrit dans le message de
> news:ekQ2AxhXEHA.1684@.tk2msftngp13.phx.gbl...
> > I need to pass a parameter to determine the visibility of a remark. If
> the
> > parameter defines it to be visible, I also need to toggle the visibility
> of
> > the remark when the user expands or collapses the containing group. I
do
> > not want the visibility turned on when the parameter wants it always
> > invisible.
> >
> > If the Visibility.ToggleItem was based upon an expression, I believe I
> could
> > easily do this. Does anyone know how or when this could be done?
> >
> >
>

Conditional stored procedure question

I need to create a stored proc which has a conditional WHERE clause depending on the value of a passed parameter. I'm having trouble handling the condition. I'm missing something here.

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)

AS

SELECT uid, name, date, registration_confirmed
FROM tbl_members

WHERE
If @.iShowAll = 0
begin
(uid = @.myID) AND (registration_complete = 0)
end
else
begin
(uid = @.myID)
end

GO

Thanks,
davidyou can use a CASE statement but i do not know the syntax..heres another way of doing it

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)
AS
if @.iShowAll = 0
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID AND registration_complete = 0
else
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID
go

HTH|||Here's an example using a Case

SELECT uid, name, date, registration_confirmed FROM tbl_members
WHERE (uid = @.myID) AND registration_complete = CASE WHEN @.iShowAll = 0 THEN 0 ELSE registration_complete END

Conditional Parameter in Where Clause

I'm trying to figure out a way to filter a dataset using a parameter only when the user enters a value for the parameter and to not apply the filter if the parameter is left blank (or null) by the user. I would like to do this within the WHERE clause of the SELECT statement to minimize the size of the dataset whenever possible. Is there such a thing as a default parameter value that equates to "any value"?

Nothing I have tried works (but I'm new to SQL, Report Server and the Visual Basic Development Environment).

Thanks in advance,

Chris

Rather than leaving the parameter unselected, you need to add an option with a value of NULL and text that matches your scenario e.g. blank, "All", "N/A", "Unspecified" etc. To do this you'll need to modify the query for the paramter dataset to:

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

Then update your main query with the following WHERE clause

WHERE id = ISNULL(@.param, id)

so when the null option is selected the WHERE clause equates to id=id which is always true and hence all rows are returned.

Hope this helps.

|||

Thanks Adam,

I was not familiar with ISNULL. I got it to work sort of like I wanted it to by checking the "Allow Null Value" checkbox and making the default value NULL in the Report Parameters dialog box and then putting this in the WHERE clause:

WHERE LITEM.SIZE = ISNULL(@.Input_Size, LITEM.SIZE)

However, I could not figure out where to put the following statement (everything I tried resulted in an error - but I'm probably missing something obvious):

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

...and therefore, the user must uncheck the NULL checkbox in order to enter a filter value and it's not real obvious that when NULL is checked, that the filter is not applied.

Thanks again for pointing me in the right direction!

Chris

|||

By your response it seems like your parameter is a textbox the user types into, is that correct?

My prerred way is to present the user a list of options i.e. a dropdown. In that case you don't get a null checkbox. The options in the dropdown can either be typed in on the paramter screen or can come from a dataset. The SELECT statement I provided is meant as an example of query used to populate such a dataset i.e. it includes a NULL option.

If you wish to use a textbox then you could alter your SQL query and rather than using ISNULL you could use an OR in your WHERE clause as follows

WHERE LITEM.SIZE = @.Input_Size
OR @.Input_Size = '' -- empty string

If LITEM.SIZE and @.Input_Size are integers then it gets a little more complicated. You'll need to experiment.

|||

Adam,

Thanks! It's now working just the way I wanted it to!

Chris Heitman

Conditional page breaks

I want to set a page break at the change of a group conditionally based on a
parameter value. Is there any way to do this in Reporting Services?Any help here? Because I would *really* like to figure out how to do this.
Once again, I have a grouped table in a report layout. I would like to have
a page break at the end of a grouping happen conditionally based on a boolean
parameter that I pass to the report.

Conditional Order by?

Is there a way to do a conditional order by so that a user can give a parameter to a stored proc and it give back results sorted the way they want?

I want it so that the user can do 1 of 4 things,
* sort by "title" ascending,
* sort by "title" descending,
* sort by "synopsis" ascending,
* sort by "synopsis" descending

Can it be done? This is what I have but I get a syntax error:

select * from Blah

Order By
Case
when @.orderId = 1 then title asc
when @.orderId = 2 then title desc
when @.orderId = 3 then synopsis asc
when @.orderId = 4 then synopsis desc
end

Any help is greatly appreciated!

You need to split the query as ASC Query & Desc Query.

You can choose your order by columns (if it is single column) dynamically but Sorting Order you can't.

Use your query as follow as

if @.OrderId in (1,3)

select * from Blah
Order By
Case
when @.orderId = 1 then title
when @.orderId = 3 then synopsis
end asc

else

select * from Blah
Order By
Case
when @.orderId = 2 then title
when @.orderId = 4 then synopsis
end Desc

Or

You can use dynamic SQL

Declare @.SQL as NVarchar(1000)

Select @.SQL = N'select * from Blah

Order By ' +
Case
when @.orderId = 1 then 'title asc'
when @.orderId = 2 then 'title desc'
when @.orderId = 3 then 'synopsis asc'
when @.orderId = 4 then 'synopsis desc'
end

Exec (@.SQL)

|||Why don't you dynamically add the final order by clause to the select query string ?
I guess it is more clear to understand and much more flexible for any future changes.|||Thanks both for the replies, I didnt know it would be so detailed, I have a rather large select query that I am applying this to and I dont really want to create a string then execute it, it just looks messy to me. But I guess if I have no option I guess ill have to.

Thanks again|||In some cases I have done this:

SELECT ....,
SortOrder = Case when @.orderId = 1 then title
when @.orderId = 2 then REVERSE(TITLE)
when @.orderId = 3 then synopsis
when @.orderId = 4 then REVERSE(synopsis) END
ORDER BY SortOrder

The only issue with this is all the vars in the CASE must be the same type, or cast to a certain type.

|||

Easiest is to do below:

order by

case @.orderId when 1 then title end

, case @.orderId when 2 then title end desc

, case @.orderId when 3 then synopsis end

, case @.orderId when 4 then synopsis end desc

Note that you will get the best performance (assuming you have indexes on the column(s) and the plan can use it) if you use dynamic SQL to form the ORDER BY with required columns or use different SELECT statements. But in most cases, I have found that the above construct is easier to use, safe from SQL injection (dynamic SQL is prone to it if you are not careful) and with few conversions which single CASE expression requires.

|||Thanks all for the help with this topic, I think Umachandar's answer will fit my solution best.

Conditional group by

Hi,

Can anyone help me in writing this sql query, i want to group my select statement depending on the parameter user is passing.

Say when @.group='Cell' I want to group by CellID otherwise different conditions, something like below query but it is not working. I know we can't use case directly in where but please let me know if there is any other work around.

I don't want to use dynamic query and also this is big SP so i dont want to break sp in four conditions.

declare @.group varchar(10)

set @.group='Cell'

select cellid,sum(count)

FROM CellImpressionFact

WHERE ImpressionTypeLevelId = 2

AND ImpressionTypeId = 4

group by

case when group='Cell' then GROUP BY CellId

else group by activityID

end

This is not a good idea really. I would use dynamic SQL to provide this kind of capability if you really need to. It is possible (see code) but I would be very concerned about performance.

create table test
(
grouper int,
grouper2 int,
value decimal(10,5)
)
go
insert into test
select 1,1,10
union all
select 1,2,10
union all
select 1,3,10
union all
select 2,1,10
go
declare @.groupby varchar(10)
set @.groupBy = 'grouper2'

select max(grouper) as grouper,
max(grouper2) as grouper2,
sum(value) as valueSum
from test
group by case when @.groupBy = 'grouper' then grouper else grouper2 end

Note that the grouper2 column is of any value when you group by grouper, and vice versa (say it five times fast.)