CiviCRM Community Forums (archive)

I am trying to create a custom report. It should report the number of people from each city that attended each program. So for example, ten people from orlando attended xyz program whereas 23 people from tampa attended xzy program. So what I was wondering is how are participants tied to an event – i.e. where in the database tables can I find which participants attended xyz event?

Then after that I’d have to probably do a join table with the participant’s addresses (i'm guessing based on participant_id or something like that) and then place in arrays based on city, reporting the sizeof() the city_arrays.

Any thoughts?

Thanks Dave! That information will help us with a different portion of the report but I mistyped a word in my question. I meant to say program, not event. I am trying to create a custom report within civicase (I guess it’s vital to know that we translated civicase --> programs), so I’m guessing that information is in a different table. Currently the report (a grid) looks like this (with blank fields which will be filled by query):
Program   AP CR CL FV GA HS KD MV RA WF WE ZE OT
abc
ghi
xyz

The programs are listed along the left and the capital two-letter words across top represent cities. For example, the first blank would be filled in by the number of people from AP city who are enrolled in abc program (formerly civicase). Any help would be appreciated!

Alright, so we're still having some troubles with this query. Although this time it's not so much with the query itself but rather what we're going to do with it. We are still trying to fill in the same values as described above. Right now our query returns values such as 'apex 5' where apex will correspond to AP and then 5 is the value associated with 'program abc' from the table. However the query only returns cities that have values. Therefore if there's nobody from city CR then the table will be off. Also, have to translate cities from the query to cities across top of table i.e. 'apex'='AP' and then fill in corresponding value, which is where the buildRows function comes in. We would like to override it in our custom report.

So what we need to know is where to find the buildRows function - i.e. which class is it defined in?

Any help would be greatly appreciated!