Nicole McDaniels
asked on
How to select one number or leave at all for a parameter?
I know there was a similar question asked a few years back, but it didn't work for me. I JUST now realized I have the optional parameter (oops) but I can't get it to work..
I have a report that has parameters that need to be entered and want to leave 1 parameter (PR_ID) to either be "ALL ID's" (so basically filter off of the other parameters) OR have the user enter a specific ID. I set my PR_ID to "Optional Prompt" I assume that means it's optional?
Then, in my select expert, I have the following:
Not HasValue({?PR ID}) OR {PR.ID} = {?PR ID}
I ran the report and it errored highlighting the {?PR ID} doesn't have a value.
Before realizing I could make the prompt optional, I would normally add the option of * to the picklist and have it as the default. Then I would put
(if {?PR ID}= "*" then True else{PR.ID} like{?PR ID}
but since this field is a number, it's not liking that either.
Which is the better option? I feel like the optional parameter is making my report super slow...
I have a report that has parameters that need to be entered and want to leave 1 parameter (PR_ID) to either be "ALL ID's" (so basically filter off of the other parameters) OR have the user enter a specific ID. I set my PR_ID to "Optional Prompt" I assume that means it's optional?
Then, in my select expert, I have the following:
Not HasValue({?PR ID}) OR {PR.ID} = {?PR ID}
I ran the report and it errored highlighting the {?PR ID} doesn't have a value.
Before realizing I could make the prompt optional, I would normally add the option of * to the picklist and have it as the default. Then I would put
(if {?PR ID}= "*" then True else{PR.ID} like{?PR ID}
but since this field is a number, it's not liking that either.
Which is the better option? I feel like the optional parameter is making my report super slow...
ASKER
My full statement is
{PROJECT.NAME} = "CAPA (CAPA)" and
{TW_V_FUNCTIONAL_UNIT.S_VA LUE} = {?Functional Unit} and
{PR_STATUS_TYPE.NAME} = {?Status}and
Not HasValue({?PR ID}) OR {PR.ID} = {?PR ID}
{PROJECT.NAME} = "CAPA (CAPA)" and
{TW_V_FUNCTIONAL_UNIT.S_VA
{PR_STATUS_TYPE.NAME} = {?Status}and
Not HasValue({?PR ID}) OR {PR.ID} = {?PR ID}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you SO much. I have learned so much from you!
You probably need to put ( ) around that so it processes correctly.
I use optional parameters all the time and I don't notice a difference in execution but perhaps that has as much to do with limited data as it does with anything.
Is the PR.ID field indexed?
mlmcc