The Excel-Mania - Best Ways of Implementing Excel-Like ADF

Thiago

Hello people, good afternoon!

I would like to share with you a doubt i have implementing complex, matrix-like forms in ADF. More often than i'd like to hear, users ask for input forms that closely resemble Microsoft Excel, where the dimensions are layered both vertically and horizontally, and the intersection between them must contain an input or output component, allowing themselves to rapidly input the data they need or to create a web version of Oracle Reports' Matrix Report. Some cases are:
1) The user wishes to associate the employees (located in table EMPLOYEES) to the projects (located in table PROJECTS) in a screen that lays out the employees as columns and the projects as rows in a table. The intersection between them must contain a checkbox, which will insert a third record when selected, on a table called EMP_PROJ, which associates the employees to the projects.
2) The user wishes to input a timecard in a time control system. This system must have a screen that shows the days in the week as columns, and the projects the employee is working on as rows. On the intersection, we must provide an input text where he will enter the hours he worked on that specific project on that day.
3) The user wishes to see a screen that shows the Open Auctions they have in a Purchasing system as rows in a table, crossed with the suppliers that have bidded on these (as the columns). The intersection must contain the price each supplier bidded on each Auction.

As you can see, i run across this requirement A LOT . And, as much as i have polished my "Web-Like-Applications-Are-Not-Like-This" and "You-Are-Not-Working-With-Excel" speeches, our beloved users never learn
Nevertheless, i have been looking for a consistent way to implement this behavior, and i have found some options along the way. I would like to know your impressions on this matter, and if you have any "Best-Practices" that you imagine for this case.

CASE 1: Creating a Dynamic, On-The-Fly View Object by using ADF BC APIs and using af:table component to do the trick on the page
PROS: Less work in the View layer, Preservation of the Table CSS Layout (very nice blue header and light brown cells)
CONS: A LOT of work on the BC layer, creates an awful lot of View Objects, and generates tons of java code on the Application Modules.

CASE 2: Creating the model as usual and working with repeating layouts on ADF Faces (for example, enclosing an af:cellFormat on an af:forEach to repeat each column)
PROS: Simplifies the BC layer, making it much cleaner and maintainable.
CONS: Loses a lot of the declarative wonders of ADF Faces and also the CSS Layouts, since we are using cellFormat, rowLayout and tableLayout instead of table tag we have to manually apply the styles to the columns and rows.

Of course, both implementations take an awful lot of time. Any other implementation styles are quite welcome (ADF Wizards, step in!)
Anyone wants to discuss better ways of doing it?

Best Regards,
Thiago Souza

avrom

Well, you could do the following:

1) Create a stored procedure that assembles the data into a rowset with rows and fields like the format you want.
2) Create stored procedures that handle insert, update, and delete.
3) Create an entity object definition with all transient attributes. Make the attributes match the elements of one row.
4) Override doDML() in the entity object class to call your procedures (the doc explains how to do this). You might also need to do a bit of research and figure out if you need to override some other method so you can report rows with transient attribute changes only as needing posting. (getPostState(), maybe?)
5) Create a view object definition with entity-derived attributes based on your EO attributes.
6) Override the appropriate methods to call your data assembly procedure rather than execute a query (this is also in the doc).

Still kind of kludgy, but it keeps your business components pretty clean, especially if you use framework classes to do most of the work for you. (I have a partial example of how to do that here.) Of course, it keeps your business components clean by moving the real work to the DB, but some people find that more maintainable that a kazillion business components.

Hope this helps,
Avrom

Frank Nimphius-Oracle

Hi,

note that JDeveloper 11 comes with a pivot table that allows users to change the axis on the fly (so this gets you ahead of Excel).

Frank

Thiago

Hi guys,

Thanks a lot for the input. Frank, some guys here have already begun exploring ADF 11g TP4, so i will ask them if they have tried the pivotTable component, thanks for the tip. Any pointers to documentation regarding this feature?
Avrom, nice solution there, and nice blog post by the way... agree with you, that is one BIG stored procedure to develop, and moves all the fun to the DB but in shops like mine where most guys are expert in PL/SQL that is an advantage after all. Will definitely consider this approach when having such requirements. And that can be implemented in a reusable way either! Sounds nice...

If you have any other considerations, please share!
Thanks and Regards,
Thiago