<% on error resume next if request.querystring("action") = "" then 'the first time they have been there%>

Inventory Reporting

This report tracks class inventory and profit organized by series or by class with a start and end date. Each of the two reports have sub options. For a "by series" report, be sure to enter a start and end date. For a "by class" report, be sure to choose how you would like the report sorted

Display Grouped By Series (if choosing this option fill in both dates below)
      Start Date (Like 01/01/2002)

      End Date (Like 06/30/2002)

Display Grouped By Class (if choosing this option also choose one option below)
     Order by Class Name
     Order by Date
     Order by Capacity
     Order by Enrolled
     Order by %Sold
     Order by Maximum Revenue
     Order by Current Revenue
     Order by Difference of Max. Revenue and Revenue

     
<%else call openconn ("nothingtoit_7051") if request.form("bywhat") = "bySeries" then 'start with a table... %>

Inventory Report by Series from <%=request.form("startdate")%> and <%=request.form("enddate")%>

TOTAL CAP is the total number of persons that could possibly register for any one series

CUR ENR is current enrollment over the whole series (counted in persons)

ENR PCT is the percentage of spots registered for in a series

POS REV is the total possible revenue earned if every class in the series was completely sold out

CUR REV is the current revenue for each series

DIF is the difference between what is currently sold and what the total possible sellout revenue would be

<% '...now we need to loop through every one of the series... SQL = "SELECT class_series_id, class_series FROM class_series_tbl" call openRS(SQL) seriesArray = rs.getrows for each trow in seriesarray rowcount = rowcount + 1 rs.movenext next call closeRS(SQL) rowvar=0 'this alternates the cell colors for x = 0 to ((rowcount/2)-1) class_capacity = 0 class_enrollment = 0 sellout_value = 0 enrollment_value = 0 difference_value = 0 percent_enrollment = 0 SQL = "SELECT " SQL = SQL & "[class_datetime_tbl].[class_capacity], [class_datetime_tbl].[class_enrolled], [class_tbl].[class_price]" SQL = SQL & "FROM (class_series_tbl INNER JOIN class_tbl ON [class_series_tbl].[class_series_id]=[class_tbl].[class_series_id]) " SQL = SQL & "INNER JOIN class_datetime_tbl ON [class_tbl].[class_id]=[class_datetime_tbl].[class_id] " SQL = SQL & "WHERE class_tbl.class_series_id="& (x+1) &" and ([class_datetime_tbl].[class_datetime] BETWEEN #"&request.form("startdate")&"# AND #"&request.form("enddate")&"#);" call openRS2(SQL) 'now we have to do some math on the VB side to get everything correct... '...to do that we need to loop and count while not rs2.eof class_capacity = class_capacity + rs2("class_capacity") class_enrollment = class_enrollment + rs2("class_enrolled") sellout_value = class_capacity * rs2("class_price") enrollment_value = class_enrollment * rs2("class_price") difference_value = sellout_value - enrollment_value rs2.movenext wend percent_enrollment = class_enrollment/class_capacity 'now write each row... if rowVar = 0 then bgvar = "lightrow" rowvar = 1 else bgvar = "darkrow" rowvar = 0 end if response.write "" response.write "" response.write "" response.write "" response.write "" response.write "" response.write "" response.write "" response.write "" total_class_capacity = total_class_capacity + class_capacity total_class_enrollment = total_class_enrollment + class_enrollment total_sellout_value = total_sellout_value + sellout_value total_enrollment_value = total_enrollment_value + enrollment_value call closeRS2 next %> <% response.write "
SERIES TOTAL CAP CUR ENR ENR PCT POS REV CUR REV DIF
"&seriesArray(1,x)&" "&class_capacity&""&class_enrollment&""&FORMATPERCENT(percent_enrollment,1)&""&FORMATCURRENCY(sellout_value,0,TRUE,TRUE,TRUE)&""&FORMATCURRENCY(enrollment_value,0,TRUE,TRUE,TRUE)&""&FORMATCURRENCY(difference_value,0, TRUE,TRUE,TRUE)&"
  <%=total_class_capacity%> <%=total_class_enrollment%> <%=FORMATPERCENT(total_class_enrollment/total_class_capacity,1)%> <%=FORMATCURRENCY(total_sellout_value,0,TRUE,TRUE,TRUE)%> <%=FORMATCURRENCY(total_enrollment_value,0,TRUE,TRUE,TRUE)%> <%=FORMATCURRENCY(total_sellout_value-total_enrollment_value,0,TRUE,TRUE,TRUE)%>
