• Official comment
    Franck Reyherme (Edited )

    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!


  • Avatar
    Marianna Ripple

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

  • Avatar
    Franck Reyherme

    Hi Marianna,

    Sure thing.
    Here’s a simple example:

     FROM students stu
     WHERE stu.student_id IN (SELECT student_id FROM($P!{visibility_sql}) visibility)
  • Avatar
    Christopher Albans

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

Please sign in to leave a comment.