Thursday, May 7, 2009

Displaying report parameters in Word using BI Publisher

There are many cases where you want to display the report parameters in the output. This is especially useful for scheduled reports. Finding no documentation on the subject online here is a step wise howto.

I explain how to do it in oracle. Your SQL Query will vary depeding on your database server e.g oracle uses ':' for named parameters, TopN queries are done using where rownum <>



Things to note:
  • put all your parameters in the query e.g. above you see I put BSC (:BSC) and DaysBack (:DaysBack) into the query
  • select from a table that has at least one entry :) otherwise no data will be returned from the query. Basically we are selecting the first row but putting are parameters as a column and not showing any of the the original tables columns.
Update: Oracle has a special table dual purely designed for this purpose :)
you should do:
select :BSC from dual

  • Parameters are case sensitive. Therefore it should be :BSC and not :bsc as shown in the screenshot.
Save. Next startup your bi publisher template builder and the parameters should become available in fields as shown:

Enjoy!