I have seen quite a few templates recently that are using our updatable variables to keep a track of the current record being processed. Regular readers will know my dislike for the updatable variables. Don't get me wrong, they have their place ... sometimes. It might be the way you set the dang things up that annoys me so much and I should take some blame for not chasing the dev team up to simplify the declaration and retrievel. Here's the code to define and assign a value to a variable x:
<?xdoxslt:set_variable($_XDOCTX, ’x’, 10)?>
now lets update the variable, adding 10 and then show it
<?xdoxslt:set_variable($_XDOCTX, 'x', xdoxslt:get_variable($_XDOCTX, 'x')+10)?> <?xdoxslt:get_variable($_XDOCTX, 'x')?>
Too much right? Imagine using them to track the number of the current record. Folks are doing this in their templates. There is no need - the underlying standard for RTF templates is XSL and it has some neat native functions that can be used to track the current record and the last record.
position()- will return the current record counter. In XSL it starts at 1
last()- will return the last record counter
For some examples I indebted to Chris from UK Oracle Consulting. Chris will be out at Oracle World again this year so if you would like to hear how the Queen's English should be spoken drop by the demo grounds. I seem to have developed some mid-atlantic drawl - my family in the UK think Im lost to those danged Yankees! I do still pronounce butter with t's rather than d's but my kids are struggling to understand me when I ask for a 'glass of water' rather than 'wadder'. Enough language mangling already! Back to Chris' examples. He came up with the following XML data.
<ROOT> <LIST_G_ORDER_HEADERS> <G_ORDER_HEADERS> <HEADER_ID>123</HEADER_ID> </G_ORDER_HEADERS> <G_ORDER_HEADERS> <HEADER_ID>456</HEADER_ID> </G_ORDER_HEADERS> <G_ORDER_HEADERS> <HEADER_ID>789</HEADER_ID> </G_ORDER_HEADERS> </LIST_G_ORDER_HEADERS> </ROOT>
and some examples of position() and last()
.//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[HEADER_ID = '123']/position() RETURNS '1' //LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[position() = last()]/HEADER_ID RETURNS '789' .//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[last()]/HEADER_ID RETURNS '789' .//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[position() != last()]/HEADER_ID RETURNS '123' and '456' .//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[2]/HEADER_ID RETURNS '456'
You'll have noticed the [ ] - these contain what are know as XPATH expressions. They are very powerful and well worth taking a look at - I'll try and cover XPATH more than we do in the documentation.
Update
===========
Leslie, our fab doc writer has just pointed out that I should have stated:
I will work with the doc writer to get some more in the documentation
I'll also try and get some more in here in the meantime :)
You can of course use them both in a conditional statement too:
<?if:position() = last()?> Last Record! <? end if ?>
Chris' third example is doing something similar but its using the condition within an XPATH expression, G_ORDER_HEADERS[position() != last()] - inside those square braces you do not need the 'if'.
So, throw out those updatable variable whenever you can and get native!
Comments (4)
Hi Tim,
Could you explain how the scope of updateable variables works? I am trying to use them to conditionally display something in the footer but can't seem to reference them.
As far as I know I have to use them as position() and last() work within the current recordset. If I reference them within the footer they return the values for the main recordset, not for the transactions. I need to display certain values in the footer of the last page so am setting updateable variables within the transaction for-each loop to reference the current and last record.
Cheers,
Jon.
Posted by Jon Bartlett | August 21, 2008 10:20 PM
Posted on August 21, 2008 22:20
Hi Jon
That would depend on where you declared them in the template.
You are correct on the scope of position and last - they are limited to the current recordest being processed.
What are you wanting to put in the footer on the final page?
Tim
Posted by Tim | August 22, 2008 9:31 AM
Posted on August 22, 2008 09:31
Hi Tim,
I Tried last() and found the result is different between R10.1.3.3.1 and R10.1.3.3.2 later.
When I use last() in for-each-group , result is below...
=========
A. BI Publisher 10.1.3.3.1
-> It returns the number of all rows.
B. BI Publisher 10.1.3.3.2 / 10.1.3.3.3(or higher)
-> It returns the distinct number that is grouped by some items.
=========
Do y know wich is correct?
--10.1.3.3.1 result---
10
110101
10
131102
10
212701
10
622101
10
633101
--10.1.3.3.2 later result ---
5
110101
5
131102
5
212701
5
622101
5
633101
--
=========
=========
test data like below
=========
1107
1
R1G502
5002
ITA_TESTxxxxx
2008/08
01
TEST
110101
chach
TEST1
0
2008-08-05T16:53:42.000+09:00
1198
2008-08-05T16:53:42.000+09:00
1198
Posted by kobito | August 25, 2008 4:22 AM
Posted on August 25, 2008 04:22
Hi Tim
I tried last() and i found the defference of resutls between 10.1.3.3.1 and 10.1.3.3.2 later.
Do y know which is correct?
When i use lasr() in for-each-group, result is below.
====
A. BI Publisher 10.1.3.3.1
-> It returns the number of all rows.
B. BI Publisher 10.1.3.3.2 / 10.1.3.3.3(or higher)
-> It returns the distinct number that is grouped by some items.
====
====
for-each-group:ROW;./ACCT_CODE
last()
ACCT_CODE
end for-each-group
====
--10.1.3.3.1 result---
10
110101
10
131102
10
212701
10
622101
10
633101
--10.1.3.3.2 later result ---
5
110101
5
131102
5
212701
5
622101
5
633101
est date is below. change # to and copy rows and modify ACCT_CODE.
===============================
#ROWSET>
#ROW>
#REQUEST_ID>1107#/REQUEST_ID>
#SORT_SEQUENCE>1#/SORT_SEQUENCE>
#REPORT_CODE>R1G502#/REPORT_CODE>
#SET_OF_BOOKS_ID>5002#/SET_OF_BOOKS_ID>
#SET_OF_BOOKS_NAME>ITA_TESTxxxxx#/SET_OF_BOOKS_NAME>
#ACCT_YYMM>2008/08#/ACCT_YYMM>
#CORP_CODE>01#/CORP_CODE>
#CORP_NAME>TEST#/CORP_NAME>
#ACCT_CODE>110101#/ACCT_CODE>
#ACCT_NAME>chach#/ACCT_NAME>
#ACCT_CODE_FROM>#/ACCT_CODE_FROM>
#ACCT_NAME_FROM>#/ACCT_NAME_FROM>
#ACCT_CODE_TO>#/ACCT_CODE_TO>
#ACCT_NAME_TO>#/ACCT_NAME_TO>
#DIV_CODE_FROM>#/DIV_CODE_FROM>
#DIV_NAME_FROM>#/DIV_NAME_FROM>
#DIV_CODE_TO>#/DIV_CODE_TO>
#DIV_NAME_TO>#/DIV_NAME_TO>
#ACCT_DATE>#/ACCT_DATE>
#DOC_SEQUENCE_VALUE>#/DOC_SEQUENCE_VALUE>
#USER_JE_SOURCE_NAME>#/USER_JE_SOURCE_NAME>
#FE_JE_NO>#/FE_JE_NO>
#OPP_ACCT_CODE>#/OPP_ACCT_CODE>
#OPP_ACCT_NAME> TEST1#/OPP_ACCT_NAME>
#DR_AMOUNT>#/DR_AMOUNT>
#CR_AMOUNT>#/CR_AMOUNT>
#BALANCE_AMT>0#/BALANCE_AMT>
#LINE_DESC>#/LINE_DESC>
#DIV_CODE>#/DIV_CODE>
#SUSP_ACCT_CLEAR_CODE>#/SUSP_ACCT_CLEAR_CODE>
#SUSP_ACCT_CLEAR_NAME>#/SUSP_ACCT_CLEAR_NAME>
#LAST_UPDATE_DATE>2008-08-05T16:53:42.000+09:00#/LAST_UPDATE_DATE>
#LAST_UPDATED_BY>1198#/LAST_UPDATED_BY>
#CREATION_DATE>2008-08-05T16:53:42.000+09:00#/CREATION_DATE>
#CREATED_BY>1198#/CREATED_BY>#/ROW>
Posted by kobito | August 25, 2008 4:42 AM
Posted on August 25, 2008 04:42