Percentile - Group By

Hi,

I have created a Measure object in Universe like below

Percentile_Disc(0.25) Within Group (Order By (table1.column1))

and also I have min(table1.column1) , max(table1.column1)

If I create report with a dimension object , min object and max object, Bo is generating correct SQL (group by that dimension)

However, If I includes the percentile object, BO is generating strange SQL i.e. group by pecentile measure object in addition to dimension object.

why/how ‘measure’ object is added in ‘group by’ of SQL (BO generated)?
How to tackle this situation?
I am using Oracle9i


ravik (BOB member since 2004-06-02)

In my honest opinion, a MEASURE object is added in GROUP BY clause only when you apply an Oracle predefined function to that MEASURE in the SELECT field, or when you apply a stored Oracle function to the same MEASURE in the SELECT field in Designer environment.

For example, a MEASURE called “Counter” that has the following SELECT definition:

count(MEASURE_TRX_HOUR.ROWID)

and SUM as predefined aggregation-function, automatically GENERATES a GROUP BY clause in the “SHOW SQL” command in BusinessObjects Reporter.

The GROUP BY clause is referred only to the DIMENSIONS objects of the Query Panel.

Please let us know if that sounds like correct.

[edited, added bbc QUOTE formatting - Andreas]


Christian Konrads :it: (BOB member since 2004-07-21)

BO is generating SQL with percentile measure object in Group by.
User does not wants this way.
How we can restrict BObj to generate SQL without Measure object in Group By.


ravik (BOB member since 2004-06-02)

you can click “sql” button at data provider query panel to open sql. and delete the clause you needn’t , then check the “do not generate SQL before running”. Running the query. You can get the result you want
8)


conniezhang :cn: (BOB member since 2004-07-01)

Hi,

Thanks for work around (overwriting SQL)
We want to build up the confidence to users that Bobj generates SQL automatically.

Is it a bug/incompatibility with Bobj that it includes percentile Measure object in ‘Group By’.


ravik (BOB member since 2004-06-02)

Note:
If Percentile is measure used along with dimensision object then BOBJ is generating correct SQl (percentile measure not included in Group BY).
But percentile is used along with other aggregate measure, then percentile measure object is included in Group By


ravik (BOB member since 2004-06-02)

Hi,

I Raised this Issue with BOBJ Tech Support. It is a Bug in BOBJ.


ravik (BOB member since 2004-06-02)