How to schedule a report with timestamp in filename

 Requirement 
Schedule a report to deliver output file with timestamp in filename so it can be differentiated 

Solution
Dynamic filename can be achieved using 2 ways - 

1) User delivery option manually in schedule

In this method in delivery tab, select the delivery method and use below syntex in filename - BIPReport_%y%m%d%H24%M%S.txt

2) Define bursting and use SQL method of appending timestamp in filename parameter -  

'BIPReport_'||to_char(SYSDATE, 'YYYYMMDDH24MISS'||'.txt' PARAMETER5

How to default Date value in parameter

Requirement

Default dynamic date parameter values 

Solution 

Use following predefined function to default date para,eters -

 {$SYSDATE()$}                            – current date

 {$FIRST_DAY_OF_MONTH()$} – first day of the current month

 {$LAST_DAY_OF_MONTH()$}  – last day of the current month

 {$FIRST_DAY_OF_YEAR)$}      – first day of the current year

 {$LAST_DAY_OF_YEAR)$}       – last day of the current year

*Dates are system date of the server on which BI Publisher is running.

Example 

How to add new attribute in Seeded Data Model - Simple Reports

Requirement 
Enhance a simple seeded report by adding additional attributes is very common. 

Solution1
We can enhance the seeded data set by modifying SQL statement using new attributes. 

Example
DataSet SQL -
SELECT
invoice_num
, invoice_id
, invoice_date
FROM
ap_invoices_all 

Need to display Business Unit Name. 

Modified Data Set SQL 

SELECT
invoice_num
, invoice_id
, invoice_date
,fbu.bu_name
FROM
ap_invoices_all apa
,fun_all_business_units_v fbu
WHERE
apa.org_id = fbu.bu_id

OR 

SELECT
invoice_num
, invoice_id
, invoice_date
,(SELECT bu_name from fun_all_business_units_v where bu_id = apa.org_id) bu_name
FROM
ap_invoices_all apa

Solution2
We can add a new data set for requested attributes and common attribute from existing data set. Then link the new data set using common attribute. 

DataSet1 SQL -
SELECT
invoice_num
, invoice_id
, invoice_date
,org_id
FROM
ap_invoices_all 

Need to display Business Unit Name. 

DataSet2 SQL 

SELECT
bu_id
,bu_name 
FROM
fun_all_business_units_v 

Linke DataSet 1 with DataSet2 using attribute Org_id (DS1) and Bu_id (DS2).

 

Profile Utilities:Diagnostics

 Utilities:Diagnostics to enable Access to  custom code under HELP > Diagnostics