Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

Schedule report users *Share* ??

 10 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
Greg Moeller
Veteran Member
Posts: 1498
Veteran Member
4/30/2012 2:46 PM
    Does anyone have a report of the report users that are attached to the schedules that they would be willing to share?

    I'm being asked to develop one, and I really don't have the time. I've got one that has the main report users, (and I'd be willing to share that) but not one that ties the report to the schedule to the report users on the schedule.

    Anyone?

    Thanks much,
    -Greg
    JeanneS
    Veteran Member
    Posts: 49
    Veteran Member
    4/30/2012 3:13 PM
      Try this:
      SELECT "ERS_REPORTS"."REPORTID", "ERS_REPORTS"."REPORTNAME", "ERS_REPORTACCESS"."ACCESSVALUE", "ERS_REPORTACCESS"."ACCESSTYPE", "ERS_REPORTACCESS"."INSTANCEID"
      FROM "RSUSER"."ERS_REPORTS" "ERS_REPORTS" LEFT OUTER JOIN "RSUSER"."ERS_REPORTACCESS" "ERS_REPORTACCESS" ON ("ERS_REPORTS"."REPORTID"="ERS_REPORTACCESS"."REPORTID") AND ("ERS_REPORTS"."INSTANCEID"="ERS_REPORTACCESS"."INSTANCEID")
      ORDER BY "ERS_REPORTS"."REPORTNAME", "ERS_REPORTACCESS"."INSTANCEID" DESC, "ERS_REPORTACCESS"."ACCESSVALUE"

      Chris Martin
      Veteran Member
      Posts: 277
      Veteran Member
      4/30/2012 3:33 PM
        You may need a small tweak to that to get users only associated with the schedule: ERS_REPORTACCESS.INSTANCEID = -1

        Here's my query...

        SELECT ERS_REPORTS.REPORTID, ERS_REPORTS.REPORTNAME, ERS_REPORTACCESS.ACCESSVALUE, ERS_REPORTACCESS.ACCESSTYPE, ERS_REPORTACCESS.INSTANCEID
        FROM LawsonRS.dbo.ERS_REPORTS ERS_REPORTS
        LEFT OUTER JOIN LawsonRS.dbo.ERS_REPORTACCESS ERS_REPORTACCESS
        ON ERS_REPORTS.REPORTID=ERS_REPORTACCESS.REPORTID
        AND ERS_REPORTACCESS.INSTANCEID = -1
        ORDER BY ERS_REPORTS.REPORTNAME, ERS_REPORTACCESS.INSTANCEID DESC, ERS_REPORTACCESS.ACCESSVALUE

        Chris Martin
        Veteran Member
        Posts: 277
        Veteran Member
        4/30/2012 3:39 PM
          Modified query to included the schedule name:

          SELECT ERS_REPORTS.REPORTID, ERS_REPORTS.REPORTNAME, ERS_REPORTACCESS.JOBNAME, ERS_REPORTACCESS.ACCESSVALUE, ERS_REPORTACCESS.ACCESSTYPE, ERS_REPORTACCESS.INSTANCEID
          FROM LawsonRS.dbo.ERS_REPORTS ERS_REPORTS
          LEFT OUTER JOIN LawsonRS.dbo.ERS_REPORTACCESS ERS_REPORTACCESS
          ON ERS_REPORTS.REPORTID=ERS_REPORTACCESS.REPORTID
          AND ERS_REPORTACCESS.INSTANCEID = -1
          ORDER BY ERS_REPORTS.REPORTNAME, ERS_REPORTACCESS.INSTANCEID DESC, ERS_REPORTACCESS.ACCESSVALUE
          Greg Moeller
          Veteran Member
          Posts: 1498
          Veteran Member
          4/30/2012 3:46 PM
            Chris: When I filter to InstanceID = -1, I don't get any records returned. What am I doing wrong? The very first query you provided returns lots of pages of data.
            Greg Moeller
            Veteran Member
            Posts: 1498
            Veteran Member
            4/30/2012 3:52 PM
              OK... It works and returns data when I just use the sql expression, but I'm trying to convert it to a Crystal report. I'll get it. I'm too bull-headed to quit.
              Greg Moeller
              Veteran Member
              Posts: 1498
              Veteran Member
              4/30/2012 6:28 PM
                Well, after some difficulties, I finally got it to work. Validation is probably the next step. Thanks all for your help!!
                Chris Martin
                Veteran Member
                Posts: 277
                Veteran Member
                4/30/2012 6:48 PM
                  Glad to hear. Mind sharing your working query?
                  Greg Moeller
                  Veteran Member
                  Posts: 1498
                  Veteran Member
                  4/30/2012 7:18 PM
                    Not a problem: Here's what we've gotten to work. It's not validated yet, but it's returning some of the expected data.
                    SELECT DISTINCT "ERS_REPORTACCESS"."INSTANCEID", "ERS_REPORTS"."REPORTNAME", "ERS_REPORTACCESS"."JOBNAME", "ERS_REPORTACCESS"."ACCESSVALUE", "ERS_REPORTS"."REPORTID", "ERS_REPORTACCESS"."ACCESSTYPE"
                    FROM "LAWRS"."ERS_REPORTS" "ERS_REPORTS" LEFT OUTER JOIN "LAWRS"."ERS_REPORTACCESS" "ERS_REPORTACCESS" ON "ERS_REPORTS"."REPORTID"="ERS_REPORTACCESS"."REPORTID"
                    WHERE "ERS_REPORTACCESS"."INSTANCEID"=-1
                    ORDER BY "ERS_REPORTS"."REPORTID", "ERS_REPORTS"."REPORTNAME", "ERS_REPORTACCESS"."JOBNAME"
                    Greg Moeller
                    Veteran Member
                    Posts: 1498
                    Veteran Member
                    4/30/2012 8:07 PM
                      In my Crystal report I set up a group by on ReportID, ReportName and JobName.
                      Greg Moeller
                      Veteran Member
                      Posts: 1498
                      Veteran Member
                      5/2/2012 6:52 PM
                        Here's an actual .rpt file if you want. :)
                        Attachments