02-08-2023 02:14 PM
Hi All,
I'm trying to see if it's possible to have dashboards that run a custom Oracle SQL query, to change what parts of the query get's ran based on the viewing user's user groups.
We have several reports that have nearly identical SQL queries and the only thing that's different is that one will show all user's documents/forms while the other only shows the items for the user that's viewing the dashboard (using the current user macro in the where clause).
I'd like to have a way to use a macro for getting the current user's user groups, checking if it equals a specific user group(s) and running the macro that only brings in the data for the user that's viewing the dashboard.
This would help clearing up all the essentially duplicate dashboards and data providers. It would also mean that I can have 1 dashboard, and when someone in a leadership role views it, it would show the documents/forms worked on by all users. Then if one of those users that are not in a leadership role select that same dashboard, they can only see what they themselves have worked on.
This is just the only way I've thought that this could be accomplished but I'm definitely open to any other suggestions.
Thanks!
02-13-2023 01:48 PM
Hi
I am reaching out to our Reporting Dashboards team to possibly provide some advice for you. We will get back to you as soon as we have information or subsequent questions.
Meanwhile, this question is still open for all other community members to provide a potential solution.
Thanks,
~Alan
02-13-2023 01:53 PM
Hi
Have you looked at the macros documentation here: https://support.hyland.com/r/OnBase/Reporting-Dashboards/English/Foundation-22.1/Reporting-Dashboard...
You could combine the user ID macro with a subquery to get user IDs for certain user groups (using USERXUSERGROUP) to determine what results to display.
02-14-2023 05:16 AM
Hi
You should definitely be able to do this just through IF statements in your SQL. Here is a quick example I wrote up for SQL Server, something similar should be possible through Oracle.
Here is some documentation on that: https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems024.htm
BEGINDECLARE @admingroup INTselect @admingroup = count(*) from hsi.userxusergroup where usernum = ##OB_CURRENTUSER_ID and usergroupnum = 1IF @admingroup > 0 BEGIN select * from hsi.itemdata where itemtypenum = 476 ENDELSE BEGIN select * from hsi.itemdata where itemtypenum = 476 and usernum = ##OB_CURRENTUSER_ID ENDEND
So in this example I am checking to see if my current user is part of usergroupnum 1 (MANAGER). Then if they are I am returning all documents for Doc Type 476. If they are not, then I am just returning documents that my user created in Doc Type 476
Logged in as Manager
Logged in as non-Manager
02-15-2023 12:11 PM
This looks great and is so close to what I've had been testing that It should do the trick. I'll test this out and I'll report back with my results. Thank you!
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.