Skip to Content
0
Former Member
Dec 19, 2008 at 07:47 AM

GROUP BY in Desktop Intelligence

127 Views

Question: Is there a way to use GROUP BY statement at the Universe level?

Detailed Example: I have Cricket Teams and Team Members. I want to display all the team members for a team in a single cell as comma separated values. At database level, I can achieve this by using an oracle function (stringagg) as follows:

SELECT Team, stringagg(Team_Members)

FROM Cricket_Team

GROUP BY Team

In Business Objects Universe, I have created two objects:

1> Team as Cricket_Team.Team

2> Team Members as stringagg(Team_Members)

When I drag these two objects in the report I get DA0005 error (Exception: DBD, ORA-00937: not a single-group group function

State: N/A)

This is because the SQL generated by Desktop Intelligence is

SELECT Team, stringagg(Team_Members)

FROM Cricket_Team

I can fix this at reporting level by customizing the SQL, but I want this to be fixed at the Universe level so that all the users can get the Team_Members list in comma separated values for each Team.

So, is there a way to get this GROUP BY Team statement at the Universe level?