August 6, 2014 at 5:38 pm
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
August 6, 2014 at 8:20 pm
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.
August 6, 2014 at 10:34 pm
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
August 7, 2014 at 9:30 am
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
August 7, 2014 at 9:42 am
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.
August 20, 2014 at 5:52 pm
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
August 20, 2014 at 9:48 pm
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))
August 20, 2014 at 10:16 pm
I understand now. Thank you very much.
Frank
August 21, 2014 at 10:39 am
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
August 21, 2014 at 12:14 pm
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply