Data Model   Bugs, Boojums   & Gotchas     
       
          The BI Publisher Guarantee: never knowingly overtested!  

 

 

 

Introduction

 

Some time ago we invited OBIEE developers visiting this site to detail any issues they had with BI Publisher, ranging from bugs, missing functionality, to interface usability issues. In this article, we’ll have a look at some 80+ “infelicities” in the implementation of the BI Publisher data model, some new, some that have been present in many previous releases (we believe that all the issues are still extant in the current version, 11.1.1.6). Each issue consists of a title, the question as posed by the contributor (tided up, combined where related, and abbreviated as necessary), and a discussion of any workarounds that we’ve found based on our experience of building BI Publisher data models.

 

For those IT managers amongst you who may be considering adopting BI Publisher and who may be having misgivings based on the number of issues raised against just this one component of BI Publisher, we should state that overall BI Publisher is a very powerful and flexible reporting suite, and that there are very few issues with the core reporting engines. Almost all the issues raised here relate to the BI Publisher GUI interface that is used to specify the metadata used by the data extraction engine. Once a data model is generating valid sample XML, there are unlikely to be many problems thereafter (though it’s important to perform some testing on the production platform after each release to ensure that any synchronization errors don’t corrupt the generated reports). Nonetheless, these issues will add considerably to the learning curve for developers new to the product, and will have a negative impact on the productivity of experienced developers who have encountered most of the issues in the past and who are familiar with the workarounds available.

 

We’d like to thank all our contributors and we hope that this article will be of some benefit to them and to anyone else who is battling with the BI Publisher data model GUI (we hope to add similar articles on other aspects of BI Publisher at a later stage).

 

 

Data Model Issues

 

It would be somewhat of an understatement to assert that the Data Model has not been very well tested. While the number of bugs encountered will depend to some extent on the BI Publisher and browser versions in use and on the complexity of the data models being constructed, it is very hard to see why Oracle has released the product in its present state. Even a few hours of casual usage will reveal a dozen bugs that add to the learning curve and have a negative impact on productivity, and a few days of systematic testing would be enough to uncover all the issues detailed below. It’s difficult to understand why Oracle would not have allocated at least this amount of time to testing each release, or why given that it had performed this perfunctory level of testing, it would then release the product with so many issues still outstanding, given the negative impact they are certain to have on the product’s reputation – as developers opine to management, “We didn’t have all these problems when we used to work with other vendors’ BI suites”, views that will, in turn, colour management's feedback to industry analysts, such as Gartner, on product robustness, usability, and productivity.

 

The BI Publisher GUI is characterized by the poor quality of its validation and consistency checking. For example, validation is often applied when a item is created, but omitted when the item is updated; items which shouldn’t be moved, can’t be moved when selected singly, but can be moved when part of a multiple selection; processes are initiated without validating that they can succeed, leading to infinite loops; and invalid bind variable and PL/SQL function references are replaced by nulls, so that corrupted XML is generated without any error or warning being issued.

 

Apart from those issues that correspond to functionality that just doesn’t work, there are others that are examples of poor interface design. As is so often the case with BI vendors, their product interfaces are designed by those who have considerable product expertise, an understanding that blinds them as to how these products will be perceived by those learning to use them, or their latest features, for the first time. Developers tend to learn to use a product on a trial and error basis, rather than by reading the documentation, so it’s very important that the interface leads them to make the correct “educated guess” as to functionality, and that any counterintuitive behaviour is well flagged up by way of informative error messages. That’s not to suggest that the BI Publisher documentation itself is very helpful. While the Data Modelling Guide provides an excellent overview of the functionality, with an increased use of diagrams that is very welcome, it so often falls down went it comes to the details needed to resolve an issue, by being either absent completely (functionality in the GUI that is not in the documentation, and vice versa), or by being completely uninformative (the sole description of a component being “operator” when if fact it is function possessed of a rather unusual syntax).

 

Miscellaneous:

 

*  The Error in the Error

*  Saving and reopening the Data Model doesn’t clear the Error

*  Data Model lost on Refresh

*  Descriptive XML Output not Refreshed

*  Error “Failed to Save File” when trying to save Data Model

*  Data Model hangs with “Saving ...”

*  Save gives “Are you sure you want to navigate away from this page” Message

*  Deleting a Data Model moves context to a different Folder

*  Temporary Data Models clog up Recent List

*  Action Menu Items not Visible

*  No Scroll Bar on Structure Diagram

*  Changes to XDM File are not applied

 

Expression Elements:

 

*  Expression converted to Non-existent Column with Data Model Corruption

*  Column Name in Expression is Overwritten

*  Recursive Expressions don’t Work

*  Modified Expression reverts to Original Value without Warning

*  Please do not use Invalid Characters ... for XML Tag Name

*  Can’t move Expression Element to Parent Subgroup

*  Moving Expression Element to Parent Subgroup corrupts XML

*  Moving Expression Element to Child Subgroup corrupts XML

*  Expression Naming in Groups that share Query Columns

 

Aggregate Elements:

 

*  Changing Aggregate Properties causes Reference Error

*  Properties won't open after creating Aggregate

*  Updating the Definition of a Global Aggregate Element deletes Global Expressions

*  Can’t move Element to Aggregate Drop Box due to Error Pop-Up Window

*  Deleting Data Link does not flag-up Invalid Aggregate Element

*  No Minimum Aggregate Function for Strings

 

Sorting and Ordering:

 

*  Can’t change Sort Order

*  Sorting by Element Value in any Group

*  Changing Element Display Order on Screen and in Generated XML

*  Reordering XML Output from Top-Level Sibling Groups

*  Reordering XML Output from Sibling Groups with a Common Parent

 

Groups, Data Links and Bind Variables:

 

*  Ungroup Command containing Aggregate causes Infinite Browser Loop

*  Ungroup Command converts Expression Element to Non-existent Column with Browser Warning

*  Group Link already exists, you cannot create Element Link here

*  Bind Variable Syntax

*  Group Link returns no Data or not all Variables are Bound

*  Query with Group link returns no Child Data

*  Data Link that joins on Multiple Columns

*  Lack of Bind Variable Reference Validation leads to Corrupted XML

*  Missing Bind Variable Alias leads to Element named NULL

*  Referencing Columns from Ancestral Queries

*  Referencing Global Elements in Group Queries

*  Creating Multiple Data Links from same Parent Group Element to Multiple Child Groups

*  Element-Level Data Link to Expression / Aggregate Element allowed but Generation Fails

*  Link Deletion converts Element-Level Data Link to Group-Level Data Link

*  Reference Error on renaming Groups

*  Can’t Rename Group

 

Group Filters:

 

*  Group Filter deleted without Warning when Group Link Created

*  Edit Group Filter Menu Item disabled after deleting Element Link

*  Variable is not able to return Type 4

 

PL/SQL Packages and Function References:

 

*  No Connection to Data Source

*  Oracle DB Default Package synchronization Error

*  Package does not exist in Default Data Source

*  Package exists in Default Data Source but Parameter is not declared as Global Package Variable

*  PL/SQL Parameter must be Declared Error occurs, even when it has been

*  Parameter to Global Packaged Variable Data Type Conversion

*  Global PL/SQL Element returns Null when Parameter passed to Packaged Function

*  PL/SQL Element Tag replaced by Null in XML

*  Intermittently working PL/SQL Group Filter leads to Security Breach

*  Data Model still runs when Packaged Function is renamed in Database

*  Syntax for referencing the Context Element in Argument to PL/SQL Function

*  Can’t see PL/SQL Package when adding Global Element by PL/SQL

 

In-Built Functions:

 

*  Operator “If” has no Definition

*  Operators “And” and “Or” don’t work

*  The “set” used by “Max” and “Min”

*  The “expr” used by “Avg” and “Sum”

*  Number of Arguments for “Concat”

*  String to Number Conversion Function

*  String to Number Conversion fails

*  Documentation on “Replace” and “Decode”

*  Function “Replace” doesn’t work

 

Bursting Definitions:

 

*  Bursting Definition Prerequisites

*  Bursting Definition is not validated

*  Split By and Deliver By Element Names not displayed

*  Bursting Query TEMPLATE defaults to first Layout when TEMPLATE is invalid or missing

*  Locale defaults to that of Layout when invalid or missing

*  Multiple Reports sent to the same File Delivery Channel overwrite one another at random

*  Deliver By Element can occur in Ancestor or Descendent of Split By Group

*  Bursting XML Block consists of all Split By Group Elements plus Descendant Group Elements

*  Split By and Deliver By Elements truncated

*  Sorting and grouping of Bursting Split By Groups

*  Full XPath Split By and Deliver By Expressions also needed for non-CLOB Sources

*  Bursting Report Job fails with no Output and “ORA-01789: query block has incorrect number ...” Error

 

 

The Error in the Error

 

Q: What’s wrong with the data model when I get an “End tag 'span' does not match the start tag 'img'” error when generating XML?

 

The problem is not with your data model but with the ability of the BI Publisher development team to write HTML. Whenever BI Publisher cannot generate XML due to some error you’ll see the following displayed in the browser:

            The XML page cannot be displayed ...
            End tag 'span' does not match the start tag 'img'. 
               Error processing resource 'http://<server>:7001/xmlpserver/servlet/xdo'. Line...
            <table style='background=color: #d9e5ef; margin-top: 10px;' width='100%' 
               cellspacing='0' cellpadding='0' border='0' summa...

The error occurs because BI Publisher has discovered an error, and has created a HTML web page to display an appropriate error message. Unfortunately, it has forgotten that HTML tags need to be properly closed. It has constructed an “img” element embedded within a “span” element:

 

*  <span><img src='/xmlpserver/resource/blafplus/generic/spacer.gif' width='5px' height='1px' alt=''></span>

 

but has forgotten to close the “img” tag, and so the browser complains about the incorrect HTML, and never displays the error message as intended.

 

Right click on the web page and select View Source (IE) from the menu. At the bottom of the HTML code you’ll find a “div” element with an “id” of “errordetail” that contains the text of error message; for example:

            <div class='errorMessage' id='errordetail' style='display: none; margin-top: 10px;'>
               oracle.xdo.XDOException: oracle.xdo.XDOException: oracle.xdo.XDOException:
               oracle.xdo.XDOException: java.sql.SQLException: ORA-06550: line 2, column 13:
               PLS-00302: component 'BUDGET' must be declared
               ORA-06550: line 2, column 1:
               PL/SQL: Statement ignored
            </div>

