Tech Blog

Analytics API and filters: how to send complex sql filters that work

In all example links below change {yourApiKey} and {yourPath}  to your real API key and Analytics report path.

As described in Working with Analytics REST APIs and How to use an API to send filters and retrieve an Alma Analytics report in 5 easy steps you can get reports sending filters as part of the API and then retrieve the Alma Analytics report with those filters: it is useful when you need to get via API different results using the same saved analysis.

The example filters given in the tech blogs are basic (“column” operator “value”) and they are built using the Analytics gui: what happen if we need to build a more complex filter using SQL? Let’s try a simple case.

In the old forum discussion from 2014 Case-Insensitive Filter for Alma Analytics API? there was a need of a case insentive text filter to find any occurrence of the word “biology” in the subject field in whatever way it was written; the SQL filter to apply was:

UPPER("Bibliographic Details"."Subjects") LIKE '%' || UPPER('biology') || '%'

and the relative XML expression appearing in advanced analysis XML textbox is :

<saw:filter><sawx:expr xsi:type="sawx:sql">UPPER("Bibliographic Details"."Subjects") LIKE '%' || UPPER('biology') || '%'</sawx:expr></saw:filter>

so the namespaced XML to use as filter in API should be:

<sawx:expr xsi:type="sawx:sql" xmlns:saw="com.siebel.analytics.web/report/v1.1″ xmlns:sawx="com.siebel.analytics.web/expression/v1.1″ xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">UPPER("Bibliographic Details"."Subjects") LIKE '%' || UPPER('biology') || '%'</sawx:expr>

and this should be the encoded API url:

https://api-eu.hosted.exlibrisgroup.com/almaws/v1/analytics/reports?path={yourPath}&limit=1000&apikey={yourApiKey}&filter=%3Csawx%3Aexpr%20xsi%3Atype%3D%22sawx%3Asql%22%20xmlns%3Asaw%3D%22com.siebel.analytics.web%2Freport%2Fv1.1%22%20xmlns%3Asawx%3D%22com.siebel.analytics.web%2Fexpression%2Fv1.1%22%20xmlns%3Axsi%3D%22http%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema-instance%22%20xmlns%3Axsd%3D%22http%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema%22%3EUPPER%28%22Bibliographic%20Details%22.%22Subjects%22%29%20LIKE%20%26apos%3B%25%26apos%3B%20%7C%7C%20UPPER%28%26apos%3Bbiology%27%26apos%3B%29%20%7C%7C%20%26apos%3B%25%26apos%3B%3C%2Fsawx%3Aexpr%3E

but if you try it the filter seems not to be applied: you obtain also records with no word biology in the subject field as reported in the discussion forum.

The reason is the API needs a filter prompted saved in the report to replace it with the XML filter, but you cannot define a prompted filter using directly SQL.

The solution is to modify the filter formula: add a new filter for the subject column and edit its formula clicking on “fx”:

Modify the filter formula adding the upper function:

complete the filter adding “contains any” operator and the word BIOLOGY (in upper case) and save it:

In the tab advanced the XML filter expression is:

<saw:filter>
<sawx:expr xsi:type="sawx:list" op="containsAny">
<sawx:expr xsi:type="sawx:sqlExpression">UPPER("Bibliographic Details"."Subjects")</sawx:expr>
<sawx:expr xsi:type="xsd:string">BIOLOGY</sawx:expr>
</sawx:expr>
</saw:filter>

So the xml expression to use in API should be:

<sawx:expr xsi:type="sawx:list" op="containsAny" xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:sawx="com.siebel.analytics.web/expression/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <sawx:expr xsi:type="sawx:sqlExpression">UPPER("Bibliographic Details"."Subjects")</sawx:expr> <sawx:expr xsi:type="xsd:string">BIOLOGY</sawx:expr></sawx:expr>

Before using the API change the filter operator in “Is Prompted”:

