PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 05/15/2014 4:20 PM by  JeffCozzag
Is there a key in the EFS schema that can link to the REPORTID field in the ERS schema?
 8 Replies
Sort:
You are not authorized to post a reply.
Author Messages
mlarson
Software Developer
Duvo Software
Basic Member
(11 points)
Basic Member
Posts:5


Send Message:

--
01/10/2014 1:54 PM
    I'm trying to figure out how to link the data in the ENBATREE table of the EFS schema to the report data found in the ERS schema via a SQL query. After analyzing the database tables I have not seen anything from the EFS schema that can be linked to the REPORTID field which is used by the majority of the tables in the ERS schema. Any help would be appreciated, thanks!
    Greg Moeller
    Private
    Private
    Veteran Member
    (4035 points)
    Veteran Member
    Posts:1439


    Send Message:

    --
    01/10/2014 3:34 PM
    What exactly are you trying to accomplish? Perhaps someone would have ideas if given some requirements?
    mlarson
    Software Developer
    Duvo Software
    Basic Member
    (11 points)
    Basic Member
    Posts:5


    Send Message:

    --
    01/13/2014 6:29 AM
    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.com/forums/pm/lbi/which-database-tables-are-used-for-storing-lbi-report-dashboard-group-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
    Greg Moeller
    Private
    Private
    Veteran Member
    (4035 points)
    Veteran Member
    Posts:1439


    Send Message:

    --
    01/13/2014 9:55 AM
    You may be able to figure out a way to link ENPUSERMAP.USERID --
    See:
    https://www.lawsonguru.com/gurucontent/datadict/LBI-10.1.0.0-149/LAWFS/relationships.html

    Let us all know what you figure out. I'll keep you updated as well, if I figure out anything.
    mlarson
    Software Developer
    Duvo Software
    Basic Member
    (11 points)
    Basic Member
    Posts:5


    Send Message:

    --
    01/28/2014 6:54 AM

    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.




    MHRoy
    Sys Admins/Database Admins
    City of Nashua, New Hampshire
    Basic Member
    (13 points)
    Basic Member
    Posts:5


    Send Message:

    --
    05/09/2014 2:27 PM
    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...
    Kate Liamero
    Private
    Private
    Veteran Member
    (144 points)
    Veteran Member
    Posts:70


    Send Message:

    --
    05/12/2014 2:24 PM
    @mhroyI would love to have copies of these reports to try and clean up lbi. Thanks
    Kate
    kate.liamero@edisonlearning.com
    Greg Moeller
    Private
    Private
    Veteran Member
    (4035 points)
    Veteran Member
    Posts:1439


    Send Message:

    --
    05/14/2014 5:09 PM
    @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
    JeffCozzag
    Systems Analyst
    SCI Companies
    Basic Member
    (16 points)
    Basic Member
    Posts:6


    Send Message:

    --
    05/15/2014 4:20 PM
    @mhroyI would love to have copies of these reports. Thank you for your time and efforts.
    jcozzaglio@platformone.com
    You are not authorized to post a reply.