Editing Oracle Reports

If you’re like me, the above picture will probably have given you a frisson of dread. Just a little. The example here is not a very complicated report, just a few repeating groups based on quite a nice data model. What catches me every time, however, is a few little apparent quirks in Oracle Reports – and I’ve decided to post them here so that I’ll remember them next time I have to maintain someone’s reports. I keep forgetting them because I don’t have to work with Oracle Reports very often.

(These instructions are for Oracle Reports 10g – 9.0.4.0.33)

1. When you change the structure of the query, it will often change the order of the labels in the model editor – and the order is important. So, don’t forget to check that the order of the columns matches the order of the columns in the query.
2. Don’t forget to add the data links between the query groups. Otherwise, you may find the report takes a looooong time to run – if the status page says something like “Generating page 557,634…” for a report you expected to fit on 1 page, this may be the cause.
3. If you modify a query, you may find that some or all data links to that group will now be invalid (e.g. if you add a GROUP BY to a query that is a child group, you may find that at runtime it will simply ignore the data link entirely – leading to the dreaded million-page report). You may have to delete the data links and recreate them. To create a data link:
   1. Click the Data Link icon (View -> Tool Palette)
      
   2. Mouse down on the join column in the parent group
   3. Drag the line to the join column in the child group, and release.
      If the Data Link cannot be created for some reason (e.g. because of a Group By in the child group), Reports will then create a generic “group link” – which means you need to add the join criteria manually to the WHERE clause in the child query.
4. When adding a calculated field, don’t forget to change the default data type from Number to Char or Date if applicable. Otherwise, you’ll probably get something like “Invalid value for CF_BLA” at runtime.
5. When editing the print layout, don’t forget to review these important modes accessible from the toolbar. In particular: Confine On / Confine Off, Flex On / Flex Off, Select Parent Frame. For example, to move something from one parent frame to another:
   1. Select the object.
   2. Set Confine Off.
   3. Move the object to the desired location.
   4. Set Confine On.
   5. See if the hierarchy is now all messed up. If so, Undo and try again – make sure the object is fully within the target parent before you set Confine On.