The dashboard looks good but it still doesn’t provide what the User/Group Report is providing with the security group descriptions assigned to a user. What we would like to do is provide a manager a report of what security groups (with description) a user is currently assigned and approve it for a new hire to have the same access. The dashboard requires some cross referencing between group codes which is more effort for the managers.
Are sub queries available in E9 or in E10 only? Do you have the query available?
The entire query is
select
[UserFile].[DcdUserID] as [UserFile_DcdUserID],
[UserFile].[GroupList] as [UserFile_GroupList],
[SubQuery2].[SecGroup1_SecGroupCode] as [SecGroup1_SecGroupCode],
[SubQuery2].[SecGroup1_SecGroupDesc] as [SecGroup1_SecGroupDesc]
from Erp.UserFile as UserFile
left outer join (select
[SecGroup1].[SecGroupCode] as [SecGroup1_SecGroupCode],
[SecGroup1].[SecGroupDesc] as [SecGroup1_SecGroupDesc],
(SecGroup1.SecGroupCode+'~%') as [Calculated_code1],
('%~'+SecGroup1.SecGroupCode+'~%') as [Calculated_code2],
('%~'+SecGroup1.SecGroupCode) as [Calculated_code3]
from Ice.SecGroup as SecGroup1) as SubQuery2 on
UserFile.GroupList LIKE SubQuery2.Calculated_code1
Or
UserFile.GroupList LIKE SubQuery2.Calculated_code2
Or
UserFile.GroupList LIKE SubQuery2.Calculated_code3
Or
UserFile.GroupList = SubQuery2.SecGroup1_SecGroupCode
order by SubQuery2.SecGroup1_SecGroupCode
Please test it thoroughly to make sure it doesn’t miss any data or return superfluous or dup records
EDIT: Explanation added
- The above gives a record for each User-SecGroup combination.
- Sorted by SecGroupCode
- Users with no sec group set are returned, with the secGroupCode field being blank
I tried running the query in SQL Studio Mgmt but it error’d with the 'invalid object name ‘Erp.UserFile’. Is there something I’ll need to change specific to our database setup?
I’m guessing E9 doesn’t support sub queries in BAQs, else you would have just done it there.
I have no idea how “portable” the BAQ query is to SQL.
Calvin,
Could you perchance export the query to a .baq file? If it’s convenient.
Thanks,
Joe
I didn’t think it would have totally matched your requirement, but thought it might be a useful start. I really posted it under guise of “Showing a person how to fish”
I assume what you really want it so normalize the group data against each user resulting in a proper dataset then pivot the data. I am pretty sure I may have done something similar before, but not for a while. I’ll see when I get back into work if I have any snippets of sql to do this. A quick google on splitting a multi valued column into multiple rows should help.
I hope that helps.
1 Like
Joe - Here’s the exported BAQ.
FWIW - this is from version 10.1.400.23
CK-UserSecGroups.baq (21.6 KB)
4 Likes
Received the BAQ import error “can’t find a query for import or more than one query is defined in export file.” This is for E9.
Sorry, I can’t help with E9, as we jumped straight from V8 to E10
(and our V8 was via Progress OpenEdge)
What is the “Erp.UserFile” referencing that I can compare against our database?
Chia -
I figured out away to do it without sub queries (still might not work in E9), and is kind of a hack.
I just put the text that I want in the final SQL in the “=” and “)” fields.
The SQL phrase generated is
select [UserFile].[DcdUserID] as [UserFile_DcdUserID], [UserFile].[Name] as [UserFile_Name], [SecGroup].[SecGroupDesc] as [SecGroup_SecGroupDesc], [SecGroup].[SecGroupCode] as [SecGroup_SecGroupCode], [UserFile].[GroupList] as [UserFile_GroupList] from Erp.UserFile as UserFile left outer join Ice.SecGroup as SecGroup on UserFile.GroupList LIKE SecGroup.SecGroupCode +'~%'Or UserFile.GroupList LIKE '%~'+ SecGroup.SecGroupCode +'~%'Or UserFile.GroupList LIKE '%~' + SecGroup.SecGroupCode Or UserFile.GroupList = SecGroup.SecGroupCode
3 Likes
Could you upload the BAQ query file? Somehow in SQL Studio, it looks like the Erp.UserFile table needs to be created first.
The Erp.UserFile is just the UserFile table - I think that even existed in V8.
The “Erp.” prefix migh be new in E10. I think they separated the tables into “Erp.” and “Ice.”, where Erp tables are the ones that hold your data that is specific to the ERP system, and the ICE tables refer more to the Application itself. But I’m speculating here.
1 Like
E10 uses SQL views so “ERP.” in front of the table names. For V9 and before, it’d just be the table name: UserFile
E9 query designer is based on Progress (even if you have a SQL server), so the queries Calvin is making may not work one-for-one. You may have to tweak them to work.
That worked if I simply removed the Erp and Ice from the prefix in E9. Now I’ll have to see how to automate this whether in a BAQ or SSRS.
1 Like
Cool. Thanks.
Joe
Not wanting to sound like a SQL Stickler, but objects in sql databases follow a specific notation which is databasename.schemaname.tablename.fieldname where tablename can be replaced with things like views, stored procedures etc. here is an example:
Epicor.ERP.userfile.company
The ERP part or schemaname allows you to divide up access to the database to different groups of users within the specific database. By default every database that is created has at least one schema, and that is dbo, With E10 there are multiple schemas for the database, which then alters the view of what objects can be seen by specific users of the database.
This is a really simplistic overview, security in SQL server can get pretty complex, but I have to say I am glad the Epicor put some thought into using different schemas in E10, E9 you end up scrolling for ever in SSMS!
As always there are plenty of resources on the interweb about SQL server security and I encourage you to take a look.
And if this post needs to go somewhere else then by all means.
1 Like
