Event Costs and Fees

Just cooked this up for one of our trainers, but it’s something I think a lot of admins may end up wanting once they start to see it, so here’s the stuff needed for it.

CREATE PROCEDURE dbo.cust_sp_evnt_costs_and_fees
@profile INT
AS
SELECT
'Cost: '+ec.title AS [Cost or Fee Name]
,COUNT(*) AS [Number of Paid Registrants]
FROM
evnt_registrant_cost AS erc
JOIN evnt_registrant_cost_payment AS ercp ON ercp.registrant_cost_id=erc.registrant_cost_id
JOIN evnt_cost AS ec ON ec.cost_id=erc.cost_id
WHERE erc.profile_id = @profile
AND erc.paid=1
GROUP BY ec.title
UNION
SELECT
'Fee: '+ef.title+ISNULL(': '+NULLIF(erf.selected_value,''),'') AS [Cost or Fee Name]
,SUM(erf.quantity) AS [Number of Paid Registrants]
FROM
evnt_registrant_fee AS erf
JOIN evnt_registrant_fee_payment AS erfp ON erfp.registrant_fee_id=erf.registrant_fee_id
JOIN evnt_fee AS ef ON ef.fee_id=erf.fee_id
WHERE erf.profile_id = @profile
AND erf.paid=1
GROUP BY ef.title,erf.selected_value

This is the setup in Pages (explanation below):

Explanation:
On the Event Tags page's Main Content tab, add a new module.
Per the instructions in the image, drag the new module up one spot and back down (to force the program to recognize that it should be at the bottom of the page)
Open the new module and change the type to Report Grid From Query (it's in the Reporting section).
Set the Parameter and Query options as shown in the image (don't forget the semi-colon at the end of the parameter).
Refresh the cache and you're good to go.

What this does is use a Report Grid From Query at the bottom of the Event details page showing a count of how many of each cost and fee (it separates out fees by selected values, so if you’re doing T-shirts for $10 each, you’ll see something like “Fee: T-Shirt: X-Large……3”) have been selected, restricted to the current event (it does not roll up sub-events). It’s just an aggregate count, and it only includes Paid costs & fees, but gives you a quick count of how many vegetarian meals to order, or whatnot.