SQL query: courses with iClicker registrations

SELECT cm.course_id, u.firstname, u.lastname, u.email
  FROM bb_bb60.course_main cm
  INNER JOIN bb_bb60.course_users cu ON cm.pk1 = cu.crsmain_pk1
  INNER JOIN bb_bb60.users u ON u.pk1 = cu.users_pk1
  WHERE cm.row_status = 0 AND cu.row_status = 0 AND cu.available_ind = 'Y' AND cu.role = 'P' AND cm.course_id LIKE '%20148' AND cm.pk1 IN (
    SELECT TO_NUMBER(regexp_replace(ir.course_id, '_(.*)_1', '\1'))
    FROM bb_bb60.iclicker_registration ir WHERE regexp_like(ir.course_id,'_[0-9]+_1')
  );

Defining Custom Template Variables in a Building Block

The NSSE template variable b2 can be downloaded here.  My presentation from Bb DevCon 2015 can be found in that folder as well.

I would like to thank Simon Ponder of Purdue's Informatics group for sharing his code on which I based the below upon.  Hopefully, this post will give others looking to create their own custom template variables in Blackboard Learn enough information to get started.

To start off, we have to define our plugin as a java extension in the bb-manifest.xml file like so:

<?xml version="1.0" encoding="utf-8"?>
<plugin>

    <name value="bsTemplateVar" />
    <handle value="bs-template-var" />
    <description value="Brett's Custom Template Variable" />
    <default-locale value="en_US" />
 <webapp-type value="javaext"/>
...
</plugin>