Configurable Reports Custom Variables

Re: Configurable Reports Custom Variables

by James Todd -
Number of replies: 3
I'm not entirely sure what you mean.
Here the code I am using for the currently logged on.

SELECT DISTINCT u.firstname AS first_name, u.lastname AS last_name, uid1.data AS manager, f.name AS course, DATE_FORMAT(FROM_UNIXTIME(fsd.timestart),'%d-%m-%y') AS date

FROM prefix_user as u
JOIN prefix_facetoface_signups_status as fss ON u.id = fss.createdby
JOIN prefix_facetoface_signups as fsu ON fss.signupid = fsu.id
JOIN prefix_facetoface_sessions as fs ON fsu.sessionid = fs.id
JOIN prefix_facetoface as f ON fs.facetoface = f.id
JOIN prefix_facetoface_sessions_dates as fsd ON fs.id = fsd.sessionid
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_data AS uid1 ON uid1.userid = u.id
JOIN prefix_facetoface_session_data AS fsda ON fs.id = fsda.sessionid

WHERE uid.fieldid = '2'
AND uid1.fieldid = '1'
AND fsda.fieldid = '1'
AND fss.statuscode ='100'
AND superceded='0'
AND uid.data = %%userid%%
Average of ratings: -
In reply to James Todd

Re: Configurable Reports Custom Variables

by Miguel González Laredo -
Picture of Plugin developers

I think you have it almost. Try change:

AND uid.data = %%userid%%

for

u.id = %%userid%%

If you want to obtain data from current user executing the report as manager. That is I understood from your explanation.


Average of ratings: -
In reply to Miguel González Laredo

Re: Configurable Reports Custom Variables

by James Todd -
Thanks, that brings up my own results.

I've currently cut the code right back to see if I can just see a list of my staff.
without the %%USERID%% variable it just show a list of people who have put their own email address in the manager field by accident, or they manage themselves, Lucky people.


SELECT DISTINCT u.id AS userid, u.username AS staff, u.email AS email, uid.data AS manager

FROM prefix_user as u
JOIN prefix_user_info_data AS uid ON uid.userid = u.id

WHERE u.id = %%USERID%%
AND u.email = uid.data
Average of ratings: -
In reply to James Todd

Re: Configurable Reports Custom Variables

by James Todd -
Can Juan Leyva or Sara Ajona Tellez comment on this please.

Basically how do I create a variable for current user email address.
Average of ratings: -