An existing report I am modifying has this Filter expression in the Query and I am trying to wrap my head around this but to no avail.
There is an Output Format (Select 'Single' or 'Double') prompt box which parameter = output_format. There is also a Account Code multi-select prompt which parameter = ACCT_CDE.
?output_format? = 'Single' and char_length (#sq(promptmany('ACCT_CDE','string',' '))#) = 5
The filter means IF output_format selected is 'Single' THEN ... this is where I am lost. Would appreciate if you can help me. Thank you in advance.
What is your intention? Do you want to make sure only accountnumbers 5 positions long are passed?
Someone else created this report. I am going to create a new report (rather than modifying) but I have to use this report to begin with as there are many calculations I can leverage which is why I am going over each query to understand the details. So, I am trying to understand this Expression Filter intention which I cannot wrap my head around for the 2nd parameter.
In the Account Code prompt - all codes are 3 characters long only; BUC, CCR, GLR
Could you please explain/interprete what does this filter do? --> char_length (#sq(promptmany('ACCT_CDE','string',' '))#) = 5
Hi,
char_length() returns the length of a character string. In this instance, the string in question is the ACCT_CDE parameter, encapsulated in single quotes (so, for example 'BUC', which when including the single quotes is 5 characters in length). The single quotes are added by the sq() macro function. Now, because the promptmany() macro function can allow multiple entries to be selected, and therefore contained in the parameter (separated by commas), there may be situations where users are choosing more than one account code, and in this case the resultant parameter would be longer than 5 characters in length. In this case your filter would return a False result. So, if your users select "Single" from the first prompt, indicating they are interested in only one Account code, but then they go ahead and choose multiple Account codes in the second prompt, your report will not return any data. Simples :)
Hope that helps a little!
MF.
if I select BUC --> char_length (double-quote'BUC'double-quote) = 5 char (includes the encapulated single quotes)
if I select BUC, CCR --> char_length (double-quote'BUC,CCR'double-quote) = 9 (1 set of single quotes for the multi-selects)
I get it now. Thank you all for your explanations.