Simple Reusable Tabular Reports
The simplest kind of report we can build will just list data from our log tables. However, as you saw in the previous chapter, the base tables that collect the data from our site are not suitable for querying efficiently. Instead, we use the special 'summary' tables that we update every week from the main log files. The tables we have available are shown in the next diagram:
The fact that we have several different tables, combined with the requirement to sort and summarize the information in various ways, means that there are a lot of different combinations. We could provide a different page for each report that was required, but this means a lot of work. There's also the possibility that, even then, we won’t cover all the requirements. Instead, as you've seen earlier, we chose to build a reusable report that will work with any of the tables. By providing suitable parameters when we load it, we can display selected data from the chosen table.
The showlog.asp File
showlog.asp accepts several parameters in the query string, which change the way it behaves. The parameters are:
query - the type of report; i.e. the table to use, and the way to sort and summarize it
- site - the individual site to show results for, or all sites (our server hosts three)
- criteria - an extra
WHEREclause to select and limit the values returned
- showsql - a flag value that causes the compete SQL string to be displayed as well
As you saw in the previous section, the traffic reports menu page provides the first two of these values in the link that references our showlog.asp page. The third and fourth values are provided by a separate page. We'll look at the main
showlog.asp page first, and then see where the other two values come from afterwards.
The sample files for this book also include a line of code to implement the admin security technique we saw in Chapter 5, redirecting the user to a login page if they haven't provided a suitable username and password. We've omitted these from the printed code here to avoid unnecessary complication.
The first part of the page sets the script timeout value, inserts the connection details for our IISLogs database, and defines a couple of values for use later in the page. It also collects the value from the ASP QueryString and Form collections for the first three of our parameters:
<%@ LANGUAGE=VBSCRIPT %> <% Server.ScriptTimeOut = 600 'the report may take some time to run %> <!-- #include virtual="/common/iislog.inc" --> <% QUOT = Chr(34) CRLF = Chr(13) & Chr(10) strQuery = Request.QueryString("query") 'the name of the query we're going to run strSite = Request.QueryString("site") 'the site(s) that we want to include strCriteria = Request.Form("criteria") 'the criteria for the report ...
Selecting the Correct Site(s)
We use the
site parameter to specify a query name that indicates the IP address of the site we're interested in. All the tables except for
WeekSummary (which presents information for all sites) include the IP address in each record, so we can pull out the appropriate ones for our report. Because there are two different field names for these fields in the tables (yes, we'll admit it, it was an oversight during the original design), we create two different
WHERE clauses in each case. If the report will include all sites, however, we can leave the string empty:
... Select Case strSite Case "all" strWhere = "" strSessW = "" Case "wd" 'Web-Developer site only strWhere = " WHERE TSiteIP='18.104.22.168'" strSessW = " WHERE HostIP='22.214.171.124'" Case "cd" 'COMDeveloper site only strWhere = " WHERE TSiteIP='126.96.36.199'" strSessW = " WHERE HostIP='188.8.131.52'" Case "wa" 'World Of ATL site only strWhere = " WHERE TSiteIP='184.108.40.206'" strSessW = " WHERE HostIP='220.127.116.11'" End Select If Len(strCriteria) > 1 Then 'add the optional criteria to the WHERE clause If Len(strWhere) Then strWhere = strWhere & " AND " & strCriteria Else strWhere = " WHERE " & strCriteria End If End If ...
At the end of the previous section of code you can see how we add on to the end of the WHERE clause (or create a new one if the report covers all sites) the value of the criteria
parameter sent to this page. So, where does this value come from? The answer is in the next part of the code.
Selecting the Correct SQL Statement
Given the site
query parameter value, we can set up the required SQL statement that will extract the data from our table, and summarize and sort it in the required order. Then we just need to add on the
WHERE clause we created in the previous section of code.
However, things are made more complex because, as well as listing the individual results of the query, we want to provide a summary of the totals in some of the reports as well. This means that we will need two SQL statements in these cases—
strSQL is the statement that creates a subset of records for the main listing, and
strSum is the statement that creates a single summary record from the same subset of records:
... Select Case strQuery 'the type of query we want Case "weeks" 'the special weekly summary report strSum = "" 'no overall summary required strSQL = "SELECT * FROM WeekSummary ORDER BY TYearNumber DESC, TWeekNumber DESC" Case "hits" 'traffic volume sorted by number of hits If Len(strCriteria) < 1 Then 'we need to collect a criteria string Response.Redirect "getcriteria.asp?query=" & strQuery & "&site=" & strSite End If strSum = "SELECT start=MIN(TSumDate), finish=MAX(TSumDate), " _ & "TotalHits=SUM(THitCount), TotalKBytes=SUM(TKBytes) " _ & "FROM DaySummary" & strWhere strSQL = "SELECT HostIP=MAX(TSiteIP), TargetURL=MAX(TTarget), " _ & "Hits=SUM(THitCount), KBytes=SUM(TKBytes) FROM DaySummary" _ & strWhere & " GROUP BY TTarget ORDER BY SUM(THitCount) DESC" Case "volume" 'traffic volume sorted by number of Kbytes If Len(strCriteria) < 1 Then Response.Redirect "getcriteria.asp?query=" & strQuery & "&site=" & strSite End If strSum = "SELECT start=MIN(TSumDate), finish=MAX(TSumDate), " _ & "TotalHits=SUM(THitCount), TotalKBytes=SUM(TKBytes) " _ & "FROM DaySummary" & strWhere strSQL = "SELECT HostIP=MAX(TSiteIP), TargetURL=MAX(TTarget), " _ & "Hits=SUM(THitCount), KBytes=SUM(TKBytes) FROM DaySummary" _ & strWhere & " GROUP BY TTarget ORDER BY SUM(TKBytes) DESC" ... ... 'repeated for other query types ... Case "lang" 'listing by browser language/country code strSum = "" 'no overall summary required strSQL = "SELECT Hits=SUM(ItemCount), Language=MAX(ItemText) " _ & "FROM CountrySummary" & strSessW & " GROUP BY ItemText " _ & "ORDER BY SUM(ItemCount) DESC" End Select %> ...
In the code above, you can see the various SQL statements that select, summarize (
GROUP) and sort (
ORDER) the data into recordsets ready for listing. However, notice how, for the
volume queries, we demand a value for the
criteria parameter. These (and some other) listings are huge, because they include all the pages on our site. We try to persuade the user to limit the records that will be included in the final report by redirecting them to another page that will create a value for the
We'll look at this criteria page,
getcriteria.asp, in more detail later on. For the meantime, you just need to know that it does two things. Firstly, it creates a SQL
WHERE clause string that further limits the records that will be included in the result recordset, but without the
'WHERE' keyword at the start of the string. It then adds this parameter onto the original query string that we send to the page. Secondly, it can add the parameter
showsql=on to the query string as well. The getcriteria.asp page then loads our
showlog.asp page again, sending back the query string with its new additions.
Displaying the Results
We're now ready to send something back to the user from our
showlog.asp page. We output the HTML for the start of the page, then check to see if the
showsql parameter was supplied. If it was, we print the SQL statements into the page. Then we can get on and open our database connection ready for some real work:
... <html> <head><title>Results of your traffic query</title></head> <body BGCOLOR="#FFFFFF"> The results of your query:<p> <% '--show the contents of the two SQL strings, useful when debugging-- If Request("showsql") = "on" Then Response.Write strSum & "<P>" Response.Write strSQL & "<HR>" End If On Error Resume Next Set oConn = Server.CreateObject("ADODB.Connection") oConn.Open strConnect 'open the database connection ...
Showing the Overall Summary Results
Some of the reports, as we saw earlier, also display a summary of all the records included in the listing. In this case, the variable
strSum will hold a suitable SQL statement to generate these. We execute the statement to create a recordset, which will have just one record in it. Using these values, we can calculate and display the summary information:
... If Len(strSum) Then 'this query provides a summary for the period Set oRs = oConn.Execute(strSum) 'this is the first SQL query declared earlier If (Not oRs.EOF) And (Err.Number = 0) Then datStart = CDate(oRs("start")) 'date of the earliest record datEnd = CDate(oRs("finish")) 'date of the latest record intDays = DateDiff("d", datStart, datEnd) + 1 'period covered in days lngTotalHits = oRs("TotalHits") 'total number of hits lngTotalKBytes = oRs("TotalKBytes") 'total number of bytes %> For the period <B><% = datStart %></B> to <B><% = datEnd %></B>, a total of <B><% = intDays %></B> days.<P> Total number of hits: <B><% = lngTotalHits %></B><BR> Total traffic volume: <B><% = lngTotalKBytes %></B> KBytes<BR> Hits per day: <B><% = CLng(lngTotalHits / intDays) %></B><BR> Traffic volume per day: <B><% = CLng(lngTotalKBytes /intDays) %></B> KBytes<P> <% End If End If ...
Here's how it looks in the browser. In this case, we chose to display the SQL statements as well, so that you can see what they look like. We also chose to include all sites, and we didn't specify any extra criteria for the query:
It would be possible to create the summary using ADO with the recordset that creates the main listing (from the SQL statement in strSQL), rather than by executing a separate SQL statement
strSum as we have here. We could count and total the values as we listed the records, then calculate the daily summary as shown above. However, this would place the summary at the end of the listing rather than at the start (unless we used some browser-dependent trick to create page divisions). Otherwise, to get it at the start of the page would mean looping through the records twice. Either way, this is likely to be a lot less efficient than letting our database do the work.
Checking for Errors and No Records
It's now time to execute the main query that creates the recordset for our results listing. The
showlog.asp page contains an
On Error Resume Next statement near the beginning, so if the previous SQL query failed, or returned no records, the user will just see zeros or nothing at all in the page. When we come to do the listing, it would be nice to tell them if something went wrong.
So, after we execute the main query, we check to see if we got any records, and if there was an error. In both cases, we display some helpful text. We also add instructions on how to abort the listing if it seems to be taking too long, and include a link back to the reports menu:
... Set oRs = oConn.Execute(strSQL) If Err.Number > 0 Then Response.Write "<FONT FACE=" & QUOT & "Arial" & QUOT & " SIZE=3>" _ & "<B>Sorry, the database cannot be accessed.</B></FONT></BODY></HTML>" Response.End End If If oRs.EOF Then Response.Write "<FONT FACE=" & QUOT & "Arial" & QUOT & " SIZE=3>" _ & "<B>Your query returned no records.</B></FONT></BODY></HTML>" Response.End End If %> Hit '<B>Stop</B>' to see a partial listing...<BR> Return to <B><A HREF="trafficreports.asp">Query Menu</A> page.<P> <% ...
At this point, unless there has been an error or we got no records from the main query, we're ready to list the results. Like most ASP/ADO operations, we just have to loop through the recordset and output the values. But before we do that, we need to think about what our recordset contains. We could have run a query against any of eight different tables, so it's going to be kind of hard to guess what the column headings need to be.
The Special Weekly Summary Report
In fact, we've got one particular report (the weekly summary of hits and sessions for all sites) that needs some special formatting of its own, so we will treat this as a special case. The rest are generic reports that broadly follow the same format. For the special weekly report, we have a separate section of code:
... If strQuery = "weeks" Then 'this is the special weekly summary report %> <table> <tr> <th> </th> <th align="center" colspan=3 nowrap> WebDeveloper </th> <th align="center" colspan=3 nowrap> COMDeveloper </th> <th align="center" colspan=3 nowrap> WorldOfATL </th> </tr> <tr> <th nowrap>Week </th> <th align="center" nowrap> Hits </th> <th align="center" nowrap>KBytes </th> <th align="center" nowrap>Sessions </th> <th align="center" nowrap> Hits </th> <th align="center" nowrap>KBytes </th> <th align="center" nowrap>Sessions </th> <th align="center" nowrap> Hits </th> <th align="center" nowrap>KBytes </th> <th align="center" nowrap>Sessions </th> </tr> <% Do While Not oRs.EOF %> <tr> <td align="right" nowrap align=right><% = oRs("TWeekNumber") %> : <% = oRs("TYearNumber") %> </td> <td align="center" nowrap> <% = oRs("WDHitCount") %> </td> <td align="center" nowrap><b><% = oRs("WDKBytes") %></B> </td> <td align="center" nowrap><b><% = oRs("WDSessions") %></B> </td> <td align="center" nowrap> <% = oRs("CDHitCount") %> </td> <td align="center" nowrap><b><% = oRs("CDKBytes") %></B> </td> <td align="center" nowrap><b><% = oRs("CDSessions") %></B> </td> <td align="center" nowrap> <% = oRs("WAHitCount") %> </td> <td align="center" nowrap><b><% = oRs("WAKBytes") %></B> </td> <td align="center" nowrap><b><% = oRs("WASessions") %></B> </td> </tr> <% oRs.MoveNext Loop ...
This produces the report you see below, with the three sites neatly divided into sections in the table. This report (as you can confirm from the code earlier in this chapter) doesn’t have a separate overall summary section:
The Generic Summary Reports
If the report we're producing is not the special weekly summary, we know we will have some recordset containing results to be listed. However, we don't know what column headings to use, or what the data actually represents. As it turns out, we don’t need to know either of these things.
When we created the SQL statement to extract the main listing results, we gave the fields in the recordset specific names—based on which table we were extracting them from. For example, the
CountrySummary table has fields named
ItemText, but our SQL statement renames these as
SELECT Hits=SUM(ItemCount), Language=MAX(ItemText) FROM CountrySummary ... etc.
The main limitation is that we can't include spaces and some other non-alphabetic characters. The characters that are legal in a field name depend on the database system you are running.
The syntax we use in the code listings for assigning a name to a calculated field, such as
start=MIN(TSumDate), works in SQL Server and should work in the same way for most other enterprise-level database systems. If you are using Access or another desktop database system, however, you will need to change the statement. Access uses the format
calc_field AS fieldname, for example
MIN(TSumDate) AS start. (In fact, SQL Server also supports this format, but other systems might not). Alternatively, you might prefer to create the SQL queries as stored procedures or Access Queries, rather than sending them to the database as SQL strings.
Using ADO, and the VBScript
For..Each syntax for iterating through a collection, we can iterate through the recordset's collection of field names to create the column headings, and then again to get their values:
... Else 'this is the generic 'any other table' report Response.Write "<table><tr>" & CRLF For Each objItem In oRs.Fields Response.Write "<th nowrap align=" & QUOT & "left" & QUOT _ & "> " & objItem.Name & " </th>" & CRLF Next Response.Write "</tr>" & CRLF Do While Not oRs.EOF Response.Write "<tr>" & CRLF For Each objItem In oRs.Fields Response.Write "<td nowrap align=" & QUOT & "left" & QUOT _ & "> " & objItem.value & " </td>" & CRLF Next Response.Write "</tr>" & CRLF oRs.MoveNext Loop End If Set oRs = Nothing Set oConn = Nothing %> </table> </body> </html>
That's it. This section of code can cope with any recordset, and will automatically extract the field names. It's a useful trick in many situations where you want to display the contents of some arbitrary recordset. Here’s the result for a query on our