Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using parameter in drop down list Expand / Collapse
Author
Message
Posted Wednesday, August 6, 2014 5:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:42 PM
Points: 12, Visits: 20
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

Post #1600436
Posted Wednesday, August 6, 2014 8:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:49 PM
Points: 171, Visits: 501
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.
Post #1600449
Posted Wednesday, August 6, 2014 10:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:42 PM
Points: 12, Visits: 20
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
Post #1600460
Posted Thursday, August 7, 2014 9:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:42 PM
Points: 12, Visits: 20
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
Post #1600713
Posted Thursday, August 7, 2014 9:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:49 PM
Points: 171, Visits: 501
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.
Post #1600720
Posted Wednesday, August 20, 2014 5:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:42 PM
Points: 12, Visits: 20
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
Post #1605640
Posted Wednesday, August 20, 2014 9:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:49 PM
Points: 171, Visits: 501
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))

Post #1605651
Posted Wednesday, August 20, 2014 10:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:42 PM
Points: 12, Visits: 20
I understand now. Thank you very much.

Frank
Post #1605653
Posted Thursday, August 21, 2014 10:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:42 PM
Points: 12, Visits: 20
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
Post #1605919
Posted Thursday, August 21, 2014 12:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:49 PM
Points: 171, Visits: 501
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.
Post #1605955
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse