Making a report of people with birthdays this month

  • By Michael Garrison 11 Months Ago

So you've gotten into Rock, you understand Data Views and Reports and you're feeling good about life. Then your Outreach Pastor comes and asks you to make a report showing everyone with birthdays in the current month.

"No problem" you think, and you proceed to make a new DataView and start searching for the Person Attribute of Birth Month

DV-BirthMonth.JPG
There it is! "I love this system" you think, and proceed to look for the "Current Date" option.

Then you discover it: there isn't one. (If there was, you'd be asking for a Birth Month equal to {{ 'Now' | Date:'yyyy-MM-dd' }}, which wouldn't find anyone, since what you REALLY want is Birth Month equal to {{ 'Now' | Date:'M' }})

So you go ahead and hard-code the month and set up a report using this Data View, to get it working for now. But moving forward you need to know how to tell your users to utilize this report. OK, so you have a few options (without resorting to building a SQL query in a Dynamic Data block):

  1. Set up this DataView with the month hard-coded, and set a reminder to yourself to come in and change the value every month manually

    -OR-

  2. Set up a Dynamic Report block on a page of it's own, and have the users change the month when they run the report. (In this case you may want to leave the month blank in the Data View, as that value sets a default for the Dynamic Report)

Usually people go with the latter- it's clean enough and clear to the users what's going on

DR-Default.JPG

All they have to do is change the month to 1 for January, 2 for February, etc, and hit "Filter". Good to go!

Sure.

But wouldn't it be nice to have it "just work"? So when they load this page it ALWAYS defaults to the current month?

Well, you're in luck: that's the whole reason for this article.

Updated!
There was an old version of this article which involved a LOT of javascript and manual work, and as it turned out, would sometimes cause an infinite refresh loop. There's a much easier way to do this, documented below, but click here if you really, really want to see the original article.

Step 1: Get the filter ID

Start by getting into the block configuration (the screen where you tell the Dynamic Report block which fields are visible, editable, and able to be toggled). You'll see that each filter from your DataView has a bit of data called "Filter". Sometimes this is clear enough- something like Property_FirstName and sometimes it's really obtuse with a lot of random characters. Either way, this is the ID we need that will let us do magic. In this case, your filter probably looks like this:

DynamicReport-Filter.png

Step 2: Configure the redirect

Copy that filter name that I circled, and now get into "Advanced Settings" of the Block Properties dialogue. If, like my example, your filter is Property_BirthMonth, then just copy and paste this code into Pre-HTML:

{% unless PageParameter.Property_BirthMonth %}
    {% capture url %}{{ 'Global' | Page:'Url' }}?Property_BirthMonth={{ 'Now' | Date:'M' }}{% endcapture %}
    {{ url | PageRedirect }}
{% endunless %}

If you've got a different string for a filter, then just replace the two places I marked the text in red with your actual filter, and you should be good to go.

The effect is that, if the filter is already set in the URL, nothing will happen. So your users can change the value all they want to get reports for other months. But when the page first loads, it's going to just have something like /page/123 as the address, in which case this Lava will notice that there's no filter, and provide the current month. Nifty, huh?


@mikejed
Spark Development Network
Flagstaff, AZ

Michael Garrison recently left his job in Architecture to become one of the "new guys" at Spark (the "Core Team"), but he's still helping out Christ's Church of Flagstaff and other non-profits with tech needs in his off-hours, trying to make computers do what computers do best, so that people are freed to do what we do best: relate with people!