Witango-Talk: Help with report?

Dan Stein Tue, 17 Sep 2002 06:36:57 -0700

I am trying to output a break even report. It is a list of courses and the
count of registrants.
Two tables are being searched in one action
Basket and courses
There is a one to many relationship between courses and basket.


I have no trouble pulling back the result set and then filtering it into a
local array local$revenue so I know have two arrays one with all the data
and a filtered local that is only courses actually paid for.

I have a Sum Calc that will total the fee's collected from the 11th column
of the filtered array.

What I don't know how to do is to either create another array within the
loop that contains fees for the current course in the rows loop or do
somehow do a loop within the rows so I can sum the values for that course.

It looks like I should be using a foe loop but I can't seems to get it to
work

Results page is attached.

-- 
Dan Stein
Digital Software Solutions
799 Evergreen Circle
Telford PA 18969
Land: 215-799-0192
Mobile: 610-256-2843
Fax 413-410-9682
FMP, WiTango, EDI,SQL 2000
[EMAIL PROTECTED]
www.dss-db.com


<h2 align="center"> <font color="#006600" face="Arial, Helvetica, sans-serif">Break 
Even Report</font></h2>
  
<h3><@CURRENTDATE FORMAT="datetime:%A, %B %d, %Y">&nbsp;</h3>
<p><B><A HREF="javascript:history.back()">Back</A></B></p>
<P><a href="javascript:printWindow()">Print This Page</a></P>

<@ASSIGN NAME="revenue" VALUE="<@FILTER ARRAY='resultSet'  EXPR='#12=1'>" 
SCOPE="local">

<TABLE width="100%" BORDER=1 align="left" cellpadding="1">
  <TR VALIGN=TOP ALIGN=LEFT> 
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><B>Detail</B> </font></TD>
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><B>CourseNumber</B> 
      </font></TD>
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><B>CourseShortName</B> 
      </font></TD>
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><B>Location</B> 
</font></TD>
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><B>Start Date</B> 
      </font></TD>
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, 
sans-serif"><B>Revenue</B> 
        </font></div></TD>
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, 
sans-serif"><B>Maximum</B> 
        </font></div></TD>
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, 
sans-serif"><B>Counts</B> 
        </font></div></TD>
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, 
sans-serif"><B>Open 
        Closed</B> </font></div></TD>
  </TR>
        <TR>
        <TD colspan=9><div align="center"><font size="2" face="Arial, Helvetica, 
sans-serif"><B>Courses at breakeven or above.</B> 
        </font></div></TD>
        </TR>


  <@ROWS        >
<@COMMENT>

<@ASSIGN NAME="local$course_revenue" VALUE="<@FILTER ARRAY='local$revenue' 
EXP='#1=<@COLUMN "Courses.CR_ID">'>">
</@COMMENT>





<@IF EXPR="<@COLUMN 'Courses.Counts'>>=<@COLUMN 'Courses.Break_Even'> ">
  <TR VALIGN=TOP> 
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><A 
HREF="<@CGI><@APPFILE>?_function=detail&Courses_uid1=<@COLUMN "Courses.CR_ID" 
ENCODING=URL>&<@UserReferenceArgument>&nc=<@RANDOM Low='1000' HIGH='2000000'  
ENCODING=URL> " onclick="NewWindow(this.href,'detail','700','500','yes');return 
false;">Detail</A> </font></TD>
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><@COLUMN 
"Courses.CourseNumber"> 
      </font></TD>
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><@COLUMN 
"Courses.CourseShortName"> 
      </font></TD>
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><@COLUMN 
"Courses.Location"> 
      </font></TD>
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><@COLUMN 
"Courses.StartDate" FORMAT="datetime:%m/%d/%Y"> 
      </font></TD>
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, sans-serif"><@CALC 
EXPR="SUM(@@local$revenue[<@CURROW>,11])">

        </font></div></TD>
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, 
sans-serif"><@COLUMN "Courses.Maximum"> 
        </font></div></TD>
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, 
sans-serif"><@COLUMN "Courses.Counts"> 
        </font></div></TD>
<@IF EXPR="<@COLUMN 'Courses.Open_Closed'>=0">
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, sans-serif">Full 
        </font></div></TD>
<@ELSEIF EXPR="<@COLUMN 'Courses.Open_Closed'>=1">
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, sans-serif">Open 
        </font></div></TD>
<@ELSEIF EXPR="<@COLUMN 'Courses.Open_Closed'>=3">
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, 
sans-serif">Cancelled 
        </font></div></TD>
</@IF>
  </TR>
<@ELSE>
</@IF>
  </@ROWS>
<TR>
        <TD colspan=9><div align="center"><font size="2" face="Arial, Helvetica, 
sans-serif"><B>Courses below breakeven.</B> 
        </font></div></TD>
        </TR>

 <@ROWS>
<@IF EXPR="<@COLUMN 'Courses.Counts'><<@COLUMN 'Courses.Break_Even'> ">
  <TR VALIGN=TOP> 
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><A 
HREF="<@CGI><@APPFILE>?_function=detail&Courses_uid1=<@COLUMN "Courses.CR_ID" 
ENCODING=URL>&<@UserReferenceArgument>&nc=<@RANDOM Low='1000' HIGH='2000000'  
ENCODING=URL> " onclick="NewWindow(this.href,'detail','700','500','yes');return 
false;">Detail</A> </font></TD>
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><@COLUMN 
"Courses.CourseNumber"> 
      </font></TD>
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><@COLUMN 
"Courses.CourseShortName"> 
      </font></TD>
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><@COLUMN 
"Courses.Location"> 
      </font></TD>
    <TD> <font size="2" face="Arial, Helvetica, sans-serif"><@COLUMN 
"Courses.StartDate" FORMAT="datetime:%m/%d/%Y"> 
      </font></TD>
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, 
sans-serif"><@COLUMN "Courses.Minimum"> 
        </font></div></TD>
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, 
sans-serif"><@COLUMN "Courses.Maximum"> 
        </font></div></TD>
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, 
sans-serif"><@COLUMN "Courses.Counts"> 
        </font></div></TD>
<@IF EXPR="<@COLUMN 'Courses.Open_Closed'>=0">
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, sans-serif">Full 
        </font></div></TD>
<@ELSEIF EXPR="<@COLUMN 'Courses.Open_Closed'>=1">
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, sans-serif">Open 
        </font></div></TD>
<@ELSEIF EXPR="<@COLUMN 'Courses.Open_Closed'>=3">
    <TD> <div align="center"><font size="2" face="Arial, Helvetica, 
sans-serif">Cancelled 
        </font></div></TD>
</@IF>
  </TR>
<@ELSE>
</@IF>
  </@ROWS>

</TABLE>

</BODY>
</HTML>

Witango-Talk: Help with report?

Reply via email to