How to integrate BI Publisher in Oracle Forms – Part I

In the next couple of blog entries I would like to give a step by step introduction on how to integrate Oracle’s offspring BI Publisher into Oracle Forms. Since the latest release of Oracle Forms (12.2.1.3.0) will be the last release with Oracle Reports there is the neccessary to move to another technology. Maybe in further posts I will also explain other possibilities like Jasper Reports etc.
I assume that you’ve installed Oracle Forms and the BI Publisher in the version 12.2.1.3.0 and will not explain how they need to be installed. For the demo I will use Scott/tiger to get some data into Oracle Forms and the BI Publisher.

Datasource & Datamodell

First we will create a Demo report in the BI Publisher. To do so login to the BI console by opening following URL in your Browser:

http://<Servername>:<Port>/xmlpserver

Add your database by clicking on “Administration”>”JDBC-Connection”:

Unbenannt

Click on “Add Data Source” and configure your datasource based on your installation.

Unbenannt2.PNG

Next we need to create a new datamodel for the report that we want to call from Forms. To do so we go back to the main window and click on “datamodell”. Here you can create your datamodell directly by adding a new SQL-Query and write your SQL or you can use the SQL builder. I’ve created a new SQL-Query named “Employees_in_department” with the following SQL:

select "DEPT"."DNAME" as "DNAME",
 "EMP"."ENAME" as "ENAME",
 "EMP"."JOB" as "JOB",
 "EMP"."HIREDATE" as "HIREDATE",
 "EMP"."SAL" as "SAL"
 from "SCOTT"."EMP" "EMP",
 "SCOTT"."DEPT" "DEPT" 
 where "DEPT"."DEPTNO"="EMP"."DEPTNO" 
group by "DEPT"."DNAME",
 "EMP"."ENAME",
 "EMP"."JOB",
 "EMP"."HIREDATE",
 "EMP"."SAL"

Make sure to use your new datasource “FormsDemo”. Now we can click on “Data” and produce some demodata, that we can use to see how our report will look like. Select the amount of rows you want to query and click on “Save as sample Data”. Don’t forget to save your datamodell!

Unbenannt3

Create Report & Layout

Now we can create the actual report. For this I will use the online builder rather then the word plugin. Go to the main window and click on “report”. You can either use the wizard or build the report manually. Select your datamodell, click on “Use Report Editior” and “Finish”. You will be asked, where on the server you would like to save the report. Store it in the same folder as your datamodell.
Now you can create a Layout for your report. Select “Chart and Table”. Switch the demo chart to a pie chart and add the employee name column as value and the department name as series.
In the table section add the departmant name, employee name, job, and salary and group the table by the department column by selecting the column and choose “group left”.

Unbenannt4

Click on save. Et voila – you’ve created a demo BI Publisher Report. You can click on “Done” and “View Report” to get a preview of the report.

In the next blog post I will explain how to build a demo form, which executes the report in Forms 12c.

Advertisements

ADF: Handling deletion in TreeTables

For the current project that I am working for, we had the requirement to display information in a table that should consist of Master and Detail rows. I knew that there is a component in ADF to solve this requirement quite easily: af:TreeTable.

af:treeTable

So I added my master as a TreeTable component and added the detail as a new level. Under display attributes you can select the attributes that shall be displayed.

Unbenannt

After adding the af:treeTable component you have to adjust the components in your page –

Unbenannt

for example will the added attributes be displayed in one af:outputText component in one node. To change that add new columns to the tree table by performing a rightclick on it -> insert inside Tree Table -> column. You can choose the values by using writing for example:

value=”#{node.Name}”

Hint: In Jdeveloper version 12.1.3 I faced the issue that there was only an autosuggest for the Master attributes. Just type your detail attributes likewise, seems like this is a bug.

Deleting selected row

When it came to deleting rows I had the same problem as probably  many more developers before me: I thought that I can just use the standard delete operation from the bindings. Well that is just working for the master. So I did some research and found following blog entry:
http://www.jobinesh.com/2010/05/crud-operations-on-tree-table.html

