| Back to Main Page |
Basic math, addition, subtraction, multiplication, and division, is very easy in a layout template, but there are a couple of items that have the potential to trip you up. Once you undertsand the issue, it'll be a breeze!
To get started, for this example, we'll be using a layout template that is based on the emp table in the Vision sample database. If you are not familiar with the database or you want to see the XML sample data, I have posted it here.
The layout template I'll be using is a simple table with fields for Empno, Ename, Hiredate, sal , Comm, and DeptNo. The fields of interest for this example are salary (Sal) and commission (Comm) because they are the fields containing numeric data. I've included an additional field I called Total Comp, in which I want to report the sum of the salary and the commission for each employee.
The complication for this attempt is that not every employee earns a commission payment so some of the values are null. We'll see how that plays out in a few minutes.
This is the template that will be used in the examples:
The field that I will address is the sixth field from the left, Total Compensation. Ultimately, I want to end up with this field containing the sum of the sal and comm fields, but first I'll intentionally make a couple of mistakes for your benefit.
For the first try, I'll enter each of the two fields separately, and try to add them together.
<?SAL?>+<?COMM?>
Here's what happened, when I tested it:
Take a look at those results in the Total Comp column. Each value from the two columns that I was attempting to sum shows up -- concatenated by a plus sign! Clearly the two values were not added together, the values were just placed into the output, and the plus sign appears as a literal character.
For my second attempt, I won't change the formula at all. I simply will change the datatype of the form field. If you are not aware of it, Microsoft Word allows you to specify the type of data that is expected in the field. By default, form fields are defined as character fields ("Regular Text" is what Microsoft Word calls it), because they can be used for any type of data. But, choosing the correct datatype is a better choice because you'll get a chance to use a format mask which is appropriate for the data. For my field, I am going to change the datatype to "Number", so that I can see if that will cause Microsoft Word to evaluate the two XML element fields as numbers and add them together.
To change the datatype of the field, simply double click the field within the template and the form field properties box will open. At the top left hand corner, you'll find the field to change the datatype. Click the dropdown box and choose "Number" from the list.
That output does not seem to be much of an improvement. The template still is outputting each of the two field values separately and concatenating them. The only difference is that now, since I've told the template that the field contains a number, it gets confused trying to figure out what to do with the plus sign that is between the two fields, so it just gives up and places the plus sign in front of the concatenated result.
So, I know that I need to do more than change the datatype of the field: I've got to modify the
formula itself. In the previous examples, the addition symbol is between the two fields, which
resulted in concatenation. TO make the layout template perform an actual calculation, I need to
include the plus sign within the element itself. For my next attempt, I modify the form as
shown on the next line:
<?SAL+COMM?>
Here's what happened, this time:
Run XDO Start RTFProcessor setLocale: en-us FOProcessor setData: C:\dan\presentations\2008_collaborate\EMP_XML.xml FOProcessor setLocale: en-us java.lang.reflect.InvocationTargetException at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at oracle.apps.xdo.common.xml.XSLT10gR1.invokeProcessXSL(XSLT10gR1.java:586) at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:383) at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:201) at oracle.apps.xdo.common.xml.XSLTWrapper.transform(XSLTWrapper.java:161) at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:1015) at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:968) at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:209) at oracle.apps.xdo.template.FOProcessor.createFO(FOProcessor.java:1561) at oracle.apps.xdo.template.FOProcessor.generate(FOProcessor.java:951) at RTF2PDF.runRTFto(RTF2PDF.java:626) at RTF2PDF.runXDO(RTF2PDF.java:460) at RTF2PDF.main(RTF2PDF.java:251) Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert to number. at oracle.xdo.parser.v2.XSLStylesheet.flushErrors(XSLStylesheet.java:1526) at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:517) at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:485) at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:264) at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:150) at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:187) ... 16 more
Whoa! It's an error! Even though there's only one line that I need to see, I've included the entire stack. I did so to help give the reader a feel for the typical error messages you'll encounter. At first, it can be quite daunting to see such a long series of cryptic java error messages, but don't fear. Carefully read through the list, and you'll often find one line that will explain the actual reason for the exception. Of course, it may not be tagged as obviously as this one is... !
But I digress. So why did my my report preview result in an error when I moved the addition operation inside of the tag? The problem arises from those pesky null values in the commission field. The parser must convert the value to a number before it can perform the addition -- and it does not know how to perform the conversion on the nulls.
I'll have to handle those null values myself. I can use one of the special xdofx functions
to impose an NVL logic on that field. In order to keep the size of this posting manageable,
I'll have to cover xdofx functions at another time, but suffice to say that xdofx allows me
to apply any of about a dozen familiar Oracle SQL functions to my data. Here's the change I made
to make the column work as desired:
<?xdofx:SAL+nvl(COMM,0)?>
For the unititiated, the NVL function, evaluates the value of Comm in each record, and, if
it encounters a NULL value, replaces that NULL with a zero. Now every record has a valid numeric
value for commission, and can be summed with SAL successfully.
One more thought, if you are looking at the output above and noticing that the commission field is still blank on some of the records, that's because I only applied the NVL for purposes of the calculation in the total compensation field -- I did not touch the COMM field or the underlying value at all.