Snippet: Building a Key-Value Pair Report Page

  • By Michael Garrison 2 Years Ago

Think of this as part 2 of the Benevolence Record Keeping / Attribute Display Customization post - although Rock v4 has been released, our benevolence team decided that the out-of-the-box benevolence tools were overkill for our needs at this time and so we're still using the tools I outlined in that post.

What they have decided they needed though, was a report for easily evaluating how much of their budget (monthly, yearly, whatever) they've already spent. It's easy to visit a person's profile and see what they've been given, but not so easy to get a report on everyone to whom benevolence has been granted (data views do not presently include the ability to filter based on key-value list attributes, at least not that I can find).

Remember that we were using "date" as the prompt for the key, and "amount" as the prompt for the value. Remember also that Rock isn't actually storing the data in specific datatype columns- rather it simply stores the data in a delimited string.

Assuming that the report we are creating should be able to have a dynamic starting and ending data parameter to filter on, we're going to have to do some fancy manipulation of the data, which may be in lots of different formats depending on how the users entered it (YYYY-MM-DD, MM/DD/YYYY, 05 Aug 2015, just to name a few).

Start off by creating a new page on which to view the report. Check Rock's CMS manual for more information on this process, or an earlier post where we walked through the process.

Once you've got your page created, add an HTML block at the top, and a Dynamic Data block below it. The HTML block will have very similar contents as the we have used before, wherever we want to provide a beginning and ending date parameter input. The only difference is that this time we'll have the block default to a starting date of the beginning of the current month, rather than Jan 1st of the current year.

<style>  
span.DateControl {  
    border:1px solid black;
    background-color:#fff;
}
span.DateControl input {  
    border:0px;
    background:transparent;
    text-align:center;
}
</style>

<div style="display:inline-block;padding-right:4em;">Beginning date: (MM/DD/YYYY)<br />  
    <span class="DateControl">
        <input type="number" id="iMMstart" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iDDstart').focus();},125)}" ></input>/<input type="number" id="iDDstart" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iYYYYstart').focus();},125)}" ></input>/<input type="number" id="iYYYYstart" maxlength="4" style="width:3em;" onFocus="this.select()" onKeyUp="if (this.value.length==4) {setTimeout(function(){ document.getElementById('iMMend').focus();},125)}"></input>
    </span>
</div>  
<div style="display:inline-block;padding-right:4em;">Ending date: (MM/DD/YYYY)<br />  
    <span class="DateControl">
        <input type="number" id="iMMend" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iDDend').focus();},125)}" ></input>/<input type="number" id="iDDend" maxlength="2" style="width:2em;" onFocus="this.select()" onKeyUp="if (this.value.length==2) {setTimeout(function(){ document.getElementById('iYYYYend').focus();},125)}" ></input>/<input type="number" id="iYYYYend" maxlength="4" style="width:3em;" onFocus="this.select()"></input>
    </span>
</div>  
<div style="display:inline-block;"><input type="submit" class="btn btn-primary" value="View" onClick="window.location.href=window.location.pathname + '?start=' +  
                                                                                        document.getElementById('iYYYYstart').value+
                                                                                        document.getElementById('iMMstart').value+
                                                                                        document.getElementById('iDDstart').value+
                                                                                                                '&end=' +
                                                                                        document.getElementById('iYYYYend').value+
                                                                                        document.getElementById('iMMend').value+
                                                                                        document.getElementById('iDDend').value;"></input>
</div>

<script>  
var params = {};  
if (location.search) {  
    var parts = location.search.substring(1).split('&');

    for (var i = 0; i < parts.length; i++) {
        var nv = parts[i].split('=');
        if (!nv[0]) continue;
        params[nv[0]] = nv[1] || true;
    }
}
if (params.start && params.end) {  
    document.getElementById('iDDstart').value=params.start.substring(6,8);
    document.getElementById('iMMstart').value=params.start.substring(4,6);
    document.getElementById('iYYYYstart').value=params.start.substring(0,4);
    document.getElementById('iDDend').value=params.end.substring(6,8);
    document.getElementById('iMMend').value=params.end.substring(4,6);
    document.getElementById('iYYYYend').value=params.end.substring(0,4);
    document.getElementById('iMMstart').focus();
}
else {  
    var today=new Date();
    window.location.href=window.location.pathname + '?start=' +
                                        today.getFullYear().toString() + 
                                        ((today.getMonth()+1) < 10 ? '0' + (today.getMonth()+1) : (today.getMonth()+1)).toString()
                                        + '01' +
                                                    '&end=' +
                                        (today.getFullYear()).toString() +
                                        ((today.getMonth()+1) < 10 ? '0' + (today.getMonth()+1) : (today.getMonth()+1)).toString() +
                                        ((today.getDate()) < 10 ? '0' + (today.getDate()) : (today.getDate())).toString();
}
</script>  