Whether or not the error message will actually be informative is quite another matter.

 

 

Saving and reopening the Data Model doesn’t clear the Error

 

Q: I’ve tried saving and reopening the data model to clear an error but it doesn’t work?

 

Saving and then reopening the data model is the easiest way to clear most errors that occur in the BI Publisher GUI. When it doesn’t work, what you need to do will depend on whether or not the data model has been corrupted.

 

If the data model is corrupted, then you have no alternative but to:

 

*  Edit the XDM resource file to remove / correct the corrupted code, or

 

*  Progressively delete parts of the data model in the hope that by doing so you will delete the corrupted code when you save it.

 

If the data model is not corrupted, then some structures occasionally hang around in memory even when the data model is saved and reopened. In these circumstances, try the following workarounds in turn:

 

*  Log out of BI Publisher and back in again.

 

*  Rename the data model to some other temporary name and then back again.

 

*  Bring down and restart BI Publisher.

 

One of these fixes will clear the error if the data model is not corrupted.

 

 

Data Model lost on Refresh

 

Q: If I save a data model under a new name, make some changes and decide I don’t want them, press refresh to get back to the version without the changes, then the previous version of the data model appears instead?

 

Let’s say you open a data model, DM1 – the name DM1 appears on the tab in the top, left-hand corner. You make some changes to it and save it as DM2 – the name DM2 appears on the tab in the top, left-hand corner. You make some changes to DM2, decide you don’t want them and press refresh (F5). Now you might expect that the last saved version of DM2 on disk would be displayed. But, instead, it’s DM1 that is displayed. If you don’t check the version carefully and start to add some additional functionality and save the data model, you’ll have applied changes to DM1 when you intended to apply them to DM2. The saved version of DM2 will still be on disk, so you now have to delete the changes from DM1, and apply them to DM2.

 

The remedy is to take frequent backups and always check following a refresh that the name of the data model has not changed, or avoid using refresh and always reopen a data model when you need to discard changes (though it takes more effort).

 

 

Descriptive XML Output not Refreshed

 

Q: The descriptive XML output schema doesn’t sync with changes made to display names?

 

This is an intermittent bug. When a change is made to a Display Name, either within the Properties pop-up window for an element or within the Table View on the Structure tab, then the corresponding Descriptive XML Output schema tag within the Output View on the Structure tab is not always updated. Save and reopen the data model to ensure that the Output View is synchronized.

 

 

Error “Failed to Save File” when trying to save Data Model

 

Q: I’m trying to save a data model over an existing model and get a “Failed to Save File” error?

 

Normally you can save a data model with the same name as an existing data model. When BI Publisher starts raising this error you need to bring the instance down and then up again (logging off and back in won’t help). As a short term fix, you can delete the existing data model before trying to saving the new one.

 

 

Data Model hangs with “Saving ...”

 

Q: I’ve saved the data model and it hangs with “Saving …”. The only change I made was some mods to a query before saving?

 

On occasion, when you attempt to save a data model BI Publisher will display “Saving …” , but will never complete the save. The failed save also locks up various aspects of the GUI functionality. While this issue seems to occur for a variety of reasons, in the context of modifying a query it could occur if you renamed the query and included an invalid character in the query name. A query name cannot contain characters other than letters, digits, and “_”. While validation is correctly applied when a query is first created, BI Publisher fails to apply the validation if the query name is changed. No problem will occur until you attempt to save the data model, at which point the invalid name corrupts the process. If you did change the query name, try opening the data model and make the changes again, without changing the name this time and see if the error persists.

 

 

Save gives “Are you sure you want to navigate away from this page” Message

 

Q: I’m getting a “Are you sure you want to navigate away from this page” message when trying to save a data model and nothing is saved. I get the same problem when I reopen the data model. Is there a workaround or do I have to rebuild from scratch?

 

You open a data model, make some changes, and press “Save”. You get an error message “Are you sure you want to navigate away from this page”. Irrespective or whether you press “OK” are “Cancel” the changes are not saved. You try “Save As” and get the same error. You delete the changes you’ve made and press “Save” but the problem persists. You open the data model again and press “Save” but get the same error message, so it seems that the data model is corrupted. However, rather than rebuild from scratch, if this second time around you press “Save As” the data model should be saved and the problem should disappear when you reopen it.

 

 

Deleting a Data Model moves context to a different Folder

 

Q: I’m deleting a data model from a catalog folder, but following the delete I’m now in a different folder?

 

This happens on occasion. You delete a data model from a folder using the BI Publisher GUI. Following the delete you expect to see the contents of the folder with the data model removed. Instead you’ve been moved up to the parent of the folder you were in previously, so you have to expand the folder you were previously in to see its contents, and start to make some more changes. The data model will have been deleted correctly, but it’s very tedious to have to keep navigating back each time if you’re making many changes to the folder contents.

 

 

Temporary Data Models clog up Recent List

 

Q: In the recent section on the home page I get files with names like adad227a-17e3-4de5-b60c-78d9de4b091f. How do I get rid of them?

 

These are temporary files. As soon as BI Publisher opens a new data model, it allocates a name to it and writes a header XDM / ATR file pair in the “_temp” directory. The XDM is not updated as information is added to the data model in memory, nor when the data model is saved to disk. The file remains in the “_temp” directory when a new data model is opened. Unfortunately, BI Publisher treats these temporary, empty data models like any others and displays them in the Recent list on the Home page, despite the fact that they contain no useful information. You’ll find them in the catalog in directory “MyFolders/_temp” and can delete them from there using the “More” drop down menu.

 

 

Action Menu Items not Visible

 

Q: When a group is close to the bottom of the page some of the menu items are inaccessible?

 

Yes - a slight design oversight here. The position of the group and element Action Menus is fixed with respect to the group, and as these menus are often longer than the list of group elements to which they are attached they can be partially hidden when a group is close to the bottom of the page. Pressing F11 to go to full screen mode can workaround this problem; otherwise, the group will have to be dragged up the page to access the menu item, and then back down again.

 

 

No Scroll Bar on Structure Diagram

 

Q: The diagram doesn’t fit on the screen but there is no scroll bar?

 

This can happen if you drag a group down to accommodate a growing data model so that part of the group is now off-screen. A scroll bar does not automatically appear on the right hand side. Clicking on some non-Diagram menu item and back again does not seem to have any effect, nor does saving and reopening the data model. Instead, press F11 to go to full screen mode and then press it again to return to the standard viewing mode. Doing so will make the scroll bar appear - you’ll have to do this each time you reopen the data model (note, using F11 to move to full screen mode may give you enough screen real-estate to view all the groups on the same screen).

 

 

Changes to XDM File are not applied

 

Q: I’ve made changes to the data model XDM file, saved it, opened it in BI Publisher, but when I run it the changes are not applied?

 

This is one issue where we can’t blame Oracle, as direct editing of the XDM file is not supported. This problem arises when you have one version of the data model open in BI Publisher, edit the XDM file on disk, and then reopen the same data model in BI Publisher. It’s odd, but opening the file doesn’t clear out the memory structures relating to the old version, so when you run the data model you'll see the results corresponding to the old version. But saving the data model before running it will clear out these structures and you’ll see results based on the edited XDM file, so the sequence is:

 

*  Edit the XDM file using a plain text editor, such as WordPad, and save the changes to disk.

*  Reopen the data model in BI Publisher.

*  Save the data model in BI Publisher.

*  Run the data model in BI Publisher.

 

 

Expression converted to Non-existent Column with Data Model Corruption

 

Q: For some reason an expression has been converted to a column and now can’t be deleted. It’s in the global group so I can’t delete the group. Do I have to rebuild the data model from scratch?

 

This is a particularly nasty bug. It usually happens after you get a reference error message:

 

*  <element name> element of group <group name> has a wrong reference [object]

 

when saving a data model. You clear the error and run the XML and find that some expression is no longer working correctly and is returning a null value in the XML. The expression may look okay on the Diagram tab, but when you click on its action menu icon you discover that the Remove Element menu item has disappeared. You reopen the data model, but the problem persists. Now the “fx” expression indicator has disappeared and the expression has been converted to a non-existent column with the same name as the expression.

 

Because the bug has converted an expression element to a query column element for a non-existent column and because a query column element cannot be deleted, the defunct expression element is also prevented from being deleted.

 

If the expression is not in the global group, you can add a dummy expression to the query with an alias having the same name as the former expression element. Once you save the query the group elements are updated and the element is now associated with the newly added select list alias making it a valid query column element. If you then delete the select list expression and alias from the query, the element will be deleted from the group.

 

If the expression is in any group, including the global group, it can also be deleted as follows. Add a dummy expression element to the group. Highlight both the dummy expression element and the element corresponding to the non-existent column. Then click on the action menu of the dummy expression element and select Remove Element.

 

If you have access to the XDM file that is used to store the data model, then the problem is easily remedied. Let’s suppose the expression has an Alias of RATING, a Display Name of Rating, and equals a literal string value of “A”. Then in the XDM file the expression is encoded as:

 

*  <element name="RATING" value="${'A'}" dataType="xsd:string" label="Rating" breakOrder="None" fieldOrder="0"/>

 

This bug corrupts the XDM file by changing the “value” attribute as follows:

 

*  <element name="RATING" value="RATING" dataType="xsd:string" label="Rating" breakOrder="None" fieldOrder="0"/>

 

So, to delete the expression delete the entire line, or to correct the expression modify the “value” attribute to include the “${}” syntax with the expression contained therein. While Oracle doesn’t support direct editing of the XDM file, you might well find that doing so is far more productive and much less frustrating than battling with the bug-laden BI Publisher GUI.

 

 

Column Name in Expression is Overwritten

 

Q: Can I prevent an expression column name from being overwritten by the alias?

 

