May 29, 2013 at 3:20 pm
:crazy: Hey Everyone,
This site is awesome first of all and has been very helpful. I have come across a new scenario that I need to pass. I have 3 parameters but I need the user to only select one and run the report. However, if the user selects 1 parameter, I receive the error saying, "Fill in the other parameter fields". I cannot use the Null option in the parameter properties do to inexperience users getting confused with the option. Does anyone know how to select 1 parameter and ignore the other 2? Any help would be appreciated. I am using the parameters in the WHERE Clause of my query.
SELECT DISTINCT
WaSysAssgnNbr, VendorAgtId, VendorCode, Default_ID, WA_TaxId, WA_Name, PlatformCode, BlkBusCode, GroupNbr, TermDate, PymtStructureId, PymtLvlNbr,
PymtLvlAgentSan, PymtLvlAgentName, Payee_San, PayeeName
FROM vwFmPymtStructure AS A
WHERE (Default_ID = 'YES') AND (TermDate > { fn NOW() }) AND (WaSysAssgnNbr = @WaSanParameter) OR
(TermDate > { fn NOW() }) AND (WaSysAssgnNbr = @WaSanParameter) AND (GroupNbr <> '') OR
(Default_ID = 'YES') AND (TermDate > { fn NOW() }) AND (WA_TaxId = @WaTaxIdParameter) OR
(TermDate > { fn NOW() }) AND (GroupNbr <> '') AND (WA_TaxId = @WaTaxIdParameter) OR
(Default_ID = 'YES') AND (TermDate > { fn NOW() }) AND (Payee_San = @PayeeSanParameter) OR
(TermDate > { fn NOW() }) AND (GroupNbr <> '') AND (Payee_San = @PayeeSanParameter)
May 30, 2013 at 6:08 am
you could change the way you present the parameters in the report instead of having 3 parameters.
Parameter 1 | Parameter 2 | Parameter 3
Some value | some values | some value
Have 2 with one of them being the parameter type .eg.
Parameter 1 | Parameter2
Type1 | Value entered
Type2 | Value entered
Type3 | Value entered
and then change your qery to do something along the lines of ...
(
(@Parameter1 = 1 AND WaSysAssgnNbr = @Parameter2)
OR
(@Parameter1 = 2 AND WA_TaxId = @Parameter2)
OR
(@Parameter3 = 2 AND Payee_San = @Parameter2)
_
May 30, 2013 at 6:21 am
If the user only enters one parameter why are there 3 on the report?
If the user doesn't need to enter data for the last 2 parameters you can make them hidden parameters and set them to NULL or provide a default.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 30, 2013 at 8:18 am
Okay, then I'd go with something like what Nick has suggested.
If the report is calling a stored procedure I'd handle the logic for which query parameter is set in the query using expressions. Something like:
QueryParam1 expression: =IIF(paremeter1.value = 1, paramter2.value, System.DBNull.Value)
QueryParam2 expression: =IIF(paremeter1.value = 2, paramter2.value, System.DBNull.Value)
The System.DBNull.Value could be another default that you want to provide instead of NULL.
If I was using a direct query I'd build that in the report using an expression as well.
So your query would be something like this:
="select columns from table where " + IIF(parameter1.Value = 1, "col1 = ", "col2 = ") + parameter2.value
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply