Is there a key in the EFS schema that can link to the REPORTID field in the ERS schema?

What exactly are you trying to accomplish? Perhaps someone would have ideas if given some requirements?

I want to apply the Report Id to the LBI link objects that a user has access to. Similar to when you helped me before: https://www.lawsonguru.co...roup-hierarchy-data/ but I want to return the Report Id as well so that I can access report data from multiple tables in the ERS schema.

Basically how can I link the following two schemas:
EFS - http://www.images.lawsonguru.com/tips/lbi/LBI-EFS-ERD.gif
ERS - http://www.images.lawsonguru.com/tips/lbi/LBI-ERS-ERD.gif

You may be able to figure out a way to link ENPUSERMAP.USERID --
See:
https://www.lawsonguru.co...S/relationships.html

Let us all know what you figure out. I'll keep you updated as well, if I figure out anything.

I found a solution to what I was trying to accomplish but I am not certain that this is the best way to do it. I wanted to find a way to connect the data in the framework services (EFS) schema to the data in the reporting services (ERS) schema, more specifically I wanted to relate the reporting data from EFS to the report id in ERS so that I could call reports at will from a web browser. I later found that there really is no point to connect the two schemas at all because all of the necessary data can be found in the EFS schema.

I started with the query shown below to get the entry id, entry parent id, and entry string (report name) from the ENPBATREE table by the user id and group id's belonging to a specific username:

select distinct(bt.treeentryid) entryid, bt.entryparent, bt.lvl, bt.entrystring
from 'efs_schema'.enpentryaccess ea, 'efs_schema'.enpbatree bt
where ea.treeentryid = bt.treeentryid and
(ea.accessid in
(select gm.userid
from 'efs_schema'.enpgroupmap gm, 'efs_schema'.enpusermap um
where gm.userid = um.userid and um.username = 'user1')
or ea.accessid in
(select gm.groupid
from 'efs_schema'.enpgroupmap gm, 'efs_schema'.enpusermap um
where gm.userid = um.userid and um.username = 'user1'))
and bt.lvl > 1
order by bt.lvl, entryid, bt.entryparent;

To use the above query replace 'efs_schema' with the name of your EFS database schema and replace 'user1' with the username you would like search permissions for. The lvl column shows the hierarchy of the dashboards, modules, and reports that a user has access to. In my case dashboards were lvl 2, modules were lvl 3 and reports were lvl 4.

After retrieving this data via a server side application development language (.NET, Java, etc.), I was able to recursively loop through all parent id's of the returned dashboards and find all modules/reports that belonged to each parent id.

When all of the entry id's are retrieved for the reports it is then possible to get the parameterized report url from the ENPENTRYATTR table.

I have been working on trying to link these together as well so that I can create a report showing tab, module, module user and report user entries. I have not found a link.

However, I was able to produce 2 reports.

The first shows each tab, the modules in the tab and the reports in the modules. I have drill downs and drill throughs that show all the users at each of these levels. If the "user" is a group, drill downs exist to show all of the members as well.

The second report shows report user settings for each report, including drill downs for groups.

If anyone is interested, just let me know...

@mhroyI would love to have copies of these reports to try and clean up lbi. Thanks
Kate
kate.liamero@edisonlearning.com

@MHRoy: Please send those reports to me as well! It's always interesting to see what others have been able to come up with!
moellerg@genesishealth.com

@mhroyI would love to have copies of these reports. Thank you for your time and efforts.
jcozzaglio@platformone.com