No Database Trippin'

I often get asked a specific question through email, at conferences and I have seen it on the forum -

                                       'Why can we not make a trip back to the database from the layout template?'

The reasons I hear it asked are that the 'main query does not get all the info', or 'I need to go and fetch some more data based on a value or a calculation.' My answer is, why are you not fetching all the values in the query?

Its a sore point for me and it will take a lot of convincing for me to let you do it, if you have some good arguments please comment. Im an old Oracle Reports developer, my former team had nearly 300 Oracle Reports to maintain for our product in EBS. We were the 'Financials for EMEA' product providing localizations for countries across EMEA in the form of statutory reports, EFT formats, specific customer requirements for a country, etc. All good stuff but we spent a lot of time performance tuning these reports and the number one culprit, in my opinion, for bad performance was the 'developers friend' the formula column. Maybe you got an enhancement to add a field to a report, you were under pressure to get the fix out so rather than rework the query you just added a formula column. Over the years the reports would become more and more bloated with them until a customer was running their year end VAT report trying to generate a 500,000 page document and it was taking more than 24 hours - time to rip them out and do the right thing and re-work that query.

So, Im no fan of the formula column and hence my aversion to allowing you to get back to the database from the layout - that's not to say you could not do it with some custom extensions and a little effort. But dont expect support to be there for you and Im not answering your questions on the forum - Im kidding.

The only case I can think of where a forumula column or its functionality would be useful is in the case of a lookup and only then in a very specific case. I dont mean getting a localized user value from a lookup table based on some extracted code - get back and re-write that query to fetch that. The only case where I think you might have an argument for it is if you need to compare a value in the extracted data against a larger set of values - even then, why not push this back to the database and add a column in your extract to set a boolean flag to set whether a value is 'in' or 'out'. So, you dont need to make a trip back to the db ... period!


Ahhhh ... but how can I check if a value is a member of a larger data set in the template, I cant do it in the database cos I need some derived value based on a user input or some live web service retrieved value that can only be fetched at runtime.
For user input - re-write the query you lazy bum; for some real time value - you got me. Finally we have a case that I think worthy of a database trip.

But we are still not going to support you doing that so what can you do? XSL variables is what!

Variables in XSL are not like variables in other languages, yes you create them and assign them values but you typically can not update them during the course of a transformation - more like constants then. Publisher does provide an updateable variable solution that you can use. But there is an advantage in XSL variables, they can not only take a single value but they can also hold a complete tree of data or a node. Now if you can load those members of the group to a variable and then test your derived value to see if its in there then we have a way around the formula column conundrum.

Lets take a simple example, say we were reporting on a bunch of supplier invoices and we did not know whether they had been paid by our bank or not until we executed a web service in our report to fetch the inovice numbers that had been paid that day. In our report thou we wanted to report on all invoices but flag those that had been paid.

So I build a report that extracts the invoice data from my application database and I also create a secondary datasource that executes the web service to bring back the paid invoice numbers.  You can do this in the standalone release very easily, in EBS you need to write a little code and a java concurrent program, in Ps and JDE I think you can punch out to fetch WS content ... pleae dont quote me on that. You will have spotted that Im forcing you back to the data extraction layer here ... still no sneaky trips to the database. I get back some XML similar to :


<MYREPORT>
 <INVOICE_DATA>
  <INVOICE>
   <INV_NUMBER>1000</INV_NUMBER>
   <INV_AMOUNT>990.00</INV_AMOUNT>
   ...  
  </INVOICE>
  <INVOICE>
   <INV_NUMBER>1001</INV_NUMBER>
   <INV_AMOUNT>390.00</INV_AMOUNT>
     ...
  </INVOICE>
  <INVOICE>
   <INV_NUMBER>1002</INV_NUMBER>
   <INV_AMOUNT>920.00</INV_AMOUNT>
   ...
  </INVOICE>
  <INVOICE>
   <INV_NUMBER>1003</INV_NUMBER>
   <INV_AMOUNT>190.00</INV_AMOUNT>
   ...
  </INVOICE>
  <INVOICE>
   <INV_NUMBER>1004</INV_NUMBER>
   <INV_AMOUNT>1090.00</INV_AMOUNT>
  </INVOICE>
   ...
 </INVOICE_DATA>
 <PAID_DATA>
  <INVOICE>
   <PAY_NUM>990</PAY_NUM>
   <PAY_DATE>12-JAN-2007</PAY_DATE>
  </INVOICE>
  <INVOICE>
   <PAY_NUM>992</PAY_NUM>
   <PAY_DATE>12-JAN-2007</PAY_DATE>
  </INVOICE>
  <INVOICE>
  <PAY_NUM>993</PAY_NUM>
   <PAY_DATE>12-JAN-2007</PAY_DATE>
  </INVOICE>
  <INVOICE>
  <PAY_NUM>994</PAY_NUM>
   <PAY_DATE>12-JAN-2007</PAY_DATE>
  </INVOICE>
  <INVOICE>
  <PAY_NUM>995</PAY_NUM>
   <PAY_DATE>12-JAN-2007</PAY_DATE>
  </INVOICE>
  <INVOICE>
  <PAY_NUM>996</PAY_NUM>
   <PAY_DATE>12-JAN-2007</PAY_DATE>
  </INVOICE>
  <INVOICE>
  <PAY_NUM>997</PAY_NUM>
   <PAY_DATE>12-JAN-2007</PAY_DATE>
  </INVOICE>
  <INVOICE>
  <PAY_NUM>1000</PAY_NUM>
   <PAY_DATE>12-JAN-2007</PAY_DATE>
  </INVOICE>
  <INVOICE>
  <PAY_NUM>1002</PAY_NUM>
   <PAY_DATE>12-JAN-2007</PAY_DATE>
  </INVOICE>
 </PAID_DATA>
</MYREPORT>
In our report we can assign all of the paid INV_NUM values to a variable, notice we have a PAY_DATE element too so we need a little XPATH to fitler the data vlaues out.
 <?variable: paidInv; /MYREPORT/PAID_DATA/INVOICE/*[name()='PAY_NUM']?>

the asterisk gets me all values ie the complete tree from INVOICE down, the name()='INV_NUM' gets me jus the values where the element name is equal to 'INV_NUM'. So now I have a tree of paid invoice numbers that I can compare against with each of my reported invoices.

 <?if:$paidInv = INV_NUMBER?>Paid<?end if?>

if the current invoice number is a member of the paid group then flag the row with 'Paid'. You can of course embed the logic in a much more complex construct.


If you need the functionality across multiple templates then either create a subtemplate or wait a little while and we will have sharable data sources. Demo files available here.


So, Im still not letting you go back but at least you have a way of checking data against a larger dataset.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today