Posted by More on the QRP pieces .. because the simplest results view with only one source does not do justice to the power we built into the engine.
QRP Pieces – the Formula override
If you have ever designed a view in a single database that has documents that have been created by different forms, you perhaps have encountered the problem of morphing view columns into a single set of like values, as well as how to order view entries in a way that users would understand and easily traverse.
This problem is much more pronounced when multiple databases contribute data to a single view. While those databases may well have similar or identical designs, they often do not. And though the relationship of data may be well known to the developers of the application, that relationship can also be an invention in itself.
As mentioned in the last post, the addCollection / addDominoQuery calls are the way to define the sources of data for the QueryResultsProcessor engine. The (Notes)DocumentCollection or (Notes)DominoQuery objects have single database context which is inherently sent to the QRP object. They each identify a set of documents where data is to be computed, and they each have a unique name. Also as mentioned, the addColumn call specifies a programmatic name, akin to the Designer beanie tab unique name (within a given view).
The addFormula call matches the addColumn programmatic name and the addCollection name and provides Formula Language to compute the value for the QRP view in that context. To give an example:
DominoQuery dql = srcdb.createDominoQuery();
QueryResultsProcessor qrp = m_DB.createQueryResultsProcessor();
String query = “@all”; // DQL query to find all documents
qrp.addDominoQuery(dql, query, “result1”); // “result1” is the name of the input collection
qrp.addColumn(“Year”, “Year Value”, “”,
QueryResultsProcessor.SORT_ASCENDING, false, true);
// Matches “Year” with the column name and “result1” with collection name
qrp.addFormula(“@Text(@Year(@Created))”, “Year”, “result1”);
The field “Year” may or may not even exist; the column name is used to compute data for that specific input collection. Now, if multiple collections are in play:
DocumentCollection doccol = db.FTSearch(“Field x contains \”interessantes Dokument\””);
qrp.addCollection(doccol, “result2”);
then the addFormula call above would not apply to that input collection, but another would have to be specified or a wildcard used to include both input collections:
qrp.addFormula(“@Text(@Year(@Created))”, “Year”, “result*”);
or the value for the Year field (which could be null) would be returned for the result2 input collection. Using the wildcard (*), databases where a single formula would compute the same values. For databases where the design is the same but data is partitioned by date, region or any other criteria, a single addFormula call across all input collections from those databases.
About aggregates
The QRP engine supports aggregate columns. These columns must follow immediately after a categorized column. Their syntax is:
// A categorized column
qrp.addColumn(“Year”, “Year Value”, “”,
QueryResultsProcessor.SORT_ASCENDING, false, true);
// Two aggregate columns
qrp.addColumn(“@@avg(salary)”, “Average Salary”, “”,
QueryResultsProcessor.SORT_UNORDERED, false, false);
qrp.addColumn(“@@sum(salary)”, “Sum of all Salaries”, “”,
QueryResultsProcessor.SORT_UNORDERED, false, false);
where salary is the name whose values are to be computed for both columns.
qrp.addFormula(“HourlyWage528”, “Salary”, “result*”);
Note – only @@avg and @@sum are supported using QRP views, @@max, @@min and @@count are available using QRP JSON. This is because Domino view APIs can produce those values automatically with the right sorting.
Aggregate processing will only function properly with numeric values. Non-numeric values produce unpredictable results. Also, values will be floating point, and rounding must be employed to make them render uniformly.
Where to create your QRP views
Due to the volatility of the data where views are built to expire and be deleted then created and populated on demand, it is recommended that you keep your QRP views in databases separate from production databases. Obviously, do what you want, and they can be created anywhere, but since their data has potentially many sources, a dedicated store would also maintain separation of data.
The executeToView call
In your code, the (Notes)QueryResultsProcessor object is created in the database in which you want the view to be built. All that remains is to call executeToView with a view name, the hours till expiration and a list of readers (groups or “/CN=*/O=*” for open access) you want to be able to open and use the view. QRP views do ONLY enforce security at the view level – that is, anyone granted access to a QRP view can open it and see its entries. Any attempt to open a document to which a user has no access will fail, no matter which source database contains that document.
Once the hours till expiration have lapsed, updall will automatically purge the view from the database. While 24 hours is the default for this value, you can keep a view around for a year with 8760 hours specified.
QRP views concepts and attributes
To review and expand, here are things to know about QRP views:
• Secured at the VIEW LEVEL – you pass in a readers list (or “/CN=/O=”)
• Hidden views
• Designed to expire and be purged automatically (you can override – 8760 hours is one year)
• Can contain data from any database given any criteria
• Can be stored in any database (recommend – create a dedicated QRP db)
• Have artificial NoteIDs that can be found in the view but nowhere else
• Have hidden column at the end to provide database path and original NoteID
• Support categorization, hidden columns, aggregates for average and sum
• From HCL Notes® and HCL Nomad® clients, will open the original document with double click
There will be more about the client interaction in the next instalment.
J D Curtis 