CPS Report with Schedule and Job Parameter Information

[Former Member]

Is it possible to create a report in CPS that will provide the below information?

Job Number

Job Name

Description

Program Name

Variant name

Time Window

Submit Frame

Job Chain Name

I am aware how to create a filter, then the report and use it with the system job to get the HTML format file through email.

What I am struggling to get is Schedule information (Time Window and Submit Frame) and Prog Name and Variant Information in the report (through a filter)

Best Regards,

Kedar

[nanda_kumar21 · Accepted solution]

The problem in creating this report is the information you are looking for is located in two different tables.

The Job table (JCS_JOB0) contains all the information except program and variant names as they are parameters. These parameters are located in another table called JobDefinitionParameter(JCS_JOBDEFPARAM0).

So you can try writing a  sql query in your report (you can query only the datamodel, not the tables directly)-

I'm sure it is going to be a complex query, as the 'program' and 'variants' are a part of the same column.

Will post a sample query, if i get time.

Thanks

Nanda

[gmblom · Accepted solution]

You can use a query like this:

select j.JobId, j.Description, p1.InValueString ProgramName, p2.InValueString VariantName, tw.Name, jd.Name

from Job j, JobParameter p1, JobParameter p2, TimeWindow tw, JobDefinition jd

where p1.Job = j.UniqueId and exists (select 1 from JobDefinitionParameter jdp1 where jdp1.UniqueId = jp1.JobDefinitionParameter and jdp1.Name = 'ABAP_PROGRAM_NAME')

and p2.Job = j.UniqueId and exists (select 1 from JobDefinitionParameter jdp2 where jdp2.UniqueId = jp2.JobDefinitionParameter and jdp2.Name = 'ABAP_VARIANT_NAME')

and tw.UniqueId = j.TimeWindow

and jd.UniqueId in (select j.JobDefinition from Job jp1 where jp1.UniqueId in (select jp2.ParentJob from Job jp2 where jp2.UniqueId in (select jp3.ParentJob from Job jp3 where jp3.UniqueId = j.UniqueId))

Regards Gerben

[Former Member · Accepted solution]

Hi Nanda / Gerben,

I will try the guidelines you gave and update about it.

Thank you.

[Former Member · Accepted solution]

Hi Gerben, I am also trying to create a cps report that will pull the Average Run Time from the Statistic of jobs in cps. I have created many filters or reports, but cannot seem to get this information, so I am thinking it might be something like kedar, to where I would need to create a SQL query,but I am not that up on using them in relationship to cps reporting. I know when I create a filter and inside that filter, it has a box labelled Extend filter with user-defined SQL. And when I create an actual report I can add that Query filter. Otherwise I also have used the Query area to add an Obect Definition Query: when creating a report. I was able to create a report from another example back about a year ago when I created a report to pull ABAP Variants.  Would it be possible to see if you could help make create one using the string I would need?  I have included a screenshot of what I have been trying to come up with as well as that one report I did.

Rick

[Former Member · Accepted solution]

Hi Gerben, I recieved an e-mail from you, but it was addressed to a Alyssa. So checking to making sure it was meant for me or someone else about the CPS keep clause.

Hi
Alyssa,  

Dont
change all your definitions. Just introduce a global keep clause.

  1)
Create a Job Definition called System_Defaults_System: save and close

  2)
Open it again, set the required Keep Clause

Done, the System_ProcessKeepClauses maintenance job will take this setting in
consideration.

Just in case, my question I was hoping you could help me on, was creating some type of job or report that, when ran, would pull the Statistics stat for the Average Run time on a job in cps. The only 2 query's I see in the system is a thing I see that StatisticCategory, JobDefinitionStatistic and a JobChainCallStatistic, but when I have ran those, it only seems to pull the Standard Run time and not the Average Run time. Not sure if I have access or how I could create of of my own or if this is a redwood thing. But maybe you have some background in this and can show me what I could do, to pull this information. Thanks again Gerben for anything,

[gmblom · Accepted solution]

Hello Rick,

The update to Alyssa was in a different thread, so I dont know why it ended up with you.

Anyway. You are looking in the right direction. The JobDefinitionCounter and JobChainCallCounter tables hold the counts, the JobDefinitionStatistic and JobChainCallStatistic tables hold the totals, you will have to divide them with the counts.

All items are counted and added by the StatisticCategories (runtime, completedcount, errorcount etc) indeed.

The easiest way to get this information is calling the getAverageRuntime() method on a JobDefinition or JobChainCall object. But then you have to do your report in RedwoodScript.

Regards Gerben

[Former Member · Accepted solution]

Hi Gerben,

Thanks for all your words of wisdom.  When you stated RedwoodScript, I decided to look under Applications, Redwood and in Healthcheck folder, I found a job that was already there labeled, Healthcheck_jobreportrunstatistics. When I edited and looked at the source coding, it did have ("Job Definition,Average Run-Time) So I ran it and it pull the Average Run-Time for all jobs. I did compared it to the jobs in cps and they matched. Thanks again for working with me on it.