CiviCRM Community Forums (archive)

It appears that the native reporting function does not include itemized fees (or any fees for that matter) for a conference (i.e, event) registration.

Client needs to be able to determine which registrants paid for which individual events (like golf outing, registration, formal dinner, etc..) for their conference registrations, but that info does not appear in the event report (using export function).

This would seem to be a fundamental feature of any reporting tool, so I am hoping I'm just missing something. The individual  events (for each conference) are all price sets, if that helps figure this out.

Any ideas what we can do to get this info from the database?

Thanks,

Teddy

Donald - Anything available for 1.9? We were told by our developer that we've done too many modifications and we can't upgrade.

There is always the possibility that I have the client setting up the forms incorrectly. How did people do this before price sets? Maybe price sets is not the proper way to set up and price an event?

Again, being able to get information about your own event seems to be fundamental and I can't imagine it was overlooked as a feature. I must be doing something else wrong, no?

Thanks - T

What's PMA?  I suspect we'll have our developer do some additional customization on the reports function :-).

Thanks for the time and explanation Donald!

Donald, Teddy,

I just reached this same point in my site development today also. I won't be upgrading to 2.x until 1) I get this site going (almost done) and 2) get it on the development machine that's waiting to be configured, since I must also replicate numerous modifications.

So, I (and I suspect Teddy also) would like a few pointers on using PMA to export registration particulars. For instance, my conference registration on-line form uses a price set with lots of fields. When someone registers, I hope the data for the different fields is stored somewhere in the database. If so, in which table might that be? Is it tied to the contact ID? So, could I export the price set field data for each contact that uses the registration form? If so, how might that be done?

Any help would be greatly appreciated.

Chrys

So, I (and I suspect Teddy also) would like a few pointers on using PMA to export registration particulars. For instance, my conference registration on-line form uses a price set with lots of fields. When someone registers, I hope the data for the different fields is stored somewhere in the database. If so, in which table might that be? Is it tied to the contact ID? So, could I export the price set field data for each contact that uses the registration form? If so, how might that be done?

Event registration price set data is stored in the civicrm_line_item table. The table contains a foreign key to the civicrm_contribution record which is the actual "payment" for the event. The line_item table should contain most / all of the info you need - but if you're running multiple events then you'll need to use some JOIN statements to select only the line_item records for a particular event.

I've attached a screen-shot of some sample civcirm_line_item data - viewed using phpMyAdmin ("Browse" tab) - so you get an idea of what's there.

A quick and dirty draft query to run (you'll need to modify based on the outputs you need):
SELECT contact.sort_name, item.label, item.qty, item.unit_price, item.line_total
FROM civicrm_line_item item, civicrm_contact contact, civicrm_contribution contribution

WHERE
item.entity_table = 'civicrm_contribution' AND
item.entity_id = contribution.id AND
contribution.contact_id = contact.id

ORDER by item.price_field_id


Brian - This works consistently for front-end and back-end registrations in 2.1. Both processes create line_item table entries.

Million dollar question...
Do you anticipate upgrade scripts will rebuild the event_level data into the line_item table?

This is currently not implemented, nor are there any plans to do so.

lobo

2.0
The line_item table is not used for backend registrations in 2.0. Registration details are only stored in the event_level field of civicrm_participant.

Issue came up with re: to this: http://forum.civicrm.org/index.php/topic,4217.0.html