I'm on the struggle bus and want to know if anyone has set up a template to track pledges receivable-new pledges, pledge payments, pledge installments, etc. I'm unsure how to set up the members in the dimensions so that it all comes together when I want to pull all pledges for a pledge account or another account or by donor ID/name. I've started over at least a dozen times. If anyone would like to share how they have set this up, I would be so grateful. Or if you have a different template that does the same thing, I will take whatever knowledge you have.
Could you give us more details on what you need to do? It sounds a little bit like you might need to use an alt hierarchy. But it depends a lot of how your data model has been structured.
Thank you for responding, Mario.
@Darrell Rooney, I believe you are also in Higher Education. Do you use Vena to track pledges?
Thanks @Giovanna Reinking!
Thanks for responding. I'm wanting to save the following data in Vena so that I can pull various reports. Reports to pull pledge balance per Donor ID or Pledge account or Fund Account and pull reports to calculate pledge allowance and discount. Would also like to pull into other reports to show cash balances of fund accounts. Sorry this may be more information you need. I feel if I could just have a few ideas I could then run with that and build/map the template correctly. If this doesn't work then we may need to look into asking for more help, but would like to try this first.
Donor ID (There will be a lot of these)
Donor Name (could be donor id alias)
Pledge (Date and Amount)
Fund Account (offset of pledge account)
Pledge installments due dates (Date and Amounts) Also need to be able to make adjustments (because things change)
Pledge Payments (Date and Amounts)
Our dimensions are:
Account String (The entire GL number xx-xxxx-xxxx)
Account (last 4 of account string)
Fund (first 2 of account string) (similar to Vena Entity dimension)
Dept (mid 4 of account string)
Project Code (unique identifier) was thinking of using this for pledge account (only have about 4-5 of these accounts) and the account string dimension for the fund account because they already exist there and there are hundreds of these.
Item (Vena dimension) - nothing specific pulling in from our ERP more of a placeholder dimension
Source Code (unique identifier) for type of transaction from our ERP system, but for pledges this dimension could be used for something else
Year (would like to use to map timing of pledge, pledge installments, pledge payments)
Period (would like to use to map timing of pledge, pledge installments, pledge payments)
Scenario (thought about using to identify if transaction was a new pledge, pledge payment, pledge installment)
Currency (always Local, we don't have foreign currency)
Measure (not sure which members to put here verse if I should put them in another dimension, so that I can pull for various reports.)
I also would like the person entering the pledge information to be able to "add" or "insert row" for new transactions or some sort of that functionality if I can (but this might be my hangup and I may need to go a different direction). I've used LIDS before and tried to use this functionality in what I have already build for pledges, but not sure this can work if I want to use some of that data in other reports by specific member.
Pulling in the GL data from our ERP system doesn't have enough attributes to be able to identify the type of transaction it is or what fund the pledge is for if we just use the staging query data from the pledge accounts. But I could copy/paste this information into a new table to be able to save the data I need from the staging query into Vena.
If you have a template that does some of this functionality I can usually visualize and customize a template that will work for our specific needs. I have pulled a few templates that I thought has some of these pieces, but I think I'm missing how to build the members in the dimensions.
I appreciate any sugguestions.
I did some drafting between your reporting requirements and the data/dimensions available and it looks like the root issue is that you need some extra data in your report compared to what exists in your cube.
Comparing your dimensions and requirements, it would look to me that you would need another cube with items like Donor, Pledges, etc. Potentially you could share the dimensions in yellow, but the rest would need to be loaded in a cube for you to be able to report on them.
So in summary, since the data contains more dimensions than regular financial data; it would require an extra cube (or perhaps tables). This leads me to think that perhaps our NFP may be suitable for your case. If you don't have a NFP solution already, we could look at having you talk with one of our specialists in NFP.
Let me know if that helps.
Forum brought to you by Vena Solutions Inc.