RME Reports with filtered features

[philipbaileynar]

Background

The current RME report includes all records in the database. It's common that users will want a subset of the data, such as filtered to just perennial streams (that flow year round), or that exist on certain land ownership (e.g. government) or both. This is easily achieved using SQL WHERE clause to filter the data.

To make this clean and simply we will try a new strategy whereby we produce multiple reports that are complete duplicates of each other, except that each uses a different WHERE clause to filter to a subset of records.

Solution

We will start with the following reports:

Report Title	Report HTML File	WHERE clause
All Records	rme.html	none
Perennial Streams	rme_perennial.html	FCode IN (46006, 55800)
Public Lands	rme_public_lands.html	rme_dgo_ownership = 'BLM'
Public Perennial	rme_public_perennial.html	(FCode IN (46006, 55800)) AND (rme_dgo_ownership = 'BLM')

The idea is that the method that generates a report can have an optional where_cluse parameter that takes one of the clauses listed above. Then in any code within the report that select or groups records it optionally appends this WHERE clause. For example.

SELECT blah, blah, blah
FROM blah blah blah
WHERE (FCode IN (46007, 33400)) AND (rme_dgo_ownership = 'BLM')
ORDER BY blah

Implementation Details

Provide each report with a meaningful file name (see above). Note that you can reuse the file name (without the extension) to separate the PNG graphics for each report.

Within the project.rs.xml file you will need to duplicate the HTMLFile tag that references the report:

<HTMLFile id="REPORT_ALL">
    <Name>RME Report</Name>
    <Path>outputs/rme.html</Path>
</HTMLFile>
<HTMLFile id="REPORT_PERENNIAL">
    <Name>RME Perennial Streams Report</Name>
    <Path>outputs/rme_perennial.html</Path>
</HTMLFile>
<HTMLFile id="REPORT_PUBLIC_LANDS">
    <Name>RME Public Lands Report</Name>
    <Path>outputs/rme_public_lands.html</Path>
</HTMLFile>
<HTMLFile id="REPORT_PUBLIC_PERENNIAL">
    <Name>RME Public Perennial Report</Name>
    <Path>outputs/rme_public_perennial.html</Path>
</HTMLFile>

Backwards Compatibility

It's important that this feature doesn't break any other types of reports (BRAT, tauDEM etc). That said, the ability to feature features might be extremely useful for those other types of reports.

FYI @MattReimer @KellyMWhitehead @jtgilbert @joewheaton

[jtgilbert]

Two things. FCodes weren't right, so I updated them. Second, "public lands" should technically include USFS and Parks and USFWS etc. so we should either do that report also, or just stick with what's listed here, but call it explicitly BLM instead of public lands.

[philipbaileynar]

Thanks @jtgilbert

What's your preference, all public, BLM or both?

[jtgilbert]

I think, given that our biggest audience right now is BLM, we do that. But we should keep the report script general so that we could go back and change the parameters to run it for whatever ownership is requested.

[philipbaileynar]

@r-k-g can you

1. confirm that the way you wrote the code is to take a literal string for the WHERE clause, such that we can tweak it and add other report permutations as needed....
2. Please change the public lands report file name and title to to to rme_blm_lands.html per @jtgilbert request.

[r-k-g]

The requested changes have been made. I updated the code such that it needs to be changed in two places to add new report permutations (not convenient to only have one, due to needing to update the project XML). The filter is a string placed in the WHERE clause of an SQL query.

[joewheaton]

Thanks @jtgilbert

What's your preference, all public, BLM or both?

Both and by other public agencies individually.

[philipbaileynar]

Let's produce a "Perrenial" (FCode 46006, 55800) for the following ownership:

• All public agencies. i.e. All ownership except
  • PVT
  • UND
  • None
  • BIA
  • LG
• Select individual public agencies
  • USFS and 4USFS
  • BLM
  • NPS
  • ST
  • FWS