[Anonymous]

I’m making an Analytics API script that filters results by subject, following the directions here: https://developers.exlibrisgroup.com/blog/Working-with-Analytics-REST-APIs

The problem: I’m finding that the filter is case sensitive, whether I use containsAny, LIKE, or other variants. A subject filter for “Biology” will return a different set of results than a filter for “biology” (which returns records for Molecular biology, Microbiology, etc., but misses Biology itself).

Is it possible to make a case-insensitive filter?

[Anonymous]

Perhaps the direction suggested here is helpful, i.e. if you normalize your filter’s search conditions you might get what you need. In theory, your expression would look something like this:

Code:

<sawx:expr xsi:type=”sawx:sql”>UPPER(“Bibliographic Details”.”Subject”) LIKE ‘%’ || UPPER(‘Biology’)|| ‘%'</sawx:expr>

If you create a test app and share its key, and include your report’s path and filter details, we (or other OBI experts in the community) might be able to offer futher help further if necessary.

Opher

[Anonymous]

I’ve tried the suggested SQL filter, but the API returns a general error, even though the filter is the same as if I apply it directly in Analytics and check the XML.

Filter (with namespaces, though the result is the same without):
<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&#8221; xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>UPPER(“Bibliographic Details”.”Subjects”) LIKE ‘%’ || UPPER(‘biology’) || ‘%'</sawx:expr>

API Key: l7xxbb2a378ea6354fff83a0a3b0113318c3

Encoded URL:
https://api-na.hosted.exlibrisgroup.com/almaws/v1/analytics/reports?path=%2Fshared%2FPortland%20Community%20College%2FReports%2FPCC%20New%20Books&apikey=l7xxbb2a378ea6354fff83a0a3b0113318c3&limit=50&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%27%25%27%20%7C%7C%20UPPER%28%27biology%27%29%20%7C%7C%20%27%25%27%3C%2Fsawx%3Aexpr%3E

The result:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<web_service_result xmlns=”http://com/exlibris/urm/general/xmlbeans”&gt;
<errorsExist>true</errorsExist>
<errorList>
<error>
<errorCode>GENERAL_ERROR</errorCode>
<errorMessage>A Gateway error has occurred – Make sure you add an appropriate apikey as a parameter or a header.</errorMessage>
</error>
</errorList>
</web_service_result>

Note: The API key is not the source of the problem, because using a standard LIKE filter (xsi:type=”saw:list” op=”like”) with the same key, path, and limit returns results. https://api-na.hosted.exlibrisgroup.com/almaws/v1/analytics/reports?path=%2Fshared%2FPortland%20Community%20College%2FReports%2FPCC%20New%20Books&apikey=l7xxbb2a378ea6354fff83a0a3b0113318c3&limit=50&filter=%3Csawx%3Aexpr%20xsi%3Atype%3D%22sawx%3Alist%22%20op%3D%22like%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%3E%22Bibliographic%20Details%22.%22Subjects%22%3C%2Fsawx%3Aexpr%3E%3Csawx%3Aexpr%20xsi%3Atype%3D%22xsd%3Astring%22%3E%25biology%25%3C%2Fsawx%3Aexpr%3E%3C%2Fsawx%3Aexpr%3E

[Anonymous]

The error message is indeed misleading – it is returned by the API gateway because it suspects the single quotes are a security threat. We’ll have to see if we can configure that error message (or relax the validation policy for analytics filters).

Does the same query work if you replace the single quotes with double quotes?

Opher

[Anonymous]

The query works only with single quotes. If I change it to UPPER(“Bibliographic Details”.”Subjects”) LIKE “%” || UPPER(“biology”) || “%”, I get this error in Analytics:

Error Codes: YQCO4T56:OPR4ONWY:U9IM8TAC:OI2DL65P
Odbc driver returned an error (SQLExecDirectW).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27005] Unresolved column: “%”. Please have your System Administrator look at the log for more details on this error. (HY000)

[Anonymous]

I was able to get results by replacing the single quotes with & apos; (with no space – added to prevent the forum system from rendering as ‘ ) – does that work for you?

