Project and Portfolio Management Practitioners Forum
cancel

how to create a report

Highlighted
riznad
Super Contributor.

how to create a report

Hi;

I created a custom query to find user who have not submited their timesheets.

I want to create is as report on PPM system. And I want to run it every monday.

I created a new report on admin menu > configuration > Report types

on this report they where shall I wtire the query?

I only find custom command place on the screen below.

Is here the right place?

 

Shall I definethe query here?

Or any other method?

 

select ku.full_name, ku.email_address
from PPMTFKB_USR.knta_users ku
where ku.end_date is null and
ku.user_id not in
(
select ts.resource_id from ppmtfkb_usr.tm_time_sheets ts
where ts.period_id  in(select kp.period_id-1 from ppmtfkb_usr.KTMG_PERIODS kp
where kp.start_date < (select sysdate from dual) and kp.end_date > (select sysdate from dual))
and ts.status_code in (2,4,6,7) --pending approval, approved, frozen, closed
)
and ku.user_id in (select up.user_id from ppmtfkb_usr.knta_user_products up
where up.product_id=5)

13 REPLIES
SureshR
Super Contributor.

Re: how to create a report

Hi Riznad,

 

There are two ways to create reports in the PPM:

 

1)      Oracle Script

2)      JSP Reports

 

Define your business logic in the Oracle Script/JSP file and call the same from the Report Type with the help of custom command.

 

Regards,

Suresh

Celil
Acclaimed Contributor.

Re: how to create a report

Hi Riznad,

 

I haven't got any experience about your topic but I have a book about custom reporting in PPM. It a little bit old but working. If you realy need the book, please give me your email.

 

 

PS: Please keep alive our forum via KUDOS the professionals

 

Celil

IT Governance Professional
& PPM Solution Architect
knopporn
Regular Contributor.

Re: how to create a report

Can you provide me the book as well?

AlexSavencu
Acclaimed Contributor.

Re: how to create a report

Hi,

 

I can also provide the book if anyone is interested.

 

The one that I have is basically the reference to the PPM JSP reporting tag library.

 

For your problem, I would strongly recommend to investigate an OOTB report and to create a new one in a similar manner.

 

cheers

alex


--remember to kudos people who helped solve your problem
Celil
Acclaimed Contributor.

Re: how to create a report

Hi Alex, Knop,

Please share your email addresses.
Celil

IT Governance Professional
& PPM Solution Architect
bukem909
Valued Contributor.

Re: how to create a report

Hi Celil

 

Can I have a copy of the reference book please?

 

Thanks

 

 

Celil
Acclaimed Contributor.

Re: how to create a report

Hi bukem, Please share your email address.
Celil

IT Governance Professional
& PPM Solution Architect
bukem909
Valued Contributor.

Re: how to create a report

Hi Celil

 

I have PM'd my email address to you.

 

Thanks again

bukem909
Valued Contributor.

leRe: how to create a report

Hi Celil

 

Did you receive my PM that I sent you?

 

Thanks

 

 

TurboMan
Honored Contributor.

Re: how to create a report

Hi Riznad,

 

If I were you I wouldn'T go with the PPM reporting, use ORACLE JOBS instead.

Create a SP in ORACLE and send mails , then create a ORACLE job and schedule  the procedure as follows.

 

DECLARE
  jobid number;
BEGIN
  --Every DAY at 6:00 o'clock--
  dbms_job.submit(jobid, 'sp_unsubmited_timesheets;', NEXT_DATE => TRUNC(sysdate+1)+6/24, INTERVAL => 'SYSDATE + 1');

  --To run every DAY at 7:30 o'clock ,make  NEXT_DATE => next_day(trunc(sysdate),'MONDAY')+7/24+1/48
  dbms_output.put_line(jobid); --Prints out the job number, check whether it is the running jobs list

  COMMIT;
END;

 

Check the running jobs with the following query.

SELECT * FROM user_jobs

;

 

Hope it helps

 

Regards

T.M.

RRK_19
Respected Contributor.

Re: how to create a report

Hi,

 

Can you send the documentation to roopesh.rrk@gmail.com

 

 

Thanks

J_e_s
New Member.

Re: how to create a report

Can you please send me the documentation you refer to in your 2011 post?  Thank  you in advance!

Ramazan1
Super Contributor.

Re: how to create a report

Hi Celil,

 

could you please send me the book too.

 

ramazan.cellek@bawagpsk.com

 

Regards,

Ramazan