AnsweredAssumed Answered

BO Universe Conditions/subquery Help!!!

Question asked by cshah on Sep 23, 2010
Latest reply on Oct 14, 2010 by Chris_Hackett
Hello,

Tables involved: Project Table and Status Report Table

Table 1: NBI_Project_Current_Facts

Table 2: ODF_CA_Catsprjstatusrep o
I have a custom Universe that has the Table 2 and have joined Table 1 and Table 2
I have also created a measure for MaxDate for Report_date*

Table 2 fields of concern:
o.odf_parent_id
o.report_date
o.report_update
o.che_key_reminders
o.che_proj_detail,
o.che_current_progress
o.che_accomplishments, che_planned

Report Style: Webi

Expected Result:
When prompted for a Project Name (nbi_project_current_facts), based on the latest date of the status report, I should get the above fields of concern from Table 2.

What i did was concentrated on Table 2 (I did not join table 1 because i just wanted working sql to make sure i can follow th logic there first) and have a working sql statement that returns data that I need using a subquery.

********
select o.odf_parent_id, o.report_date, o.report_update, che_key_reminders,
che_proj_detail, che_current_progress, che_accomplishments, che_planned
from odf_ca_catsprjstatusrep o,

(select max(report_date) as maxdate, odf_parent_id from odf_ca_catsprjstatusrep group by odf_parent_id)maxresults
where o.odf_parent_id = maxresults.odf_parent_id
and o.report_date = maxresults.maxdate
and o.odf_parent_id = '5000347'
order by odf_parent_id
********

So my question is if I create a condition in my Universe, by the way, i have never created conditions so it's difficult to specify in the where clause what i need when i have a subquery involved, how do i do this??
I am reading manuals etc on conditions but ofcourse its always the simple stuff :)

*Also, the other option I tried to play with is webi subqueries, however I'm not sure where to place objects when creating a subquery.  I have a measure for max date however where does it play into the picture.

If I do not use a subquery for the maxdate aggregate and having the group by, and I add other fields from table 2, I would ofcourse then not get the latest date and get all the dates.

Any ideas, suggestions, opinions, options are welcomed. I am really stuck here and can't move forward with my report.

Thank you in advance.

Outcomes