The author discribes how to implement methods in the Bean to extract the RowIterator and the key of the af:TreeTable. With that you can implement a method in the model to delete the any selected row and add it as a client implementation.

After that I asked myself, if that was all or if I would have to implement some more. You have to modify your association to be able to delete Master records and cascade the deletion to the details. For that, open the association and click on relationship. Under behavior you find the option composite association & implement cascade delete. When you select this option, the deletion of a master row will result in deleting all child records.

Unbenannt

 

 

 

 

 

 

 

JDeveloper Plugin for Forms2ADF Migration

I’ve migrated my first Oracle Forms module last week. It is the first time for me to be part of a migration project. The colleagues that I am working with instead have a high experience when it comes to ADF and migrating Forms to this technology, while my focus the last couple of years was mostly Oracle Forms. I think that is a possible scenario for many projects, where the aim is to migrate Forms to ADF.

So how can Forms developers start to migrate their application? The first step for me was to gather information on ADF, its architecture and the basics. I tried this with some self training, online materials and the help of my colleagues. When I came to the point to migrate the first module by myself I used the Forms Developer & PITSS.CON to understand how the form worked before and how the workflow was. There are people out there, that tell you that tools won’t do the job for you – and I agree. But they can make your job as a developer much easier. My colleague Mathias Waedt has developed a plugin for JDeveloper, that helps you as a developer when struggling with a migration. It helped me to understand which objects in ADF have been generated and which not. It showed me, were I had to put my effort into. The plugin fetches its information from the PITSS.CON repository.


Unbenannt

When the plugin is started it shows the basic information of the project like the database scheme, the names packages for the different objects (entity objects, view objects, etc.) and some other parameters. With a click on the details tab it is possible to see information of single forms. The form summary tab displays information regarding how many Forms objects already have been transformed to ADF components with PITSS.CON. This gave me a first overview of the upcoming work for that specific module. I saw, that most of the graphical objects were generated and only small modifications  were neccessary here, while I had to modify the business logic that was formerly located in triggers and program units.


Unbenannt2

 

With a click on Form Details it is possible to see which objects have been generated and which not. So for me as a newbie it was a big relief to use this kind of a checklist.


Unbenannt3

You can see all the objects from Oracle Forms and the corresponding generated objects in ADF – with a doubleclick you will directly jump into the generated object (e.g. Java Bean). As a future functionality it is planned to mark the objects that were added by the developer and are considered to be done. So you can work your way through the module without the danger to forget a functionality that was present in Forms up to 100%. The plugin is currently in the beta phase.

ADF Tip: Custom Excel Export Performance

I’ve been working on my first ADF-project the last couple of months and its time for a first blog post regarding ADF-development. We had the requirement to export data from the new developed ADF-Application to Excel. The standard Excel Export functionality was not sufficient, since also the chosen search criteria and other values should be exported, too.

For that purpose we used the docx4j project, with which the manipulation of excel, word and powerpoint files is possible. To export the data from the view objects to the java class that is responsible for creating the excel, the view object class and the view row class was created on view object level. In the view row class we used following code to export the data of the view object:

ModelUtils.getText(writeXML(1, XMLInterface.XML_OPT_ALL_ROWS));

In the beginning the view object had a Master-Detail-Relationship. When testing the export with a huge amount of data we ran into performance issues. We found out, that the relation was the cause of the bad performance and merged the different view objects into one view object – with that change the performance increased a lot. It seems that ADF evaluates all details and concatenates the resulting XML – and that takes time.

So when you have the requirement to export a lot of data and the standard ADF functionality is not enough, keep in mind that the XML-export could cause performance issues due to a Master-Detail relationship.

How to enable scrolling in Forms Developer Object Navigator

Since I started developing Oracle Forms within the Forms Developer there has always been an issue that was quite annoying: The fact, that srolling in the object navigator is not possible. But there is a tool out there, which enables additional mouse support called KatMouse. It provides universal mouse scrolling – here the link to download the tool:

http://ehiti.de/katmouse/

After the installation and start of KatMouse it is possible to use the mouse wheel in the Forms Developer which is not a big deal – but it makes life easier. Thanks to my colleague Peter Kopac for the hint.