Wednesday Oct 23, 2013

BIP 11g Dynamic SQL

Back in the 10g release, if you wanted something beyond the standard query for your report extract; you needed to break out your favorite text editor. You gotta love 'vi' and hate emacs, am I right? And get to building a data template, they were/are lovely to write, such fun ... not! Its not fun writing them by hand but, you do get to do some cool stuff around the data extract including dynamic SQL. By that I mean the ability to add content dynamically to your your query at runtime.

With 11g, we spoiled you with a visual builder, no more vi or notepad sessions, a friendly drag and drop interface allowing you to build hierarchical data sets, calculated columns, summary columns, etc. You can still create the dynamic SQL statements, its not so well documented right now, in lieu of doc updates here's the skinny.

If you check out the 10g process to create dynamic sql in the docs. You need to create a data trigger function where you assign the dynamic sql to a global variable that's matched in your report SQL. In 11g, the process is really the same, BI Publisher just provides a bit more help to define what trigger code needs to be called. You still need to create the function and place it inside a package in the db.

Here's a simple plsql package with the 'beforedata' function trigger.

Spec

create or replace PACKAGE BIREPORTS AS 

 whereCols varchar2(2000);
 FUNCTION beforeReportTrig return boolean;

end BIREPORTS;

Body

create or replace PACKAGE BODY BIREPORTS AS

  FUNCTION beforeReportTrig return boolean AS 
   BEGIN
      whereCols := ' and d.department_id = 100';
      RETURN true;
   END beforeReportTrig;

END BIREPORTS;

you'll notice the additional where clause (whereCols - declared as a public variable) is hard coded. I'll cover parameterizing that in my next post. If you can not wait, check the 10g docs for an example.

I have my package compiling successfully in the db. Now, onto the BIP data model definition.

1. Create a new data model and go ahead and create your query(s) as you would normally.

2. In the query dialog box, add in the variables you want replaced at runtime using an ampersand rather than a colon e.g. &whereCols.

 

select     d.DEPARTMENT_NAME,
...
 from    "OE"."EMPLOYEES" e,
    "OE"."DEPARTMENTS" d 
 where   d."DEPARTMENT_ID"= e."DEPARTMENT_ID" 
&whereCols

 

Note that 'whereCols' matches the global variable name in our package. When you click OK to clear the dialog, you'll be asked for a default value for the variable, just use ' and 1=1' That leading space is important to keep the SQL valid ie required whitespace. This value will be used for the where clause if case its not set by the function code.

3. Now click on the Event Triggers tree node and create a new trigger of the type Before Data. Type in the default package name, in my example, 'BIREPORTS'. Then hit the update button to get BIP to fetch the valid functions.
In my case I get to see the following:


Select the BEFOREREPORTTRIG function (or your name) and shuttle it across.

4. Save your data model and now test it. For now, you can update the where clause via the plsql package.

Next time ... parametrizing the dynamic clause.

Thursday Sep 13, 2012

Dynamic Bursting ... no really!

If any of you have seen me or my colleagues present BI Publisher to you then we have hopefully mentioned 'bursting.' You may have even seen a demo where we talk about being able to take a batch of data, say invoices. Then split them by some criteria, say customer id; format them with a template; generate the output and then deliver the documents to the recipients with a click. We and especially I, always say this can be completely dynamic! By this I mean, that you could store customer preferences in a database. What layout would each customer like; what output format they would like and how they would like the document delivered. We (I) talk a good talk, but typically don't do the walk in a demo. We hard code everything in the bursting query or bursting control file to get the concept across. But no more peeps! I have finally put together a dynamic bursting demo! Its been minutes in the making but its been tough to find those minutes! Read on ...

It's nothing amazing in terms of making the burst dynamic. I created a CUSTOMER_PREFS table with some simple UI in an APEX application so that I can maintain their requirements. In EBS you have descriptive flexfields that could do the same thing or probably even 'contact' fields to store most of the info.

Here's my table structure:

Name                           Type
------------------------------ -------- 
CUSTOMER_ID                    NUMBER(6)
TEMPLATE_TYPE                  VARCHAR2(20)
TEMPLATE_NAME                  VARCHAR2(120)
OUTPUT_FORMAT                  VARCHAR2(20)
DELIVERY_CHANNEL               VARCHAR2(50)
EMAIL                          VARCHAR2(255)
FAX                            VARCHAR2(20)
ATTACH                         VARCHAR2(20)
FILE_LOC                       VARCHAR2(255)

Simple enough right? Just need CUSTOMER_ID as the key for the bursting engine to join it to the customer data at burst time. I have not covered the full delivery options, just email, fax and file location. Remember, its a demo people :0) However the principal is exactly the same for each delivery type. They each have a set of attributes that need to be provided and you will need to handle that in your bursting query. On a side note, in EBS, you use a bursting control file, you can apply the same principals that I'm laying out here you just need to get the customer bursting info into the XML data stream so that you can refer to it in the control file using XPATH expressions.

Next, we need to look up what attributes or parameters are required for each delivery method. that can be found in the documentation here.

 Now we know the combinations of parameters and delivery methods we can construct the query using a series a decode statements:

