BI/XML Publisher interview questions

http://docs.oracle.com/cd/E10091_01/doc/bip.1013/b40017/T421739T481157.htm#do_regroup



---->  how to get the outer group values into inner group
ans-use XPath expressions in there.
 if you are inside the invoice loop you can still access the fields in the outer loop by modifying the form field and prefixing with ../

e.g. <?VENDOR_NAME?> becomes <?../VENDOR_NAME?>

Page Breaks <?split-by-page-break:?>

To create a page break after the occurrence of a specific element use the "split-by-page-break" alias. This will cause the report output to insert a hard page break between every instance of a specific element.

To insert a page break between each occurrence of a group, insert the "split-by-page-break" form field within the group immediately before the <?end for-each?> tag that closes the group. In the Help Text of this form field enter the syntax:

<?split-by-page-break:?>

For the following XML, assume you want to create a page break for each new supplier:

<SUPPLIER>
 <NAME>My Supplier</NAME>
 <INVOICES>
  <INVOICE>
   <INVNUM>10001-1</INVNUM>
   <INVDATE>1-Jan-2005</INVDATE>
   <INVAMT>100</INVOICEAMT>
  </INVOICE>
  <INVOICE>
   <INVNUM>10001-2</INVNUM>
   <INVDATE>10-Jan-2005</INVDATE>
   <INVAMT>200</INVOICEAMT>
  </INVOICE>
 </INVOICES>
</SUPPLIER>
<SUPPLIER>
 <NAME>My Second Supplier</NAME>
 <INVOICES>
  <INVOICE>
   <INVNUM>10001-1</INVNUM>
   <INVDATE>11-Jan-2005</INVDATE>
   <INVAMT>150</INVOICEAMT>
  </INVOICE>
…

In the template sample shown in the following figure, the field called PageBreak contains the split-by-page-break syntax:

Place the PageBreak field with the <?split-by-page-break:?> syntax immediately before the <?end for-each?> field. The PageBreak field sits inside the end of the SUPPLIER loop. This will ensure a page break is inserted before the occurrence of each new supplier. This method avoids the ejection of an extra page at the end of the group when using the native Microsoft Word page break after the group.

1.      What is a template Builder?     The Template Builder is an extension to Microsoft Word that simplifies the development of RTF templates. The Template Builder is tightly integrated with Microsoft Word and allows you to perform the following functions:•        Insert data fields•        Insert data-driven tables•        Insert data-driven forms•        Insert data-driven charts•        Preview your template with sample XML data•        Browse and update the content of form fields•        Extract boilerplate text into an XLIFF translation file and test translationsValid template file types are:eText - Inbound, eText - Outbound, PDF, RTF, XSL-FO, XSL-HTML, XSL-TEXT, and XSL-XML.

2.      To handle parameterized layoutIf the requirement is to display a different layout based on a user parameter value or a List of Value selection, then the parameter can be passed to RTF layout and conditionally display the layout.On the Report Editor you first need to define a parameter with name DeptName. If this parameter has to be associated with a List of Value (LOV), then create a LOV on the Report Editor page.Next, in the parameter definition select parameter type as “Menu” and then select the LOV from the selection.In the RTF template define a parameter using syntax - <?param@begin:DeptName?>Now when user selects a department name from the List of Value on the report viewer page, the value gets passed to RTF layer into the DeptName parameter. To display the layout based on this user selection, you can use an IF statement or a CHOOSE statement to evaluate the parameter value and call the associated sub template.Use CHOOSE statement when too many conditional tests are to be done and a default action is expected for rest of the values, for example, for each department here we have a different sub template and if the user parameter has a department name with no associated sub template, then a default sub template can be called in otherwise section.<?choose:?><?when:$DeptName=’Accounting’?><?call:tAcc?><?end when?><?when:$DeptName=’Sales’?><?call:tSales?><?end when?><?when:$DeptName=’Marketing’?><?call:tMark?><?end when?><?otherwise:?><?call:tDefault?><end otherwise><?end choose?>3.  Adding Page/Brought Forward/Carried Forward Total
Placed before the column nameà PT
<?add-page-total:pt;'TRANS_AMOUNT_REMAINING'?> 4.Display Page Total in Footer
<xdofo:show-page-total name="pt" format="99G999G999D00"/>5.Display Brought Forward Total in Header<xdofo:show-brought-forward name="pt" format="99G999G999D00"/>The values for the form fields in the template are shown in the following table:

