Crosstab - array error

[BACFL]

get the following error with my code below. Please Help.

Table will be rows = dscgrd, columns = dsckcrse, rows x cols = count of dsckextid.  All grouped by teacher_anumber.

0 is not greater than zero or less than or equal to 0.

The range passed to ArraySet must begin with a number greater than zero and less than or equal to the second number.

The error occurred in C:/ColdFusion10/cfusion/wwwroot/CFIDE/groups/crosstabs.cfm: line 41

39 :           GROUP="dscgrd">  40 :  <tr><th>#dscgrd#</th>  41 : <CFSET temp = ArraySet(course_idarray, 0, b, "N/A")>

<CFQUERY NAME="crosstabquery" DATASOURCE="LIISthinkgate">
  SELECT     teacher_anumber, dsckcrse, dsckextid, dscgrd
    FROM     evaluationstudentcourse
  ORDER BY     dscgrd, dsckcrse
</CFQUERY>

<CFQUERY NAME="crosstabcolumns" DBTYPE="query">
  SELECT distinct     dsckcrse
    FROM             crosstabquery
  ORDER BY             dsckcrse
</CFQUERY>

<CFSET course_idlist = ValueList(crosstabcolumns.dsckcrse)>
<CFSET course_idheaders = ListToArray(course_idlist)>
<CFSET b = ArrayLen(course_idheaders)>

<CFSET course_idarray = ArrayNew(1)>

------------------------------------------------------------
<table>
<CFOUTPUT QUERY="crosstabquery"
          GROUP="dscgrd">
<tr><th>#dscgrd#</th>
<CFSET temp = ArraySet(course_idarray, 0, b, "N/A")>
     
     
     
<CFOUTPUT>
   <CFSET i = ListFind(course_idlist
                  , crosstabquery.dsckcrse)>
   <CFSET temp = ArraySet(course_idarray, i, i, crosstabquery.dsckcrse)>
</CFOUTPUT>
<CFSET rowtotal=0>
<CFLOOP index="j" FROM="1" TO="#b#">
   <td>#course_idarray#</td>
   <CFSET rowtotal=rowtotal+course_idarray>
</CFLOOP>
<td>#rowtotal#</td>
</tr>
</CFOUTPUT>
</table>

[BACFL]

An update.

this line: <CFSET temp = ArraySet(course_idarray, 0, b, "N/A")>

Should be: <CFSET temp = ArraySet(course_idarray, 1, b, "N/A")>

[BKBK]

0 is not greater than zero or less than or equal to 0.

I shall report that error message as a bug. It is false. It should read: "0 is not greater than zero and less than or equal to 0."

[BKBK]

3 remarks:

• The line <CFSET temp = ArraySet(course_idarray, 1, b, "N/A")> should be put outside the cfoutput loop. There is no need for it to run at every pass of the loop.
• The cfoutput tag within <cfoutput query="crosstabquery"> is unnecessary.
• The line <CFSET temp = ArraySet(course_idarray, i, i, crosstabquery.dsckcrse)> is perhaps equivalent to <CFSET course_idarray = crosstabquery.dsckcrse)>

[BACFL]

Hi BKBK!

I appreciate your assistance.  What appears to have been the problem here from some research that I did is that a Cold Fusion array does not start with zero, it starts with 1.  So I changed the array to: "

<CFSET temp = ArraySet(bucketarray, 1, 2, crosstabquery.amount)>" so that the count starts with 1 and the next count is 2.

Here is my updated code, for which as you can see below the COUNT that I added is not working properly.

<CFQUERY NAME="crosstabquery" DATASOURCE="LIISthinkgate">
  SELECT distinct teacher_anumber,dsckcrse,dscgrd,  count(dsckextid) as amount
    FROM evaluationstudentcourse
    WHERE teacher_anumber = 'A054863'
    group by teacher_anumber,dsckcrse,dscgrd
  ORDER BY teacher_anumber,dscgrd,dsckcrse
</CFQUERY>

<CFQUERY NAME="crosstabcolumns" DBTYPE="query">
  SELECT distinct dsckcrse
    FROM crosstabquery
    WHERE dsckcrse = crosstabquery.dsckcrse
  ORDER BY dsckcrse
</CFQUERY>

<CFSET bucketlist = ValueList(crosstabcolumns.dsckcrse)>

