SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


using parameter in drop down list


using parameter in drop down list

Author
Message
fliu2265
fliu2265
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 50
Hello Everyone:

I am new to SSRS and have a problem using parameter.
For example, I have a select statement like: Select name, enrollmentDate, exitDate from tblName [WHERE...]

Then I create three parameters as:
@startDate
@endDate
@enrollmentType (with two values: New Enrollment and Total Enrollment)

So if I select New Enrollment, the WHERE clause is : enrollmentDate >= @startDate and enrollmentDate <= @endDate.

If I select Total Enrollment, the WHERE clause is: (enrollmentDate <= @endDate and exitDate IS NULL ) OR (enrollmentDate <= @endDate and exitDate>=@startDate)

Do I have to write expression to do it? If so, how do I write it?
Thanks

Frank
geoff5
geoff5
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 543
No special SSRS expression is required, just a marginally more complex T-SQL WHERE clause that has separate conditions for each value of @enrollmentType. The WHERE clause would be something like this:

WHERE
(@enrollmentType = 'New Enrollment' AND
enrollmentDate BETWEEN @startDate AND @endDate)
OR
(@enrollmentType = 'Total Enrollment' AND
enrollmentDate <= @endDate AND
COALESCE(exitDate, @startDate) >= @startDate)



This use of the BETWEEN comparison assumes (as does your original example) that all the dates occur at midnight if they are datetime data types. If the time value is recorded for enrollmentDate, then a more complex comparison is required. The COALESCE command substitutes the @startDate value whenever exitDate is null, thereby simplifying the overall WHERE clause.
fliu2265
fliu2265
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 50
Thank you very much. I am always thinking about writing expression or something. You show me a much easy way to do it with WHERE clause. You are great.

Regards.

Frank
fliu2265
fliu2265
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 50
Hello geoff5,

One more question, I wish you could help.

I have a query like: SELECT name, agency from tblname.

I also have a query like: SELECT username, grant from tblgrant.

Then I created a parameter from the second query with grant values: All, CLC and CDD.

If I select All, I want to see all agencies from the first query, if I select CLC, I can only see agency 240 and 241, and If I select CDD, I can only see agency 243, 244, and 245.

Is there any easy way to do that in SSRS?

Thanks

Frank
geoff5
geoff5
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 543
If it were me, I would make the query like this:

SELECT
username,
grant,
grant_type = CASE
WHEN grant IN (240,241) THEN 'CLC'
WHEN grant IN (243,244,245) THEN 'CDD'
END
FROM
tblGrant



Then apply multi-select parameter value to the grant_type field of the dataset. You could also do this directly in SSRS by adding a calculated column to the dataset, but the coding would be simpler in the query.
fliu2265
fliu2265
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 50
Hi Geoff5,

I am just learning SSRS and wish you could help me.
If activity code is between 50 and 65, I will count unique applicationNumber
However, The following expression does not work.

=CountDistinct((IIF(Fields!ActivityCode.Value) BETWEEN 50 and 65,
Fields!ApplicationNumber.Value,NOTHING)


Thanks

Frank
geoff5
geoff5
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 543
The expression language in SSRS is a variety of Visual Basic, not T-SQL, so it does not support the BETWEEN comparison operator. You will have to use >= And <=, like this sample:

= CountDistinct(IIf(Fields!ActivityCode.Value >= 50
And Fields!ActivityCode.Value <= 65,
Fields!ApplicationNumber.Value, Nothing))


fliu2265
fliu2265
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 50
I understand now. Thank you very much.

Frank
fliu2265
fliu2265
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 50
Hi Geoff5.

Sorry to bother you again.
I appreciate your help one more time. I just want to combine several agency codes into one agency name in my report.
The following expression I write does not work for me. The error code is BC30198.

=SWITCH
(
(Fields!EnrollAgencyCode.Value = "245" or Fields!EnrollAgencyCode.Value = "245A" OR Fields!EnrollAgencyCode.Value = "245B","CLC MANCHESTER"),
(Fields!EnrollAgencyCode.Value = "250" or Fields!EnrollAgencyCode.Value = "250A" OR Fields!EnrollAgencyCode.Value = "250B","WHCC"),
(Fields!EnrollAgencyCode.Value = "593" or Fields!EnrollAgencyCode.Value = "593A" OR Fields!EnrollAgencyCode.Value = "593B","PROTEUS RURAL")
)


Frank
geoff5
geoff5
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 543
Technically you have unbalanced parentheses because you do not have a closing parenthesis in front of each comma that separates the value pairs of the function, and you do not have an opening parenthesis after each such comma.

You do not need to put parentheses around each pair of values or even around any specific parameter value inside the function's opening and closing parentheses. Remove all the parentheses except the first and the last that enclose the list of value pairs, and it should work.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search