Form Field	Syntax	Description
RtotalVar	<?xdoxslt:set_variable($_XDOCTX, 'RTotalVar', 0)?>	Declares the "RTotalVar" variable and initializes it to 0.
FE	<?for-each:INVOICE?>	Starts the Invoice group.
10001-1	<?INVNUM?>	Invoice Number tag
1-Jan-2005	<?INVDATE?>	Invoice Date tag
100.00	<?xdoxslt:set_variable($_XDOCTX, 'RTotalVar', xdoxslt:get_variable($_XDOCTX, 'RTotalVar') + INVAMT)?> xdoxslt:get_variable($_XDOCTX, 'RTotalVar')?>	Sets the value of RTotalVar to the current value plus the new Invoice Amount. Retrieves the RTotalVar value for display.
EFE	<?end for-each?>	Ends the INVOICE group.

3.PAGE BREAKIn order to have different set of page number for each customer’s document you can use a section break.Double click on the first ‘for-each’ for Customer Name text form.Change the ‘for-each’ loop to ‘for-each@section’Example:Before<?for-each:G_CUSTOMER?>After<?for-each@section:G_CUSTOMER?>Click OK.  CONDITIONAL 4.FORMATTING
The XSL code you need is:<?if:TRANS_AMOUNT_REMAINING >1000?><xsl:attribute xdofo:ctx="block" name="background-color">red</xsl:attribute><?end if?>.

5.  BARCODE
Select File à Properties à Custom Tab
In the Name field enter: xdo-font.Free 3 of 9.normal.normal – notice the name of the font must match the name of the font in the MSWord font drop downIn the Value field enter: truetype.c:\windows\fonts     \FREE3OF9.ttf  

6.SORTING:
<?sort:TRANS_AMOUNT;'ascending';data-type='text'?> 

