01-02-2020 03:27 PM
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.
01-03-2020 04:41 AM
Hi David,
Thanks for posting on Community. This should be a pretty simple fix for you as you are just missing a set of parenthesis around the OB_OPTIONSELECT_ALL. When using the IN clause in SQL you have to have parenthesis.
eg. SELECT * FROM his.itemdata where itemnum in (123,456,789)
So for your query you can just use:
AND sl1.logdate between @{LogDates}
AND sl1.usernum in (##OB_OPTIONSELECT_ALL{(@{UserAccount}, -1)})
I hope this helps, let us know how it turns out!
01-03-2020 04:41 AM
Hi David,
Thanks for posting on Community. This should be a pretty simple fix for you as you are just missing a set of parenthesis around the OB_OPTIONSELECT_ALL. When using the IN clause in SQL you have to have parenthesis.
eg. SELECT * FROM his.itemdata where itemnum in (123,456,789)
So for your query you can just use:
AND sl1.logdate between @{LogDates}
AND sl1.usernum in (##OB_OPTIONSELECT_ALL{(@{UserAccount}, -1)})
I hope this helps, let us know how it turns out!
01-03-2020 08:23 AM
Mike,
That did the trick - thanks for the help!!
02-05-2024 07:23 PM
Can we get this information added to the product documentation? https://support.hyland.com/r/OnBase/Reporting-Dashboards/English/Foundation-22.1/Reporting-Dashboard...
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.