A SQL Query Use Case

Sometimes t.admins need information that is not accessible through TGReports.  Alternative solutions include customized TG reports or SQL queries.  The following use case involved a customer request and the resulting SQL query. This query can be used by customers who use TG LAN_sql or TG Web.

 This content is intended for t.admins who have

• An intermediate to advanced knowledge of Test Generator and TG Reports... 
• Created, edited and published tests...
• Experience using sql queries or have technical assistance available
  
  

 

Customer Request Request:

"I need the following information in a single report."  

Course Name	Percent
Attempts	End Date/Time
Result	First Name
Last Name	UserID
Custom User Fields

Solution: SQL Queries and more...

Providing SQL queries has become the preferred solution--faster to implement and involves less development time.  Other, fee-based options include customizing Crystal Reports (2008 or later) and browser-based reports accessible using TG's Admin Dashboard.  Please contact us if you'd like to explore these options,  

 

---

Query

 Video : a short, silent demonstration is available here*
*video capture courtesy loom.com

SELECT

                t.tst_name as 'Test',

                c.cl_code 'Course Name',

                s.SU_ATTEMPTS 'Attempt #',

                case when s.SU_Q_BANK_SCORE =0 then 0 else

                round(s.SU_LASTSCORE/s.SU_Q_BANK_SCORE *100,

                2) end 'Percent',

                s.SU_DATEENDED 'End Date',

                case when s.su_passed = -1 then 'Passed' else 'Failed' end Result,

                tk.TA_FIRST 'First Name',

                tk.TA_LAST 'Last name',

--- replace the CF #s with the names of your custom fields and remove any not in use

                tk.ta_custom1 as 'CF-1',

                tk.ta_custom2 as 'CF-2',

                tk.ta_custom3 as 'CF-3',

                tk.ta_custom4 as 'CF-4',

                tk.ta_custom5 as 'CF-5',

                tk.ta_custom6 as 'CF-6',

                tk.ta_custom7 as 'CF-7',

                tk.ta_custom8 as 'CF-8',

                tk.ta_custom9 as 'CF-9',

                tk.ta_custom10 as 'CF-10'

FROM

                tests t,

                tests_classes tc,

                classes c,

                takers tk,

                takers_tests tt,

                summary s

WHERE

                t.TST_ID = tc.TST_ID AND

                tc.CL_ID= c.CL_ID AND

                t.TST_ID = tt.tst_id AND

                tc.CL_ID = tt.cl_id AND

                tt.ta_id= tk.TA_ID AND

                tt.tst_id = s.TST_ID AND

                tt.cl_id = s.CL_ID AND

                tt.ta_id= s.TA_ID AND

                s.su_passed=-1 AND

                c.cl_code='your course name here'

ORDER BY

                c.cl_code,

                t.tst_name,

                tk.TA_ID,

                s.SU_ATTEMPTS