On Account usage reporting

How about instead of having the Transaction Type in your columns, you use bucketing of values to end up with different value columns?

1. Duplicate your widget (so that you still have the original version).

2. Turn off the Transaction Type in your columns.

3. Duplicate your item in the Values (one for each value column you want as a result).

4. Use Bucketing to only give you the total Transaction Amount for a specific transaction type and/or payment method.

Bucketing general format:

(Sum[Value], [Filter Field1], ]Filter Field2], ...)

The [Filter Field]'s can either be a Field (e.g. Transaction Type) or a Value (e.g. > 0).

You may need to find specific examples of each possible situation, so that you can check that your formulas work. You could add a filter to only look at specific Order ID's, Transaction ID's, or some other distinct identifiers.

Does that make sense?

Thanks for the idea but this does not seem to be working for me. When I add a filter with a payment method, I get no results. I know that we have sold gift certificates using on account funds in the past 30 days so something should be there... My dashboard level filters are Payment Method Type is On Account and Transaction date in last 30 days. There are no additional filters on the widget. These are my new parameters - the filtered payment method is Gift Certificate:

What am I missing?

Thanks
Jess Levy

Hi Jess,

I fixed that problem by changing the Row field from something other than Payment Method.

When using a field in a formula as a filter, it will override any row level grouping, widget filter, or dashboard filter of that same field. You could use Payment Method Type instead of Payment Method in the formula value filter and it would work without breaking out of the Rows Payment Method groups.

Hurray! Community to the rescue again! This fixed most of what I needed (yea!!!) but now I have a discovered another issue - The total doesn't match what is in the details

If you add up all the rows, they equal -9263, not -9714 which is what is displayed as the total. I have validated what is in each individual row and those numbers are all correct and match my traditional reporting. Is there a reason for this discrepancy?

Thanks
Jess

Hi Jess,

The formula that you have there as "Gift Certificate", when calculated in aggregate, ignoring the Constituent ID and Months in Date, in this case returns a different result than the sum of the results of that formula across all the rows of the output. To work around this quickly, edit the widget, and from the Value's options menu, select Subtotal By, and change it from Auto (which re-executes the formula at the grain of the whole widget) to Sum (which adds up the results of that formula from the rows within the widget).

Ignore my specific Values.

Hi Jess,

The formula that you have there as "Gift Certificate", when calculated in aggregate, ignoring the Constituent ID and Months in Date, in this case returns a different result than the sum of the results of that formula across all the rows of the output. To work around this quickly, edit the widget, and from the Value's options menu, select Subtotal By, and change it from Auto (which re-executes the formula at the grain of the whole widget) to Sum (which adds up the results of that formula from the rows within the widget).

Ignore my specific Values.

Thanks, Chris. That did the trick when the constituent id row is turned on. Is there a way to keep that calculation without listing all the constituent id's? In my ideal scenario I would have one row for each month as opposed to one row for each constituent. When I turn off the constituent row, I go back to the original amount I was getting when the subtotal was set to Auto (I double checked and the subtotal is still set to Sum for the Gift Certificate value)

Best
Jess

Hi Jess,

In order to help from here, I think I need to go back to the beginning. Apologies in advance. What is this widget intended to show? Based on your OP, you're looking for the amount of On Account money spent, specifically to purchase Gift Certificates, and grouped by the Month of the Transaction Date. Can you share what filters are in place on the dashboard/widget, and what the Value formula is that you're using?

My quick attempt requires doing some fancy filtering on Transaction ID to limit results to those that HAVE an On Account bit to them, but without limiting the data to only the On Account payments within those transactions. This is using the Advanced filter type:

{
 "attributes": [
 {
 "dim": "[PAYMENT METHOD.Payment Method Type]",
 "filter": {
 "members": [
 "On Account"
 ]
 }
 }
 ],
 "custom": true
}

Then also limit to Transaction Type = Zero Transaction to limit to those internal, money-movement transactions.

For the Value I have: ( [Total Credit Amount] , [Payment Method Type = GC] ).

In my own data I don't have any examples of OA being used to purchase GC, but do have exactly one transaction in which OA is used to pay an Invoice, which is basically the same thing from a technical reporting perspective.

In my case, the Auto and Sum sub-totaling in the pivot table have the same results, but I clearly don't have enough in my results to prove it out. If you're still having trouble, can I ask that you open a support ticket to have someone take a closer, more direct look at your specific dashboard?

Many thanks!
Chris

Hi Chris

The ultimate goal of this dashboard is to track how one specific On Account fund is being used..

All of the funds related to our cancelations were moved to On Account funds. Up until this point, all of the processing of those funds was done by Box Office staff so we have been able to track how that money was being used (refunded, donated, turned into a gift certificate, used to purchase other tickets) with source codes and custom fields in orders. Next week, we are going to begin allowing patrons to use these funds on our website meaning we lose some of the ability we have to track (for example, a gift certificate purchased online with a credit card and one purchased with on account funds will both have the same source code). 

My dashboard level filter is transactions in the past 30 days and Payment Method type in On Account.

I am going to keep working on it and will open up a support ticket if needed.

Thanks
Jess