BI Publisher SQL functions

24/07/2009 by Simon Tomey.

Here’s the first in a series of Blog posts giving tips about great applications for Business Intelligence Publisher. We’re focusing on the “what to”, not so much on the “how to”. If you need more help on the “how to” drop me a line.

BI Publisher has access to a number of libraries which gives it a wide range of available functions.

• These all support my claim that “if the data is there already, you can do absolutely anything with it in a BIP presentation template”. (Moreover, if you use data templates you can also get any data out that you want too).
• One of these libraries (XDO) contains a number of SQL equivalents – check them out in the XDO user guide.

We recently used one of the functions in this library to clean up a remittance advice.

• This function is substring (which is the equivalent of mid string for MS Excel users).
• The standard remittance advice output puts a very unhelpful “GB” in one of the address fields (the UK country code) which would just confuse the postman and irritate our suppliers, so we need to eliminate it. If it was only this in the field, we could just exclude the field altogether, but unfortunately is also contains the post code.

<C_ZIP_PR>GU33 8PQ  GB</C_ZIP_PR>

Hence we used substring to suppress the last two characters of this address field.

Here’s what it looks like before using substring:

Here’s what it looks like thanks to substring and without the annoying “GB”.

Posted in BI Publisher | Print | No Comments »