select distinct cp.customer_id "KEY",
     cp.template_name TEMPLATE,
     cp.template_type TEMPLATE_FORMAT,
     'en-US' LOCALE,
     cp.output_format OUTPUT_FORMAT,
     'false' SAVE_FORMAT,
     cp.delivery_channel DEL_CHANNEL,
     decode(cp.delivery_channel,'FILE', cp.file_loc 
      , 'EMAIL', cp.email
      , 'FAX', cp.fax) PARAMETER1,
     decode(cp.delivery_channel,'FILE', c.cust_last_name||'_orders.pdf'
      ,'EMAIL','cc@BIPublisher.com'
      ,'FAX', 'faxserver.com') PARAMETER2,
     decode(cp.delivery_channel,'FILE',NULL
      ,'EMAIL','customer_service@bipublisher.com'
      ,'FAX', null) PARAMETER3,
     decode(cp.delivery_channel,'FILE',NULL
      ,'EMAIL','Your current orders'
      ,'FAX',NULL)    PARAMETER4,
     decode(cp.delivery_channel,'FILE',NULL
      ,'EMAIL','Please find attached a copy of your current orders with BI Publisher, Inc'
      ,'FAX',NULL)  PARAMETER5,
     decode(cp.delivery_channel,'FILE',NULL
      ,'EMAIL','false'
      ,'FAX',NULL) PARAMETER6,
     decode(cp.delivery_channel,'FILE',NULL
      ,'EMAIL','reply@bipublisher.com'
      ,'FAX',NULL) PARAMETER7
from cust_prefs cp, 
customers c,
orders_view ov
where cp.customer_id = c.customer_id
and cp.customer_id = ov.customer_id
order by  cp.customer_id

Pretty straightforward, just need to test, test, test, the query and ensure it's bringing back the correct data based on each customers preferences. Notice the NULL values for parameters that are not relevant for a given delivery channel.

You should end up with bursting control data that the bursting engine can use:

 Now, your users can run the burst and documents will be formatted, generated and delivered based on the customer prefs.

If you're interested in the example, I have used the sample OE schema data for the base report. The report files and CUST_PREFS table are zipped up here. The zip contains the data model (.xdmz), the report and templates (.xdoz) and the sql scripts to create and load data to the CUST_PREFS table.  Once you load the report into the catalog, you'll need to create the OE data connection and point the data model at it. You'll probably need to re-point the report to the data model too.

Happy Bursting!

Thursday Dec 01, 2011

Dynamic Content using Sub Templates

I have written about sub templates in the past on a few occasions; the principle behind them is pretty simple. If you have common report components that can be shared across reports; be they blocks of text like standard contract clauses or maybe some common calculation or function, drop them into a sub template and share the love. Develop once, use everywhere!
A colleague was recently tasked with conditionally bringing into a report output, paragraphs of static text based on some user preferences. That’s an ideal candidate for a sub template approach; drop all of the paragraphs in to an RTF subtemplate and then just conditionally pull them in based on some boolean expressions.
You might, quite naturally think about conditionally importing a series of sub templates rather than defining one, with all the content. However, XSL does not allow the conditional import of sub templates so you must take the single template approach. You can of course, import multiple sub templates if you have a lot of content to bring in but in most cases I would expect a single sub template will suffice.

BIP does need to know what those paragraphs need to be for each user whether that’s as a set of parameter values or a data element in the incoming data set. For this example I have used both approaches and they work all flavors of BIP. Implementation of the sub template onto the servers is going to be a little different but the main principle is the same. I have mercilessly ripped out a nice graphic from Leslie’s (doc writer extraordinaire) documentation.



This is for the 11g version that supports loading sub templates into the report catalog as objects.  They can then be referenced in your main template using the import statement:

<?import:xdoxsl:///subtemplatefolder/subtemplatename.xsb?>

The subtemplate folder is going to be from the /SharedFolders  or /My Folders root. For instance, I have a sub template ‘paragraphs’ loaded into a ‘test’ folder under  Shared Folders. The import statement in my main template is ‘<?import:xdoxsl:///Test/ParaSubTemplate.xsb?>’

Update from Leslie

For those of you testing using your own My Folder area. The syn tax is

<?import:xdoxsl:///~username/path to subtemplate.xsb?> where username is your user name. For example: <?import:xdoxsl:///~tdexter/Subtemplates/Template1.xsb?>

Recommend you move them into the shared folder area in production.

For 10g you will either need to drop them into an accessible directory and use the file URI or mount them into the web server directory structure and access them via an http URI. I normally mount them in a directory under the ‘xmlpserver’ directory e.g J2EE_HOME\applications\xmlpserver\xmlpserver\subtemplates, a template is then accessible via the URI ‘http://server:port/subtemplates/template.rtf’

Make sure you set the Allow External References property to true for the report so that the sub template can be accessed.



The actual content of the sub template is pretty straight forward. It’s a series of paragraphs bounded by the ‘template’ command e.g.

