Need Help with Universe/BO Reporting Derived Table

[Former Member]

I've been working with Crystal reports for many years,  and am decent at SQL,  I'm new to the Business Object 4.0 and Universe Design Tool.

To give an idea of what I want to do...

I have Table 1 which consists of (Change Tickets) (one record) Ticket Information...  I have Table 2 that contains Approvals of Tickets for the associated Change..(many)  (1-many relationship)

now back in the Crystal Reports Days, I would literally just create a command that joins table 1 and table 2 and flattens them in such a way that I get a 1 to 1 relationship..  so that I can find a direct approval based on a condition...  in other words.. I wanted to read Table 2 and if a group approved it, then it would make a derived table (command) such that a ticket number is shown, and approval status based on a group would be shown.. I needed to do this so that I can sort by whether a group approved it or not... on the main report..

now..   I am trying to see how i can do the same thing with Webi and Universe Design tool.

essentially what I want to do is read Table 2 to see if say Group 1 approved a ticket...   and since there are multiple approvals....  if i were to move all the fields from both tables... i would end up getting multiple ticket Numbers... cause there are multiple groups in a ticket..  but I really am only interested in creating a field such that Group 1 approved,  another field where Group 2 approved... etc...

so really what I want is to flatten Table 2 to fields...in the main table so I can do the report correctly..

cause I need to group by Group 1 based on if status was approved or not..    and then i need to group by Group 2 based on approved or not..

So I am not sure exactly which method would work best,  I was thinking of making a derived table in UNiverse design tool...  but am wondering if there is a way to do this in business object's webi...

give samples tables here

Table 1

Ticket No.  | Status | Submitter

123456      | Active  | John Smith

234567      | Active  | Daniel Smith

345678      | Active  | Carol Smith

Table 2

Ticket No. | Approval_Status | Approval_Group

123456      | Pending            | Group 1

123456      | Approved          | Group 2

123456      | Pending           | Group 3

234567      | Approved           | Group 1

234567      | Pending            | Group 2

234567      | Pending            | Group 3

345678      | Approved            | Group 1

345678      | Pending             | Group 2

345678      | Approved           | Group 3

So what I really want to see when i do a report   and lets assume just excel file

I want to see

Report

Ticket No.  | Status | Submitter      |  Approval_Group 1 |Approval_Group 2 |Approval_Group 3 |

123456      | Active  | John Smith    | Pending               |Approved             | Pending               |

234567      | Active  | Daniel Smith  |Approved              |Pending               |Pending               |

345678      | Active  | Carol Smith   | Pending               | Pending             | Approved              |

So I just need to know if this is possible.... with Webi,   

if that is not possible in webi,  then to use universe and create a derived Table such that when I add those fields in a report  it would show when I add it to webi..

i hope this explains a little of what I want to do...

sorry if it was long... i've been stumped on this for a while now.

thanks

[Former Member]

Hi Daniel,

Did you tried a cross tab report in webi.

I think it should give you desired results.

In this cros tab, your horizontal headers will be Ticket,status and submitter and your vertical headers will be approval group. and intersection will be approval status.

Thanks

Gaurav