Combining/processing D4H reports

D4H comes with a powerful reporting capability to analyse your team data. Much of the data your organization has gathered in D4H is available via reports. That said, we have custom logic that we need to apply for our firefighter’s annual requirements.  We gather the firefighter’s fire training hours, their medical training hours, their station training hours and more. Each of these come from a separate D4H report. Eight of them. We combine the outputs from a number of D4H reports into one combined report – with our logic and our presentation – for ease of use by our firefighters. That takes a bit of processing…

We use the “Download to Spreadsheet” option on the report page (see below) which creates and downloads a CSV (comma separated variable) text file that can be easily processed by Excel (and other spreadsheet programs) or by scripting languages:

We download the output and rename the file as (say) fire.csv (with one named for each of the others.) We then process these files using a ruby script…

require 'csv';
# Fire
puts "---------------------------------------------------------------------- Fire"
CSV.foreach("fire.csv", :encoding => 'bom|utf-8') do |row|
    if $. > 1
        # Skipped header line...
        raw_name = row[1]     # Name in column 1
        name = raw_name.strip # Trimmed spaces from name
        fire_hours = ( row[5].to_f / 60 ) # Fire hours...
        ...

In order to speed the process of generating the report data … by removing the manual steps of selecting the report, selecting the date range (and not making a mistake), remembering to press ‘GO’ to re-run it with the date range, then downloading … we created a local HTML file called edit-to-download-reports.html with links to each report.

To download we (1) login to D4H in a browser (2) open the HTML file with links (anchor tags) for each of these, where you would replace:

1. The subdomain to your D4H subdomain
2. The report identifier (get it from the URL of the report)
3. The start and end dates. We typically have the start of year and the end of the month we are reporting to.

Extract the report ID from the end of the report web address:

https://{YOUR SUBDOMAIN}.d4h.org/team/reports/custom/view/{REPORT_ID}

Remember to edit the “endDate” if you are running these for (say) a new month:

https://{YOUR SUBDOMAIN}.d4h.org/team/oldexport/?category=reports_custom&format=csv&param%5Breport_id%5D={REPORT_ID}&param%5BstartDate%5D=2018-01-01&param%5BendDate%5D=2018-12-31

We’ve found that by using D4H reporting and then exporting to CSV file format we can extend the functionality to our custom needs.