No! When creating a new expression, you’re required to enter values for the Name, Alias, and Display Name. You save the data model and reopen it only to find that the Name (now called Column Name) field value has the same value as that which you gave to the Alias, and that the Column Name field is now non-updateable. It’s not possible to have a Column Name that differs from the Alias, as the XDM file only stores the Alias and the Display Name, but not the Column Name. So, it’s pointless and confusing for the BI Publisher GUI to ask for data that it’s immediately going to discard as soon as the data model is saved.

 

 

Recursive Expressions don’t Work

 

Q: When I create a recursive expression using the editor it seems to treat self-references as null?

 

It’s perfectly possible to define an expression, say G_1.E1 as ‘A’, save it, and then open it up again, only to find that E1 is now present in its own Available list in the left hand pane. You can then modify the expression to include a self-reference, say CONCAT( ‘A’, G_1.E1 ). Pressing the Validate Expression button will tell you the expression is valid. You can then save it and generate the XML, all without any error. The resulting XML will either treat the self-reference as a blank or will return an element with no value. Like the other mini-scripting languages within Oracle products, sophisticated functionality within BI Publisher, such as recursion, is not supported. Sadly, what also seems to be absent is sufficient product testing to ensure that recursive references cannot be created in the first place.

 

 

Modified Expression reverts to Original Value without Warning

 

Q: At times I change an expression, save the data model, but find later that the expression hasn’t changed?

 

This is a rather annoying feature. At times you modify an expression that defines an element, press OK, save the data model, but find on opening the expression editor again that the value is unchanged. This behaviour frequently occurs following a reference error even though BI Publisher indicates that the changes have been saved, so it’s worth checking the changes you’ve made following the last save whenever a reference error occurs.

 

 

Please do not use Invalid Characters ... for XML Tag Name

 

Q: I’m creating an expression and have entered TAX_RATE for the alias, but I’m getting a “Please do not use invalid characters … for XML Tag Name”. I can’t see what’s wrong and I’ve used TAX_RATE for the alias on other occasions without any problem?

 

It’s clearly not the error that BI Publisher is complaining about – TAX_RATE is fine as an alias. When creating an expression you enter values for three different names, Name, Alias, Display Name. But if you enter an invalid character in the Name field BI Publisher will display the “Please do not use invalid characters … for XML Tag Name”, suggesting that the problem is with the alias field – so you end up changing the alias field, but no matter what changes you make the problem still persists. So whenever you get this message when creating an expression check both the Name and Alias field values for invalid characters.

 

 

Can’t move Expression Element to Parent Subgroup

 

Q: Can an expression be moved to a parent subgroup if it doesn’t refer to child elements?

 

If should be possible to move an expression element from a child subgroup to a parent subgroup if the expression does not reference a child element – for example, if it references parameters and literals. However, sometimes BI Publisher won’t allow the expression element to be moved, even when the child subgroup contains other elements. In this case you’ll have to delete the expression element from the child subgroup and recreate it in the parent subgroup.

 

 

Moving Expression Element to Parent Subgroup corrupts XML

 

Q: I’ve manged – don’t know how – to move an expression from a child subgroup to a parent subgroup, even though it references a child element. How can a parent element reference a child, when there are many values for the child?

 

The answer is that it shouldn’t be possible to move an expression element from a child subgroup to a parent subgroup if the expression references an element that still remains in the child. It’s yet another example of the very poor level of validation and consistency checking performed by BI Publisher. As to the how. If you select an expression element together with a query column element in the child subgroup, then the action menu on the query column element will usually allow you to move both to the parent subgroup. Then when you run the data model, no error or warning will be issued. References to the child element in the expression that has been moved to the parent will be replaced by null in the XML.

 

 

Moving Expression Element to Child Subgroup corrupts XML

 

Q: When I move an expression element from a parent to a child subgroup the XML gets corrupted?

 

An expression element can be moved from a parent subgroup to a child subgroup as part of a selection that also includes one or more query column elements. In the moved expression element the references to parent group elements are altered by changing the name of the group prefix to that of the child group. When the data model is run no error or warning is issued, and the renamed parent group references are replaced by nulls, corrupting the XML.

 

In principle, there is no reason why a parent expression element can’t be moved to the child, since the parent expression element is well defined in the context of the child, but BI Publisher can’t handle it automatically. In order to achive this change, delete the parent expression element, and add the expression followed by an alias to the select list of the defining query. This alias will appear as a new query column element that can then be moved between groups without difficulty.

 

 

Expression Naming in Groups that share Query Columns

 

Q: When I create two groups based on the same table that have a column in common then one of the elements has a “_1” appended to it?

 

This is just a quirk of the BI Publisher GUI. There is no reason why the two elements need different names since they belong to different groups. Sometimes the suffix will disappear all by itself when you perform other operations, but you can remove it by editing the Alias / XML Tag Name in the Properties pop-up / Structure Table View.

 

 

Changing Aggregate Properties causes Reference Error

 

Q: How can I rename an aggregate element without raising an error??

 

An attempt to rename an aggregate element or to change any of its properties, such as Value If Null or Do Not Reset, produces an error when saving the data model:

 

*  <element name> element of group <group name> has a wrong reference [object]

 

As with renaming a group, reopen the data model to clear the error.

 

 

Properties won't open after creating Aggregate

 

Q: After creating an aggregate element the properties are locked?

 

This is an intermittent problem. Sometimes on creating a new aggregate element either the function selection drop-down list or the Properties pop-up window doesn’t appear after clicking on the relevant menu item. Try using the Table View on the Structure tab instead, or else save and reopen the data model.

 

 

Updating the Definition of a Global Aggregate Element deletes Global Expressions

 

Q: When I update the definition of a global aggregate element global expressions get thrashed. Is there a workaround?

 

When you change a global aggregate element you may find that all global expression elements have been converted to non-existent columns. The columns can’t be deleted directly, but there is a workaround (see the issue entitled, “Expression converted to Non-existent Column with Data Model Corruption”). You will then have to recreate the expression elements. It’s good practice to take a backup before modifying a global aggregate element. Then if this error occurs you can always restore the data model from the backup.

 

 

Can’t move Element to Aggregate Drop Box due to Error Pop-Up Window

 

Q: I’m trying to drag an element from a group to the aggregate drop box in another group but I get a message that a Group Link already exists. Once I cancel the pop-up and click on the element the pop-up keeps reappearing?

 

When both a subgroup and one of its descendant subgroups have data links defined and you click on an element in the subgroup the message “Group Link already exists. You cannot create element link here” appears. It is not possible to prevent it. While some functionality is disabled (for example, the aggregate element drop down list of functions), the desired result can always be obtained by other means (for example, by using the Properties pop-up). In the case of dragging an element to another group to create an aggregate, as long as you don’t release the mouse button the message will not appear and you can drag the element to the desired destination.

 

 

Deleting Data Link does not flag-up Invalid Aggregate Element

 

Q: I can delete a data link even when an aggregate element makes use of the link, and the aggregate element is left in the parent?

 

If you have a data link from a parent group to a child group, define an aggregate element in the parent based on a child element, and then delete the data link, the aggregate element is left in the parent. You can generate XML without any error and the aggregate element values will be either zero or empty depending on the aggregate function selected.

 

It would have been a better design to either prevent the deletion of the link while a dependent aggregate element exists, or, preferably – as the aggregate element will still work if the link is recreated – to colour-code the hanging aggregate element, in say red, so that it’s obvious to the developer that the aggregate element needs to be deleted or the link reinstated.

 

 

No Minimum Aggregate Function for Strings

 

Q: Is there a workaround that allows a minimum aggregate function to be used on strings in the same way as maximum?

 

This looks like another instance of very poor quality control. In general, the data model supports the following aggregate functions: Count, Average, First, Last, Maximum, Minimum, and Summary. Clearly, Average and Summary are only applicable to numeric types, so for a string type we would expect to have Count, First, Last, Maximum, and Minimum. If Maximum is available then there is clearly no technical reason why Minimum should not also be available, and each function is as likely to be of interest as the other. However, Minimum is missing from the GUI Function drop-down lists (and it also doesn’t work if coded directly within the XDM file).

 

There is a convoluted workaround. First sort the element instances in the descendant group from which the aggregate is being calculated. If sorting can’t be performed in the data source and the descendant group is a non-leaf subgroup, then sort this subgroup – ascending or descending – on the element of interest. If the group is a unitary group or a leaf subgroup, then split it into subgroups by creating a dummy literal expression within the group, and then by using Group By with all elements other than the dummy expression selected, so that a leaf subgroup containing only the dummy expression is created – doing so will ensure than sorting is enabled for the element of interest which now belongs to a non-leaf subgroup (the dummy XML instances can then be ignored when subsequently processing the XML file; due to a bug, the dummy element is often converted to an non-existent column and will appear as a valueless element group in the XML, for example as <G_2 />). Now that the descendant element has been sorted you can use either the First or Last aggregate function, depending on the sort order, to produce a surrogate for the Minimum aggregate function.

 

 

Can’t change Sort Order

 

Q: The sort order field in the properties pop-up is locked?

 

While elements that belong to a non-leaf subgroup can be sorted, the Sort Order field in the Edit Properties pop-up window is usually disabled. Saving and reopening the data model does not help. However, the icon in the Sorting column within the Table View on the Structure tab will still be enabled, and can be used to workaround this bug.

 

 

Sorting by Element Value in any Group

 

Q: How can I sort the element values in a group that has no subgroups?

 

One of the oddities of BI Publisher is that it restricts the sorting of XML element values to non-leaf subgroups. If you want to sort by XML elements in a leaf subgroup or in a unitary group that is not divided into subgroups then add a dummy expression element, say “DUMMY”, with a literal value to the group in question. Select all the other elements in the group and then select “Group by” from the menu of one of the selected elements. Doing so will create a subgroup, let’s call it G_DUMMY, containing just the expression element (now converted to a non-existent query column element due to a bug). You can now sort using any of the elements in the original group, which are now in a non-leaf subgroup. When XML is generated the G_DUMMY subgroup will contain no elements (due to the bug). Only an empty group element tag will appear in the XML at the end of the element list as “<G_DUMMY />”, and it can be ignored when creating any reports based on the data model.

 

 

Changing Element Display Order on Screen and in Generated XML

 

Q: How can I change the display order of the elements on the Structure Diagram screen and in the XML output?

 

