*Please note that the values found in here are hockey player names and the phone number is the Detroit Redwings Ticket Line and do not reflect the opinons of the people or organizations with which they are involved. The values were used as a test data set to produce valid results.
Initially this module’s post for the Technology Skills Boot Camp (Tools of the Trade) series was going to cover Livebinders but a question posed to me at work presented a new topic. It all started with a form that we created in Google Docs to collect inquiries from people interested in starting a charter school. The entries are stored in a spreadsheet and the question posed was can we create a report with the information in the spreadsheet and oh yea…can we set a start and finish date? The skills learned in the original task were also able to be implemented into a spreadsheet that I have on charter school eRate information and technology plan statuses. I will be the first to admit that there is probably a better way to do this and this is by no means the best method to use in a production setting. How ever with that being said, my goal here is to create a working report that will be used by a few people.
Task:
Create a user friendly form that generates a quick report of entries.
Create a user friendly form that generates a running report based on date ranges.
The running report only needs to have certain fields from the whole spreadsheet
On the surface it appears simple and would appear it wouldn’t take to long for someone to piece this together…right? It took me a total of four days to figure out how to make this work and there were plenty of learning moments along the way.
The reports were all created using two different formulas as the basis for extracting the information from the established spreadsheet. A formula is an entry in a spreadsheet cell that will generate a value based on the functions found within the formula. In the case of our reports the following two formulas were used:
The links above will take you to the Google Docs Help Section entry for each of the function. In this case, I am using the INDIRECT function to reference the value based on a record level found in a different sheet. The QUERY function allowed me to use the SQL programming syntax that I am familiar with to be select in the fields and values I selected.
In creating the running report, I used the INDIRECT function which I learned about through Eric Curts and the Google Apps User Group…so THANK YOU! Also a shout out goes to Shawn F. who aided me in this challenge. The INDIRECT function is pretty cool in the fact that it can be used as a placeholder value. In the report the user enters the record value in the spreadsheet to generate the value. In this example we are using the record number 8 that the user entered in column B3. Here is the statement that is used to generate the value for the report: =INDIRECT(“responses!A”&B$3)
In this case, INDIRECT is the function and the information within the parentheses is being pulled from a spreadsheet called responses and the value found within column A of the 8th record (value entered in B3).
Now on to the more challenging part of the tasks…giving the user the ability to set a start/end date to generate a report. Simple right?
The first step I took was to create a new sheet within the spreadsheet document. This was done in order to create a separate environment where no changes to the data can occur because the values are being pulled from a different spreadsheet.
I assigned the name querytable to the sheet and then entered the following string into cell A1: =QUERY(‘responses’!A2:N101,!A2:N101). The = sign is an instruction to the spreadsheet that this is going to be a formula. The word QUERY identifies the function that the formula will be using. The items found within the parentheses is where the values for query table will be populated. techplaninfo identifies another spreadsheet within the document, the exclamation point tells the program that it should expect a range value. A2:N101 are the values found in the cells of the techplaninfo spreadsheet. If I only wanted to select certain fields within the range (A2:N101) I could build the query statement as follows: =query(‘responses’!A2:N101, “SELECT B,C,G,D,F,I,J”) and the end result would be a querytable populated only with the values found in columns A,B,C,G,D,F,I,J in that order. At this point further refining of the data could occur using the commands found on this website.
Now that the querytable has data, we can proceed to start building the report. The next challenge I had was establishing a way for the user to specify a start and end date for the report. When the form is submitted a time/date stamp in this format 2/29/2012 21:04:33 is created as an entry in the spreadsheet. In this instance I could care less about the time and more about the date so I created a new column and used =DATEVALUE(A2) as the value. This takes the value in A2 and formats it into a date value minus the time stamp. During my initial testing the date format was giving me error messages so I had to think outside of the box. I then set the entire column to be formatted as a normal number. This takes the date value and formats it into a number that takes the date and enters the number of days since January 1st 1900. Example: November 21st 2011 is 40,868.
At this point the next challenge is presented and I am left scratching my head. The user will not know how to use the DATEVALUE function or that November 21st 2011 is the 40,868 day from January 1st 1900. I left the start and end dates as date values that the user can enter as 11/21/11 and be unaware of the trickery taking place behind the scenes. In my querytable I created two new columns used the QUERY function to pull the values found in the start/end date columns on the report. I then used the DATEVALUE and established the numeric value.
The following query was used to select the required values in the report and to generate only those results that were within the given date range stated by the user.
=query(‘querytable’!A1:I100,”SELECT A,B,C, D, E, F, G where A >= H and A <= I”)
In my querytable column A (A) is the date value that is assigned to the entry when the user submits the form. Columns H and I (H,I) are the date values entered by the user on the report form. Remember that the date values entered were converted to a numeric value through the DATEVALUE function and this is where those converted values are stored unbeknown to the end user. The word WHERE tells that program that a conditional setting will be coming up. The word AND tells the program that both conditions stated must be matched in order for a valid entry to be generated. The symbols >= and <= are two operators that are used to compare the values in the entire table. The >= means that the value found in column A must be grater than or equal to the value found in H AND the value found in column A must be less than or equal to the value found in column I.
At this point we have completed all of the goals we stated at the start of the project. The user only needs to know the start/end dates that they want for the report and how to print the report if desired. As always the goal is to provide the end user with what they need and do it as simply as possible from the end users perspective. While it appears simple to the end user, the actions taking place in the background are more than simple. This task further illustrates that the position of technology coordinator within a school district is much more than just knowing one side of the job.
