Would anyone know how to recreate an On Account report in analytics? I wanted to make a dashboard that would update automatically for one of our teams so they can look at that instead of needing to pull the On account report every day.
I cant seem to find the values and filters that I need however when working in the analytics cubes. I've tried using the finance cube and the credits field which gets me some of the information from the report but it also adds a bunch of other data that doesn't match the filters I'm looking for.
Any help would be appreciated fell free to ask an clarifying questions if you need to.
Hello! Because I like a challenge and we would also find this useful, I attempted to set this up in analytics.It's not working yet, but maybe the discussion will help spark some ideas and other people can build on this.
I tried setting up a pivot table which includes Constituent ID and Constituent Sort name, and then added the following value formula: ([Total Credit Amount], [Payment Method Type]) - ([Total Debit Amount] , [Payment Method Type])
Where Payment Method is filtered only to 'On Account' as the payment type. So theoretically I thought this would pull any credit amount that was paid to the on account, and any debits from the on account, giving the total remaining on account balance. This seems to have pulled the correct value for some constituents, but is pulling incorrect numbers also. I'll keep investigating over the next few days and see what I can come up with!What information are you wanting to display? You mention a bunch of extra data, are you just wanting the constituent ID and total value on account?
Your formula actually does pretty much what I was trying to figure out. I think the main thing that I need to figure out now is how to filter by the above formula so that the table only shows values that are above 0.
I do see what you mean though. For a few of the constituents on my list its showing a negative number and a few are showing more than their actual on account balance.
As for the extra data. I was incorrectly only pulling the credit amounts so it was showing multiple constituents that didn't actually have an on account balance.
I think you are on the correct track with what you found. Ill play around with it to see if I can figure something out to filter it our a bit more. I'll let you know if I figure something out but thank you for your help so far.
Does filtering Nicola's forumla to >0 work for you?Nicola Cann Here is a possibility for Constituent ID discrepancies: I have an odd value (that doesn't show up in On Account Tracking report) because the tickets were transferred and it looks like the finance cube has that other half of the transaction following the current Owner of the Order
Yep filtering the formula by 0 removed all the people that no longer had credit on their account. Thanks for that suggestion!
I did run into one issue where some constituents had a negative value on their account. After doing some research it looks like those accounts used On-Account credit from one of the other Organizations. So that accounted for the negative balances and the organizations that showed more credit that they actually had.
That issue doesn't occur on the On-Account report so I wonder if that's just a limitation of the analytics cubes.