The display order within a group and within the generated XML is set by the query column order at creation. Changing the query column order after a group has been created won’t reorder the elements.

 

You can reorder the elements by first creating a child subgroup. Move the elements to be reordered and any that come after them to the child subgroup. Then move the elements back to the parent subgroup in the desired order until only one element remains in the child subgroup. Then ungroup the child subgroup. Of course, editing the XDM file is the simplest way to reorder the elements, but this option is not always available.

 

 

Reordering XML Output from Top-Level Sibling Groups

 

Q: I have two unrelated groups at the top-level below the global group, and I need to change the order in which XML is generated?

 

The XML will be generated according to the order in which the two groups were created. The only way I can think of reordering the output in the BI Publisher GUI is to cut and paste the SQL from the group that currently comes first into a new query, and then delete the group that comes first. Alternatively, you can swap the group order quickly in the XDM file.

 

 

Reordering XML Output from Sibling Groups with a Common Parent

 

Q: I have two links from the same parent group to two different child groups, but the XML output has the child groups in the wrong order. How can I change the order in which the child groups are processed?

 

The child groups will be processed according to the order in which the data links from the parent to child group were created. So delete both data links and then recreate them in the desired order (any aggregate elements that depend on the links will also have to be dropped and recreated). Alternatively, you can swap the group order in the XDM file.

 

 

Ungroup Command containing Aggregate causes Infinite Browser Loop

 

Q: I have a subgroup containing an aggregate element and an element-level data link to another group. When I press Ungroup, IE detects an infinite loop and asks “Stop Running this Script”. Everything seems to be locked up?

 

The problem here is that BI Publisher doesn’t know how to move an aggregate element from a subgroup to its parent. It moves the non-aggregate elements to the parent, and tries repeatedly, failing each time, to move the aggregate element to the parent. The browser will detect the infinite loop as a slow running program and ask if you wish to terminate it. You do!

 

The best advice is to avoid getting into this situation in the first place. When a subgroup contains aggregate elements and you want to ungroup, then:

 

*  Delete the aggregate elements.

 

*  Ungroup the subgroup.

 

*  Recreate the aggregate elements .

 

*  Save the data model (saving will generate a reference error).

 

*  Reopen the data model to clear the error.

 

However, that doesn’t answer your question, so let’s assume that you have tried to ungroup the subgroup and have cancelled the infinite loop. You can’t move the aggregate element to the parent subgroup and you can’t delete it as it’s the only element in the child subgroup. Proceed as follows:

 

*  Move one non-aggregate element from the parent subgroup back to the child subgroup.

 

*  Delete the aggregate element(s) from the child subgroup.

 

*  Ungroup the child subgroup.

 

*  What you do next depends on whether you have saved and reopened the data model after exiting from the loop. If you have then the level of data model corruption will have increased considerably. While the original element-level data link from the child subgroup to the aggregate element source group will have been moved to the parent subgroup, you’ll find that a new group-level data link between the child subgroup and the aggregate element source group will have been mysteriously created. You need to delete this spurious group-level data link as follows:

 

*  Try to delete the group-level data link (Delete Group Link).

 

*  If there are other data links from the same subgroup hierarchy then the Delete Group Link pop-up will not show the group-level data link but will show an element-level data link instead; proceed with the delete and the element-level data link will be deleted but the group-level data link will remain.

 

*  Try to delete the group-level data link again; this time it may show a corrupted pop-up in which the group-level data link has no child; if so, then press Cancel and delete all the element-level data links from the subgroup hierarchy that contained the child subgroup.

 

*  When all the element-level data links have been deleted, you can successfully delete the spurious group-level data link.

 

*  Now recreate all the element-level data links that you deleted.

 

*  After recreating the element-level data links, you will observe that no connecting lines appear on the Structure Diagram; however, the connection icons will appear against the connected elements; save and reopen the data model to show the lines and clear the error.

 

*  Recreate the aggregate element(s) that you deleted from the child subgroup.

 

*  Save the data model (you will get a reference error) .

 

*  Reopen the data model to clear the error.

 

Bet you wished you had a recent backup to hand!

 

 

Ungroup Command converts Expression Element to Non-existent Column with Browser Warning

 

Q: When I try to ungroup a subgroup with an expression I get a “Stop running this script” message and the expression is replaced by a column that isn’t in the query?

 

When trying to ungroup a subgroup that contains an expression element sometimes the expression element is left in the subgroup and BI Publisher enters an infinite loop as it tries repeatedly to move the element but fails. The browser detects this loop as a slow running program and displays a pop-up message, “Stop running this script”. On other occasions the ungroup may succeed. Whether it succeeds or not, it’s likely that the expression element will be converted to a query column element for a non-existent column, one that cannot be deleted using the element’s action menu (to delete the element see the issue entitled, “Expression converted to Non-existent Column with Data Model Corruption”).

 

 

Group Link already exists, you cannot create Element Link here

 

Q: When I try to create a second element link from a group divided into subgroups I get a message saying that a Group Link already exists, but there’s no Group Link?

 

Another very misleading bug. Suppose you have a group that is divided into subgroups. You create an element-level data link from a subgroup to some other group. Then you attempt to create an element-level data link from a parent or ancestor of that subgroup. As soon as you click on the element you get the error message:

 

*  Group Link already exists, you cannot create element link here

 

The error is misleading in two ways. Firstly, no Group Link exists. Secondly, there is nothing wrong with creating multiple element-level data links from different subgroups. This error occurs when an attempt is made to create an element-level link from a parent or ancestor after one has been created from a child. To avoid the error, create links top-down, parents before children. If you’ve already created a link from a child subgroup and need to add one from a parent or ancestor subgroup, then delete the child link, save and reopen the data model, and then add the links in the preferred order.

 

 

Bind Variable Syntax

 

Q: What is the syntax for referencing a bind variable?

 

Let’s take an example, and assume that the parent group, G_DPT, is based on table DEPT.

 

If the parent query select list contains the DEPT_ID column without an alias in any of these formats:

 

*  "DEPT"."DEPT_ID"

*  DEPT.DEPT_ID

*  "DEPT_ID"

*  DEPT_ID

 

then the corresponding group element name in G_DPT will be DEPT_ID, and the child query can refer to the parent element using any of these formats:

 

*  :G_DPT.DEPT_ID

*  :DEPT_ID

 

If the parent query select list contains the DEPT_ID column with an alias of, say V_DEPT_ID, in any of these formats:

 

*  "DEPT"."DEPT_ID" AS V_DEPT_ID

*  DEPT.DEPT_ID AS V_DEPT_ID

*  "DEPT_ID" AS V_DEPT_ID

*  DEPT_ID AS V_DEPT_ID

 

*  "DEPT"."DEPT_ID" AS "V_DEPT_ID"

*  DEPT.DEPT_ID AS "V_DEPT_ID"

*  "DEPT_ID" AS "V_DEPT_ID"

*  DEPT_ID AS "V_DEPT_ID"

 

then the corresponding group element name in G_DPT will be V_DEPT_ID, and the child query can refer to the parent element using any of these formats:

 

*  :G_DPT.V_DEPT_ID

*  :V_DEPT_ID

 

In addition:

 

*  Don’t create a bind parameter when asked to do so (you will get no details rows in the XML). If you do create a bind parameter by mistake, just delete it.

 

*  Make sure that no element name in the child table has the same name as the bind variable if the shortened form (without the group name prefix) is used. For example, if both parent and child table join columns are called DEPT_ID and DEPT_ID appears as an element in the child table, then a bind variable reference of :DEPT_ID won’t work (even though the value of DEPT_ID is the same for both parent and child), but a reference of :G_DPT.DEPT_ID will work.

 

Note, that the element being referenced by the bind variable doesn’t have to exist in the parent group, but can occur in any ancestral group further up the hierarchy, including the global group.

 

 

Group Link returns no Data or not all Variables are Bound

 

Q: I’m testing out a group link and I have

            select "DEPARTMENTS"."DEPARTMENT_ID" as "DEPARTMENT_ID",
                   "DEPARTMENTS"."DEPARTMENT_NAME" as "DEPARTMENT_NAME"
              from "OE"."DEPARTMENTS" "DEPARTMENTS"

in the parent query. With

            select "EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
            	   "EMPLOYEES"."DEPARTMENT_ID" as "DEPARTMENT_ID" 
              from "OE"."EMPLOYEES" "EMPLOYEES"
             where "EMPLOYEES"."DEPARTMENT_ID" = :"DEPARTMENT_ID"

in the child query, I get “ORA-01008: not all variables bound” and with

            select "EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
            	   "EMPLOYEES"."DEPARTMENT_ID" as "DEPARTMENT_ID" 
              from "OE"."EMPLOYEES" "EMPLOYEES"
             where "EMPLOYEES"."DEPARTMENT_ID" = :DEPARTMENT_ID

in the child query, the XML is generated without any detail rows?

 

BI Publisher’s default of generating quoted aliases that match column names can cause problems if you don’t make the appropriate adjustments. In the first case, BI Publisher doesn’t allow bind variables to have double quotes as part of the name (:"DEPARTMENT_ID"). In the second case, the bind variable (:DEPARTMENT_ID) is fine (the fact that the alias is quoted in the master query does not cause a problem), but the bind variable has the same name as a query column element in the child table (derived from "EMPLOYEES"."DEPARTMENT_ID" as "DEPARTMENT_ID") – logically, this shouldn’t confuse BI Publisher, but it does.

 

The remedy is to change the alias used for "EMPLOYEES"."DEPARTMENT_ID" in the child query; for example:

            select "EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
            	   "EMPLOYEES"."DEPARTMENT_ID" as "DEPARTMENT_ID1" 
              from "OE"."EMPLOYEES" "EMPLOYEES"
             where "EMPLOYEES"."DEPARTMENT_ID" = :DEPARTMENT_ID

or prefix the bind variable with the name of the parent group; for example:

            select "EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
            	   "EMPLOYEES"."DEPARTMENT_ID" as "DEPARTMENT_ID" 
              from "OE"."EMPLOYEES" "EMPLOYEES"
             where "EMPLOYEES"."DEPARTMENT_ID" = :G_DPT.DEPARTMENT_ID

