Help with optional SQL prompt - ignore if left blank

[Patrick77]

Hello,
I know that if I use Report Studio GUI filters I can create a prompt that is literally optional - that is, if the prompt is left blank, it is completely ignored. I am trying to do the same using a query written in SQL, and I will spare you the details for now as to why I am trying to do it this way.

FYI: the field I am subsetting is a list of middle names (e.g. Patrick) 

I realize that I can use this line, which will return everything if the prompt is left blank 

Middle_Name = #prompt('MiddleName', 'string', '%')#   

however, what I am trying to do is more complicated because I am using 2 prompts together to get what I need, like this:

Code Select Expand

(
Middle_Name = #prompt('MiddleName', 'string', '%')#   
OR
Middle_Name = substr(#prompt('MiddleName', 'string', '%')#, 1, 1)   
OR
Middle_Name = #prompt('MiddleInitial', 'string', '%')#   
OR
Middle_Name Like #prompt('MiddleInitial%', 'string', '%')#   
)

so I really need it to just ignore that a prompt even exists if it is left blank, otherwise it ends up returning everything.  Is this possible, and if so, how?
Thanks for your time. 

[dougp]

I don't know how

Code Select Expand


#prompt('MiddleInitial%', 'string', '%')#

will behave.  It doesn't look right unless your parameter is "MiddleInitial%", but then you'll never get that to look like 'S%' for 'Smith' or 'Saunders'.

I think using the "token" data type leaves off quotes, so you get more flexibility than with "string".

How about this macro for the last line of your filter:

Code Select Expand


Middle_Name like #sq(prompt('MiddleInitial', 'token', '%') + '%')#


If MiddleInitial is S, you should get 'S%'.
If MiddleInitial is not provided, you should get '%%'.
(No, I didn't try this -- just spitballing.)

[Patrick77]

You are right, this doesn't work - I had some version of this (perhaps using sq() ) that did, but that's beside the fact I suppose.  I was really hoping to avoid the 'default' value all together, and instead be able to ignore that this line even exists if the prompt was left blank.

Thanks for your time and help.

I don't know how

Code Select Expand


#prompt('MiddleInitial%', 'string', '%')#

will behave.  It doesn't look right unless your parameter is "MiddleInitial%", but then you'll never get that to look like 'S%' for 'Smith' or 'Saunders'.

I think using the "token" data type leaves off quotes, so you get more flexibility than with "string".

How about this macro for the last line of your filter:

Code Select Expand


Middle_Name like #sq(prompt('MiddleInitial', 'token', '%') + '%')#


If MiddleInitial is S, you should get 'S%'.
If MiddleInitial is not provided, you should get '%%'.
(No, I didn't try this -- just spitballing.)