Intro

This tool was created as part of my Capstone Project back in 2016. It was design to connect to any view in a MS SQL Server database an allow you to run queries from it.
It offers different visualizations options using the google visualization API, including pie charts and a heat map.
Since this webserver runs on MySQL, I had to make a few modifications, as well as, generate fake data to work with it.
Feel free to test it out and get some sample code from it!

The original project included repurposing old hardware (I got a couple from good will and put the good parts together), installing the operating system (Ubuntu), setting up a web server (Apache with PHP), connecting to an existing database (MS SQL from the university) and displaying meaningful data (What you see here - jQuery, PHP and SQL).

How to use it

Admissions

The admissions page was designed to show the user what’s the best of the item selected. For example, the best source will be the source that brought most students to UIU, or the best state will show the state where most students come from.

  1. The first item is the main query, simply choose a category to find out who’s the best at it.
  2. Choose a range of years the data is referring to.
  3. Choose the stage code from the dropdown.
  4. Limit the data for specific major.
  5. Choose an output method. The program outputs tables, pie charts, bar charts and heatmaps. Heatmaps require State or Zip as the main Query (The first dropdown).

Hit Submit and the output will be displayed above.

Alumni

Much like Admissions the Alumni page returns who’s the best of, however, the best is chosen given on the sum of Giving Capacity. For example, state would return the state with the most cumulative giving capacity, or first name would return the name most used by people with big donating capacities.

  1. Choose the main header you want to know that has the most giving capacity.
  2. Select a range in giving capacity, so only results within that range will display;
  3. Choose a range of the rank of the donors considered in your query.
  4. Choose an output method. The program outputs tables, pie charts, bar charts and heatmaps. Heatmaps require State or Zip as the main Query.

Hit Submit and the output will be displayed above.

Master Query

The master query was designed for more experienced users. It allows one to query anything from the view, and drill down into the data acquired from the previous interfaces. One could get ahold of the name and email of donors from a select state, for example, or filter the queries by other fields not available in the regular queries. It is basically a user interface to build SQL statements.

  1. Choose what department you are in.
  2. These are the header that will be returned to you, you can press + to increase the number of queries .
  3. The output also includes export, which outputs a version that can be copied.
  4. Filters, there can be as many filters as you want. Once the header is selected, options will appear for that header. If numeric or date, please select a range, else select a matching value.
  5. Group data, required to calculate the sum or count. Group the main header of your query if sum or count is used.
  6. Order, order results based on the field selected.
  7. Limit the number of records to be output or taken in consideration for the query.
  8. Click Query and see the results

Idea Behind It

The idea was to build a simple tool to be used by stakeholders to query their own data. The biggest impact it had was the usage of the Heatmaps, between the other visualization optons provided. The source code and hardware was provided to the respective stakeholders in the university, but I don't belive it was put into production.

Presentation

Research