or, preferably, select a bind variable name that does not occur as an alias or column name in either table; for example:

            select "DEPARTMENTS"."DEPARTMENT_ID" as V_DEPARTMENT_ID,
                   "DEPARTMENTS"."DEPARTMENT_NAME" as "DEPARTMENT_NAME"
              from "OE"."DEPARTMENTS" "DEPARTMENTS"

in the parent query and:

            select "EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
            	   "EMPLOYEES"."DEPARTMENT_ID" as "DEPARTMENT_ID" 
              from "OE"."EMPLOYEES" "EMPLOYEES"
             where "EMPLOYEES"."DEPARTMENT_ID" = :V_DEPARTMENT_ID

in the child query draws attention to the fact that a bind variable is being used in the child query and that the alias in the parent query is being used elsewhere as a bind variable (using :V_DEPARTMENT_ID for a bind variable reference and :P_DEPARTMENT_ID for a bind parameter reference makes the nature of the binding clear).

 

Query Builder’s habit of quoting everthing reduces the readability of the SQL code, but unfortunately there is no means of turning it off - a generic replace in a text editor is required to tidy it up.

 

 

Query with Group link returns no Child Data

 

Q: I’ve followed the example in the documentation for creating a group link but no child data is returned?

 

The example in the documentation should work (having a bind variable with the same name as an element in the child group can also cause this problem – see the previous issue – but this is not the case with the example in the documentation). The only possibility I can think of is that when you finish editing the child query to add the bind variable predicate, BI Publisher asks, “Do you want to create a bind parameter?” Given that you do want to create a bind variable it’s tempting when you see this pop-up to just press OK – you’re just seeing “bind” and assume that a “bind parameter” is just another way of talking about a “bind variable”. If you do create a bind parameter then you will get parent rows with no children, just as you describe – if this is the case, just delete the bind parameter and the correct XML data should be generated. It would have been a better interface design if the BI Publisher pop-up had said something along the lines of:

 

*  To create a bind parameter press “OK” (if you were adding a bind variable press “Cancel”)

 

so as to make it clear that there is more than one type of binding – though, in any case, you should be wondering what that blank field with no label is doing on the XML generation screen!

 

 

Data Link that joins on Multiple Columns

 

Q: I need to create a data link than joins on two columns. When I select Create Link it only allows me to select one column?

 

The GUI is not at all helpful on the linkage options. The documentation is a little better but still not very clear. There are two types of data link, an element-level data link (select Create Link from the parent element menu, or drag and drop the parent element to the child element) and a group-level data link (select Create Group Link from the parent group action menu). Once created, the element-level data link will have an information icon embedded in the link and connection icons following the joined elements in both the parent and child groups, whereas a group-level data link will not.

 

You can think of each type of data link as creating a Cartesian join between the queries associated with the parent and child groups. But, in addition, the element-level data link adds a where clause that joins the columns corresponding to the selected elements from the parent and child groups. To join on one or more columns in the case of group-level data link or to join on more than one column in the case of an element-level data link you need to add the appropriate join conditions to the where clause of the query that defines the child group. However, it will be clearer to a developer that subsequently has to maintain the data model if a group-level data link is used when joining on more than one column.

 

The syntax of each join condition is either of:

 

*  child-column-name = :<parent-element-name>

 

*  child-column-name = :<parent-group-name>.<parent-element-name>

 

 

Lack of Bind Variable Reference Validation leads to Corrupted XML

 

Q: Is there any way to ensure that bind variable references actually exist?

 

No. A non-existent bind variable reference is replaced by null, without raising an error or warning. Unless the null value invalidates the query’s syntax, the data model will run, generating corrupted XML.

 

 

Missing Bind Variable Alias leads to Element named NULL

 

Q: I’m getting an element named NULL in a group, but I didn’t name any element NULL?

 

This could be due to the query having a bind variable reference as a select list item with no alias. If this happens then BI Publisher does not do the sensible thing and name the element after the bind variable, as it would do if you had a select list column without an alias. Instead it names the element NULL. If you go ahead and generate XML you’ll find that the element is omitted with no warning or error message. So, check the syntax of the SQL query.

 

 

Referencing Columns from Ancestral Queries

 

Q: I’m forced to have two groups as the data for parent and child come from different sources. I need to create an element in the child group that is an expression based on the elements in the parent?

 

Use bind variables to refer to the elements from the parent group when adding the expression to the child query select list. Bind variables are illustrated as part of a where clause in the documentation, but they can be used anywhere in the child query, not just for the purpose of creating a data link.

 

While the documentation suggests that bind variables can only be used with a group-level data link, they can also be used with an element-level data link, whether to add additional predicates, or, as in your case, to define additional child query select list columns.

 

The documentation also suggests that the scope of a bind variable reference is the parent group. However, a bind variable can refer not just to elements in the parent group, but to elements in any ancestor of the child group, even the global group. The standard bind variable syntax can be used. There is no need to use the group prefix (“global” for the global group) unless some ambiguity would result.

 

 

Referencing Global Elements in Group Queries

 

Q: Is it possible to refer to global elements when performing calculations within other groups?

 

Yes, you can use a global element name as a bind variable in any group query (:<global-element-name> or :global.<global-element-name>). However, this type of reference is a little “incestuous” since the global element may well depend on the elements in the group where you are going to use it, leading to a circular reference and some strange results. A reference to a:

 

*  Global PL/SQL expression element will work as the expression is calculated first and independently of the source data.

 

*  Global non-PL/SQL expression element will not cause an error, but no element will appear in the XML (note references to expression elements in non-global groups have the same problem).

 

*  Global aggregate expression element will produce output, but only as a delayed running sum, which is unlikely to be of any value (note references to aggregate elements in non-global groups have the same problem).

 

 

Creating Multiple Data Links from same Parent Group Element to Multiple Child Groups

 

Q: I need to create two data links from the same element in a parent group to two separate child groups, but I can only get Create Link to work once?

 

Create Link produces an element-level data link, which is the new, “go faster” method for creating data links, but it only works in the simplest of cases, whereas the older, group-level data link is more flexible. For your use case, either create a group link to implement the second data link (adding the relevant bind variable predicate to the child’s query), or, preferably – to prevent too much head scratching when it comes to future maintenance – use two group links and predicates so that there is symmetry in the methods of implementation.

 

 

Element-Level Data Link to Expression / Aggregate Element allowed but Generation Fails

 

Q: Is it possible to create a link to an expression element? It allows me to do so but generation fails?

 

It’s possible to do so, but it doesn’t work - yet another instance of inadequate validation. If you try to create an element-level data link by drag and dropping to an expression / aggregate element, you’ll get an error and the operation does not succeed. But if you try to do the same using the Create Link menu item then the operation succeeds. When you generate the XML and disentangle the “error in the error” you’ll get an “ORA-03001: unimplemented feature” error message.

 

 

Link Deletion converts Element-Level Data Link to Group-Level Data Link

 

Q: Occasionally when I try to delete an element-level link, the screen seems to lock up, and I get a group-level data link?

 

Sometimes when an aggregate element has been created in the parent group, an attempt to delete an element-level link to the child group will freeze some of the screen functionality. If you press Save then the screen will free up but the element-level link, instead of being deleted, will have been converted to a group-level data link. Delete the group-level link in the normal way.

 

 

Reference Error on renaming Groups

 

Q: When trying to rename a group I sometimes get the error message “… element of group … has a wrong reference”. Is there a workaround?

 

After you’ve renamed a group using the Properties pop-up on the Diagram tab or from within a Table View column on the Structure tab and then saved the data model, you’ll sometimes get the error message:

 

*  <element name> element of group <group name> has a wrong reference [object]

 

(for example, renaming the global group when it contains an aggregate elements always seems to cause this problem). Then as you navigate around the GUI you’ll find that this error keeps reappearing.

 

The XDM resource file contains two references to a group name, one as the value of the “tagName” attribute on the “dataStructure” element, and the other as the value of the “name” attribute within the “layout” element. The problem occurs because while BI Publisher updates the “dataStructure” reference correctly, it does not update the “layout” element to match.

 

The workaround for this bug is to reopen the data model and then save it immediately. Doing so will update the “layout” reference correctly.

 

You can often avoid this problem by renaming elements as soon as possible, especially before adding any aggregate functions to a group.

 

 

Can’t Rename Group

 

Q: Pressing OK after changing a group name doesn’t do anything?

 

This is an intermittent bug. You’ve changed a group name within the Properties pop-up window and pressed OK, and then nothing happens, even if you press the button repeatedly. Press Cancel, save and reopen the data model, then and try again.

 

 

Group Filter deleted without Warning when Group Link Created

 

Q: I’ve a group filter defined on a group, but when I create a group link to the group, the filter is deleted. Can I restore it?

 

Not in this case. Creating a Group Link will delete any group filter defined on the child group of the link without any warning. Saving and reopening the data model won’t restore the group filter. You’ll have to recreate it from scratch.

 

 

Edit Group Filter Menu Item disabled after deleting Element Link

 

Q: The child group of an element data link has a group filter defined. When I delete the link, the filter icon still appears, but the edit group filter menu item is disabled?

 

Another synchronization error. The Edit Group Filter menu item for a group is disabled if an element-level data link to that group is deleted. In this case, the filter still exists. Save and reopen the data model and the Edit Group Filter menu item will be enabled.

 

 

Variable is not able to return Type 4

 

Q: I get a “Variable is not able to return type 4” error when trying to create a group filter?

 

If BI Publisher’s communication skills were a little better if would have said, “Variable is not able to return Boolean data type”.

 

 

No Connection to Data Source

 

Q: How can I tell if the problem is that BI Publisher can’t connect to the data source?

 

This is easy because in this case you get an informative error message along the lines of:

            oracle.xdo.XDOException: oracle.xdo.XDOException: oracle.xdo.XDOException: 
            Could not get data source connection for: <data source name>

 

Oracle DB Default Package synchronization Error

 

Q: I’ve changed the name of the DB Default Package and saved the data model, but I can’t see the functions when I try to add an expression?

 

