I'm writing a utility that reports on data stored to text log files. The text log files are created by a syslog daemon that records outgoing network traffic requests from various workstations on an internal LAN to the Internet.

One particular site I'm working with generates between 10,000 and 20,000 requests per day so the data need to be summarized :) In the logs the four main columns of interest, and the desired summarization are:

WS_IP (Workstation IP Address e.g. 192.168.0.25)
  PortProt (Port/Protocol e.g. 80(TCP)
    PDomain (Primary Domain e.g. microsoft.com)
      FDomain (Full Domain e.g. msdn.microsoft.com)

I'd like to report request counts per summary level. With the report writer one very easy way to do this is by putting a calculated field in each group footer. To put some actual numbers to an example, you end up with something like this:

<br>
                                                   Requests
Workstation 192.168.0.2
  Port 80(TCP)
    microsoft.com
      msdn.microsoft.com                                 12
      office.microsoft.com                               27
    ----------------------
    Total Requests, microsoft.com                        39
    ----------------------
    yahoo.com
      movies.yahoo.com                                    5
      rd1.yahoo.com                                      16
    ----------------------
    Total Requests, yahoo.com                            21
  ----------------------
  Total Requests, Port 80(TCP)                           60
  ----------------------
  Port 110(TCP)
    somedomain.com
      pop3.somedomain.com                                47
    ----------------------
    Total Requests, somedomain.com                       47
  ----------------------
  Total Requests, Port 110(TCP)                          47
--------------------------------
Total Requests, Workstation 192.168.0.2                 107

This approach has the great benefits of being easy to create in the Report Writer, and easy to create a cursor that can drive the report. However, I find there are the following drawbacks:

- The start of a given section (e.g. Port 80(TCP)) and its summarization line can be many pages apart, and hard to find, requiring flipping back and forth in a printed report or on-screen preview
- A lot of separator lines typically need to be used to visually separate group footers from subsequent group headers. While (fortunately) this doesn't waste much vertical space with modern Windows-style reports it's still a hassle managing them properly
- This style of report can waste a lot of space when grouping is simple e.g. with the Port 110 section in the above example

Bearing the above in mind, what I'd like to do for this utility is eliminate group footers, pre-summarize request counts and report them in the group headers instead (a more "treeview-like" report). The resulting report would look like this:

Workstation 192.168.0.2 (107)
  Port 80(TCP) (60)
    microsoft.com (39)
      msdn.microsoft.com (12)
      office.microsoft.com (27)
    yahoo.com (21)
      movies.yahoo.com (5)
      rd1.yahoo.com (16)
  Port 110(TCP) (47)
    somedomain.com (47)
      pop3.somedomain.com (47)

My question: is it possible to write a single SELECT - SQL to group the data and pre-summarize as required for the above? I'd like to know, because if so it would probably be easier to extend a single SELECT to more grouping levels than to brute-force it with extra SELECTs after the initial GROUPing.

While it would probably be most elegant to do this in the report-driving cursor, I'm also open to any approaches anyone can think of that can use the report writer (90 or 80), including report variables if they would be helpful. The "holy grail" would be an approach that can programmatically adapt to varying numbers of grouping levels.