Next, we're going to have to find the AttributeId number of our Key-Data attribute. There's probably a better way to do this, but honestly the quickest way I came up with was to store a "benevolence granted" record on someone with a searchable string, or else to SELECT TOP 10 * FROM [AttributeValue] ORDER BY [Id] DESC right after you create someone's first benevolence granted request on their profile and find the row with the data you entered. Then look for the Attribute Id column and take note of the number. In my case it was 1612- I'll use that in my example below.

Now the actual query to return the data is really short- it's not as easy to use a SQL query to break up the data like we need so we'll just return it as raw data and process it all in JavaScript.

SELECT av.[EntityId] AS 'Id', (p.[NickName] + ' ' + p.[LastName]) AS [Name], av.[Value] FROM [AttributeValue] av LEFT JOIN [Person] p ON p.[Id]=av.[EntityId] WHERE av.[AttributeId] = 1612 AND av.[Value] <> '' AND av.[Value] IS NOT NULL  

Now jump down to the "Formatted Output" box. Let's step through this one since it's the first time we've done this type of a query.

We start off by creating an empty DIV into which we'll later inject our formatted data using JQuery. And we create variables to hold a few pieces of data: the total dollar amount from the data meeting our filter, a string to contain any errors about non-translatable dates (more on that later), the filtering start date and end dates based on the page's URL (the JavaScript for which is already contained in the HTML date block higher on the page), and finally the string that will contain the data to display:

<div id="theoutput"></div>  
  <script>
    var total=0;
    var error="";
    var startDate = new Date(params.start.substring(4,6) + "/" + params.start.substring(6,8) + "/" + params.start.substring(0,4));
    var endDate = new Date(params.end.substring(4,6) + "/" + params.end.substring(6,8) + "/" + params.end.substring(0,4));
    var content="<table class=\\"grid-table table table-bordered table-striped table-hover\\"><tr style=\\"border-bottom:1px solid black;\\"><th>Name:</th><th>Date:</th><th>Amount</th></tr>";