Another case of “BI Publisher’s left hand not knowing what its right hand is doing!” You might expect that as soon as you make a change and remove focus from a field that the GUI internals would be updated to reflect the amended value. You would certainly expect that this synchronization would occur following a data model save. However, in this case, as in so many others, you have to reopen the data model in order to update the GUI internals and make the consequences of the change visible within the other GUI widgets.

 

 

Package does not exist in Default Data Source

 

Q: What’s missing when I get the following error message:

            oracle.xdo.XDOException: oracle.xdo.XDOException: oracle.xdo.XDOException: 
            oracle.xdo.XDOException: java.sql.SQLException: ORA-06550: line 2, column 1:
            PLS-00201: identifier 'ACCOUNTS.FISCAL_END_DATE' must be declared
            ORA-06550: line 2, column 1:
            PL/SQL: Statement ignored

In this case, the data model has defined the value of Oracle DB Default Package as ACCOUNTS, but this package is not visible within the Default Data Source. Make sure you can logon to the database schema using the user name associated with the Default Data Source connection (see Administration => JDBC => <data source>) and access the package directly. When you can, the error message (in this specific format) should disappear.

 

Note, that BI Publisher doesn’t check for the existence of a package unless it is referenced within the data model – unless referenced you can enter any package name for the Oracle DB Default Package and XML will still be generated successfully. But note that due to a bug (see the next issue) a package can still be referenced even when the data model does not need to make use of any functionality within the package!

 

 

Package exists in Default Data Source but Parameter is not declared as Global Package Variable

 

Q: I’ve got a report with a parameter – it’s not even used by the report. I run the report and it works fine. Then I add an Oracle DB Default Package. Even though I’m not using any functions within the package the XML is no longer generated and I get an “XML Page cannot be displayed” message?

 

This seems a really bizarre bug when first encountered. If you play around with the combinations of adding a parameter and adding a package you might conclude that though you can use parameters or PL/SQL separately within a data model, you cannot use both together at the same time. The problem isn’t helped by the fact that BI Publisher’s error generation page itself contains an error, so you don’t see the correct error message displayed (see the issue entitled, “The Error in the Error”). If you dig out the error message buried in the HTML (right-click on the web page and select View Source) you’ll find something like:

            oracle.xdo.XDOException: oracle.xdo.XDOException: oracle.xdo.XDOException: 
            oracle.xdo.XDOException: java.sql.SQLException: ORA-06550: line 2, column 13:
            PLS-00302: component 'TAX_RATE' must be declared
            ORA-06550: line 2, column 1:
            PL/SQL: Statement ignored

In these circumstances, BI Publisher can see the Oracle DB Default Package in the Default Data Source, but can’t find package component TAX_RATE declared in the package header.

 

While this could happen when the “component” in question is a function this is very unlikely – PL/SQL functions are picked from an Available list when creating a global PL/SQL expression element or a PL/SQL group filter (it is, of course, possible that the function was deleted from the package after the data model was created).

 

In almost all cases when this error message occurs the component referred to, TAX_RATE in the example, is a data model parameter.

 

*  What BI Publisher assumes is that for each parameter in the data model a global variable with the same name as the parameter is declared in the header of the Oracle DB Default Package (when one is defined), and it tries to set this package variable to the context value of the parameter before trying to generate any XML.

 

Note, that preventing parameter values from being included in the XML (Data Model => XML Output Options => Include Parameter Tags) will make no difference. If the corresponding global variable doesn’t exist in the package header (when a package is defined in the data model), then you’ll get an error similar to that shown above.

 

Now you’re thinking, “Why does BI Publisher behave in such a crazy manner?” After all, most parameters are used as bind parameters and have nothing to do with PL/SQL at all, and where PL/SQL is used – as in global PL/SQL expression elements, PL/SQL group filters, or event triggers – data model parameters can be passed in as arguments to the PL/SQL functions being called (the parameters are listed in the Available pane on the left-hand side of the expression editor).

 

There is, however, another mechanism available to pass information to a packaged function. The value of variable declared in the package header is accessible by any function in the package body, so instead of passing a value directly to a package function as a parameter it’s possible to write it to a header variable and then have the package function read the variable when required (though this use of global variables is not usually considered best programming practice). It seems that in order to facilitate this alternative method of parameter passing, the designers of BI Publisher decided to make it possible for the value of a data model parameter to be written to a global package header variable with the same name. Now there is nothing wrong with this approach in principle. However, given that this mechanism is going to be used very rarely we might have expected that a developer would have to explicitly designate which parameters, if any, should have their values written to package header variables in this manner. This is where the BI Publisher design falls down. It assumes that the developer wants to pass the values of all parameters to corresponding variables in the package header in this manner (there is no check box in the parameter definition screen that allows a developer to “opt a parameter in” to this alternative parameter passing mechanism when required). As a result, whenever you define a parameter, BI Publisher will assume that a variable with the same name appears in the header of the Oracle DB Default Package and will attempt to write the parameter value to this variable at the start of XML generation. So, to prevent BI Publisher from falling over, for every parameter a dummy global variable must be added to the package header, even though, as will almost always be the case, no packaged function will ever access or make any use of the dummy variable.

 

 

PL/SQL Parameter must be Declared Error occurs, even when it has been

 

Q: I’m passing a data model parameter to a packaged function. I’ve put a dummy variable with the same name as the parameter in the package header but I’m still getting a not declared error?

 

When a package is defined as the Oracle DB Default Package, each data model parameter must be declared as a dummy variable with the same name as the parameter in the package header. If not, then a “PLS-00302: component '<parameter name>' must be declared” error will occur (see the last issue for details). However, on occasion this error will occur even when the parameter has been declared in the package header.

 

This error tends to occur when you have a working data model, rename the parameter in both the package and the data model (including all parameter references). Even though you run the modified package to apply the changes, and then open the modified data model the error still occurs, and BI Publisher complains that a component with the old parameter name must be declared. Saving and reopening the data model does not work, nor does logging out of and then back into BI Publisher. If you examine the XDM resource file you’ll find that there are no references to the old parameter name, so it seems that something is still hanging around in memory. While bringing BI Publisher down and then up again will clear the error, you may have a long wait when working in a shared development environment. The quick workaround is to rename the data model, and then rename the renamed data model back to its original name. When you run the data model the error will have disappeared.

 

 

Parameter to Global Packaged Variable Data Type Conversion

 

Q: How do parameter data types map onto packaged variable data types?

 

Following on from the last issue, in which we discussed why it is necessary to create dummy package variables in the Oracle DB Default Package header when parameters and a package are defined in a data model (even if the package does not need to make use of the parameters). If the parameter value passed in cannot be converted to the PL/SQL data type associated with the package variable then an error similar to the following will be generated:

            oracle.xdo.XDOException: oracle.xdo.XDOException: oracle.xdo.XDOException: 
            oracle.xdo.XDOException: java.sql.SQLException: 
            ORA-06502: PL/SQL: numeric or value error: character to number conversion error
            ORA-06512: at line 2

Now you may recall that BI Publisher is very particular about PL/SQL function return types – for example, declare a function with a return type of VARCHAR2, rather than VARCHAR, and the function won’t be available for use when defining a global PL/SQL expression element. However, when it comes to passing data in the other direction – from a parameter to a package variable – it’s the RDBMS that’s responsible for the conversion and the normal PL/SQL rules for data value conversion apply. So, it makes no difference if a package variable is declared as VARCHAR or VARCHAR2, and a parameter that is defined as an XML String type can be converted to a PL/SQL numeric type provided the parameter value contains the appropriate character sequence (for example, a parameter string value of ‘123’ can be converted to a PL/SQL NUMBER(10) data type).

 

 

Global PL/SQL Element returns Null when Parameter passed to Packaged Function

 

Q: I’m passing a parameter to a PL/SQL package function when defining a global PL/SQL element, but the XML is displaying null. I tried hardcoding the function argument as a literal but got the same error. Then I hardcoded the function return value as a literal and still got the same error. I redefined the function so that it didn’t take a parameter and it worked. Redefined it again so that it took a parameter and it failed. I reopened the data model after each change was made?

 

This is a weird and very frustrating bug. It seems, at times, when a function is defined to take a parameter BI Publisher can’t process the call and assumes that the return value is null. This can occur even if a literal argument value is passed to the function, the function doesn’t do anything with the argument, and the function just returns some literal value. It seems that the very fact of the function having a parameter is enough to cause the problem. Reopening the data model doesn’t clear the error. The only workaround is to delete the expression entirely and recreate it – just editing the PL/SQL function call in the editor window and selecting the changed function definition from the Available list doesn’t seem to work.

 

 

PL/SQL Element Tag replaced by Null in XML

 

Q: Occasionally I find the value of a global PL/SQL element missing from the XML. It’s not just an empty tag, but the pair of tags is missing as well, and I get … null</DATA_DS>. Then some time later the XML is fine again, even though no change has been made to the data model?

 

The tag pair and value associated with a global PL/SQL expression element will be replaced by “null” if the packaged function used to define the expression element is no longer available. And BI Publisher does not generate an error which can be problematic for reports that make use of the data model.

 

This behaviour can easily happen when moving BI Publisher reports and resources between different environments, if PL/SQL package updates have not be made in sync between the different environment database schemas. Once the packaged function is made available once again, then the XML will be generated correctly, so the problem can appear to be self-correcting if, as is often the case in larger organizations, one team is maintaining BI Publisher data models and another is maintaining database objects. So, if someone is making changes to the packaged function in question or it has been temporarily dropped by some process then the “null” value could appear on an intermittent basis just as you describe.

 

 

Intermittently working PL/SQL Group Filter leads to Security Breach

 

Q: We have a major issue with the reliability of the BI Publisher reporting system. Now and then it fails to filter the client data correctly and sends transaction details in a client statement that belong to other clients. The data is filtered using a PL/SQL group filter. Every time we’ve tested it on both the development and prod platforms it works fine. What could be causing it?

 

The only BI Publisher specific reason that I can think of would occur if the packaged function used to define the group filter becomes temporarily unavailable. If you develop and test a PL/SQL group filter and its working fine, but then someone or something at the database end makes the packaged function used to define the filter unavailable, then BI Publisher does not generate an error but continues to generate unfiltered XML, which is then fed on to the reports that consume this data. Once the packaged function becomes available again filtering is restored when the data model is next run.

 

