Clarity

Hi ,

I need to create a program status report by using Jaspersoft Studio. So i need to extract all the project informations, Financial Informations & Program informations. i'm working on this functionalities and also i'm getting stuck with complex queries although need your support to complete this.

Thanks in Advance!!!

Regards,

Karthick

The inv_hierarchies and the inv_flat_hierarchies tables store master-sub relationships for investments in CA PPM.
In the inv_hierarchies table you will have records for each immediate relationships.
So you can also use inv_flat_hierarchies. This tables simplifies the record access. You will see Parent ID, Child ID and the Gierarchy Path in that table. In this way, you can run a simple select query and get all the sub projects under a specific investment.

for example:
Suppose there is a program called Program1 whose internal ID is 5000001.

Now run this query and it will show you all the sub-investments under program1 in this hierarchy.
Select child_id from inv_flat_hierarchies where parent_id = 5000001

Hope this was helpful.

Cheers!

thank you Abhisek

Program status report has 2 slides, first slide should be program information  and second slide should be project information of the program. So i got the table relationship from you which would be more helpful. I just want to know can we display sub project details in jaspersoft studio. because it doesn't have loop functionality. so how to segregate the sub project information for create a status report. Is there a possible to make report?