" else 'display report order by class %>

Inventory Report by Class

If you need to resort the report by any category, click 'back' and re-generate the report after choosing the appropriate radio button

CAP is the set capacity of the class

ENR is the number of people enrolled

%SOLD is the percentage of enrolled in that class

MAX REV is the maximum revenue generated by the class if it were sold out

REV is the revenue the class is currently generating

DIF is the difference between the maximum revenue and the current revenue

<% 'Here is the root of the SQL statement withour the orderby clause... SQL = "SELECT [class_datetime_tbl].[class_datetime] AS DATETIMES, " SQL = SQL & "[class_datetime_tbl].[class_capacity], [class_datetime_tbl].[class_enrolled], " SQL = SQL & "[class_datetime_tbl].[class_enrolled]/([class_datetime_tbl].[class_capacity]) AS sold," SQL = SQL & "[class_tbl].[class_price] * [class_datetime_tbl].[class_capacity] AS maxrev," SQL = SQL & "[class_tbl].[class_price] * [class_datetime_tbl].[class_enrolled] AS rev," SQL = SQL & "rev - maxrev AS dif," SQL = SQL & "[class_tbl].[class_name], [class_tbl].[class_price]" SQL = SQL & "FROM class_tbl INNER JOIN class_datetime_tbl ON [class_tbl].[class_id]" SQL = SQL & " =[class_datetime_tbl].[class_id] " 'now add the order by clause... SELECT CASE UCASE(request.form("orderby")) Case "MAXREV" SQL = SQL & "ORDER BY ([class_tbl].[class_price] * [class_datetime_tbl].[class_capacity]);" CASE "REV" SQL = SQL & "ORDER BY ([class_tbl].[class_price] * [class_datetime_tbl].[class_enrolled]);" CASE "DIF" SQL = SQL & "ORDER BY (([class_tbl].[class_price] * [class_datetime_tbl].[class_enrolled])-([class_tbl].[class_price] * [class_datetime_tbl].[class_capacity]));" CASE "SOLD" SQL = SQL & "ORDER BY ([class_datetime_tbl].[class_enrolled]/[class_datetime_tbl].[class_capacity]);" CASE "NAME" SQL = SQL & "ORDER BY [class_tbl].[class_name]" CASE "DATE" SQL = SQL & "ORDER BY [class_datetime_tbl].[class_datetime];" CASE "CAP" SQL = SQL & "ORDER BY [class_datetime_tbl].[class_capacity];" CASE "ENR" SQL = SQL & "ORDER BY ([class_datetime_tbl].[class_enrolled]);" END SELECT rowvar = 0 call openRS(SQL) While not RS.EOF if rowVar = 0 then bgvar = "lightrow" rowvar = 1 else bgvar = "darkrow" rowvar = 0 end if response.write "" response.write "" response.write "" response.write "" response.write "" response.write "" response.write "" response.write "" response.write "" response.write "" rs.movenext Wend call closeRS response.write "
CLASS DATE & TIME CAP ENR %SOLD MAX REV REV DIF
"&rs("class_name")&""&rs("datetimes")&""&rs("class_capacity")&""&rs("class_enrolled")&""&formatPercent(rs("sold"),1)&""&formatcurrency(rs("maxrev"),0,TRUE,TRUE,TRUE)&""&formatcurrency(rs("rev"),0,TRUE,TRUE,TRUE)&""&formatcurrency(rs("dif"),0,TRUE,TRUE,TRUE)&"
" end if call cleanup end if %>