Using HTML Coding in sqlReports

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 - 34, 58, and 59.  Below are some examples of coding, with parts of the sql statement that use the html coding bolded:


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.:

select '<a href=/admin/students/home.html?frn=001'||to_char(dcid)||' target=_blank>'||lastfirst||'</a>', 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:

select '<img src=/admin/stpthumb/'||id||'ph_thumb.jpeg>', 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:

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')

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:

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')

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:

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

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:

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>'

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:

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

<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:

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

  Back to the List of Articles