CRI reports should be easier to consume

[bjpatterson]

Description

The reports are a bit fat in their current state, which makes them hard to read. It has been suggested that we rework these for better ease-of-use.

Potential Tasks

• [x] FIPS codes can be removed from most/all reports (definitely the OCRA ones, at least)
• [x] Consider fancy angled headers to reduce column widths in downloadable reports
• [x] Split web-based report into two tables
• [x] Stack leadership and organizations sections vertically in separate sheets in downloadable reports
• [x] Instead of Presentation completion status, show the date (or specify that a community has opted out #50)
• [x] Put "recent activity" information in separate sheet
• [x] defDone: Existing reports are more informative and require ~no horizontal scrolling

[PhantomWatson]

The library that I'm using for generating spreadsheets has trouble with rotated text.

• If the rotation isn't -90, 0, or 90 degrees, the borders of those cells also rotate and become disconnected from the borders in other rows. We can do away with borders, but I think they make the spreadsheet easier to read.
• The merged cells that serve as headers for columns groups (PWRRR Alignment, Internal Alignment, Aware of Plan) aren't taken into account when automatic column width resizing takes place, so those cells get shrunk down too small to read their contents.

If we're committed to rotating this text, this could work:

• Header text rotated 90 degrees
• Column headers reworded
• "PWRRR Alignment" and "Aware of Plan" column group headers removed

[bjpatterson]

Two possibilities come to mind:

• I haven't looked too closely at PHPExcel, but might it be possible to use a pre-formatted template spreadsheet, and copy it to generate reports? If PHPExcel struggles with the angled text, maybe we could pre-angle the text. (If this approach works, it would also potentially allow laypeople to make report format edits without needing to code.)
• If the above approach doesn't work (or smells bad to you), I'm OK with the final example you showed, although I would rotate the text the other way. (It seems to read more intuitively for me that way.)

Ultimately though, the primary goal is for the report to fit horizontally on one screen's width. If we can get that with no rotation, then that's totally fine. (Bonus points if it can be printed out on the width of a single landscape page, but that just gets you brownie points with ICI.)

[PhantomWatson]

Actually, I think I misspoke. There's no trouble setting the text at whatever angle we want (the screenshots above came from spreadsheets generated by the website), but the awkward angling of the borders is unavoidable.

The column resizing issue is caused by this: If cells are merged across columns A and B, the content of those cells are ignored when column A is auto-resized or when column B is. However, if both columns are selected together and auto-resized, then the content of the merged cells is taken into account. But PHPExcel only supports auto-resizing one column at a time, as far as I can tell.

However, we can either

• manually set the width of these columns, or
• avoid using merged cells (like in the second screenshot) and keep all sizing automatic

[bjpatterson]

As many times as we've already reworked these reports, manual formatting smells pretty bad to me.

Avoiding merged cells is probably the best option for the internal report, despite making some of the headers a bit wordy (ugly but readable is fine for internal use).

For the external report, I'm less certain about a best policy (although it looks like the merged cells in that file aren't actually causing trouble right now.)

[PhantomWatson]

I think they're looking pretty good now. These spreadsheets can be printed out pretty legibly if they're scaled to fit on one sheet and rotated to landscape orientation. Splitting this up into multiple sheets made a huge difference.

Admin report: http://cri.cberdata.org/admin/reports/admin OCRA report: http://cri.cberdata.org/admin/reports/ocra