Thank you very very much for your help. It works like a charm !! :) It does exactly what I need.
Best regards.
Original Message:
Sent: Thu January 30, 2025 04:45 AM
From: Marc Reed
Subject: Multi-select prompt that would work with an SQL Exists condition
First method is the one I posted initially and by using reporting optional params setting we don't need to add that complexity into the macro. See example report.
Challenge is still on to find the smallest bit of code to count the values in a parameter!
------------------------------
Marc Reed
Reporting Lead
Original Message:
Sent: Thu January 30, 2025 04:29 AM
From: Paul Mendelson
Subject: Multi-select prompt that would work with an SQL Exists condition
#length(
join('',
substr(split(';',promptmany('Fruit','integer')),0,1)
)
)#
Split the prompt many into an array of values.
[123,234,345,456,567]
substring each element to a single character
[1,2,3,4,5]
join into a string with no separator
12345
count the characters
5
This works with strings, muns, integers, and so on. multiselect intervals are a bit weird and I'd have to experiment a bit. You can use a case statement to handle optional parameters that are unpopulated:
#
let param = promptmany('Fruit','integer','NA');
let arr = split(';',param);
case param
when 'NA' then sq('No values selected!')
else sq(length(
join('',
substr(arr,0,1)
)
)+ ' values selected')
end#
------------------------------
Paul Mendelson Product Manager
Product Manager
PMsquare
Original Message:
Sent: Thu January 30, 2025 03:45 AM
From: Marc Reed
Subject: Multi-select prompt that would work with an SQL Exists condition
@German Rodriguez I was thinking there must be a simpler way to count the number of values returned by the prompt. I came up with this:
#
let vFruit = promptmany( 'pFruit', 'integer' ) + ';' ;
length( join( ';;', split( ';', vFruit) ) ) + ' - ' + length( vFruit )
#
This works similar to the sql method, but in macro code. It's a bit simpler as we can use a variable to park to the prompt parameters.
pFruit will be a semicolon separated list.
First, add a trailing semicolon as the last value will not have a semicolon after it.
Then replace every semicolon with two semicolons.
Then compare the lengths of the two strings.
For example
pFruit = 1;2;3
becomes
pFruit = 1;2;3; with a length of 6
becomes
pFruit = 1;;2;;3;; with a length of 9
compare the length of the two strings
We then create the expression '9 - 6' to be evaluated by the sql engine as macros dont do maths.
I use the split/join functions to do a find and replace.
Wonder if anyone else can come up with a more efficient way of counting values in a parameter?
------------------------------
Marc Reed
Reporting Lead
Original Message:
Sent: Wed January 29, 2025 03:48 AM
From: German Rodriguez
Subject: Multi-select prompt that would work with an SQL Exists condition
Hi Marc,
Thank you for your help and all the explanations for this good idea.
I'll try to do that in my report and I'll come back as soon as possible.
Best regards,
German
------------------------------
German Rodriguez
Original Message:
Sent: Wed January 29, 2025 03:19 AM
From: Marc Reed
Subject: Multi-select prompt that would work with an SQL Exists condition
I think @Paul Mendelson and I have come up with the same solution to this.
The IN clause will return people who have any of the fruit selected. So if your prompt answer was 'Apple';'Banana';'Cherry' your report would return people who have any of these alergies.
The trick is to count the number of allergies that were returned for each person and see if it equals the number of fruit selected in the prompt.
For example, lets say filtering on the above fruit returns the following list using the IN filter
- Marc Banana
- Marc Apple
- Marc Cherry
- Paul Apple
- Paul Banana
- German Apple
If we count the number of allergies for each person we see
- Marc = 3
- Paul = 2
- German =1
Our prompt contained 3 fruit, so with another filter of counting the alergies per person = the number of values in the prompt, the list will now only return Marc.
Counting the number of answers in the prompt parameter is the messy bit.
I think an additional filter looking something like:
count( distinct [Fruit] for [Person) =
#
length(
join(
'',
substr(
split(';', promptmany( 'Fruit','integer') )
, 0, 1
)
)
)
#
Explaining the filter; the first bit is easy - count the distinct number of fruit for each person. The bit between the #'s is the macro that is counting the number of prompt answers. It's not obvious but here goes...
(this code is very similar to Paul's but I have a slightly simpler version)
PromptMany will be the prompt answer, as its promptmany and an integer, this will be a semicolon separated list - for example 1234;3456;987
We split this into an array of elements based using the semicolon to split. this give an array with three elements.
For each element in the array we do a substring and just get the first character. we will now have an array of three elements, each element will just be the first chararacter.
I then JOIN all the elements in the array back into a single string. I then use LENGTH to determine the length of the string - which will be the number of prompt answers.
------------------------------
Marc Reed
Reporting Lead
UK Government
Original Message:
Sent: Wed January 22, 2025 05:02 AM
From: German Rodriguez
Subject: Multi-select prompt that would work with an SQL Exists condition
Hello experts !
I was wondering how to do work a multi select (checkbox) prompt with a SQL code that need to use a EXIST condition ?
Any information would be appreciated.
Thank you for any help
------------------------------
German Rodriguez
------------------------------