3 Replies Latest reply on Nov 6, 2012 12:22 PM by fmpros

    ExecuteSQL: When the Count is 0

    jbrown

      Hi all,

      I have an execute sql statement in a graph that counts the number of detentions (and other behavior types) a student has during each of the trimesters. It's on each student's record and uses the unique record ID number of the student to count the number of detentions in the behavior table. The graph works great when kids have more than 0 detentions.

       

      A lot of students have 0 detentions, so the function returns a count of 0 on the bar graph. It looks rather silly to see a 0 at the bottom of the graph for each trimester. (see attached pic).

       

      Is there a way to keep the 0 from showing? I guess I could take the value off the bars themselves, but I'd rather keep the numbers on each bar.

      THe function is this:

       

      Let ([

      $Tr1 = 1;

      $Tr2 = 2;

      $Tr3 = 3];

       

       

      ExecuteSQL("

      SELECT

      COUNT (Case When MarkingPeriod = ? THEN EventType else Null End),

      COUNT (Case When MarkingPeriod = ? THEN EventType else NULL END),

      COUNT (Case When MarkingPeriod = ? THEN EventType else NULL END)

       

       

      FROM BehaviorTable

      WHERE fk_StudentID = ?

      AND EventType Like ?"

      ;"¶";"";$Tr1; $Tr2 ; $Tr3; A_KIPPsters::Apk_StudentID; "%CP")

      )

       

      Anyideas?

      Thanks

        • 1. Re: ExecuteSQL: When the Count is 0
          fmpros

          Hi Jeremy,

          Maybe it's my browser but I don't see the pic file.

          Blindly then, have you tried testing the SQL result for 0 and nulling it?

           

          William

          • 2. Re: ExecuteSQL: When the Count is 0
            jbrown

            William, I forgot to post the pic. How would you null it in that sql statement? Use a if statement in the sql?

            • 3. Re: ExecuteSQL: When the Count is 0
              fmpros

              Thanks for the pic.

              Just thinking of something like this:

               

              Let ([

              $Tr1 = 1;

              $Tr2 = 2;

              $Tr3 = 3;

               

               

              $result = ExecuteSQL("

              SELECT

              COUNT (Case When MarkingPeriod = ? THEN EventType else Null End),

              COUNT (Case When MarkingPeriod = ? THEN EventType else NULL END),

              COUNT (Case When MarkingPeriod = ? THEN EventType else NULL END)

               

               

              FROM BehaviorTable

              WHERE fk_StudentID = ?

              AND EventType Like ?"

              ;"¶";"";$Tr1; $Tr2 ; $Tr3; A_KIPPsters::Apk_StudentID; "%CP") ;

               

              $result = Substitute ( $result ; "0" ; "" )

              ];

              $result

              )

               

              This is crude and I'm still learning the in's and out's of charts but it appears to me from a quick test that substituting out the 0's from the delimited result list still leaves the element's space on the chart without putting the value.

               

              William