Recently in SIR Database Category

A glitch was reported to Paul and I this week with the most recent data added to the SIR Database for Winter 2007. The new reports, it seems, were not available for instructors to view online.

Paul and I spent much of the afternoon yesterday working on a solution. When the new data was added, the resulting COURSE_INFO and COURSE_INFO_PUBLIC tables were out of sync. We decided to add a new table to the application, PUBLIC_INSTR, that will be used when someone decides to make their reports available to the public. We had to make significant changes to our code to make this work properly, but we believe we are getting close. Now we just need to work with an instructor to test this [hopefully] improved functionality.

There were very few surveys completed during Winter Term; for this academic year most of the surveys were completed during Spring Term. We expect to receive these packets any day. It will be nice to have the above glitch solved as we prepare for this next update to the database.

Yesterday I put a new release of this application into production. When doing some testing of the new features, I discovered that some of the reports were, in fact, not working. Paul and I were able to quickly fix these bugs. We needed additional logic to handle multiple reports displayed on separate pages -- Paul developed this logic. Working through the code to fix the problems has helped me to gain a better understanding of this part of the application.

As a result, this morning I placed yet another upgrade of the application into production. The major changes to the reports include formatted text and CSV text versions of the Comparison Report. When testing things after the migration, everything seemed to work fine.

As a follow up to my last blog, we have corrected the problem with the display of the generic reports. In testing, everything now seems to work properly. Paul looked into this and had it fixed by Sunday evening.

Today I placed into production a new release of the SIR Application. The major enhancement to this version is a new format for the Comparison reports, allowing the data to be displayed in the form of a horizontal bar chart. I have worked on the development of this report for some time, as there were many details involved with the calculation of the results and formatting of the HTML to display the report.

Another small challenge was deciding how to display the bar charts for values which are fairly close in range. For such values, the bar charts would appear, visually at least, to be almost equal. I solved this problem by printing the data value, then multiplying it by the number 50 and generating the bar chart based on this new number. With this scheme, the differences in the data will be more visable.

Another important feature of this new release is the capability to generate multiple reports and have them display as a separate report for each course on a separate page. Paul developed this capability, and we worked together to incorporate this into our code.

Over the last several weeks, we have redesigned much of our code in order to accommodate the new features, including the capability to display the separate reports mentioned above. (I have documented much of this in recent blogs.) I discovered one casualty of these changes today after I placed the new version into production. It appears that our generic reports no longer work. These are the reports that do not require one to login to the application. We hope to have these reports working again sometime on Monday.

I hope to add the following features in the near future.

â–º Display Comparison Reports in Formatted Text and CSV formats.
â–º Save reports directly to a file.
â–º Send reports directly to a printer.
â–º For the Comparison Reports, provide a better breakdown of the data for Question 43.
â–º Display vertical bar charts.

Here is a link to the current production version of the SIR Application:

http://www.wou.edu/~goodeld/sir.htm

In my test environment, I now have a report that displays bar chart graphs that will allow for the comparison of data between an instructor, a department, division, college and WOU. With the numbers being so similar, however, the bar charts themselves also look much the same. I have made the slight differences in the graphical representation of the values appear visually by multiplying the value by 10 before generating the bar chart. With this scheme, the bar charts will be greater in length and the proportions will display better.

I still have some work to do with this part of the project, but I believe I am getting close to putting this report into production. Creating the bar chart graphs was fairly easy; the difficulty was taking care of the necessary calculations to get the data ready for graphing.

This week we have made the following improvements to the SIR Application. These changes will be put into production on Friday, June 2.

â—? Fixed a problem with the report titles.

â—? Provided more customization for row shading within the reports, including allowing the user to select their own color.

â—? For the email reports, we created an email address, siradmin@wou.edu, as the sender and as a contact.

â—? Added the capability to send reports via email to any email recipient.

â—? Added decimal precision for the Percentage and Comparison reports.

â—? Added the ability to save default preferences within the SIR application.

â—? Mass Faculty Evaluations. Paul has perfected this option. This allows application administrators to custom-filter the list of courses from which reports are generated.

â—? Optimized the Rebuild Course Table command. Paul has improved the functionality for this option to minimize application downtime from about 8 minutes to about 15 seconds.

â—? Added the Division Chairs as application administrators for their respective divisions.

� As a whole, we “cleaned up� the application, to put this version into production.

The items listed below will be completed in the near future.

â—? Bar Chart Report. In progress.

â—? Separate reports for each course.

� Option to reset a user’s report options back to the defaults.

This week Paul and I have each made significant changes to our respective parts of the SIR Application.

For the reports, I have completely changed the way that the data is output. Now, all report data is sent to the OUTPUT_REPORT table. From there, depending on the choice made by the user, the report is either displayed on the screen or sent to the user in the form of an email message. The idea of using this table will be useful when I develop the capability to save the reports to a file.

The re-design of the reports has helped Paul in his development of an Options window, which will allow the user to generate multiple reports, with each on a separate page. The Options window will also allow the user to toggle choices to display the course list, question text and report summaries.

