Report detailing activity by Category

Report detailing activity by Category

by Chris Chapman -
Number of replies: 2

Hi all I Recently installed this block (2.5+ Stable), and have managed a few useful reports. However one thing I cant seem to manage which would be most useful is to produce Reports on various different activity by Categories.

 

When I say Categories I mean Parent categories which include all sub categories within. at our Institution this for example would mean Humanities as a 'Parent' and all sub categories are subjects which contain various courses.

 

hope this makes sense- can anyone shed any light on this?

thanks in advance.

Average of ratings: -
In reply to Chris Chapman

Re: Report detailing activity by Category

by Randy Thornton -
Picture of Documentation writers

Here's a snippet I have for categories of courses. I used it as a basis to wrap around what data I actually want.

SELECT c.shortname AS 'Course', cc.name AS 'Category',
CASE
  WHEN cc.parent = 0 THEN 'Top'
  ELSE (SELECT cp.name FROM prefix_course_categories AS cp WHERE cc.parent = cp.id)
END AS 'Parent'
FROM prefix_course AS c
JOIN prefix_course_categories AS cc ON cc.id = c.category

It only goes one level deep instead of going through the whole category tree, so it is simple, but it shows you the relations so you can easily get the category you may want.

 

-Randy

 

Average of ratings: Useful (1)