I'm loving the idea of KoolReport -- I can see it simplifying output of a lot of statistical fitness data I am collecting for a client. But I have a situation where KoolReport crashes as I try to compile the dataset. I want to fetch the raw data from a set of fitness statistics, and I need to calculate both the number of "healthy" students and the percentage of students that are healthy. I though I could do that by calculating the number of healthy students first using the group function, and then dividing that by the total number of students. One of the two fields is in my original SQL query but the other was calculated by KoolReport. When I try this second-step calculation, it doesn't recognize my first calculated field . (it DOES find the other field needed which was in the SQL query to begin with.)
Here's an edited version of my sequence:
protected function setup() {
$this->src('fitttr_data')
->query("SELECT 1 as cnt, value, normallo,normalhi,
DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(dob,'%Y') - (DATE_FORMAT(NOW(),'00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age,
FROM....
")
//for each student, calculate if they were healthy
->pipe(new CalculatedColumn(array(
"ishealthy"=>"{value}>={normallo} && {value}<={normalhi}",
)))
//now group by age, summing up values
->pipe(new Group(array(
"by"=>"age",
"avg"=>"value",
"sum"=>"cnt, ishealthy"
)))
//sort by age
->pipe(new Sort(array(
"age"=>"asc"
)))
//try to calculate the percentage healthy for each group
->pipe(new CalculatedColumn(array(
"pcthealthy"=>"{ishealthy}/{cnt}*100"
)))
//and output
->pipe($this->dataStore('fitttr_data'));
}
It's this line -- "pcthealthy"=>"{ishealthy}/{cnt}*100" -- that crashes, failing to find a variable named "ishealthy" Yes, I could put "ishealthy" into the original SQL query, but logically it is clearer to do calculations after getting the raw data. Is this a fixable bug?
--Larry