Dashboard or Report: constituency over time

Patrick Edwards · July 2015

I'm trying to find a Dashboard (or Report) which can accomplish the following:

Show me a count of constituents with a specific constituent code over different specified periods.

Implied here is that all constituents with the code will be counted (even those with no Date From or Date To) so long as those constituents do NOT have a Date From or Date To which falls before or after the period (respectively).

What am I missing? Why is this basic task of "Monitoring Constituency" apparently so difficult to accomplish?

Thanks.

Patrick Edwards · July 2015

So for example:

Records

Constituent A - Code X - From: blank - To: blank

Constituent B - Code X - From: 1/1/13 - To: blank

Constituent C - Code X - From: 3/10/15 - To: 4/25/15

Constituent D - Code X - From: 6/27/15 - To: blank

2015 Report

Count of Constituent Code X

Jan - 2

Feb - 2

Mar - 3

Apr - 3

May - 2

June - 3

July - 3

John Heizer · July 2015

For me the big factor on this is fuzzy dates used by Constituency Codes. I had to create a Custom Report with Crystal to get accurate results because the fuzzy date functions in RE will translate all fuzzy dates of MM/YYYY into MM/01/YYYY, but I want Date From to be MM/01/YYYY and Date To to become MM/last day of month/YYYY. Even worse RE will always translate YYYY into 01/01/YYYY.

Trying to compare a range of dates (an entire month as in your example) instead of one particular date makes it even more complex.

Patrick Edwards · July 2015

John, I see your point.

For my specific need here, I'd settle for consistency in how fuzzy months or fuzzy years are handled. It appears from what you've said that at least there is consistency. I agree that all fuzzy dates should not default to earliest, and would suggest the most common sense solution is for Date From to interpret as the latest possible and Date To to interpret as the earliest possible. Though I could see cases (as with yours) where being able to toggle the interpretation when applicable would be helpful.

However, having to create a custom report or export for analysis in Excel begs the question for me: Date From and Date To simply do not provide for very basic reporting of constituency over time.

Joshua Bekerman bCLO bCRE · July 2015

I don't have a solution other than a custom report in Crystal Reports, as John mentioned. I am wondering though if it would help us to know your specific constituent codes and the purpose of reporting on constituent codes by month. I realize everyone uses constituent codes differently so maybe knowing the end result would help find a solution or work-around.

Thanks.

Patrick Edwards · July 2015

Josh,

Sure. I've laid it out in a way that I think the context is really unnecessary, and would welcome some examples of constituency codes for which any organization might not benefit from this basic report of constituency over time.

Context: In this case we're using the code "Community Member" for anyone who has an account in our online community (which is housed in a separate database) with From and To dates (account created and account ended) brought in to RE along with lot's of other data via ImportOmatic.

The purpose of the dash/report is for the staff to have dynamic monitoring of our community. I don't have more of an end to that statement, such as "...so that the staff can accomplish X task and Y task." Nevertheless, it seems a rather large assumption to say "the only period which really matters is the one that ends today". Comparison and Summary report seems to offer a solution, since it has the ability to count constituency during specific periods; but the results are not correct and I do not know why.

When I was looking at the Dashboard "Constituents (Custom)" under the Metrics reports which are apparently included with NetCommunity, I expected that one of the four "series" queries could accomplish this since we are given the option there to set the count periods—however, the "hidden" criteria with that dash appears to be that it can only count based on "Date added", not constituency date. The hope of course was to build on this periodic monitoring with different slices (for example, Global Region which we source from the Preferred Country) by including other criteria in the initial query.

So back the decision of why Community Member is tracked this way? Obviously Constituent Code seemed like the perfect place for any information which requires tracking a constituent's fundamental relationship to our org, particularly when that relationship changes over time and the specifics of that history will continue to be important. I do see that for any staff member who happens to open the constituent record, constituent code is doing its job in being the most intuitive and inviting place for "non-destructive interaction" by the person opening that record. Beyond that, I'm baffled that this basic (even implied) reporting functionality is missing from "the can" !

Am I missing something? Crystal Reports or bust

Patrick

Patrick Edwards · July 2015

With regards to building this in Crystal (or Excel), any tips on how to accomplish the relative reporting periods?

It's one thing to define a column as "January 2015" and ensure that the results count any date from 1/1/15 to 1/31/15, it's another to define a column as "[Name] [Year] for the month that was 6 months prior to the report date" and expect that when reporting on 7/7/15 the results will show any date from 1/1/15 to 1/31/15... but it seems that a "sustainable" report with any sizable number of periods would need to built in a way that those columns do not constantly require "redefining".

Joshua Bekerman bCLO bCRE · July 2015

Hi Patrick. Our constituent codes are Individual, Corporation, Foundation, Estate, CFC, United Way, etc. These are based on our reporting needs, mostly financial. They don't change over time and we usually don't use the Date To.

I would have to look up the exact formulas (or John might beat me to it), but there is a way in Crystal Reports to have dynamic date ranges based on "today's date."

Patrick Edwards · July 2015

Hi Josh!

Thanks of for the insights. I'll have to sit with those codes for a bit a think about the value of reporting Foundation constituents, for example, year over year and how I might desire to correlate that pool of "opportunity" to either past performance or future fundraising goals. Obviously when reporting gifts we don't need to resort to Crystal Reports for the past performance, and depending on how those contacts are coded as prospects (supposing you're using those features) the opportunity could be sufficiently pictured there.

Thanks as well for the assurance of those formulas in CR, I will look them up when I get there.

Patrick

James Andrews · July 2015

Patrick Edwards:

With regards to building this in Crystal (or Excel), any tips on how to accomplish the relative reporting periods?

It's one thing to define a column as "January 2015" and ensure that the results count any date from 1/1/15 to 1/31/15, it's another to define a column as "[Name] [Year] for the month that was 6 months prior to the report date" and expect that when reporting on 7/7/15 the results will show any date from 1/1/15 to 1/31/15... but it seems that a "sustainable" report with any sizable number of periods would need to built in a way that those columns do not constantly require "redefining".

Assuming you can solve your problems with redefining the fuzzy date fields as actual dates, dateadd formulas are what you're looking for. You'd set up a parameter to be the "As Of" date, and then set up formulas for six months before, a year before, whatever. You can then use those formulas in other formulas when you're testing for dates.

Dashboard or Report: constituency over time

Comments

Categories