So, if for example, you have a batch process running hundreds of BI Publisher reports one after another, and some database maintenance process temporarily impacts the visibility of the packaged function used by the filter, then you would find a contiguous set of corrupted reports somewhere during the run. If you think this might be applicable in your case it might be worthwhile running some process before each report to ensure than any PL/SQL packaged functions used by the report are accessible.

 

 

Data Model still runs when Packaged Function is renamed in Database

 

Q: Is it possible to enforce a consistency check to ensure that packaged functions used in a data model exist in the database?

 

No, BI Publisher does not perform any consistency checks on PL/SQL packaged functions referenced in a data model at run-time. When a PL/SQL-based expression is being constructed, BI Publisher will only list packaged functions with the relevant return type, so there is no issue on expression creation. However, if the packaged function is subsequently dropped or renamed in the database after the data model has been created, then the data model will still run, no warning will be issued, and the XML will be corrupted.

 

If you’re really enthusiastic, you could call a Java program from a data model event trigger, parse the PL/SQL references found in the XDM file using some regular expressions, and then verify that packaged functions with the appropriate names, parameters, and parameter data types exist in the database. For example, XDM PL/SQL function references always have the following format:

            ${plsql.DK_TUTORIAL.KF_FILTER_EMPLOYEES( :T2_C3_SALARY, :P_EMPLOYEE_CUTOFF_SALARY ) }

which would allow you to determine the package name, function name, and function argument names. The data types of the arguments could then be determined by looking up the relevant elements or parameter definitions; for example:

            <element name="T2_C3_SALARY" value="T2_C3_SALARY" dataType="xsd:integer" 
               label="T2_C3_SALARY" breakOrder="None" fieldOrder="3"/>

            <parameter name="P_EMPLOYEE_CUTOFF_SALARY" defaultValue="100000" dataType="xsd:integer" rowPlacement="1">

You could then cross check the data model packaged function name, arguments, and argument data types with RDBMS data dictionary views, such as ALL_PROCEDURES and ALL_ARGUMENTS. However, this type of basic validation should be done by the vendor, not the user!

 

 

Syntax for referencing the Context Element in Argument to PL/SQL Function

 

Q: When constructing a group filter the editor refers to an element by GROUP.ELEMENT_NAME. When I use this syntax with a filter based on a PL/SQL function I don’t get an error but the XML isn’t correct?

 

When you construct a group filter using an expression the editor lists the available elements and refers to them via the GROUP.ELEMENT_NAME syntax in the body of the expression. When you construct a group filter using PL/SQL no such assistance is available, so it’s natural to assume that the same syntax applies. However, the syntax to use in this case is different (and does not appear to be documented). As with a bind variable or a bind parameter, you need to precede the reference by a colon; either :GROUP.ELEMENT_NAME or :ELEMENT_NAME will work.

 

BI Publisher performs no syntax checking on PL/SQL function references, either at design time or at run time, so you can type in anything you like by way of a function name or an argument. If an argument is invalid it is treated as null, so the XML will be corrupted.

 

Given that BI Publisher is so unreliable in this respect, it’s worth adding a thick layer of validation code to any PL/SQL function that is reused with BI Publisher to ensure that the arguments passed to the function make sense. For example, if a user can select a parameter value from an LOV, it’s worth adding the LOV code to the PL/SQL function to validate the user’s selection. If you haven’t permission to change a DB supplied PL/SQL function, then consider creating a wrapper function, that does the validation and then calls the DB supplied function.

 

 

Can’t see PL/SQL Package when adding Global Element by PL/SQL

 

Q: I’ve added an Oracle DB Default Package but I can’t see the package when adding an element by PL/SQL at the global level. Is there some permission that needs to be set to see the package?

 

No! This is one of those bugs that send a developer off on completely the wrong track. When PL/SQL is used to add either a global element or to create a group filter, then only those functions within the package that match the appropriate return type are displayed as Available. In the case of a group filter, BI Publisher always displays the package name so that it’s obvious that it can see the package. However, in the case of adding a global PL/SQL element the package name is not displayed when there are no suitable functions within the package, so your first thought is likely to be that there is some permissions problem that prevents BI Publisher from seeing the entire package.

 

The second thing to take note of is that BI Publisher is very pernickety about the return type. In the case of a group filter there is only one version of boolean so there is no problem. However, in the case of the string type required for a global element there are a number of possible candidates. It’s common practice to code packaged functions with a return type of “varchar2”, but unless the return type is “varchar” then BI Publisher will not recognize the packaged function and it will not be listed. If there are no “varchar” functions in the package, then even the package will not be listed. When no “varchar” functions are present, it would have made far more sense if the selection window had displayed a message along the lines of, “This package does not contain any VARCHAR functions”, thereby making it obvious to the developer what the source of the problem was.

 

There is also another difference in PL/SQL packaged function behaviour between group elements and group filters. If a package is modified to make new functions available then on reopening the Edit Group Filter window the relevant new packaged functions will be visible. However, on reopening the Add Element by PL/SQL window the relevant new packaged functions will not, so if you’re used to the group filter behaviour you first thought is likely to be that the package modifications haven’t been applied correctly. The issue seems to be that the data model refreshes the package contents in one case but not in the other. Saving and reopening the data model will make the changes visible in the Add Element by PL/SQL window.

 

 

Operator “If” has no Definition

 

Q: What does the “if” operator do? I’ve tried “if … then” style constructs but I get an error?

 

Well, when in doubt we should consult the documentation, shouldn’t we! Both the Function Reference section in the Data Modelling Guide and the GUI Help describe “if” as an “operator”, and say nothing else about it – perhaps consulting the documentation is not such a good idea when it’s Oracle’s documentation! When we select "if" from within the expression editor we get a template of:

 

*  if(expression)

 

so, at least, we can be confident that "if" takes a single expression as an argument! However, after some trial and error we discover that this template is more than just a little misleading, and should, in fact, be something along the lines of:

 

*  if( boolean-expression, expression-when-true, expression-when-false )

 

The first argument is evaluated; if true, then the return value of the “if” function is the second argument, otherwise the third argument; for example:

 

*  if( G_1.T2_C3_SALARY < 20000, 1, 2 )

 

will return “1” for salaries less than “20000”, otherwise “2”.

 

 

Operators “And” and “Or” don’t work

 

Q: “And” and “or” don’t seem to work – “expression and expression” just gives an error?

 

Well, they do work, but not in way you might reasonably expect. As in the case of “if”, both the Function Reference section in the Data Modelling Guide and the GUI Help describe “and” / “or” as an “operator”, and say nothing else about them (the expression editor confirms the idea that they are operators, as the templates created contain just the keywords “and” and “or”). If they were indeed operators, then a construction like “expression and expression” would, of course, work. And, apart from some obscure programming languages that belong in academia, “and” and “or” always work as operators in this manner. However, the designers behind the BI Publisher data model seem to have been weaned on functional programming, so, believe it or not, “and” and “or” are actually functions, with templates of:

 

*  and( boolean-expression, boolean-expression )

*  or( boolean-expression, boolean-expression )

 

respectively – if there ever was a case when documentation was needed then this is it. These functions return the same results as most other programming and scripting languages do when “and” and “or” are operators that sit between the pair of boolean expressions; for example, in “data model speak”:

 

*  if( and( 1 < 2, or( 4 < 4, 4 < 3 ) ), 1, 2 )

 

equals “2”, equivalent to:

 

*  if( 1 < 2 and ( 4 < 4 or 4 < 3 ) ) then 1 else 2

 

when sweetened with the customary “syntactic sugar” – Oracle certainly likes to make life easy for its competitors!

 

 

The “set” used by “Max” and “Min”

 

Q: “The “max” and “min” functions say they return the maximum / minimum value of the element in the set. What set?

 

As with “if”, “and”, and “or”, “max” and “min” provide yet another “cryptic crossword” clue for the would-be BI Publisher developer to puzzle over. The documentation states that they “Return the maximum / minimum value of the element in the set”, and the expression editor templates are:

 

*  max(expr)

*  min(expr)

 

suggesting that “expr” is an expression that evaluates to a set, and that these functions are similar, in some sense, to the “max” and “min” aggregate functions where a set of XML element values belonging to some descendant is the argument under consideration. However, once again the documentation and templates are very misleading. Both “max” and “min” turn out to be variadic functions, and their templates would be better written as:

 

*  max( expression-1, expression-2, …, expression-N )

*  min( expression-1, expression-2, …, expression-N )

 

For example:

 

*  max(1, 2+1, 3, 3+4, 4 )

 

equals 7.

 

 

The “expr” used by “Avg” and “Sum”

 

Q: In “avg(expr)” and “sum(expr)” what is “expr”? How do you sum a single value?

 

We have the same problem here as with “max” and “min”. The documentation describes “sum” by “SUM(expr) returns the sum of value of expr”, and the expression editor templates are:

 

*  avg(expr)

*  sum(expr)

 

suggesting that “expr” is an expression that evaluates to a set, whose values are averaged or summed. However, once again the documentation and templates are misleading, as both “avg” and “sum” turn out to be variadic functions, and their templates would be better written as:

 

*  avg( expression-1, expression-2, …, expression-N )

*  sum( expression-1, expression-2, …, expression-N )

 

For example:

 

*  sum( 1, 2+3, 5 )

 

equals 11.

 

 

Number of Arguments for “Concat”

 

Q: Is there a better way of concatenating multiple string values than using a nested set of “concat” functions?

 

The documentation describes “concat” by “CONCAT(char1, char2) returns char1 concatenated with char2”, and the expression editor template is:

 

*  concat(char1, char2)

 

suggesting that if you want to concatenate the strings “I ”, “take ”, “two “, “arguments”, and “- but I lie!”, you would have to write:

 

*  concat( 'I ', concat( 'take ', concat( 'two ', concat( 'arguments ', ' – but I lie!' ) ) ) )

 

However, the documentation and template are misleading, as “concat” is a variadic function, and its template would be better written as:

 

*  concat( expression-1, expression-2, …, expression-N )

 

so that in the case of the example:

 

*  concat( 'I ', 'take ', 'two ', 'arguments ', ' - but I lie!' )

 

