Can I write 1 SQL report to generate viewer-dependent data?

Can I write 1 SQL report to generate viewer-dependent data?

by Kimber Warden -
Number of replies: 2

Can I, as an admin, write one SQL report intended for teachers to use, which dynamically includes only the courses relevant to the viewer? All of my teachers want the same report (with info about their own students), and I don't want to have to create separate reports for each of them, using a WHERE course.shortname='Algebra' -type of filter.

Average of ratings: Useful (1)
In reply to Kimber Warden

Re: Can I write 1 SQL report to generate viewer-dependent data?

by Michael E -
Picture of Core developers Picture of Testers

I have the same request. But in my case, I want this report (always the same report) only to be included in certain courses. I would like to avoid creating hundreds of the same reports.

Average of ratings: -
In reply to Kimber Warden

Re: Can I write 1 SQL report to generate viewer-dependent data?

by Alan Hare -

I use MS SQL database so you will need to modify my references below for whatever databas you use.

Check the Permissions tab.  Use either the Report capability AND/OR Create a role (duplicated from your Student Role and modify), and add permissions;

Example:
C1 - Report Capabilities
C2 - User with the selected role/s in the current report course
C3 - Any user in the current report course
(C1 OR C2) AND C3
 
 
or In your query you limiit what is viewed only to those of the same Cohort or UserID of the user viewing the report.
 
For the example below, I am seperating the query by everyone with the same first 2 digits of their USERNAME which is basicly their group/location identifier from LDAP userid.
 
Example list of users:
01joesmith
01janesmith
01jimwrite
04johnadams
04janeadams
07bobedwards
 
Example pre-query to seperate by USERNAME
DECLARE @myuserid VARCHAR(2)
SELECT @myuserid = LEFT(username,2)
FROM prefix_user AS u
WHERE u.id = %%USERID%%
 
..query...
 
AND LEFT(u.username,2) = @myuserid
 

So if 01joesmith ran this report/query, he would get all the data for users of the same IDs, which in this example would list data for;

01joesmith
01janesmith
01jimwrite
 

Note:

  • If you create a report/query on the Front Page is becomes a Site Wide report and lists ALL Data not specific to any one course.
  • If you create a report/query on the Course Page is useually only queries data specific to that Course.
Average of ratings: -