Run Different SQL Based On The User Group of The User Viewing a Dashboard

[Alex_Alpert]

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!

[aboucher]

Hi @Alex Alpert,

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 

[Larissa_Armand]

Hi @Alex Alpert 

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. 

[Mike_Walkuski]

Hi @Alex Alpert 

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

[Alex_Alpert]

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!