will suffice.

 

 

String to Number Conversion Function

 

Q: How do you convert a number to a string?

 

In the expression editor, the list of available functions includes “date”, “format_date”, and “format_number”, suggesting that while string conversions to and from a date are possible only a number to string conversion is possible. However, while not listed in the BI Publisher GUI, a string to number conversion function, “number(char)”, is detailed in the documentation – and it works … well … provided your concept of what constutes a number is a very modest one (see the next issue).

 

 

String to Number Conversion fails

 

Q: What’s the format for converting a string to a number? For example, “number( '-123,456' )” fails?

 

The description of the “number” function is “NUMBER(char) converts char to a number data type”. Now string representations of numbers often contain embedded commas so we might expect:

 

*  number( '123,456.78' )

 

to work, but it doesn’t. But surely:

 

*  number( '-123456.78' )

 

will work? No, it seems “advanced” mathematical concepts, such as negation, are beyond the ken of Oracle’s BI Publisher designers. However, you’ll be pleased to find that:

 

*  number( '123456.78' )

 

does work as expected.

 

So, if you want to convert a string like “-123,456” to a number, you’ll need some rather convoluted expression, like:

 

*  if( substring( '-123,456', 0, 1 ) == '-', -1, 1 ) * number( replace( replace( '-123,456', ',', '' ), '-', '' ) )

 

But, if your dealing with floating point strings, such as “-123,456.78”, then the expression will become yet more complicated as the “replace” function gets rather pernickety when the target string contains both a decimal point and a comma (see the issue entitled ‘Function “Replace” doesn’t work’).

 

 

Documentation on “Replace” and “Decode”

 

Q: Where is the documentation on “decode” and “replace”?

 

None that I can find. Neither function is detailed in the Function Reference section in the Data Modelling Guide nor in the GUI Help. However, the templates created by the expression editor are reasonably self-explanatory (“decode” has the same functionality as the SQL “decode” function).

 

 

Function “Replace” doesn’t work

 

Q: The function “replace( '123,456.78', '1', '2' )” gives “Invalid Expression”?

 

It would be hard to make it up, but while both:

 

*  replace( '123,456978', '1', '2' )

 

and

 

*  replace( '1239456.78', '1', '2' )

 

work as expected:

 

*  replace( '123,456.78', '1', '2' )

 

does not.

 

It seems that according to BI Publisher a valid source string for replace can contain a comma or a decimal point, but not both simultaneously. As a workaround, a combination of “if”, “instr”, and “substring” could be used to split the source string into two components, one containing the comma and one the decimal point. After applying “replace” to each component separately, the results could then be stitched back together again.

 

 

Bursting Definition Prerequisites

 

Q: What’s the minimum I need before adding a bursting definition?

 

According to the documentation the prerequisites for adding a busting definition to your data model are as follows: you need the have defined the data set; provided a sorting definition for the split by element; and ensured that the formatting and delivery details are available to BI Publisher and that the report definition, including layouts, has been created. This is not correct unless we redefine this as the prerequisites for running a report job that makes use of a bursting definition. For the purpose of adding a busting definition to a data model all you require is that the data model has one query group defined (it need not have any relevance to the bursting definition – it need not contain the split by and deliver by elements).

 

 

Bursting Definition is not validated

 

Q: When I enter a query defining a group the query is validated, but when I enter a query for a bursting definition it is not?

 

The only aspect of a bursting definition that is validated is that the query is not empty. You can leave the “Deliver by” and “Split by” fields as “Enter”, or you can enter any characters you like. The SQL query select list does not need to contain any specific fields, can reference a non-existent database table, and, indeed, it does not need to be syntactically valid SQL. You’ll need to run the SQL separately to ensure that it is syntactically valid and returns the appropriate data.

 

 

Split By and Deliver By Element Names not displayed

 

Q: I’ve created a new bursting definition, but I’m not seeing the split by and deliver by element names from the groups in the data model displayed?

 

Occasionally, when you create a new bursting definition the data model elements for the “Split by” and “Deliver by” lovs are not populated and you just see “rowset/row”. Save and reopen the data model and the data model elements should now be visible.

 

 

Bursting Query TEMPLATE defaults to first Layout when TEMPLATE is invalid or missing

 

Q: I changed the template name in the delivery query, but when I ran the job it used the previous layout (I don’t have any caching enabled)?

 

Are you sure you didn’t misspell the layout name assigned to TEMPLATE in the delivery query. Now when a delivery select list item is invalid the job normally fails. While it doesn't mention this in the documentation, if no template select list item is specified or if the template value is invalid then no error is raised and the job proceeds using the first layout in the report definition. So, it’s very important to check that this item is correctly specified, as by bursting with the wrong layout you might send inappropriate data to the recipients of the associated delivery channel.

 

 

Locale defaults to that of Layout when invalid or missing

 

Q: Is a locale required in a bursting delivery query?

 

The documentation suggests it’s mandatory, but it can, in fact, be omitted, in which case the report will use the default locale for the layout. Oddly enough, even if the locale is specified but is invalid, the report job will still run and will use the default locale for the layout.

 

 

Multiple Reports sent to the same File Delivery Channel overwrite one another at random

 

Q: What happens when multiple reports are sent to the same file delivery channel?

 

If multiple Split By key values map via the Deliver By key value and the delivery query onto the same Output File Name value (parameter2), then multiple reports will be sent to the same file. It would have made sense if BI Publisher had appended the second and subsequent reports to the file, but instead it overwrites the file, so that the last report written to disk “wins”. As processing takes place in parallel, the report that is written to a particular file can seem to be selected in a random manner if you run the same job repeatedly with the same data.

 

 

Deliver By Element can occur in Ancestor or Descendent of Split By Group

 

Q: Does the deliver by element have to be in the same group as the split by element?

 

No, the Deliver By element can occur in a group that is:

 

*  An ancestor of the Split By group, so that several Split By key values will share the same Deliver By key value, and hence the same delivery channel destination, or

 

*  A descendant of the Split By group, so that a single Split By key value will have multiple Deliver By key values. But in this case only the last one based on the XML ordering will be used.

 

The general rule is that every time a Deliver By element is encountered in processing the XML, then its value is used to update the context Deliver By value. When processing reaches the end of a group that contains a Split By element, then the context Deliver By value will be used to look up the delivery mapping created from the delivery query, and hence to the determine the formatting and delivery of the report associated with the XML block containing with the context Split By element value.

 

 

Bursting XML Block consists of all Split By Group Elements plus Descendant Group Elements

 

Q: Does a bursting block start whenever a new split by element occurs in the XML? Some elements that occur before a split by element seem to be processed as though they occurred after the split by element?

 

The documentation is uninformative as to how each bursting XML block is defined. Given the concept of the Split By element, you might think that BI Publisher would work it’s way sequentially through the XML and start a new block whenever it encountered a new Split By element. If this was the case and your Split By element was, say, the third element in a block, you’d expect elements one and two to appear in the previous block and elements four and greater to appear in the current block.

 

The XML block associated with a particular Split By element consists of all elements that occur in descendent groups of the group containing the Split By element plus all elements that occur in the group containing the Split By element (irrespective of whether or not these elements precede or succeed the Split Element in the XML ordering). So, for example, suppose we have a group G_COMPANIES with a detail group of G_EMPLOYEES, and G_COMPANIES contains the elements, in order, COMPANY_ID, COMPANY_NAME, MARKET_VALUE. If COMPANY_NAME is selected as the Split By element, then the values of all the elements in G_EMPLOYEES plus the values of COMPANY_ID, COMPANY_NAME, and MARKET_VALUE will appear in the same XML block and will be burst to the same report – even though COMPANY_ID precedes the Split By element it is processed in the same block and not in the preceding block.

 

 

Split By and Deliver By Elements truncated

 

Q: Is there any way to view the full split by and deliver by elements on the bursting definition screen?

 

Despite an enormous amount of screen real-estate, the width of the Split By and Deliver By LOVs is so miserly that many XPath expressions are often only partially visible. Unfortunately, the usual tricks of pressing End or CTRL-A don’t work in this case. You can usually work it out from the element sequence that appears in the LOV if the group names are short enough. You can always find the names in the data model resource file, “_datamodel%2exdm”. If you’re keen you could try to post-process the web page to increase the width beyond the default of 160pt (the HTML select lists are named “burst_deliver” and “burst_split” and the JavaScript is contained in file “burstingpanel.js”).

 

 

Sorting and grouping of Bursting Split By Groups

 

Q: What does the documentation mean when it says that split by groups must be sorted or grouped? It’s often not possible to sort within the data model?

 

The documentation states that “data set must be sorted or grouped by this element [Split By]”. This is not true except in a very loose sense. For a start, the XML data does not have to be sorted by the Split By element – whether at source or by using the data model’s sorting functionality in non-leaf subgroups. And it only has to be grouped in the sense that the elements that are intended to appear in the same burst report instance must be within the same data model group as the Split By element or in one of its descendant subgroups or descendant groups linked via data links.

 

 

Full XPath Split By and Deliver By Expressions also needed for non-CLOB Sources

 

Q: The documentation says that the full XPath expression must be entered for a CLOB source, suggesting that it’s not needed for a non-CLOB source?

 

You need the full XPath expression for any source, not just a CLOB source. What the documentation is trying to say (and puts it well in a subsequent section) is that for a non-CLOB source it can parse the XPath expressions out of the XML and can create LOVs that contain the correct expressions for selecting the Split By and Deliver By elements. For a CLOB source it can only partially parse the XPath expressions, and so the expressions that appear in the LOVs are only a subset of those available (and not of any use in practice). So for a CLOB source the developer must work out the correct XPath expressions from the sample XML and manually enter these expressions into the LOVs (where it says Enter).

 

 

Bursting Report Job fails with no Output and “ORA-01789: query block has incorrect number ...” Error

 

Q: I get no output and ORA-01789: query block has incorrect number of result columns with a bursting job?

 

In the context of a bursting job, this error suggests that your bursting query consists of a union with different numbers of columns in each component (BI Publisher does not validate Bursting Query SQL syntax so it won’t be picked up until you burst a job). Try running the bursting query separately in SQL Plus (remembering to quote any bind parameter references).