Add Student Selector to Publish SQL Report

Follow

Marianna Ripple

October 17, 2018 17:58

How do I add the Student Selector when creating a Jasper Report based upon SQL?

1

• Official comment

  

  Franck Reyherme October 18, 2018 21:20 (Edited April 19, 2019 16:40)

  Hi Marianna,

  To add the the Student Scope Selector to your SQL-Based reports, you need to attach the magical "visibility_sql" custom report to your BI Tool report.  

  • Open your BI Tool report in Illuminate
  • Click the "Edit" button
  • Where it says, "Select Custom Report", search for "visibility_sql"
  • Select the "visibility_sql" custom report.
  • Click Save.

  IMPORTANT: Your report's query MUST use the $P{visibility_sql} parameter in the WHERE clause.  Otherwise, the Student Scope Selector will not work, and your report will not respect visibility.  Feel free to reach out to me if you need any help with this.

  NOTE (for users who have been using the BI Tool pre-2019):  You no longer need to attach the visibility_sql custom report in a separate step.  You can now install your JRXML and attach the visibility_sql custom report in a single step.  Yay!  That also means, you can update your reports without having to remove and re-attach the visibilty_sql custom report.  Yay!

   

  Permalink
• 

  Marianna Ripple November 08, 2018 17:31

  Can you share an example of including the visibility parameter in the WHERE clause?

  1

  Permalink
• 

  Franck Reyherme November 08, 2018 18:41

  Hi Marianna,

  Sure thing.
  Here’s a simple example:

  SELECT 
   stu.last_name,
   stu.first_name
  
   FROM students stu
   WHERE stu.student_id IN (SELECT student_id FROM($P!{visibility_sql}) visibility)
  

  1

  Permalink
• 

  Christopher Albans June 10, 2019 17:12

  Thanks for this. Seems I need to be adding $P{visibility_sql} to my BI Tool queries! Hmm.

  0

  Permalink

Please sign in to leave a comment.