HI Juan,
do you know if configurable reports can handle things like Declare SET Define as it throws errors for me when I try to use them. Any way around that?
I need to use it because I have one table that has the needed information but for some reason registers the course as course 0 when they click it in another course (blackboard collaborate for attendance) yet in the column other in prefix_logstore_standard_log I see the course number easily but I can't seem to query it out various ways as the %%course%% for some reason does not work using my sql below: (the ## I tried various ways without success when the course in my example is 108 so using AND L.other LIKE '%108%' works great but I'm trying to replace 108 with a variable or query instead to pull it out)
Thanks, Jamie
## DECLARE @CID
##SELECT @CID = D.id from prefix_course as D WHERE D.id = %%COURSEID%%
SELECT U.firstname as First, U.lastname as Last,L.userid, L.courseid,L.component,
FROM_UNIXTIME( L.timecreated, '%m-%d-%Y' ) as DATE,
FROM_UNIXTIME( L.timecreated, '%r' ) as TIME
FROM prefix_logstore_standard_log as L
LEFT JOIN prefix_course as C ON L.courseid = C.id left
JOIN prefix_user as U on L.userid = U.id
WHERE L.other like '%loadmeeting%'
AND L.component like '%mod_elluminate%'
## AND L.other LIKE @CID
## AND D.id = %%COURSEID%%
## WHERE L.courseid = %%COURSEID%%
##AND L.other LIKE '%108%'
## AND L.other LIKE Select %%COURSEID%%
## AND L.courseid = %%COURSEID%%
%%FILTER_STARTTIME:L.timecreated:>%%
%%FILTER_ENDTIME:L.timecreated:<%%