In this article we will look into the most frequently asked interview questions on BI Publisher Reports.
If you haven’t gone through the BI Publisher-related posts, have a look at them to get most of the answers to the questions listed below.
Interview Questions on BI Publisher Reports:
- Can we create multiple data sets which aren’t interdependent in BI Reports? – Yes we can create multiple data sets and use them on the templates rtf, excel etc. The data sets could be linked by a common field or they can be independent too if they refer to completely different set of data.
- Can we create a bursting query to burst the complete output to a single FTP server without splitting by any key? – yes we can add a dummy value to the dataset and use that dummy value as split by key to send the complete output to FTP server.
- Can we use before or after triggers in BI Report in Fusion? – Only the seeded reports can use the before and after triggers as we do not have that scope. Oracle creates some complex reports using the before and after triggers.
- Can we write Non-Standard SQL in BI Report and what’s the advantage of it? – Yes we can create procedure style sql queries using pl/sql, however I haven’t seen a use case where we had to use it.
- Can we use an attribute selected in one dataset in another dataset? – Yes we can select the attribute in another data set by referring to the attribute name like :PERSON_NUMBER, however both data sets needs to be grouped to make it work
- What is the difference between Split by and Deliver by fields while setting up bursting? – Split by helps to split the xml output of the report into multiple xml data blocks. It means single xml output is subdivided into multiple xml outputs. The Deliver By element is the element from the data that governs how formatting and delivery options are applied. In most of the cases, both split by and deliver by will contain the same attribute.
- How to turn off the auto-run for BI Reports? – In the report properties, General tab, there is an option with name “Auto Run’ which defines if the report should run automatically or wait for the user input.
- Can we achieve dependent parameter lookup for BI prompts? – Yes it is possible to add a dependent parameter by using the first parameter in the second parameter LOV. Ex: We can use the :PAYROLL_NAME in the pay period LOV
- Can we make an input parameter required on BI Report? – Yes. We can mark the input parameter as required on the parameters screen.
- Can we get the BI output in .csv format by using a custom template without input values getting shown on the .csv file? – No. We can use the seeded csv format from the list of output formats, however, it will also add the input parameters as columns. If we design an etext template, the data will be comma separated but the file extension will be .txt only.
- In which cases do we use rtf/etext and excel templates?- When there is a need to format the output and perform grouping on data and sorting or if there is a huge list of fields that needs to be shown on the output.
- If we use excel template can we get the output on the interactive viewer online when we run the report? – No. if we use an Excel template, we cannot view the output in an interactive viewer. The output will be.xls only.
- Can we get excel output in multiple worksheets split by department/business unit if there is a requirement? – Yes it is possible using excel template. There are some advanced functions in excel template that need to be used to achieve this requirement.
- Is it possible to generate an excel output with different tabs using rtf template? – Yes it is possible. Use <?spreadsheet-sheet-name: {‘Test’}?>
- Can we show person images on BI Report? – Yes we can show dynamic images on BIP.
- Can we create master detail BI reports with one linking to another using an hyperlink? – Yes it is possible. We have to design a template which uses hyperlink and redirect the link to another detail report.
- Can we use deep links in BI Report and pass parameters to navigate to that page directly when an employee/manager receives an email using bursting? – Yes it is possible and only some deep links allow the usage of parameters. So it is possible on case to case basis.
- Can we create BI Report which produces output in HDL format so that it can be loaded directly on HDL without any modifications to it? – We need to design the etext template with pipe delimiters and format the data to generate the HDL output. We just need to rename the file and import it.
- Can we add the BI Reports to Dashboard? How does the BI prompts work? – Yes we can add BI reports to Dashboard. However if there are prompts for BI Report, then we need to create separate dashboard prompts and then link them to the BI Report prompts.
- Can we generate an encrypted output from BI Report? – If the FTP server is encrypted using pgp key, then we can use bursting query to send output to FTP server to achieve encrypting of file.
- Is it possible to upload the output of BI Report to UCM? – yes it is possible by writing a bursting query
- How to add dynamic date parameters to BI Report parameters? – We have dynamic date defaults
- Can we run the reports from Payroll flow? – yes, we need to create a custom payroll flow pattern and add the BI Report to it.
- Can we change the default timeout settings of BI Publisher report? – Yes, we can override the default timeout settings
- Can we retrieve the XML output of the BI Report which is scheduled? If yes how to retrieve it? – Yes we can retrieve the output by navigating to the report history. It is only possible if you inherit the BI Administrator privilege.
- How to enable diagnostic log for BI Reports? – While scheduling the report, if the diagnostics are turned on, we can view the diagnostics after the run.
- What are session variables and how to use them in BI Report? – Yes there are pre-defined session variables that can be used in a query to get the logged-in user information and some properties.
- Can we achieve QR code in BI Report? – Yes. It is possible to achieve QR code using the qrcode function
- Is it possible to add digital signature to BI Report? – Should be possible
- Can we create multi-lingual reports in BI Publisher? – Yes, we need to install language packs and create templates for all languages that are required and load them.
- How to customize seeded templates? Will the seeded template also change when we change the custom template? – We need to navigate to xmlp server and then customize the report. Customizing a report creates a similar folder structure under the custom folder.
- How to set custom permissions to BI Report? – Permissions can be set using the More > Permissions for the BI Report and Data Model.
- How to set custom scheduling of BI Report to schedule on alternate days and skip the first and last day of the month? – We can create a payroll flow pattern and then use flow schedule fast formulas
- What’s the use of schedule triggers in BI Report? – They are used to skip schedules
- If a schedule trigger is setup on one data model, can we use it to schedule another report as well? – yes it is possible as long as the schedule trigger is able to pull true/false.
- Can we invoke BI Report using web services? – Yes it is possible using SOAP service
- What is the output format of BI Report when you run it using web services? – it would be base64 encoded. We need to base64 decode it to get the actual output.
- Can we add BI Report as hyperlink to the navigator and hide the BI catalog, headers and other options? – yes it is possible by using dynamic hyperlink and using xmlpserver paths.
- Can we create Job and Job Set for executing multiple BI Reports at once? – Yes it is possible to create Job and then Job Sets and run them using schedule process.
- Can we create dynamic password pdf and excel output using BI Report? – We can create dynamic password protect pdf but for Excel we can only set hardcoded password
- Can we get the CLOB fields in html format on the BI output? – Yes, we need to use htmltofo function to get the output formatted
- Can we create audit report for Business object changes? – We need to build query based upon the audit tables that end with _ after the audit is turned on
- Can we audit the BI Report and check which report was run by which user and when? – Yes, we need to use the BI Audit mechanism
- Can we create PDF templates with form fields and use it to fill information using BI Reports? – It is possible, however the pdf template needs to be created using some pdf editor
- Can we use flex fields on BI Reports? – yes, we need to identify the underlying tables for flexfields and use them in query.
- Can we use an excel file as data source in BI Reports? Can we map the excel field to another data set field to get the output? – Yes. we can add Excel data sources and also link them to the SQL Query-based data sets.
- Is it possible to use web service as an input to the data model? – Yes, we have the option to use web service as a source for the data set.
- Can we get the zipped pdfs directly from BI Report without using any additional coding outside of BI? – Yes, it is possible without any additional coding.
- How to use the caching feature in BI Reports and is it advisable to use it every time? – We can use the caching feature if the output doesn’t change by the end user.
- What is the usage of XSL style sheets and how do they work with BI Reports? – XSL stylesheets are used to format the default XML generated by the BI Report. It is mostly used when the third-party system accepts only formatted XML as input which the BI Report can’t generate.
Can you pls post the answers for this?
Sure. Will try to post them some time in the future.. I would be happy if some audience take time to answer these and reach out to me. For HDL Questions, Phani has come up with the answers, I have verified and updated the answers..