I hope to make progress early next week in the display of the Comparison report data in the form of a bar chart graph. The re-design of the reports should make this much easier.

Comparison Report

I believe I have completed all of the "number-crunching" needed to generate the bar charts for the new comparison report. The comparison information, in columner format, is now working within my test environment. Next week I expect to complete much of the code to have the bar charts working.

Reorganization of Code

Paul and I met this morning to discuss the current logic flow of the application. I will be making significant changes to improve the efficiency of the reports in the next day or so. These improvements will allow my code to work better with code that Paul is developing to provide greater flexibility in generating reports for instructors who teach in multiple departments, divisions and colleges.

I am working on a new report that will allow instructors to compare their report statistics with the average data from the same academic year for their department, division, college and the University as a whole. I am nearly finished working through the raw numbers, including calculations and the generation of the report.

Next, I'll add the capability to generate bar charts to display the comparison data. I don't expect the generation of the graphs to be terribly complicated; in doing some quick research, I have found a couple of websites with good example HTML code that seems easy to follow.

As with other SIR reports that I have completed thus far, it seems that one of the most difficult tasks is that of "mining" the data contained within the Oracle tables. Once again, Paul Lambert has been an excellent resource. As he developed the first version of our SIR application (including the creation of most of the current tables) over a year ago, his knowledge is invaluable.

Today I put a new version of the SIR Application into production. This new release allows the user to send reports as an email message in HTML, Formatted Text or CSV Text formats. Paul Lambert also developed the capability to generate multiple reports, with one report per page, to allow more efficient bulk printing.

The next major update of this application will allow users to generate comparison reports, both in columner/numeric format and bar charts.

I have made significant progress the last day or so with the development of the capability to send SIR Reports via email. I have the functionality working, but need to do some "clean-up" of my code and the pages being displayed. I expect to put this into production within the next day or so. With this functionality, users will be able to email reports to themselves in three formats -- HTML, CSV text and formatted text.

One obstacle I had to overcome had to do with how the report was formatted within the actual email message. Sending the HTML tags and text resulted in the literal HTML tags and text being displayed, which wasn't very nice and was hard to read. After consulting with a couple of my colleagues and doing some research, I didn't have a definitive solution in mind to try to solve this issue. I decided, however, to see what would happen if I included this line

utl_smtp.write_data (mailconn, 'Content-Type: ' || 'text/html' || crlf);

within my code that sends the email message. This single line of code ended up solving the entire problem, as the resulting email message now properly formats the HTML and the report displays as intended.

It has taken some time to work out several technical details, but I believe I now have a report page in the proper format to allow the capability to send it as an email message.

To accomplish this, I'm inserting the page into an Oracle table as it is generated. Now that I have the capability to generate and properly save a dynamic page, I'll work on the code to actually send it within an email message. I already have working code to send email, so now I just need to incorporate this with the dynamically-generated page. Once I have the capability to send an HTML page, I should be able to quickly develop the code to send the other report formats within an email message.

Next I'll develop an interface to save the reports directly to files. Eventually, we will add functionality to the reports to combine percentages and to make comparisons with data from prior terms.

This week I completed a new SIR report option for Formatted Text. This is now part of the production version of the SIR Application. With this new reporting option, we now have three formats for the reports -- HTML, Text (CSV) and Formatted Text. I hope to make progress next week with an option to allow users to generate and send the reports via email.

This morning, after working out a few kinks, I put the latest development version of the SIR Application into production. The most significant change with this rollout is the report mentioned in my last blog entry -- the capability to display the answer totals for each survey question as either an aggregate (summary) or percentage value.

I have nearly finished with a new version of the Class Report within the SIR Application. For each survey question, the numeric value (total responses) for each possible answer is converted to a percentage. In production, users will be able to select this report format from the list of options. I expect to put this new report into production early next week.

This morning, I put a new version of the SIR Application into production, with the following features:

-- Capability to display the question text as part of the report. Users can toggle this feature on/off.
-- Clicking on the question number/text opens a new window with the text and the possible answers for that question.
-- Users can choose whether or not to display the list of classes taught and the report summaries as part of the report page.
-- Toggle on/off alternate row shading on the report page.

We have also imported the latest data into the tables.

Next week we'll develop alternative report formats, including email, formatted text, comma-separated values and SPSS-formatted text.

Paul and I met with Bill earlier this week to give a demonstration of the SIR Application, which is now in production. Bill had some good ideas for providing the capability to generate the reports in several formats, including sending as an email message, displaying in CSV and SPSS formats.

We also worked through several changes this week, as a result of feedback that Bill had received. On the report page, each question number contains an HTML TITLE tag that displays the question number and corresponding text when hovering with the mouse. Also, each question number is a link that, when clicked, calls a popup window containing the question number and text, as well as a listing of the possible answers for each question.

We will be working on more improvements to the SIR Application in the coming days.

