Using HTML Coding in sqlReports

Updated on 1/21/2018.

You can use html commands in your sqlReports reports to do things such as create links, display images (including student photos), and add color to reports (change background color, add progress and meter bars).  The main things to remember is you'll need to concatenate quite a bit - the || command - and you won't be able to use double quotes, colons, or semi-colons, so you need to use chr commands for them - chr(34), chr(58), and chr(59). 

Also, as of the release of 11.0.4.1 on 1/19/2018, using certain html commands in a field can give you a cross-site script error when you click submit and not allow you to save your changes.   PS will check for a < and then certain coding, and if the coding isn't allowed, PS will not save the report.  The workaround is to replace the leading < with a chr(60) command.  That will allow the report to save and when it runs it'll convert the chr(60) to a < when the report displays.

Below are some examples of coding, with parts of the sql statement in bold that use the html coding.  The first example is how you could use it prior to PS 11.0.4.1, and the second example is how you must use it on new releases of PS, with both the < and > replaced with chr commands.   To work around the cross-site scripting error, you only need to replace the first <, but the coding below replaces any < and > to be on the safe side in case changes are made in future releases.   It's recommended you use the second example even if you're on an older version of PS so that you don't have to go back and change it later.   But both versions are shown so you can compare the differences that may be applicable to your reports.


Hyperlink a Student Name

The following is an example of creating a link to the student screens for a student report - the key here is to use the dcid field and not id.  The student frn is 001 followed by the student dcid.:

Prior to PS 11.0.4.1:  

select '<a href=/admin/students/home.html?frn=001'||to_char(dcid)||' target=_blank>'||lastfirst||'</a>', grade_level, city
from students 

For any version:

select chr(60)||'a href=/admin/students/home.html?frn=001'||to_char(dcid)||' target=_blank'||chr(62)||lastfirst||chr(60)||'/a'||chr(62), grade_level, city
from students

If you have multiple tables, be sure to add the students table name or alias to both the dcid and lastfirst fields in the link.


Display a Student Photo

The following is an example of how to display the thumbnail photo of a student on a report in the admin portal - unlike with the link above, you use the id field and not the dcid field:

Prior to PS 11.0.4.1:

select '<img src=/admin/stpthumb/'||id||'ph_thumb.jpeg>', lastfirst
from students

For any version:

select chr(60)||'img src=/admin/stpthumb/'||id||'ph_thumb.jpeg'||chr(62), lastfirst
from students

For a teacher report, change the /admin part to /teachers. 

To display other types of images, see the Alerts List report for admins on how to display alert icons.


Change the Background Color of a Cell

The following is from Bob McGregor and is how to make the color of a cell on the report yellow:

Prior to PS 11.0.4.1:  

SELECT s.LastFirst, s.dcid, s.grade_level, case when pgf.Grade = 'F' then '<span style='||chr(34)||'background-color'||chr(58)||'#fff000'||chr(59)||'display'||chr(58)||'block'||chr(34)||'>'||pgf.Grade||'</span>' else pgf.Grade end, pgf.Percent, c.Course_Name, u.LastFirst, to_char(pgf.LastGradeUpdate,'MM/DD/YYYY')

For any version:

SELECT s.LastFirst, s.dcid, s.grade_level, case when pgf.Grade = 'F' then chr(60)||'span style='||chr(34)||'background-color'||chr(58)||'#fff000'||chr(59)||'display'||chr(58)||'block'||chr(34)||chr(62)||pgf.Grade||chr(60)||'/span'||chr(62) else pgf.Grade end, pgf.Percent, c.Course_Name, u.LastFirst, to_char(pgf.LastGradeUpdate,'MM/DD/YYYY')

Here's what it looks like:

 bgcolorcell

You could expand the case to cover different colors, such as when the grade equals F, do one color, when grade equals C or D do another color, and when A or B do another color.  This would be similar to using a meter bar as explained below in the sense you're using different colors to highlight differences.  Meter bars only work with numbers though, so if you want to do something similar with text, use case commands with background color changes.


Highlight Data in a Cell

This is similar to changing the background color, but instead of doing the entire cell, it only changes the background color of the text in the cell.  It's similar to taking a yellow highlighter and highlighting text.  The difference from above is the removal of the chr(59)||'display'||chr(58)||'block'|| command:

Prior to PS 11.0.4.1:  

SELECT s.LastFirst, s.dcid, s.grade_level, case when pgf.Grade = 'F' then '<span style='||chr(34)||'background-color'||chr(58)||'#fff000'||chr(34)||'>'||pgf.Grade||'</span>' else pgf.Grade end, pgf.Percent, c.Course_Name, u.LastFirst, to_char(pgf.LastGradeUpdate,'MM/DD/YYYY')

