XPATH expressions not evaluating [#2859399]

No matter what I try, I can not get XPATH expressions to evaluate. I have tried both methods of setting up an frx:field and setting calc= the xpath expression as well as adding the expression to the bottom of a report with {=expression}. I have tested the expression using an xpath expression tester and it evaluates correctly, but when I put the expression in the frx file, all I get is 0. I have read the other issue that was filed about this, but I don't seem to be able to get it to work, even after hours of trying different options.

I have pasted the code here, and I was hoping you might be able to tell me what I'm doing wrong.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE root [
<!ENTITY nbsp "&#160;">
]>
<html xmlns:frx="urn:FrxReports">
<head>
<title>Northwind Customers</title>
<frx:category>Northwind</frx:category>
<frx:options skin="demo"/>
<frx:parameters>
</frx:parameters>
<frx:docgen>
</frx:docgen>
<frx:fields>
  <frx:field id="customers_count" calc="count(//tr[starts-with(@id, 'northwind_customers')])" />
</frx:fields>
<frx:menu/>
<frx:cache/>
<style/>
</head>
<body>
<div id="northwind_customers_block" class="FrxTable" frx:block="northwind/northwind_customers">
    <table>
      <thead>
        <tr>
          <th>id</th>
          <th>company</th>
          <th>last name</th>
          <th>first name</th>
          <th>email address</th>
          <th>job title</th>
          <th>business phone</th>
          <th>test_integer</th>
          <th>mobile phone</th></tr></thead>
      <tbody>
        <tr id="northwind_customers" frx:foreach="*">
          <td>{id}</td>
          <td>{company}</td>
          <td>{last_name}</td>
          <td>{first_name}</td>
          <td>{email_address}</td>
          <td>{job_title}</td>
          <td>{business_phone}</td>
          <td id='test'>1</td>
          <td>{mobile_phone}</td>
        </tr>
      </tbody>
    </table>
</div>
<p>count = {customers_count} </p>

</body>
</html>

Comments

Comment #1

9 March 2017 at 23:33

iratau created an issue. See original summary.

Comment #2

iratau CreditAttribution: iratau commented 9 March 2017 at 23:34

Issue summary:

View changes

Comment #3

metzlerd CreditAttribution: metzlerd commented 10 March 2017 at 00:45

Easiest way forward is to move the paragraph containing the count inside the div that contains the frx:block attribute. Then the {=count(*)} expression should work.

To get the {customers_count} field to work where it is, add context="northwind_customers_block" to the frx:field definitition of that calculation, then change the expression for that field to be frx:calc="count(*)" .

finally, The XML data that you are using is NOT the HTML of the report, but an XML representation of the query. To see a dump of what the raw XML looks like add an FrxXML renderer inside the div containing the frx:block attribute like this:

<div id="northwind_customers_block" class="FrxTable" frx:block="northwind/northwind_customers">
  <div frx:renderer="FrxXML"/>
....
</div>

Then you'll see embedded in the report the XML that you are trying to generate an xpath expression for which will help a lot.

Let me know whether this clears it up for you.

Comment #4

iratau CreditAttribution: iratau commented 10 March 2017 at 20:35

Thank you! You are a gentleman and a scholar! Your two suggestions above both worked.

I did understand that that the XML data I attached to the ticket is not the report HTML. I should have clarified that when I used my XPATH expression tester, I was using it on the rendered report and not in the .frx file.

One followup question, though. I understand that the use of "count(*)" as the calc will give me a count of all rows processed. However, what if I wanted to a more complicated XPATH selection. For example, if one of my data columns has values that can be "active" or "expired" and I want to count only those that are "expired" - how could I do that? I initially had an XPATH expression as follows:

count(//td[@id="status"]/text()="expired")

...but I was unable to get that to work. Is this something that is possible?

Thanks again for your help.

Comment #5

metzlerd CreditAttribution: metzlerd commented 11 March 2017 at 02:51

Short answer: Yes try count(*[status="expired"])

Longer answer:
The expressions you are trying lead me to believe that you think the xpath expressions are working against the html generated by the report. They are not. Forena converts the SQL query results into XML prior to rendering it in the report. Is this XML that you are evaluating the xpath expressions against. That's why I was suggesting the frx:renderer syntax so that you could get a feel for what the xml might look like before crafting your expression. The XML retrieved in your north wind example data block above would look something like:

  <table>
    <row>
       <id>1</id>
       <company>Acme</company>
       <last_name>Doe</company>
       <first_name>John</first_name>
       ....
     </row>
     <row>
    <row>
       <id>1</id>
       <company>Acme</company>
       <last_name>Doe</company>
       <first_name>Jane</first_name>
       ....
     </row>    
     </row
   </table>

I'd really recommend you take a look at the frx:renderer sytax suggested so that you could see what I'm talking about here.