Oracle BI Publisher Interview Questions

1)What is BI Publisher?

Ans:

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.

2)Is BI Publisher integrated with Oracle Apps?

Ans:

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

3)What is the difference between xml publisher and BI Publisher?

Ans:

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

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

Ans:

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

5)What is a datatemplate?

Ans:

Data template 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

6)What is a layout template?

Ans:

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.

7)What are the output formats supported by layout template?

Ans:

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

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

Ans:

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

9)What is the default output format of the report?

Ans:

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

10)Can you have multiple layout templates for a single data template?

Ans:

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

11)Where do you register data and layout templates?

Ans:

• Layout template will be registered under xml publisher administrator responsibility>Templates tab.
• Data template will be registered under xml publisher administrator responsibility> Data Definitions

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

Ans:

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

13)What is the required installation for using BI Pub report?

Ans:

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

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

Ans:

Ado loader is the utility that will be used. 

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

Ans:

Template viewer will be used for the same.

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

Ans:

Data Engine will take Data Template as the input and the output will be generated in xml format which will then be applied on layout template.

17)Can BI publisher reports be used in OAF pages?

Ans:

X DO template utility helper java classes are provided for the same.

18)Name some business use cases for BI reports?

Ans:

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

19)How do you pass parameters to your report?

Ans:

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

20)What are the various sections in the data template?

Ans:

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

Subscribe For Free Demo

21)What does lexical section contain?

Ans:

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

22)What are triggers, supported in Data template?

Ans:

Before report and After report are supported

23)Where is the trigger code written?

Ans:

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

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

Ans:

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

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

Ans:

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

26)How to Upload RTF Template from Backend?

Ans:

Using XDO Loader, we can upload an RTF from Backend.

27)How to Map RTF Template via Backend?

Ans:

Using FND_PROGRAM.ADD_TEMPLATE api we can map.

28)What are the Executable and Concurrent Program used to develop a report without RDF?

Ans:

XDODTEXE is used as Executable, Java Concurrent Program is used   Concurrent Program  for developing a report without RDF.

29)How to repeat the Header of the template on each and every page of the output?

Ans:

Use <?@section: ?> in order to repeat that specific section in each page of the output.also to reset the page number.

30)How many ways we can display images in a BI Publisher Report?

Ans:

It can be done in 5 ways:

• Direct Insertion into RTF Template
• URL Reference 
• OA_MEDIA directory reference
• Image from BLOB datatype from database
• Using UI Beans 

Learn Oracle BI Certification Course to Build Your Skills & Career

Weekday / Weekend BatchesSee Batch Details

31)How to do conditional formatting in XML Publisher?

Ans:

Conditional formatting in BI Publisher/XML Publisher can be done in these some of the following ways :

1. if statements:

• <?if:condition?>
• your data
• <?end if?>

2. if then else statements

• <?xdofx:if element_condition 
• then output1 
• else output2 
• end if?>

3. choose statements : for multiple conditions

• <?choose:?>
• <?when:expression?>
• <?otherwise?>

There are more ways for conditional formatting in addition to the above like column formatting ,row formatting etc.

32)What are the XML publisher tables?

Ans:

• PER_GB_XDO_TEMPLATES
• XDO_DS_DEFINITIONS_B
• XDO_DS_DEFINITIONS_TL
• XDO_DS_DEFINITIONS_VL
• XDO_LOBS
• XDO_TEMPLATES_B
• XDO_TEMPLATES_TL
• XDO_TEMPLATES_VL
• XDO_TEMPLATE_FIELDS
• XDO_TRANS_UNITS
• XDO_TRANS_UNIT_PROPS
• XDO_TRANS_UNIT_VALUES

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

Ans:

The concurrent program ‘short name’ will be mapped to the ‘code’ of the Data template. Layout template is attached to the data template, this forms the mapping between all the three.

34) How Does The Concurrent Program Submitted By The User Knows About The Data Template Or Layout Template It Should Be Using For Generating The Output?

Ans:

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

35) What Is A Data Template?

Ans:

Data template 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.

36)What Triggers Are Supported In Data Template?