<?template:para1?>
…
…
<?end template?>
<?template:para2?>
…
…
<?end template?>
<?template:para3?>
…
…
<?end template?>
Now we have the dynamic content defined it’s a case of conditionally bringing it into the main template. For this example I have demonstrated two approaches; both rely on the required paragraph information to be in the main dataset:
1.    Using parameters to allow the user to select the appropriate paragraphs to be brought in. This means creating the parameters and ensuring that you have set the property on the data model to include the parameter values in the XML result set.

Once that’s done its just a simple case of using id statements to check if a given paragraph should be included:

<?if:.//PARA1='1'?><?call:para1?><?end if?>


This assumes my parameter is called PARA1 and that a ‘1’ means include it, it could easily be a ‘Y’ or ‘True’ value, you are just testing for it.


2.    Including a value in the data to define what paragraphs should be included. If you have stored what paragraphs should be included for a given entity i.e. customer, supplier, employee, etc. Then you can extract those values into the data set and test for them. For this demo I have a 5 character set of ‘1’s and ‘0’s to represent the paragraphs that need to be included e.g. 10110. I just use a substring command to find out if a particular paragraph needs to be included.

<?if:substring(.//PARAS,1,1)='1'?><?call:para1?><?end if?>


Where PARAS is the element holding the ‘1’s and ‘0’s string to be parsed.
You can of course use some other means of marking whether a paragraph needs to be included or not. It’s just a case of parsing out the values with a substring or similar command.
You should be able to generate dynamic content such as this:


 Notice that I have rendered the code so that I can see that the paragraphs are coming in i.e 10001 means we get paragraphs 1 and 5. I have built out demo files for the second method, available here.

Monday Jan 18, 2010

Dynamic SQL against BI Server Data Source

[Read More]

Tuesday May 02, 2006

Dynamic Images

Made it back from Nashville with a pile of requests for blog entries, I'll try and cover them over the next few weeks ... top of the list of requests was how to handle dynamic images in a layout template. There are several ways to tackle this depending on where your images are going to be at runtime. The scenario is, that I have three organizations in my company and they each have their own logo; at runtime, based on the organization I am generating a report for the output should show their logo. Lets assume we have the following XML data for all of these examples:


<INVOICELIST>
 <IMG_DIR>/images</IMG_DIR>
 <IMG_SERVER>http://xdo.us.oracle.com</IMG_SERVER>
 <IMG_DIR_PATH>c:temp</IMG_DIR_PATH>
 <G_VENDOR_NAME>
  <ORG>1</ORG>
  <IMGFILE>Org1.gif</IMGFILE>
  <VENDOR_NAME>Nuts and Bolts Limited</VENDOR_NAME>
  <ADDRESS>1 El Camino Real, Redwood City, CA 94065</ADDRESS>
   <G_INVOICE_NUM>
    <SET_OF_BOOKS_ID>124</SET_OF_BOOKS_ID>
    <GL_DATE>10-NOV-04</GL_DATE>
    ...
 </G_VENDOR_NAME>
 <G_VENDOR_NAME>
  <ORG>2</ORG>
  <IMGFILE>Org2.gif</IMGFILE>
  <VENDOR_NAME>Tick Tock Clocks</VENDOR_NAME>
  ...
</INVOICELIST>


So our XML references an image directory(IMG_DIR) on our server(IMG_SERVER) and the full path to the image directory (IMG_DIR_PATH) on our server, then each invoice has the organization ID and the image name to be used. So we can tackle this one of three ways:
1. Embed the three logos into the template directly
2. Reference the images on the disk and use a dynamic image
3. Reference the images via a URL


Embedding in the template


This is the simplest method, if we have three images, one for each organization we can add them all to the template and wrap them in conditional statements to show/hide them. 

DynImg1:

The simplest method is to use three 'if' statements, you could equally use a 'choose' statement to get the same effect. See DynamicImg1.rtf template here. If you have alot of images and/or need dynamic images in multiple templates; you could put a template function either else where in the template or ina completely separate template altogether and then call it from the main templates. See DynamicImg2.rtf for an example in the zip file


Using dynamic images


We can also just add a single dummy image to the template and at runtime point to the correct image file either on the file system or via a URL. We can build the file location of the image quite easily using the XSL concat() function.  Note at runtime the incoming image will be resized (if necessary) to fit the same dimensions as the dummy image.
So in the Web tab of the image properties we have:

DynImg2:

File System: url:{concat(../IMG_DIR_PATH,'/',IMGFILE)} See DynamicImg3.rtf
URL: url:{concat(../IMG_SERVER,'/',../IMG_DIR,'/',IMGFILE)} See DynamicImg4.rtf


Notice we use '../' to get a level up from the G_VENDOR_NAME level for the root level values we need to construct the path or URL i.e. ../IMG_DIR_PATH.


If you are an E Business Suite user you can also make use of the OA_MEDIA directory. XML Publisher will resolve this value at runtime. So for an image in the OA_MEDIA directory you could use:


url:{?${OA_MEDIA}/IMGFILE?}


You can get all the examples and XML data here.
So thats dynamic images, of course you can use it for any image and you can build much more complex conditional formatting rules around the images. Have a good day ...

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