Hello all!
We've been on Tess for several years now and always set up our campaigns to be tied to a single fiscal year. It's typically not an issue other than the odd unusual reporting request that can be met using one method or another. However, we have recently begun a large capital campaign to cover the costs of our renovation and expansion. This campaign will be spanning several fiscal years and has some very specific requests around it. We are investigating the option of having one campaign encompass the entirety of this endeavour but are not quite clear about the ramifications of that. Has/Does anyone do this and do you have any advice? I'll probably think of more questions as we explore this but initially my questions are:
1) I found the Help document a bit unclear, can you enter a fiscal year onto the campaign and then simply change the fiscal year to the next year at fiscal year end while keeping the same physical campaign? There was something on the website that implied this but it didn't go into detail so I may have been misreading it. 2) If the answer to #1 is yes, does that have any ill effect on historical data or reporting? If it is no, how do any contributions made get tied to a fiscal year for financial and general ledger purposes?3) What kinds of reports have been most helpful to you with these kinds of campaigns? I suspect we will either need a custom report or a series of dashboards to achieve the reporting requests that have been sent my way but I'd like to see what others are doing first.4) Do you have any words of wisdom or hindsight/gotcha's that you'd like to share about this method?
If you were in a similar boat and decided against the single multi-year campaign to stick with several tied to specific fiscal years, may I ask why you chose to do that? How do you handle reporting requests that span multiple fiscal years? The leadership team has been clear that they want to be able to run a report in Tessitura for any time frame they wish (could be 1 month, 1 year, or 10 years) that will give them total data without having to be manipulated in a spreadsheet afterwards. The out of the box reports all seem to break out data by fiscal year instead of simple totals. I do have the technical skills to write a report that will do what they want if I need to but I'm hoping to avoid the custom route if possible.
Thanks for any advice you might be able to provide!
Hi Beth,
Just following up on my colleague 's answer and your questions below:
1) You CAN change the fiscal year for your campaign at year's end, BUT the fiscal year is going to reflect whatever the current value is in the fyear column for that campaign in the T_CAMPAIGN table when you run your reports.
2) As a result, this is NOT going to give you accurate historical reporting when you run your reports (at least if you use the data straight out of Tessitura).
For accurate reporting of fiscal year, what I would do, and what I usually do on my custom reports, is create a new column for "Fiscal Year" that is actually calculated based on the contribution date. So for example, our fiscal year runs from 7/1 to 6/30, so to figure out what fiscal year a contribution was made in, I would write a CASE evaluation that looks at the month of the contribution date, and then evaluates what the fiscal year should be. For example:
CASE WHEN month(cont_dt) >=7 then (year(cont_dt) + 1) ELSE year(cont_dt) END AS fiscal_year
Does this make sense? As for your question about where the contribution is recorded on the general ledger, it might depend on the software you are using, but for us everything is divided up according to the contribution date. The GL lines stay the same across years (unless you decide to create new lines/funds for each year) and reporting is done based on a selected date range.
3) The canned report that will probably be the biggest help to you is the Fund Activity Report. This report allows you pull data based on a number of factors, including transaction and/or contribution dates, campaign, fund, fiscal year, and more (although again, if you are manually changing the fiscal year every year, then the report is going to report against what the current value is). Where the fiscal year comes in handy, is when you have a single fund that is used across multiple annual campaigns. If you have one campaign though that spans multiple years, the fiscal year parameter probably won't be much help since you're only working with one campaign and one fund.
Hope this all makes sense. Feel free to reach out to me at drosen@coloradosymphony.org if you have any questions, or would like help/ideas for creating a custom report.
Good luck!
David