jbrown

ExecuteSQL: When the Count is 0

Discussion created by jbrown Expert on Nov 6, 2012
Latest reply on Nov 6, 2012 by fmpros

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

Outcomes