I have a Custom SQL Data Provider that pulls information from the ScanningLog table, and I want to use the ##OB_OPTIONSELECT_ALL macro to allow the person accessing the Dashboard to select one or more users to include in the query for the Dashboard. (I wanted to use the "Document Imaging Query" Data Providers, but they are not pulling all the data I need due to other issues; reference SCR#320681 and SCR#310569.)
My query uses these lines:
AND sl1.logdate between @{LogDates}
AND sl1.usernum = ##OB_OPTIONSELECT_ALL{(@{UserAccount}, -1)}
The UserAccount parameter uses this query for the Value Select List (as I don't want the users to be able to select <<ALL>>):
SELECT username, usernum
FROM hsi.useraccount
WHERE username NOT LIKE ('%(deactivated)%')
ORDER BY 1
The Value select list is set to Restrict user input, and Allow multiple selections. Everything works great until the query is built and run. Selecting three users (who I know have records that should be returned), the actual query executed (as listed in Diagnostic Console) has these lines:
AND sl1.logdate between CONVERT(datetime, '2019-02-01 00:00:00.000', 121) AND CONVERT(datetime, '2019-02-28 23:59:59.997', 121)
AND sl1.usernum = 23122, 26546, 106
Obviously, SQL balks on the comma after the first usernum value, so I tried changing my query to use 'in' instead of '=':
AND sl1.logdate between @{LogDates}
AND sl1.usernum in ##OB_OPTIONSELECT_ALL{(@{UserAccount}, -1)}
However, now it balks before I can even post test data to validate it, throwing an exception and generating these lines (copied from Diagnostic Console):
AND sl1.logdate between NULL AND NULL
AND sl1.usernum in NULL
What am I missing?
Is anyone using the ##OB_OPTIONSELECT_ALL macro successfully willing to share the 'proper' method (since it is not clearly demonstrated in the MRG, and I am apparently not doing something right)?
I will also ask my FLOS if there is a bug in 18.0.1.71 regarding this macro, but would like some feedback from the Community as well.
