How to Create Drop-Down Menus

This article replaces the Drop-Down Menus pdf found in the various downloads on this site.

Last Update - February 23, 2017

Overview

sqlReports 4.4 and higher comes with several drop-down menus and you can easily add more. The following covers the drop-down menu system and the steps to take to add more drop-down menus. There are three steps to adding a new menu:

sqlReports 4

1. Create a new menu file in the /sqlReports4/dropdowns folder.  If you have the teacher portal version of sqlReports installed, make sure your drop-down menu files exist in the /teachers/sqlReports4/dropdowns folder as well.  Portals cannot share files in their respective folders, so the drop-down file must be in both the admin and teachers /sqlReports4/dropdowns folder.
2. Add a reference to the new menu file to the /sqlReports4/dropdownmenu.html file.
3. Add a description of the new menu to the /sqlReports4/dropdowndescriptions.html file.

sqlReports 5

1. Create a new menu file in the /sqlReports5/dropdowns folder.  If you have the teacher portal version of sqlReports installed, make sure your drop-down menu files exist in the /teachers/sqlReports5/dropdowns folder as well.  Portals cannot share files in their respective folders, so the drop-down file must be in both the admin and teachers /sqlReports4/dropdowns folder.
2. Add a reference to the new menu file to the /sqlReports5/dropdownmenu.html file.
3. Add a description of the new menu to the /sqlReports5/dropdowndescriptions.html file.

Step 1 - The Menu Files

The files that create the menus are html files located in the /dropdowns folder.  The menus can either be dynamic ones using tlist_sql, and or static ones with hard-coded choices. How to create both will be covered next. It’s recommended to create new files by copying an existing file, renaming it, and then editing it.

Dynamic Menus

Dynamic menus are created using a PS command called tlist_sql and are useful when you want to create menus specific to the current school or current year. Here’s an example of the coding that appears in the student.html file to create the Students (ID) drop-down menu:

~[tlist_sql;SELECT id, lastfirst, grade_level FROM Students WHERE enroll_status = 0 and schoolid
= ~(curschoolid) ORDER BY lastfirst] <option value="~(id)">~(lastfirst) (~(grade_level))[/tlist_sql]

Here’s a breakdown of the coding:

~[tlist_sql; - this is PS coding to indicate the start of the tlist_sql command

SELECT id, lastfirst, grade_level FROM Students WHERE enroll_status = 0 and schoolid = ~(curschoolid) ORDER BY lastfirst] - the sql to create the selection for the drop-down and is followed by a ]. The piece of information that will be passed to the parameter in the report should be the first item in the select. In the example above, the id field is passed to the parameter and is the first item selected.

<option value="~(id)">~(lastfirst) (~(grade_level)) – this is html coding used in drop-down menus. What follows value= and is in quotes is the data passed to the parameter. The part after the > is what is displayed to the user and may or may not be the same thing as the value. In the Student (ID) menu, the student id is passed to the parameter in the sql report and the student name and grade level is shown to the user.

[/tlist_sql] – this is PS coding to indicate the end of the tlist_sql command

Every dynamic menu you create should have the four parts mentioned above. If you look at the other files in the /sqlReports4/dropdowns folder you’ll see similar setups. The only exception is the yesorno.html file which is a static drop-down and covered next.

Static Menus

Static menus will display the same information every time and do not use tlist_sql. An example of a static menu is the yesorno.html file which displays yes and no in the menu and is coded as follows:

<option value="No">No
<option value="Yes">Yes

With a static menu you just create a list of items. Each item will have an option value=”” part, which will be the information you want to pass to the parameter, and a label after the >, which will be the information displayed to the user. If you want a blank at the top of the menu, put <option value=””> at the top of the list.

Step 2 - Edit dropdownmenu.html

Step 2 in adding a new menu is adding a reference to the menu to the dropdownmenu.html page located in the main sqlReports folder. The dropdownmenu.html is the file used by sqlReports to show possible menu choices in the Edit, Parameters area and is basically a static menu listing the menu files. Here is a snippet of the default dropdownmenu.html file:

<option value="">
<option value="student">Students (ID)
<option value="student2">Students (DCID)
<option value="staff">Staff (ID)
<option value="staff2">Staff (DCID)

The name in the value=”” part is the name of the menu file, minus the .html part of the file name. The part after the > is what you want to appear on the menu in the Edit, Parameters area.   For example, if you created a new menu file called honorrolllevels.html, then you would add something like the following to the dropdownmenu.html file:

<option value="honorrolllevels">Honor Roll Levels

Step 3 - Edit dropdowndescriptions.html

The final step to adding a new menu is to add a description of the menu to the dropdowndescriptions.html file located in the main sqlReports folder. The contents of the file are displayed to a user who clicks on the "Information Regarding Name to Display, Add, and Use Drop Down" area on the bottom of the Edit, Parameters page. This step is not critical to your new menu working, but is rather a way to remind yourself or others what the menu shows and what it passes to the parameter while on the Edit, Parameters page.

Backing Up Your Files

Be sure to make back up the /dropdowns folder and any copies of the files mentioned in steps 2 and 3 prior to any upgrades of sqlReports or deleting and reinstalling a plugin so that you don't lose any changes.



Technical Information / How the Menu System Works

Here’s more specific information for those who would like a better understanding of how the menu system works. When you go to the Edit, Parameters page, the “Use Drop Down” menu is created for Parameter 1 using the following coding (similar coding appears for the other parameters):

<select name="[06]ParamVal1" id="Param1">~[x:insertfile;dropdowns/~([06]ParamOpt1).html]</select>

The x:insertfile command is a PS command that inserts the contents of an html page at that spot. In the example above, the contents of the dropdownmenu.html page are inserted on the page. When one chooses a drop-down menu item, the option value for that item is saved in the ParamOpt field for the parameter. For example, if you choose the Students (ID) menu for Parameter 1, student from <option value=”student”> is saved in the ParamOpt1 field.

Next, when a user runs a report, they are taken to the params.html page which is the page with the coding that prompts the user for any parameters using text boxes or drop-down menus.  The actual drop-down for Parameter 1 is created using the following code (similar coding appears for the other parameters):

<select name="[06]ParamVal1" id="Param1">~[x:insertfile;dropdowns/~([06]ParamOpt1).html]</select>

The x:insertfile command inserts a file from the dropdowns folder based on the value in the ParamOpt1 field. For example, with student in the ParamOpt1 field, the coding above translates to the following when the page loads:

<select name="[06]ParamVal1" id="Param1">~[x:insertfile;dropdowns/student.html]</select>

and more specifically inserts the contents of the student.html like so:

<select name="[06]ParamVal1" id="Param1">~[tlist_sql;SELECT id, lastfirst, grade_level FROM Students WHERE enroll_status = 0 and schoolid = ~(curschoolid) ORDER BY lastfirst]<option value="~(id)">~(lastfirst) (~(grade_level))[/tlist_sql]</select>

If someone chose the Active Courses menu to use with Parameter 1, then the following would end up being used:

<select name="[06]ParamVal1" id="Param1">~[x:insertfile;dropdowns/courses.html]</select>

By using the x:insertfile;dropdowns/~([06]ParamOpt1).html command, adding new menus is as easy as going through the three steps mentioned above.

 

  Back to the List of Articles