7. Bursting
What is a bursting file ?Its an XML file which tells bursting enginea)      to split the XML File created.
b) to deliver the Documents.
b) to deliver the Documents.b) to deliver the Documents.b) to deliver the Documents.b) to deliver the Documents.Advantages of XMLP burstinga. No Additional Coding, just need to create XML bursting File. ( Isn't it exciting , otherwise we would have to write java program to split the XML File and then use XMLP delivery manager API to send emails .b. Process of Creating XML data Definition and Template remains AS – IS .

8.BLOB & CLOB

• Can’t generate xml data for CLOB more than 40 kb in report builder.

• BLOB Images are supported in XML Publisher 5.6.2 or later.

• Encode BLOB to CLOB use this following function:

• CREATE FUNCTION getbase64( p_source BLOB )

• RETURN CLOB

• IS

• v_result CLOB;

• BEGIN

• DBMS_LOB.createtemporary(lob_loc => v_result, CACHE => FALSE, dur => 0);

• Wf_Mail_Util.EncodeBLOB ( p_source, v_result);

• RETURN ( v_result );

• END getbase64;

• To show BLOB Images use the following code:

• <fo:instream-foreign-object content-type=”image/jpg”><xsl:value-of select=”.//IMAGE_”/></fo:instream-foreign-object>

• XML Publisher supports in general right now only JPG, GIF and PNG.

XML Publisher Report Output

Where do I get the XML file or request output file for my BI Publisher Report / Concurrent Request?

A quick post ... it's been a frenetic few months, so blog posts a bit thin lately but plenty of good stuff in the pipeline!
Generally when I'm designing a BI Publisher/XML Publisher report I'll create a concurrent program that generates XML, I'll follow the usual steps:

1. Setup the concurrent program to output Text
2. Run it to ensure its working
3. Switch the concurrent program to output XML
4. Run the concurrent program to output XML
5. SFTP/copy the o*.out XML file from $APPLCSF/$APPLOUT
6. Load it into Word via the BI Publisher Desktop plugin and design the layout "on the run".
7. Setup the BI Publisher Data Definition/Template
8. Perform system/unit tests

Is there an easier way to get the XML file from a concurrent request? Sure is - nice and easy through the front end.

Get the XML File from a BI Publisher based concurrent request/report:

1. View->Requests
2. Find the request you're interested in
3. Click on Diagnostics button
4. Click the View XML
5. Save the file to your PC by doing File->Save As, *.xml

But where does the XML file live?
Well, by default it is $APPLCSF/$APPLOUT/o{REQUEST_ID}.out.
But if that's the XML file, then where does the actual output file reside as the location of the XML file is where normal request output resides?

Get the Request Output File for a BI Publisher based concurrent request/report:

The output is $APPLCSF/$APPLOUT/{REPORTNAME}_{REQUEST_ID}_{COUNT}.PDF|RTF|EXCEL|HTML
Where

• REPORTNAME is the concurrent program short name,
• REQUEST_ID is the concurrent request ID
• COUNT is a counter based on the number of times a request has been re-published.
• One of PDF, RTF, EXCEL, HTML is the file extension/type dependent on the output formats chosen.

An example is: XXXX_FNDSCURS_2803880_1.EXCEL

What is BI Publisher?

It is a reporting tool for generating the reports. More than tool it is an engine that can be integrated with systems supporting the business.

Is BI Publisher integrated with Oracle Apps?

Yes, it is tightly integrated with Oracle Apps for reporting needs. In 11.5.10 instances xml publisher was used, in R12 we can it BI Publisher

What is the difference between xml publisher and BI Publisher?

Name is the difference, initially it was released on the name of xml publisher( the initial patchset), later on they have added more features and called it Business Intelligence Publisher. In BI by default we have integration with Datadefinitions in R12 instance. Both these names can be used interchangeably

What are the various components required for developing a BI publisher report?

Data Template, Layout template and the integration with Concurrent Manager.

How does the concurrent program submitted by the user knows about the datatemplate or layout template it should be using for generating the output?

The concurrent program ‘shortname’ will be mapped to the ‘code’ of the Datatemplate. Layout template is attached to the datatemplate, this forms the mapping between all the three.

What is a datatemplate?

Datatemplate is an xml structure which contains the queries to be run against the database so that desired output in xml format is generated, this generated xml output is then applied on to the layout template for the final output

What is a layout template?

Layout template defines how the user views the output, basically it can be developed using Microsoft word document in rft (rich text format) or Adobe pdf format. The data output in xml format (from Data template) will be loaded in layout template at run time and the required final output file is generated.

What are the output formats supported by layout template?

xls, html, pdf, eText etc are supported based on the business need.

Do you need to write multiple layout templates for each output type like html/pdf?

No, only layout template will be created, BI Publisher generates desired output format when the request is run

What is the default output format of the report?

The default output format defined during the layout template creation will be used to generate the output, the same can be modified during the request submission and it will overwrite the one defined at layout template

Can you have multiple layout templates for a singe data template?

Yes, multiple layouts can be defined, user has a choice here to use one among them at run time during conc request submission

Where do you register data and layout templates?

Responsibility: xml publisher administrator responsibility --> Home --> Data Definitions

Responsibility: xml publisher administrator responsibility --> Home -->Templates

I want to create a report output in 10 languages, do I have to create 10 layout templates?

No, BI Publisher provides the required translation for your templates, based on the number of languages installed in your oracle apps environment requires outputs are provided

What is the required installation for using BI Pub report?

BI Publisher deskop tool has be installed. Using this tool you can preview or test the report before deploying the same on to the instance.

How do you move your layout or data template across instances?

xdoloader is the utility that will be used.

What is the tool to map required data output and layout templates so that they can be tested in local machine?

Template viewer will be used for the same.

Which component is responsible for generating the output in xml format before applying it to layout template?

DataEngine will take DataTemplate as the input and the output will be generated in xml format which will then be applied on layout template

Can BI publisher reports be used in OAF pages?

XDO template utility helper java classes are provided for the same.

Name some business use cases for BI  reports?

Bank EFT, customer documents, shipping documents, internal analysis documents or any transaction documents

How do you pass parameters to your report?

Concurrent program parameters should be passed, ensure that the parameter name/token are same as in the conc prog defn and the data template

What are the various sections in the data template?

• Parameter section
• Trigger Section
• Sql stmt section
• Data Structure section
• Lexical Section

What does lexical section contain?

The required lexical clause of Key Flex field or Descriptive FF are created under this section

What triggers are supported in Data template?

Before report and After report are supported

Where is the trigger code written?

The code is written in the plsql package which is given under ‘defaultpackage’ tag of data template.

What is the file supporting the translation for a layout template?

xliff is the file that supports the translation, you can modify the same as required.

How do you display the company logo on the report output?

Copy and paste the logo (.gif. or any format) on the header section of .rtf file . Ensure you resize per the company standards.