This is the API encoded url to use:

https://api-eu.hosted.exlibrisgroup.com/almaws/v1/analytics/reports?path={yourPath}&limit=1000&apikey={yourApiKey}&filter=%3Csawx%3Aexpr%20xsi%3Atype%3D%22sawx%3Alist%22%20op%3D%22containsAny%22%20xmlns%3Asaw%3D%22com.siebel.analytics.web%2Freport%2Fv1.1%22%20xmlns%3Asawx%3D%22com.siebel.analytics.web%2Fexpression%2Fv1.1%22%20xmlns%3Axsi%3D%22http%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema-instance%22%20xmlns%3Axsd%3D%22http%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema%22%3E%3Csawx%3Aexpr%20xsi%3Atype%3D%22sawx%3AsqlExpression%22%3EUPPER%28%22Bibliographic%20Details%22.%22Subjects%22%29%3C%2Fsawx%3Aexpr%3E%3Csawx%3Aexpr%20xsi%3Atype%3D%22xsd%3Astring%22%3EBIOLOGY%3C%2Fsawx%3Aexpr%3E%3C%2Fsawx%3Aexpr%3E

and this is an extract from the results, where you can see the world “biology” in the column 1 (the subject column) is retrieved despite its case (“biology”, “Biology”):

[...]
<Row>
<Column0>0</Column0>
<Column1>
Computational intelligence.; Artificial intelligence.; Bioinformatics.; Computational Intelligence.; Artificial Intelligence.; Computational Biology/Bioinformatics.
</Column1>
<Column2>
11th International Conference on Practical Applications of Computational Biology & Bioinformatics [electronic resource] / edited by Florentino Fdez-Riverola, Mohd Saberi Mohamad, Miguel Rocha, Juan F. De Paz, Tiago Pinto.
</Column2>
</Row>
<Row>
<Column0>0</Column0>
<Column1>
Bioinformatics--Congresses.; Medical informatics--Congresses.; Computational biology--Congresses.
</Column1>
<Column2>
2009 IEEE International Conference on Bioinformatics and Biomedicine : 1-4 November 2009.
</Column2>
</Row>
<Row>
<Column0>0</Column0>
<Column1>
Bioinformatics--Congresses.; Human genome--Congresses.; Computational biology--Congresses.
</Column1>
<Column2>
2009 International Joint Conference on Bioinformatics, Systems Biology and Intelligent Computing : 3-5 August 2009.
</Column2>
</Row>
[...]

In short to apply complex filters requiring elaboration in column data you cannot use SQL but you have to modify the filter formula like the left part of the SQL expression from the operator and save it with operator “Is prompted”; in the XML use the appropriate operator (e.g. for LIKE use op=”containsAny”) and copy the formula in the “sawx:sqlExpression” part of the XML filter; if the right part of the SQL expression (the data part) contains any other formula this should be calculated from the script and added in the xsd:* part of the XML filter.

Referring to the case described above, if the subject string is obtained from a PHP script you need to ensure that it is in upper case:

<?PHP
[...]
$subject = strtoupper($subject);
$filterXML = <<<HDC
<sawx:expr xsi:type="sawx:list" op="containsAny" xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:sawx="com.siebel.analytics.web/expression/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><sawx:expr xsi:type="sawx:sqlExpression">UPPER("Bibliographic Details"."Subjects")</sawx:expr><sawx:expr xsi:type="xsd:string">$subject</sawx:expr></sawx:expr>
HDC;
[...]
?>

You can try more complex filter, e.g. the ones described in Alma Analytics SQL Filter Examples  or Alma Analytics Regular Expression Examples.

2 Replies to “Analytics API and filters: how to send complex sql filters that work”

  1. Bless you for this timely post. I actually had tried to do this earlier and had to leave off. So, my users (downstream application developers) will be able to have a customized search by barcode or call number which normally the Catalog API does not do.

Leave a Reply