Opher

[Anonymous]

Using &apos; gets results–but Analytics completely ignores the filter. For the URL-encoded string https://api-na.hosted.exlibrisgroup.com/almaws/v1/analytics/reports?path=%2Fshared%2FPortland%20Community%20College%2FReports%2FPCC%20New%20Books&apikey=l7xxbb2a378ea6354fff83a0a3b0113318c3&limit=50&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%20%26apos%3B%25%26apos%3B%20||%20UPPER%28%26apos%3Bbiology%26apos%3B%29%20||%20%26apos%3B%25%26apos%3B%3C%2Fsawx%3Aexpr%3E, the top results are:

<Row>
  <Column0>0</Column0>
  <Column2>1118980565; 9781118980569</Column2>
  <Column3>994914913801848</Column3>
  <Column4>Interdisciplinary research.; Interdisciplinary approach to knowledge.; Interdisciplinary approach in education.; Teaching teams.</Column4>
  <Column5>Multidisciplinary collaboration : research and relationships /</Column5>
  <Column6>2014-10-27</Column6>
  <Column7>Main</Column7>
  <Column8>None</Column8>
</Row>
<Row>
  <Column0>0</Column0>
  <Column2>9780387950433; 0387950435</Column2>
  <Column3>99158117730101848</Column3>
  <Column4>Classification.; Bacteria.; Bacteriology.; Bacteria classification.; Bacteria Classification.</Column4>
  <Column5>Bergey’s manual of systematic bacteriology.</Column5>
  <Column6>2014-10-23</Column6>
  <Column7>Reference</Column7>
  <Column8>None</Column8>
</Row>
<Row>
  <Column0>0</Column0>
  <Column1>Alexie, Sherman, 1966-</Column1>
  <Column2>0802121993; 9780802121998</Column2>
  <Column3>994906874701848</Column3>
  <Column4>Autobiographical fiction.; Washington (State) Fiction.; Indians of North America Fiction. Washington; Spokane Indians Fiction.</Column4>
  <Column5>The Lone Ranger and Tonto fistfight in heaven /</Column5>
  <Column6>2014-10-23</Column6>
  <Column7>Popular</Column7>
  <Column8>None</Column8>
</Row>
<Row>
  <Column0>0</Column0>
  <Column2>0323326056; 9780323326056</Column2>
  <Column3>994914612801848</Column3>
  <Column4>Older people Dental care.</Column4>
  <Column5>Geriatric dentistry /</Column5>
  <Column6>2014-10-22</Column6>
  <Column7>Main</Column7>
  <Column8>None</Column8>
</Row>

Column 4 contains the subjects, and as you can see none of these contain “biology” at all. It’s just returning the full report.

[Anonymous]

Understood. The SOAP API returns the same results, so this does not seem related to the new REST service or the gateway.

If your filter works when applied directly in OBI, please open a support case with the filter and the API request you are sending and our developers will examine what is preventing the filter from properly reaching OBI. If you can also reference this forum thread that would be helpful.

Opher

[Anonymous]

Hi Opher,

I opened a case today and, in the very first paragraph, explained that I had first posted this issue on the Developer Network. I provided a link to this topic and stated that you had instructed me to submit a case. The response:

Quote:

Thank you for sharing this issue, and explaining it.

In general, the developers will listen and answer via the Developer Network.

Would you like to try and post this question there (or have you tried already)? Would you like me to alert Opher to look for this question on the site?

