This discussion is archived
4 Replies Latest reply: Nov 21, 2012 5:01 AM by 973532 RSS

Error when using SUM function in Excel template

973532 Newbie
Currently Being Moderated
I am trying to use the XDO_METADATA to create a sum of a column from my XML data and I am getting the following error in the Template Viewer:

[111412_104246459][][PROCEDURE] Log Level is changed to PROCEDURE
[111412_104246553][oracle.xdo.common.xml.XSLTWrapper][ERROR] XSL error:
Time: 0.125 sec.
FO Formatting failed.

<Line 317, Column 116>: XML-23029: (Error) FORG0001: invalid value for cast/constructor
@Line 317 ==> <Cell Index="2" Style="R7C3" Field="XDO_?SUM_V_CR_MO_IDD1?"><xsl:value-of select="sum(.//G_CR_MST_D/V_CR_MO_IDD)"/>


when I use:

XDO_?SUM_V_CR_MO_IDD1?     <?sum(.//G_CR_MST_D/V_CR_MO_IDD)?>


or

[111412_104048508][][PROCEDURE] Log Level is changed to PROCEDURE
[111412_104048554][oracle.xdo.common.xml.XSLTWrapper][ERROR] XSL error:
Time: 0.078 sec.
FO Formatting failed.

<Line 317, Column 105>: XML-23029: (Error) FORG0001: invalid value for cast/constructor
@Line 317 ==> <Cell Index="2" Style="R7C3" Field="XDO_?SUM_V_CR_MO_IDD1?"><xsl:value-of select="sum(.//V_CR_MO_IDD)"/>

when I use:
XDO_?SUM_V_CR_MO_IDD1?     <?sum(.//V_CR_MO_IDD)?>

I believe the XSL to be correct because when I change it to a count it works and when I go into BI Publisher 11g and create the query in the data model and then create a summary from it, the summary is created.

Can anyone help?
  • 1. Re: Error when using SUM function in Excel template
    973532 Newbie
    Currently Being Moderated
    I have found this error in an XDK manual and it says:

    XML-23029: FORG0001: invalid value for cast/constructor

    Cause: This was an XPath 2.0 F&O specification error.

    Action: Check the XPath expression.

    However as I said I believe the XSL/XPATH to be correct.

    I think it has something to do with the group that I have wrapped this field around because I did a similar thing where I totalled up a column using one of these summary fields but it was just in an empty cell not enclosed within a group and that worked.

    So if it was the emp/dept example it would be like breaking on job with deptno and so there would be a repeating group to incorporate the total salaries by job and still needing to create a total of salary by department(deptno) too.

    Does anyone know of problems trying to reference aggregates within groups?
  • 2. Re: Error when using SUM function in Excel template
    973532 Newbie
    Currently Being Moderated
    Surely someone is creating reports within Excel with subtotals and overall totals.

    From an Emp/Dept perspective, all I'm trying to do is create a report by department and the broken up by job within department and I want a subtotal of salary by job and then a total of salary by department.

    Currently the work around, it may not be a work around it maybe the way it has to be done, is to create two break groups in the data model, one for the department and one for the job and then use the aggregate functions available there in order to create the summaries in the XML in the first place. What would be nice to know is if that is the only way or the best way to do it or should I not be able to create those summaries in the spreadsheet itself.
  • 3. Re: Error when using SUM function in Excel template
    BIPuser Guru
    Currently Being Moderated
    You should be able to calculate the subtotal in the spreadsheet itself..

    Have you tried using: <xsl:value-of select="sum(current-group()/V_CR_MO_IDD)"/> -- assuming that you have declared a group..

    There are some sample excel templates under your BI Publisher Desktop folder (I believe these come with the 11g version) that show you how to create the subtotals: \BI Publisher Desktop\Template Builder for Word\samples\Excel templates

    Thanks,
    Bipuser
  • 4. Re: Error when using SUM function in Excel template
    973532 Newbie
    Currently Being Moderated
    I went back to basics and created reports (via EXCEL templates) like I was asking based on good old EMP and DEPT and I found exactly the same problems I was mentioning. I looked at the templates provided but they were not calculating totals, like me they were selecting them and then just displaying them on the page.

    Anyway, I have narrowed it down to the fact that when you do aggregates like sum(.//SAL) this works if you have a salary for every value. I did an outer join with DEPT so I did have empty rows and why I still experienced the problems.

    Basically XSL does not like adding (including using the sum function) values that effectively have nulls in them which is why I get the cast/constructor errors because it is trying to turn a NaN value to a number and does not (or cannot) do it.

    You need to either have a value in every row of your column (maybe possible by selecting nvl in your query) and make sure that you check the "create empty nodes" checkbox in the data model of BI Publisher.,
    the other solution is an xsl solution where you would have to make sure that you only added non null values and for that you would have to investigate xsl blogs.

    It is, by the way, why my count worked because it is just counting that the record exists it does not care what the element content is or isn't.

    Closing thread.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points