• Search Forums
    Advanced Search Topics Posts
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 Send Private Message
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 Send Private Message
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 Send Private Message
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 Send Private Message
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 Send Private Message
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 Send Private Message
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 Send Private Message
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 Send Private Message
Posts: 277
Veteran Member
4/30/2012 6:48 PM
Glad to hear. Mind sharing your working query?
Greg Moeller
Veteran Member Send Private Message
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 Send Private Message
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 Send Private Message
Posts: 1498
Veteran Member
5/2/2012 6:52 PM
Here's an actual .rpt file if you want. :)
Attachments