TOPIC: Front End Management

We have multiple people who manage events from our website. It would be wonderful to have the ability for event reports to be run from the front-end (based on Joomla permissions).

It would also be nice to have the ability to save a default report format for an event.

Hi mkinsey,

It is possible to do what I think you are are asking for by using the extension SQL 2 Excel Pro (you may be able to use the free version - but it is such a good product!).

The follwoing SL statement would do most standard (no custom fields) for an event with both individual and group registrations. The only item you would need to change is the event.id which in this case is "11". Each Event has it's own id.

SELECT jos_dtregister_user.eventId, jos_dtregister_user.userId, If(jos_dtregister_user.userType='G',jos_dtregister_group_member.firstname,jos_dtregister_user.userFirstName) AS forename, If(jos_dtregister_user.userType='G',jos_dtregister_group_member.lastname,jos_dtregister_user.userLastName) AS surname, jos_dtregister_user.userOrganization, jos_dtregister_user.userAddress, jos_dtregister_user.userCity, jos_dtregister_user.userState, If(jos_dtregister_user.userType='G',jos_dtregister_group_member.email,jos_dtregister_user.userEmail) AS mail, jos_dtregister_user.userPhone
FROM jos_dtregister_user LEFT JOIN jos_dtregister_group_member ON jos_dtregister_user.userId = jos_dtregister_group_member.groupUserId
WHERE (((jos_dtregister_user.eventId)="11"))
ORDER BY jos_dtregister_user.userOrganization, jos_dtregister_user.userCity

Then, using the menu item relating to SQL 2 Excel and the plugin which allows SQL 2 Excel content within an article, you can have as many event reports in the front end as you like including downlaods of spreadsheets and all controlled by the Joomla permissions.

Even if you are not an SQL guru (and I am certainly not) and you have custom fields, it shouldn't be too hard to see where they go.

Regards

I appreciate the response and the information about SQL 2 Excel. The query above does not return any data on the individuals registered for each event in group mode. It only returns a single row without any of the group members details. Is there any way to get that data in the same query?

Yes it is possible - what information do you require?

I need to get a list of all registered people with their custom fields in one report. Right now, I am running 2 reports, one for group registrations and one for individual registrations. I have about 10 custom fields (that are the same for both group and individual).

I need the registered name plus the custom fields.

Any help would be greatly appreciated.

My orginal query should have given you everything in a single reort by eventID so I am unclear what else you need apart from the custom fields.

If you want me to write the query for you, I would need to know the names of the custom fields. If you are only building a test site at the moment and are happy to give me backend access, please PM me and I will try and assist.