This group is so helpful so here it goes;
I need to find a report that will give me the number of tickets sold to six specific price types; and the income; for a transaction date range (but the tickets can be for any performance date within our FY); in a specific MOS; across multiple seasons (6); to one specific payment method.
I was hoping TSTATS, but I don't see a payment method criteria anywhere in a cube.
-Jessica
Looks like a SQL query, although maybe the new Analytics Finance Cube will be able to do this sort of thing?
One caution: payment method is never going to directly connect to those other data points. At best I think you will be able to say "if Payment Methods (1,2,4) were used in part with Orders that..."
I'm going to add another caution here. Just as Payments don't perfectly sync with orders (an order may have more than one payment), neither do Seasons. We use Seasons extensively for reporting, but it is often problematic, as products from multiple seasons can be combined into a single order. That means when you want to do something like, "For each Season, how many orders did we get?" you will inherently double count orders with multiple seasons.
Source/Appeal/Campaign is more certain in it's connection to orders (although the performances and fees can be assigned to their own campaigns...)
Just to second what Gawain said. Tracking payments back to tickets is iffy at best. Payments are applied in a specific order, I believe contributions, then fees, then tickets. Don't quote me on that but let's assume that is the case. If the order has $75 in tix and $25 in fees and you split the payment between $25 in cash and $75 in visa. The $25 would be applied to the fees and $75 to the tickets. T_TRANSACTION does have a perf_no field and ties back to T_PAYMENT but what you'd see is that there would be no link to the ticket performances to the cash T_PAYMENT rows.
Besides the payment method part, a report to get you tix for a specific price type, MOS, and date - no problem. But if you must say they were paid with a particular payment method, things get iffy. If you can accept a bit of fuzzyness and ask if a particular payment method was used in a order for tickets with the above criteria then its totally fine.
Sean