<CFSET bucketheaders = ListToArray(bucketlist)>

<CFSET b = ArrayLen(bucketheaders)>

<CFSET bucketarray = ArrayNew(1)>

<CFSET temp = ArraySet(bucketarray
                  , 1, b, "N/A")>

<CFOUTPUT QUERY="crosstabquery" GROUP="teacher_anumber">
  <h4>Anumber=#teacher_anumber#</h4>
  <table border="1" cellpadding="5">
  <tr><th></th>
  <CFLOOP INDEX="i" FROM="1" TO="#b#">
    <th>#bucketheaders#</th>
  </CFLOOP>
  </tr>
  <CFOUTPUT GROUP="dsckcrse">
    <tr><th>#dscgrd#</th>
  
  
       <CFSET i = ListFind(bucketlist
                  , crosstabquery.dscgrd)>
        <CFSET temp = ArraySet(bucketarray
                     , 1, 2, crosstabquery.amount)>
 
    <CFLOOP index="j" FROM="1" TO="#b#">
      <td>#crosstabquery.amount#</td>
    </CFLOOP>
    </tr>
    </CFOUTPUT>
  </table>
</CFOUTPUT>

I got rid of the original error, however I am now not getting "0" where there should be no count. For instance 5001020 (dsckcrse), 02 (dscgrd) should have 136.  The others (01,03,04,05,KG) should be zero.  I get the following:

Anumber=A054863

	5001010	5001020	5001030	5001040	5001050	5001060
01	120	120	120	120	120	120
02	136	136	136	136	136	136
03	145	145	145	145	145	145
04	163	163	163	163	163	163
05	165	165	165	165	165	165
KG	117	117	117	117	117	117

Appreciate any insight you have.

[BKBK]

Please see my first post.

[kylec46637293]

Hello,

I am having a similar issue right now as well. On CF9, I am having no issues. Currently, I am testing on a new web server with CF11 update 10. The error I am receiving is similar:

0 is not greater than zero or less than or equal to 0.The range passed to ArraySet must begin with a number greater than zero and less than or equal to the second number.

Here is a snippet of my code (not sure if you will need more than that). The bolded code is where it is saying it is erroring out on and I believe is where I need to make a change. I am more or less looking for more ideas on what to check or if something has changed since CF9 that I have not read on.

---

<cfset bucketlist = ValueList(crosstabcolumns2.ts_desc)>

<cfset bucketheaders = ListToArray(bucketlist)>

<cfset b = ArrayLen(bucketheaders)>

<cfset bucketarray = ArrayNew(1)>

<cfset bucketTotal = ArrayNew(1)>

<cfset temp = arrayset(bucketTotal,1,b,"0")>

<cfoutput query="summedresultsNewOld" group="classcode">

  <table border="1" cellpadding="5">

  <tr><th width="225">MAIN CAMPUS</th>

     <cfloop index="i" From="1" to="#b#">

       <th>#bucketheaders#</th>

     </cfloop>

  <th width="125">TOTALS</th>

  </tr>

  <cfoutput group="ts_type" >

    <tr><th>#ts_type#</th>

   <cfset temp = arrayset(bucketarray,1,b,"0")>

   <cfoutput>

        <cfset i = listfind(bucketlist, trim(summedresultsNewOld.ts_desc))>

              <cfset temp = ArraySet(bucketarray, i, i, summedresultsNewOld.headcount)>

              <cfset temp = ArraySet(BucketTotal,i, i, (BucketTotal + summedresultsNewOld.headcount))>

   </cfoutput>

   <cfset rowtotal = 0>

   <cfloop index="j" from="1" to="#b#">

     <cfset rowtotal = #rowtotal# + #bucketarray#>

     <td width="125">#bucketarray#</td>

   </cfloop>

   <td>#rowtotal#</td>

      </tr>

  </cfoutput>

   <tr>

      <th bgcolor="##999999">TOTALS</th>

        <cfloop index="j" from="1" to="#b#">

       <td bgcolor="##999999">#BucketTotal#</td>

        </cfloop>

     <td bgcolor="##999999">#ArraySum(BucketTotal)#</td>

    </tr> 

  </table>

</cfoutput>

---

This is the result of what it should look like (currently in CF9). Any help is appreciated!

[BKBK]

@kylec46637293

Start your own thread.