Ans:

Before report and after report are supported.

37)‘IF ELSE’ condition in XML publisher 

Ans:

XML Publisher supports the common programming construct “if-then-else”. This is extremely useful when you need to test a condition and conditionally show a result. For example:

• p>
• IF X=0 THEN
• Y=2
• ELSE
• Y=3
• END IF

You can also nest these statements as follows:

Use the following syntax to construct an if-then-else statement in your RTF template:

• <?xdofx:if element_condition then result1 else result2 end if?>

For example, the following statement tests the AMOUNT element value. If the value is greater than 1000, show the word “Higher”; if it is less than 1000, show the word “Lower”; if it is equal to 1000, show “Equal”:

• <?xdofx:if AMOUNT > 1000 then ‘Higher’
• if AMOUNT < 1000 then ‘Lower’
• Else
• ‘Equal’
• end if?>

38)How to get SYSDATE in the header section dynamically when we run the report

Ans:

You cannot insert form fields in the Header section, but you can just insert the code to achieve this. For example: insert this in the header section to view the sysdate: You could format the date as you would like..

• <?xdofx: sysdate(‘YYYY-MM-DD’)?>

39) How to write a loop in rtf template design?

Ans:

<? For-each:G_invoice_no?>

  ……………………..<? End for each?>

40) How to design sub templates in rtf layout?

Ans:

Using following tags..

•   <? Template: template_name?>
•           This is Last Page
•                             <? End template?>

41)How to call a header or footer?

Ans:

Using this tag:

•  <?call:header?> and <?call:footer?>

    We have to use header section and footer section of the page.

Best Oracle BI Training Cover In-Depth Concepts By Top-Rated Instructors

• Instructor-led Sessions
 
• Real-life Case Studies
• Assignments

Explore Curriculum

42)How to break the page in specific condition?

Ans:

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

43) How to use section break?

Ans:

• <?for-each@section:G_CUSTOMER(This is group name)?>

44)  How to create multi layouts in XMLP?

Ans:

• <?choose:?>
•           <?when:CF_CHOICE=’VENDOR’?>
•          Your template….
•           <?end when?>
•                 <?when:CF_CHOICE=’INVOICE’?>
•                    Your template….
•                    <?end when?>
•                         <?when:CF_CHOICE=’RECEIPT’?>
•                          Your template….
•                         <?end when?>
• <?end choose?>

45) How to submit a layout in the backend?

Ans:

we have to write a procedure for this using the below code

• FND_REQUEST.ADD_LAYOUT
• (    
• TEMPLAT
• E_APPL_NAME     => ‘application name’,
• TEMPLATE_CODE           => ‘your template code’,
• TEMPLATE_LANGUAGE       => ‘En’,
• TEMPLATE_TERRITORY      => ‘US’,
• OUTPUT_FORMAT           => ‘PDF’
• );

46) How to display the images in XMLP?

Ans:

• url:{‘http://image location’}
•              For example, enter:
•                 url:{‘http://www.oracle.com/images/ora_log.gif’}
•                    url:{‘${OA_MEDIA}/image name’}

47)How to pass the page numbers in rtf layout?

Ans:

• <REPORT>
• <PAGESTART>200<\PAGESTART>
• ….
• </REPORT>

48)How to display last page is differently in XML Publisher Reports.

Ans:

• <?start@last-page-first:body?>    <?end body?>

49)Hide a Field in Oracle XML Publisher

Ans:

If You Want to Hide a Field:

•  <?if@column:FIELD!=’ ‘?><?FIELD?><?end if?> 

50)Displaying Page Totals in XML Publisher

Ans:

Add the below tag in the loop (with in for-each)

• <?add-page-total:’INV_AMT’;’INVOICE_AMT_PT’?>

Here INV_AMT is a field name and INVOICE_AMT_PT is a varable which stores the page total.

To show the page total , add the below tag in the footer or Header

• <?show-page-total: INVOICE_AMT_PT;’999G999D99’?>

XML Publisher Report Syntax

Sub Templates:

• <?template:header?>

This is Last Page