Each row returned by the SQL query is a person who received benevolence, at any time. It's formatted like so: key1^value1|key2^value2|...|keyn^valuen|, so since we want to show a line item for each specific "benevolence granted" event, we split these strings at | so we can loop through a series of individual key^value strings. Then we loop through those data pairs and format them to get them into a useable format (specifically, one that can be programmatically compared or calculated).

    {% for row in rows %}
      var currperson="{{ row.Value }}";
      currperson=currperson.split("|");
      for (i=0; i < currperson.length; i++) {
        if (currperson[i].length>0) {
          currvalue=currperson[i].split("^");

By splitting each pair in turn at the ^ symbol, we now get to deal with one array at a time where element [0] is the key (in our case, the date) and element [1] is the value (in our case, the amount). Now, since it's easy to enter a date format that the computer doesn't understand, and in order to display a helpful error message about such values, we store the original date data in the variable reference before using JavaScript to attempt to parse element [0] into an actual JavaScript date and a regular expression to strip all non-decimal characters out of element [1] (numerals, a decimal point and a negative sign, just in case).

          reference=currvalue[0];
          currvalue[0]=new Date(currvalue[0]);
          currvalue[1]=Number(currvalue[1].replace(/[^\\d.-]/g, ''));

JavaScript only does an okay job at making a date from a string of text, so now we apply some reality checks to make sure that the resulting date isn't before 2015 (our launch of Rock) or in the future. If the resulting date is either unusable or an unlikely date, we'll store an error message about it to display later:

          if (currvalue[0]<new Date("01/01/2015") || currvalue[0]>new Date() || !(currvalue[0] instanceof Date && !isNaN(currvalue[0].valueOf()))) { error+="<br /><i class=\\"fa fa-exclamation-triangle\\" style=\\"color:red;\\" title=\\"" + reference + "&#10;" + currvalue[0] + "\\"></i> There appears to be an error with one of the dates in <a href=\\"/Person/{{ row.Id }}/ExtendedAttributes\\">{{ row.Name }}'s</a> benevolence record"; }

If we get this far, we seem to have good data. So check to make sure that the particular data we're working with right now falls within the user-defined date range. If so, add the amount to the running total variable we have, and store the information about this particular benevolence transaction for display in content:

          if (currvalue[0] >= startDate && currvalue[0] < endDate) {
            total+=currvalue[1];
            content+= "<tr><td><a href=\\"/Person/{{ row.Id }}\\">{{ row.Name }}</a></td><td>" + currvalue[0].toDateString() + "</td><td>$" + currvalue[1] + "</td></tr>";

Now close up all of our reality checks and loops before writing out the sum of all of the transactions which fell within our filter:

          }
        }
      }
    {% endfor %}
    content+= "<tr><td colspan=2>Total:</td><td>$" + total + "</td></tr></table>" + error;

Lastly, we'll warn about any benevolence records which had dates we couldn't use in the filtering process- don't want to miss any!

    $("#theoutput").html(content);
  </script>

It might have taken four languages to extract these two pieces of data, but we've made it!

All together, the Formatted Output should contain this:

<div id="theoutput"></div>  
  <script>
    var total=0;
    var error="";
    var startDate = new Date(params.start.substring(4,6) + "/" + params.start.substring(6,8) + "/" + params.start.substring(0,4));
    var endDate = new Date(params.end.substring(4,6) + "/" + params.end.substring(6,8) + "/" + params.end.substring(0,4));
    var content="<table class=\\"grid-table table table-bordered table-striped table-hover\\"><tr style=\\"border-bottom:1px solid black;\\"><th>Name:</th><th>Date:</th><th>Amount</th></tr>";
    {% for row in rows %}
      var currperson="{{ row.Value }}";
      currperson=currperson.split("|");
      for (i=0; i < currperson.length; i++) {
        if (currperson[i].length>0) {
          currvalue=currperson[i].split("^");
          reference=currvalue[0];
          currvalue[0]=new Date(currvalue[0]);
          currvalue[1]=Number(currvalue[1].replace(/[^\\d.-]/g, ''));
          if (currvalue[0]<new Date("01/01/2015") || currvalue[0]>new Date() || !(currvalue[0] instanceof Date && !isNaN(currvalue[0].valueOf()))) { error+="<br /><i class=\\"fa fa-exclamation-triangle\\" style=\\"color:red;\\" title=\\"" + reference + "&#10;" + currvalue[0] + "\\"></i> There appears to be an error with one of the dates in <a href=\\"/Person/{{ row.Id }}/ExtendedAttributes\\">{{ row.Name }}'s</a> benevolence record"; }
          if (currvalue[0] >= startDate && currvalue[0] < endDate) {
            total+=currvalue[1];
            content+= "<tr><td><a href=\\"/Person/{{ row.Id }}\\">{{ row.Name }}</a></td><td>" + currvalue[0].toDateString() + "</td><td>$" + currvalue[1] + "</td></tr>";
          }
        }
      }
    {% endfor %}
    content+= "<tr><td colspan=2>Total:</td><td>$" + total + "</td></tr></table>" + error;
    $("#theoutput").html(content);
  </script>

Now save the Dynamic Data block configuration and reload the page. You should see it automatically fill in a reasonable date range and display the records which fall between the dates you specified, plus any warnings you may get about invalid dates.

As usual, here's a screenshot of the completed report:
Benevolence Report

And remember, although I'm using data from a benevolence tracking process (which you're probably not), this can be used to report on any key-value person attributes. You'll have to handle your own data processing if you're not using dates and currencies as I am, but hopefully other data types are easier than these two and this serves as a good model.

Happy coding!


@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!