Development of the SIR application is nearing completion. Paul Lambert and I have worked together to develop a friendly user interface and a way to very quickly display reports. Reports can be displayed for a specific class, for all of an instructor's classes (for which reports exist), for the department, division, college and for the entire University. We have even developed "admin" and "superadmin" pages for reporting and database maintenance purposes. Determination of admin or superadmin login status is handled using an Oracle table; if no entry for the logged-in user is found in the table, they are assumed to be faculty and are not taken to the admin or superadmin pages.

Through this project, I have learned a great deal about HTML and its use in displaying output from a PL/SQL application.

Paul and I have been working tha last few days on redesigning the reports generated by the SIR application. In my development schema thus far, logging in via LDAP generates a list of courses taught by an instructor. For testing purposes, I've added a few classes with myself as the instructor. From the list, I can select one or more classes from which to generate a report. Total values are shown when multiple classes are selected. I'm now working on formatting the report to be more readable (HTML), and we should be able to quickly develop other types of reports.

I now have my LDAP login and verification procedure working properly for the SIR application. Once authenticated through LDAP, my code checks a table to see if the user is a faculty member, campus administrator or dean, or a systems administrator and taken to an appropriate webpage. It may be some time before we take full advantage of this functionality, but I believe it will be useful in the future. My LDAP authentication also returns the user's V-Number.

I also created some test data in a new course info table that Paul created using the view that Michael Soukup gave us. This will allow me to authenticate through LDAP and generate test class reports. Next, I will develop a faculty page, which will display all courses taught by an instructor.

LDAP Authentication

I now have a basic LDAP authentication working within my user interface (currently running in a test environment) for the SIR Reports. I have configured the application to point to an HTTPS version of the login page, which seems to work well thus far. Michael Ellis was a great help with all of this.

I still need to do some work within the verify procedure that I am developing. When finished, users will login through LDAP and my application will distinguish between administrators (deans, depatrment heads, etc.) and "super-administrators" (myself and Paul Lambert at the moment). Administrators will be able to generate different kinds of reports than faculty; super-administrators will have greater capabilities to test and administer the application.

Additional Reporting Capabilities

Paul Lambert and I are working together to develop the capability to allow campus administrators to generate more reports. We will develop the capability to display a sum total of all responses to all survey questions for all courses within a department, division, college or the whole campus, for one or more years or terms. Yes, this is a very general goal. We have been working with Michael Soukup to develop a view to collect data from Banner, and we believe we now have access to most of the data that we will need for this project.

I will be meeting with Michael Ellis next week to get some training on how to implement LDAP authentication within the PL/SQL language. LDAP authentication will allow faculty to quickly view reports for their courses. I expect to have the LDAP authentication working in my test environment by mid-week. Paul Lambert and I met this morning to begin to design additional reports for the SIR application. These will be more general in nature, giving faculty, deans and administrators the capability to quickly view statistics for any division and college, for just about any given time period.

In my development environment, I now have the capability to display two reports in various formats. The two reports, Class Report and Class Report, Percentages, can be displayed in HTML, ASCII and CSV (comma-separated) formats.

The HTML-formatted report can be imported into Excel or Word. At this time, I can view reports through a web page as CSV, copy-paste into Notepad, save as a CSV file, and open with Excel or SPSS. With this scheme, the reports are displayed in a readable format. I need to improve the procedures for saving and viewing these various file formats.

I am making progress in the design of alternative report formats for the SIR application. I have a working link to display the Class Report in ASCII format, although I will need to make some minor changes. The design of the report in CSV format is going well thus far. Once I have these report formats completed, the design of the Percentage reports should be straightforward. With this scheme, users will view the various report formats within the browser and save the report using the browser.

I am making good progress with the changes to the SIR application. In my development schema, I now have the capability to display a list of courses for an instructor name. The instructor name is currently hard-coded, but I will soon develop the capability for users to enter this information through the use of a form.

After a course is selected from the listing, two reports can be displayed. The current working names for these are Canned SIR Report and Canned SIR Report Percent. I will eventually develop the capability for instructors to save these reports in various formats, including Excel, SPSS and ASCII.

When finished, we will offer the capability for an instructor to login to the SIR application via LDAP, select the term from which they wish to view reports, select from the list of courses taught for that term, and select the report type and format.

Paul Lambert and I worked through the process of scanning student surveys from fourteen Summer Term 2005 courses and importing the raw data into the SIR database. From start to finish, the steps are somewhat involved. Paul has automated several of the steps for importing the data, including the use of a Perl script.

In my development environment, I have implemented a simple HTML form to allow instructors to enter a CRN and term in order to generate SIR II reports on-the-fly. I still have details to fine-tune, including error-trapping, before I am ready to put this into production.

The book I have been reading, Learning Oracle PL/SQL, proved to be a great reference as I developed the form; the text contains an excellent example.

The current application consists of several Oracle tables and PL/SQL packages. The table containing the raw data for each student response, to each survey question, from each course, contains nearly 200,000 rows.

Major goals for this application are to allow instructors to

- View statistics whenever they wish, through the use of a web-based form.

- Generate summary reports to view as a web page, or gather data in a raw (summary) format for use with Excel, SAS or SPSS.

- Customize reports by selecting sections or questions of particular interest.