• <?end template?>

After that you can type in header or footer section,

• <?call:header?>

Last Page:

Take the any one of the insert field type in the following syntax,

Suppose,

• Field name : Last page body
• Status bar: <?start@last-page-first:body?><?end body?>
• This is last page

Page Break:

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

Section Break:

• <?for-each@section:G_CUSTOMER(This is group name)?>

Properties:

• <?PASSWORD?>

In XML we have to write in,

• <PASSWORD>welcome</PASSWORD>

Bar Code:

• <?register-barcode-vendor:’oracle.apps.xdo.template.rtf.util.barcoder.BarcodeUtil’;’XMLPBarVendor’?>
• <?format-barcode:JOB;’code128a’;’XMLPBarVendor’?>

Conditional Formating:

• <?if:ACCTD_AMT(This is column name)<1000?><xsl:attribute xdofo:ctx=”block” or “incontext” name=”font-weight”>bold</xsl:attribute><?end if?>

Incontext means: column level

Block means: row level

Multi Layout:

• <?choose:?>
• <?when:CF_CHOICE=’VENDOR’?>

• <?end when?>
• <?when:CF_CHOICE=’INVOICE’?>

• <?end when?>
• <?when:CF_CHOICE=’PO’?>

• <?end when?>
• <?end choose?>
  

Oracle BI Publisher Sample Resumes! Download & Edit, Get Noticed by Top Employers!DOWNLOAD

51)How do you create sub reports in BI Publisher?

Ans:

eg I have a report in OBIEE Answers with five columns – Year, Region, District, Product Type, Brand, Revenue

How do you create a BI Publisher report with two sub reports

• Year, Brand, Revenue
• Region, District , Revenue

OBIEE Report is as follows

• Create the BI Publisher Report
• Login into bi publisher through word and open the BIP report
• Insert the table wizard
• Add the columns Year, Brand and Revenue
• In the Group by select Year and Brand
• click the radio button of Group left
• click on finish.
• delete the ones indicated in circles
• create a table with 2 rows and 3 columns
• Give the column headings in the first row
• copy paste the following indicated by the arrow mark
• apply the necessary formating and delete the first table
• give the necessary aggregation as shown below
• create the other sub report similarly
• publish the template
• view it in bi publisher
• Thus we can create sub reports in BI Publisher

52)How do we create subtotals and Grand Total in BI Publisher?

Ans:

eg I have a report in OBIEE Answers with three columns – Brand,Type , Revenue.

How do I create a BI Publisher report with subtotals and Grand Total?

OBIEE Report is as follows

• Create the BI Publisher Report
• Login into bi publisher through word and open the BIP report
• Insert the table wizard and Add the columns Brand, Type and Revenue
• In the Group by – select Brand
• click the radio button of Group left
• click on finish.

For the above steps refer the screenshots provided in the above question.

• create a table with 4 rows and 3 columns
• Give the column headings in the first row
• copy paste the following indicated by the arrow mark
• Insert the following FieldsAddins- Insert – Field-
• Type Total beside inserted field
• Type Grand Total in the place shown
• Insert the field Revenue and double click on it
• specify the aggregation as sum and the number format
• delete the first table
• apply the necessary formating in the second table through word.
• publish the template
• view it in bi publisher

53)How to create multi layouts in XMLP ?

Ans:

 using below conditions

•             <?choose:?>
•             <?when:CF_CHOICE=’VENDOR’?>
•          Your template….
•             <?end when?>
•                 <?when:CF_CHOICE=’INVOICE’?>
•              Your template….
•            <?end when?>
•                         <?when:CF_CHOICE=’RECEIPT’?>
•                            Your template….
•                  <?end when?>
•                                            <?end choose?>
  

54) How to calculate the running total in XMLP?

Ans:

• <?xdoxslt:set_variable($_XDOCTX, ‘RTotVar’, xdoxslt:get_variable($_XDOCTX, ‘RTotVar’) + ACCTD_AMT(This is column name) )?>
• <?xdoxslt:get_variable($_XDOCTX, ‘RTotVar’)?>