I’ve responded reiterating that yes, I’ve already tried this avenue and was instructed to submit a case. But this has happened before, and after I get this “go to the Developer Network” response my cases tend to either disappear or close abruptly, without any attempt to address the issue. Do you have the power to convince the developers to please look at the case? (#00113757)

[Anonymous]

I see there has been some further discussion on this case in Salesforce over the weekend. The first step here needs to be creating a filter OBI will accept and process. Once you have that, the API should be able to pass it to OBI, and, if it doesn’t, that’s where our developers would need to step in.

To create your filter, the easiest way would be to use the OBI UI, and, once you’ve confirmed it works properly, grab the xml from the ‘advanced’ tab. In the meantime, we’ve configured the gateway to handle single quotes for this service, so aside from the URL-encoding the xml, it should be pretty straightforward once you have a working filter.

Opher

[Anonymous]

Opher,

The API is no longer returning general errors for the query with single quotes (%27), but it still isn’t returning filtered results.

This is the filter applied directly in the report and taken from the Advanced tab:
<sawx:expr xsi:type=”sawx:sql”>UPPER(“Bibliographic Details”.”Subjects”) LIKE ‘%’ || UPPER(‘biology’) || ‘%'</sawx:expr>

This is the identical filter in my URL (pre-encoding, with namespaces as instructed on the blog post):
<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&#8221; xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>UPPER(“Bibliographic Details”.”Subjects”) LIKE ‘%’ || UPPER(‘biology’) || ‘%'</sawx:expr>

Post-encoding: https://api-na.hosted.exlibrisgroup.com/almaws/v1/analytics/reports?path=%2Fshared%2FPortland%20Community%20College%2FReports%2FPCC%20New%20Books&apikey=l7xxbb2a378ea6354fff83a0a3b0113318c3&limit=50&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%27%25%27%20%7C%7C%20UPPER%28%27biology%27%29%20%7C%7C%20%27%25%27%3C%2Fsawx%3Aexpr%3E

The first five results from the report, if the filter is applied directly in the OBI, are:

Evolving animals: the story of our kingdom
How real is race? a sourcebook on race, culture, and biology
The amoeba in the room: lives of the microbes
Invasive species: what everyone needs to know
Virtually human: the promise–and the peril–of digital immortality

But if the same filter is sent through the API, the first five results are:

Anti-Semitism
Are natural disasters increasing?
Beauty pageants
Endangered oceans
Oil spills

…None of which contain the subject “biology.” Replacing the second UPPER command with LIKE ‘%BIOLOGY%’ doesn’t filter the results either. It returns the entire report, unfiltered, for any subject attempted.

This morning I received a response to the SalesForce case:

Quote:

I spoke to Opher about this, and he stated that the issue you’re reporting here with single quotes was resolved in the API gateway, and it should work as expected.

Which was swiftly followed by:

Quote:

Your case “Alma Analytics Filters with Single Quotes Can’t Be Passed via API” (#00113757) is now closed. We would be very grateful to hear how satisfied you were with the way Ex Libris handled this case. This will only take a moment of your time. Please choose one of the ratings below.

But it does not work as expected. I’ll attempt to keep the case open, but my hopes are not high.

(Edited because this forum keeps trying to turn “:x” in my filter into emoticons)

[Anonymous]

Thank you for the additional information. Further analysis suggests that this may be a problem with OBI itself, not Alma. We’re trying to get help from Oracle, and hope to be able to update soon.

Opher

[Nazzareno Bedini]

It seems that there is still no solution after 7 years.
After several attempts my conclusion is that it is impossible to use an sql filter directly in the Analytics API (like <sawx:expr xsi:type=”sawx:sql”>UPPER(“Bibliographic Details”.”Subject”) LIKE ‘%’ || UPPER(‘Biology’)|| ‘%'</sawx:expr>)

To create a working filter in API you must follow these steps:
1) add a further column “Bibliographic Details”.”Subject” and change its formula in UPPER(“Bibliographic Details”.”Subject”)
2) add a filter UPPER(“Bibliographic Details”.”Subject”) IS PROMPTED
3) use a XML filter like this:

<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&#8221;
xmlns:xsd=”http://www.w3.org/2001/XMLSchema”&gt;
<sawx:expr xsi:type=”sawx:sqlExpression”>UPPER(“Bibliographic Details”.”Subject”)</sawx:expr>
<sawx:expr xsi:type=”xsd:string”>BIOLOGY</sawx:expr>
</sawx:expr>

I hope my answer can still be useful after all these years

Regards
Nazzareno

[Author]

Posts