For any version:

SELECT s.LastFirst, s.dcid, s.grade_level, case when pgf.Grade = 'F' then chr(60)||'span style='||chr(34)||'background-color'||chr(58)||'#fff000'||chr(34)||chr(62)||pgf.Grade||chr(60)||'/span'||chr(62) else pgf.Grade end, pgf.Percent, c.Course_Name, u.LastFirst, to_char(pgf.LastGradeUpdate,'MM/DD/YYYY')

Here is what it looks like:

 bgcolortext

You can see it in action too with the Phone Number Search report.


Adding a Progress Bar

There's a HTML5 command that will create a progress bar and it's very simple to use.  Coding follows the format of (replace some number with actual values):

<progress value="some number" max="some number"></progress>

Here's an example of sql that would show a progress bar based on total earned credits by students, with the value being the sum calculated by sql and the max value being 24:

Prior to PS 11.0.4.1: 

select s.lastfirst, '<progress value='||chr(34)|| sum(sg.earnedcrhrs) ||chr(34)||' max='||chr(34)|| 24 ||chr(34)||'></progress>'

For any version:

select s.lastfirst, chr(60)||'progress value='||chr(34)|| sum(sg.earnedcrhrs) ||chr(34)||' max='||chr(34)|| 24 ||chr(34)||chr(62)||chr(60)||'/progress'||chr(62)

Here is how it would look:

 progressbar

Browsers must be HTML5 compatible and they will display different colors for the bar.


Adding a Meter Bar

There's another HTML5 command that will do a bar, but this one will allow you to color code the bar based on different criteria.   There is more criteria that can be set, which helps control the color.  Here is an example of the default command (replace some number with actual values).  Only value is required - click here for a detailed description of the entire command:

<meter value="some number" min="some number" low="some number" high="some number" max="some number"' optimum="some number"></meter>

The value part can be calculated with sql, while the rest of the numbers could be hard coded.  Here's an example of total earned credits using a meter bar:

Prior to PS 11.0.4.1: 

select s.lastfirst, '<meter value='||chr(34)|| sum(sg.earnedcrhrs) ||chr(34)||' min='||chr(34)|| 0 ||chr(34)||' low='||chr(34)|| 6 ||chr(34)||' high='||chr(34)|| 12 ||chr(34)||' max='||chr(34)|| 24 ||chr(34)||' optimum='||chr(34)|| 18 ||chr(34)||' ></meter>'

For any version:

select s.lastfirst, chr(60)||'meter value='||chr(34)|| sum(sg.earnedcrhrs) ||chr(34)||' min='||chr(34)|| 0 ||chr(34)||' low='||chr(34)|| 6 ||chr(34)||' high='||chr(34)|| 12 ||chr(34)||' max='||chr(34)|| 24 ||chr(34)||' optimum='||chr(34)|| 18 ||chr(34)||chr(62)||chr(60)||'/meter'||chr(62)

Here is how it would look - the colors represented can vary depending on the values you use, so make sure to experiment:

 meterbar

In the example, the optimum value (18) falls between the high (12) and max (24), however, if you set it to fall between the min and low, the red and green will switch spots.  That may be useful if you want to do reports such as where a person with a low number of absenses has a green bar and those with high numbers of absences have a red one.

Browsers must be HTML5 compatible, however, they tend to display the same color coding for the bars.


Centering Data in a Column

The following is how to center data in a column and there are two steps to it.  The first is a variation of the code above by using a span command to center the data:

Prior to PS 11.0.4.1: 

select lastfirst, '<span style='||chr(34)||'text-align'||chr(58)||'center'||chr(59)||'display'||chr(58)||'block'||chr(34)||'>'||Grade_Level||'</span>', home_phone

For any version:

select lastfirst, chr(60)||'span style='||chr(34)||'text-align'||chr(58)||'center'||chr(59)||'display'||chr(58)||'block'||chr(34)||chr(62)||Grade_Level||chr(60)||'/span'||chr(62), home_phone

The above will center the data in the column, but you must also center the column heading.  To do so, add an align="center" command to the report header like so (this will work with any version as the th command is not affected by the cross-site scripting error):

<th>Student</th><th align="center">Grade</th><th>Home Phone</th>


Set a Fixed Width for a Column

The column widths are dynamic, but sometimes you may want a column to be a set width.  For example, you have a report with the teacher name and a final grade comment.  The final grade comment column is maybe too wide and the teacher name column hard to read because it's not wide enough and putting names on two lines.  In the report header, use a width= command to set the column width like so (this will work with any version):

<th width="300">Teacher Name</th><th>Comment</th>

  Back to the List of Articles