Group By in BO universe objects.

[system]

Hi All,

I am new for business object. I want to create a object with group by clause. I want to fetch Min(row_active_date) from a table with group by clause. I used group by in object’s where clause. Parsing is OK. however when i am running this report it is giving me only one value. which is Minimum value of row_active_date. Group by is not working. Can anyone help me on this please. I have attached a screenshot which shows, the way i am doing this.

thanks in advance.

---

negiajay (BOB member since 2013-06-19)

[system]

Welcome to B:bob:B!

This is a universe question so I will move it to the Semantic Layer forum for you.

---

Nick Daniels (BOB member since 2002-08-15)

[system]

Thanks Nick

---

negiajay (BOB member since 2013-06-19)

[system]

Hi,

Why do you need the GROUP BY clause in the object definition?

The GROUP BY clause is generated automatically by the tool in an SQL statement when a dimension AND a measure are used in a query.

---

Marek Chladny (BOB member since 2003-11-27)

[system]

I used group by in object’s where clause.

Why would you put a group by in the Where clause :? ?

Hint, create a dimension for what you want to group by and use that with your measure object.

I strongly suggest reading the Designer manual to get an idea of how this all works.

---

Mak 1 (BOB member since 2005-01-06)

[system]

Thanks Marek. Mak 1

I need to apply group by on the basis of only three columns. that is the reason i want to specify the group by clause in universe object.

is there a way to do this ?

---

negiajay (BOB member since 2013-06-19)

[system]

Brief hint:
Look at derived tables in Universe Designer (similar to InLine Views in Oracle e.g.).

---

Andreas (BOB member since 2002-06-20)

[system]

Group by is something you need to view as ‘in the report’.
Webi will group on the basis of the selected field.

A universe provides information on the select * from … where table.x=table.x
A webi-report will then group further if required on basis of your selected field.

So here if you leave out the group it will take the min(.) over what is selected.
And in displaying in webi for instance it will be min(.) according to the information you show.

If your reeeeeeaaally need the grouping to be done in the universe then in deed use a derived table.

---

IngeH (BOB member since 2004-06-22)

[system]

@IngeH

Group by is something you need to view as ‘in the report’.
Webi will group on the basis of the selected field.

A universe provides information on the select * from … where table.x=table.x
A webi-report will then group further if required on basis of your selected field.

So here if you leave out the group it will take the min(.) over what is selected.
And in displaying in webi for instance it will be min(.) according to the information you show.

If your reeeeeeaaally need the grouping to be done in the universe then in deed use a derived table.

Indeed correct, as long as you set the projected aggregation correct on the measure object :), in this case it should be set to Min.

Andreas post on projected aggregation here:-

https://bobj-board.org/t/71712/3

Create a measure, with the following code in the select:-

Min(Case When Table.Column='Y'Then Table.Column End)

Create 3 dimension objects with the code in the Select

Table.Column1

Table.Column2

Table.Column3

Use all objects in your query along with others and refer to Inges post, above.

---

Mak 1 (BOB member since 2005-01-06)

[system]

indeed a measure

I must add that i prefer anything but sum to be done with variables in a report.

In 4.0 I find contexts in the report easier to use then before for some reason, which then also gives a better insight in where the calculations are done in relation to the data retrieved.

---

IngeH (BOB member since 2004-06-22)

[system]

Thanks Mak 1, IngeH, Andreas and Marek Chladny

finally it seems we can not apply group by in a object . Thanks for your valuable suggestion.

---

negiajay (BOB member since 2013-06-19)

[system]

@negiajay

finally it seems we can not apply group by in a object

Because it does not make any sense. The GROUP BY is generated for you by the tool automatically and when needed

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Hi Negiajay,

Its not recommended to write the Group By Clause in the Object definition. How ever as mentioned by Andreas you can create a derived table create the Min object for that table.

select 
  table.column1,
  table.column2,
  table.column3,
  Min(table.ColumnABC)
From Table
Group by
  table.column1,
  table.column2,
  table.column3

Join this derived table to your original table (TABLE) on
column1
column2

[Moderator Edit: Added code formatting - Andreas]
column3

---

HarpreetSingh2785 (BOB member since 2013-06-20)

[system]

@HarpreetSingh2785

Hi Negiajay,

Its not recommended to write the Group By Clause in the Object definition. How ever as mentioned by Andreas you can create a derived table create the Min object for that table.

select
table.column1,
table.column2,
table.column3,
Min(table.ColumnABC)
From Table
Group by
table.column1,
table.column2,
table.column3

Join this derived table to your original table (TABLE) on
column1
column2
column3

But why would you do this, if you could allow Webi to group the calculation on the report?

---

Mak 1 (BOB member since 2005-01-06)

[MarkP]

@Mak 1

@HarpreetSingh2785

Hi Negiajay,

Its not recommended to write the Group By Clause in the Object definition. How ever as mentioned by Andreas you can create a derived table create the Min object for that table.

select
table.column1,
table.column2,
table.column3,
Min(table.ColumnABC)
From Table
Group by
table.column1,
table.column2,
table.column3

Join this derived table to your original table (TABLE) on
column1
column2
column3

But why would you do this, if you could allow Webi to group the calculation on the report?

It may be a case of always wanting the minimum for that particular combination of objects in the derived table, no matter what set of results you have in the Webi query. A touch limiting, but the only reason that I can think of and arguably better achieved through a database function.