XLS Templates in Oracle BI Publisher (XML Publisher)
Good day, in this article I want to talk about my experience with BI Publisher and drawing up templates for MS Excel in the native * .xls format for this program.
Working with Oracle eBS, from time to time it becomes necessary to create additional reports (and, accordingly, templates in BI Publisher). In this case, there was a rather complicated report, which was collected from tables filled with data in the BEFORE REPORT trigger of the same report. The number of columns in the report could change dynamics.
')
The template was originally made in RTF format. For each number of columns was provided for its own version of the report. At the output we had to get the XLS file with the data we wanted.
When the output file is an XLS file, we may encounter the following problems, or to be more precise, restrictions:
1) We want the Excel output file to look exactly like that, because we need the right columns to be right where we need, and not go somewhere.
2) So that data types are transferred to Excel output, that is, that numbers have a numeric data type in Excel, dates have a date type, and so on ...
3) When transmitting data that starts with “0” (leading zeroes), leading zeros are truncated, that is, we need to preserve the integrity of the data.
4) The same problem appears when transferring data with zeros after the decimal point (fractional part).
If 3 and 4 problems can be solved by adding two spaces or special characters to the beginning / end of the attribute, then the rest is a bit more complicated.
In my case, I had to transfer the value “0000” to Excel without losing a single zero and not allowing any other characters in the cell.
More information about other limitations of Excel output files created using RTF templates can be found here.
As a result of a long search on the Internet, a mention of Real Excel Template was found in the Oracle blog .
That is, you can now create templateswithout leaving the cash register without leaving MS Excel. To do this, we need to create a standard * .XLS file and start to wake our wonder pattern in it.
First, a little about the differences between RTF and XLS templates.
1) In the RTF for the designation of elements, we had to put the tag
2) In the RTF to indicate the opening of groups, we had to put the tag
3) Be careful with what you write in the cell itself, since you can specify the type of data that will be in the cell using normal Excel functionality, and what is written in the cell should correspond to this type. The only thing that didn’t work out for me was to get a date at the output as a date, I had to write a small macro for this. Oh yes, now we can complement our template patterns with macros, which was not possible in RTF templates for Excel.
4) There must be a separate tab XDO_METADATA:
Everything that is written before “Data Constraints:” including the change is not necessary and it should be present in all XLS templates. That below is your function. XSL is supported, I do not know about the rest. If an element is outside groups, then it needs to be obtained via
When using the XLS template, I no longer needed to create a separate version of the table for each column number. It is enough to make a table for the maximum number of columns. Columns in which data will be present will be filled in automatically, in which data will not be and will remain empty. This avoids unnecessary work.
Additional information on the possibilities of creating such templates can be found at this link .
I hope this helps someone to avoid unnecessary trouble with drawing up templates.
Small preface
Working with Oracle eBS, from time to time it becomes necessary to create additional reports (and, accordingly, templates in BI Publisher). In this case, there was a rather complicated report, which was collected from tables filled with data in the BEFORE REPORT trigger of the same report. The number of columns in the report could change dynamics.
')
The template was originally made in RTF format. For each number of columns was provided for its own version of the report. At the output we had to get the XLS file with the data we wanted.
Problems encountered
When the output file is an XLS file, we may encounter the following problems, or to be more precise, restrictions:
1) We want the Excel output file to look exactly like that, because we need the right columns to be right where we need, and not go somewhere.
2) So that data types are transferred to Excel output, that is, that numbers have a numeric data type in Excel, dates have a date type, and so on ...
3) When transmitting data that starts with “0” (leading zeroes), leading zeros are truncated, that is, we need to preserve the integrity of the data.
4) The same problem appears when transferring data with zeros after the decimal point (fractional part).
If 3 and 4 problems can be solved by adding two spaces or special characters to the beginning / end of the attribute, then the rest is a bit more complicated.
In my case, I had to transfer the value “0000” to Excel without losing a single zero and not allowing any other characters in the cell.
More information about other limitations of Excel output files created using RTF templates can be found here.
Insight
As a result of a long search on the Internet, a mention of Real Excel Template was found in the Oracle blog .
That is, you can now create templates
First, a little about the differences between RTF and XLS templates.
1) In the RTF for the designation of elements, we had to put the tag
<?ELEMENT_NAME?> , And in XLS we must write in the properties of the cell, where we will have the element tag XDO_?ELEMENT_NAME? . Example:2) In the RTF to indicate the opening of groups, we had to put the tag
<? for-each: GROUP_NAME?> <? for-each: GROUP_NAME?> and to close the groups we had to put the <?end for-each?> , and in XLS we need to select the cells that will be filled with data from our group and set them as the XDO_GROUP_?GROUP_NAME? . It looks like this:3) Be careful with what you write in the cell itself, since you can specify the type of data that will be in the cell using normal Excel functionality, and what is written in the cell should correspond to this type. The only thing that didn’t work out for me was to get a date at the output as a date, I had to write a small macro for this. Oh yes, now we can complement our template patterns with macros, which was not possible in RTF templates for Excel.
4) There must be a separate tab XDO_METADATA:
Everything that is written before “Data Constraints:” including the change is not necessary and it should be present in all XLS templates. That below is your function. XSL is supported, I do not know about the rest. If an element is outside groups, then it needs to be obtained via
xsl:value-of select . All items in this tab should be accessed via .//ELEMENT_NAME.Afterword
When using the XLS template, I no longer needed to create a separate version of the table for each column number. It is enough to make a table for the maximum number of columns. Columns in which data will be present will be filled in automatically, in which data will not be and will remain empty. This avoids unnecessary work.
Additional information on the possibilities of creating such templates can be found at this link .
I hope this helps someone to avoid unnecessary trouble with drawing up templates.
Source: https://habr.com/ru/post/148041/
All Articles