Thursday, March 06, 2008

Running Totals in BI Publisher + Cannot convert to number

Over on the BI Publisher forums, I spotted a interesting one error when attempting to put page totals using a running total using updateable variables and templated footers:

Once I run the RTF am getting error "Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert to number"

As per the example in document XML Publisher Users Guide 11i the syntax seemed right, i.e. where INVAMT is the field to calculate running total and RTotalVar is the running total variable:

Initialize variable:

<?xdoxslt:set_variable($_XDOCTX, 'RTotalVar', 0)?>

Set variable:

<?xdoxslt:set_variable($_XDOCTX, 'RTotalVar', xdoxslt:get_variable($_XDOCTX,'RTotalVar') + INVAMT)?> 

Show variable:

<?xdoxslt:get_variable($_XDOCTX, 'RTotalVar')?>

After a bit of tinkering with the RTF and XML, I realized the syntax was correct, so what was going on?

One extenuating factor was the location of the "set variable" form field inside a nested table. Time to move it out, no need for it to be there - unless the grouping mattered ... in this case it didn't. So moving the "set variable" form field out of the table fixed things. Moral of the story?

If somethin's not working and you think it should, and the error output ain't doin it for ya - simplify simplify - or should that be KISS KISS - until it works ;-)

12 comments:

Jagesh said...

Hi Gareth,

We have a requirement to print the different footer in the last page of the report.

how can i assign the current page number to a variable?

How can i achieve this? Please advice.

Thanks.

Gareth said...

You can't do logic based on getting value of page number. There is functionality like 'all but last page' that you can use.

Regards,
Gareth

dpanupam said...

Your blog is very nice. Website hosting

chinna said...

Hi Gareth,

Where we can find "all but last page ", I have the same requirement will you please send any example or screen shot how to do its very urgent, Please help me.
Mail Id: raghavendraprasad37@gmail.com

Advance Thanks

Gareth said...

Hi Chinna,

Please see Tim's post here.

Regards,
Gareth

sonal said...

Hi Gareth,

I'm trying to implement the running total,however I'm geting the same exception error like cannot be converted to number. Can you please give the exact steps for it.

Thanks..

Gareth said...

Hi Sonal,

Fix in my case was to move the set variable outside table, but you should also check that you are not trying to convert a varchar to a number anywhere else in your template.

Regards,
Gareth

sonal said...

Hi Gareth,

Thanks for replying..I have not used table. Just one for loop and in that I have taken up an Amount field and next to it I have placed a Text form field and have selected it to be as Number for Running Totals. I'm trying to display my contents as:

Amount Running Total
100 100
200 300

Still geting the same error for this simple stuff :(...

Regards,
Sonal

sonal said...

Hi Gareth,

I just found that my value is 4,35 and XML needs a raw data..can you help me how can I convert this value which I'm geting and then perform the running total.

Regards,
Sonal.

Gareth said...

Hi Sonal,

Make sure you source has a number formatted in regular number format, and use that field.

I've heard of issues with running totals in different number formats before... keep it simple and put formatted and non-formatted fields in the source so you can choose!

Regards,
Gareth

Melody Attard said...

Gareth,

My INVAMT includes nulls. How can I go about that please?

Thanks
Melody

Gareth said...

Hi Melody, easiest would be to change the source so 0 is in the XML.

Regards,
Gareth