Unable to Create Subgroups: "Group By" disabled in BI Publisher Data Model

Eric Geddes

How do I Create Subgroups in a SQL Dataset in my BI Publisher Data Model when the "Group By" option in the contextual menu is grayed out?

The documentation says it should be easy:

Eric Geddes

I'd call this a bug, but since it's been broken for more than a decade, I'm guessing Oracle Development calls this "intended functionality".

It happens when BI Publisher doesn't like the standard SQL we write based on best practices and explicit direction from the documentation.

If your SQL statement begins with a subquery, such as

WITH ElementEntryValues AS (
    SELECT
        ElementEntryValuePEO.ELEMENT_ENTRY_ID,
        ElementEntryValuePEO.INPUT_VALUE_ID,
        …

All you have to do is add a comment at the beginning of the SQL statement. (You may wish to choose your own.)

/* Oracle wants me to suffer */
WITH ElementEntryValues AS (
    SELECT
        ElementEntryValuePEO.ELEMENT_ENTRY_ID,
        ElementEntryValuePEO.INPUT_VALUE_ID,
        …

Works like a charm!

• Special thanks for this trick goes to @Gunasekaran Rengasamy in this random post in the E-Business Suite Community Forum.
• This self-answered post is for anyone running into this situation in the future, in the hope they avoid wasting the hours of struggle that I've spent trying to find a solution.
• If Development is unwilling to fix this, I wonder what it takes to get the documentation updated to warn unsuspecting users…

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/71605#Comment_71605

Very helpful & useful information.

Thanks for sharing @Eric Geddes; much appreciated.