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

Sort:
You are not authorized to post a reply.
Author
Messages
mlarson
Basic Member
Posts: 5
Basic Member
    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
    Veteran Member
    Posts: 1498
    Veteran Member
      What exactly are you trying to accomplish? Perhaps someone would have ideas if given some requirements?
      mlarson
      Basic Member
      Posts: 5
      Basic Member
        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
        Greg Moeller
        Veteran Member
        Posts: 1498
        Veteran Member
          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.
          mlarson
          Basic Member
          Posts: 5
          Basic Member

            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
            Basic Member
            Posts: 5
            Basic Member
              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
              Veteran Member
              Posts: 70
              Veteran Member
                @mhroyI would love to have copies of these reports to try and clean up lbi. Thanks
                Kate
                kate.liamero@edisonlearning.com
                Greg Moeller
                Veteran Member
                Posts: 1498
                Veteran Member
                  @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
                  Basic Member
                  Posts: 6
                  Basic Member
                    @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.