Other Options besides Cycle Time Report for seeing duration from Submitted back to Pending

We have been using the cycle time report to see duration between pending to submit, submit to ccb, etc.
We have a process that if a user submits something and it needs a lot of work, we send it back to pending so that the clock doesn’t run for days looking like doc control is lagging while its actually the originator.
The problem is I’ve discovered a bug in that report for when you try and pull data going back statuses, in this case, going back from Submitted to Pending pulls other data such as changes going CCB to Pending which is not correct for what I want to see.

Oracle has logged a bug on this but it’ll be years till they get to it.

Anyone using different reporting to get this data – I’ve seen mention of SDK, Oracle BI Publisher and cognos. (I used to use Quiz by Cognos for MANMAN databases! That shows my age. 🙂
Do we need to purchase these other solutions or do they come with Agile?
I know SQL a bit, but not with the Agile Database. Which solution is easiest to learn? Can you get a copy of the schema somewhere or just need to dig around?
A lot of times all we need is to be able to access the workflow tab or history tab to get the data we want. Its frustrating that the agile reports and searches don’t allow that.

I realize that was a lot of questions, but basically, what is everyone using for reporting out of Agile, especially when looking at change duration between workflow steps.  (and we’d also like to be able to not include weekends in the calculations!)

Thank you,

Agile Talent Asked on September 17, 2018 in Agile PLM (v9),   Product Collaboration.
Add Comment
4 Answer(s)

When you are really serious about cycle times it seems out-of-box just does not cut it. There are scenarios that throw off the statistics like routings that span a reporting cycle and how you want to treat rejections. I have run into issues with the SDK being able to search the workflow table.When it comes to custom cycle-time calculations it seems like direct database reads is the way to go and then process the data the way you see fit. But be aware, it will take some work to account for all the different scenarios: rejections, repeated rejections, leap-frogging steps and the like.

Oracle does not publish a database schema. There are some out there from people who have had to figure this stuff out. Perhaps offering up some chocolate chip cookies will attract attention.

Agile Angel Answered on September 18, 2018.
Add Comment

Thanks for the answer Steve, and I would not only offer up chocolate chip cookies but some of my homemade pomegranate jelly!  People on this forum are quite generous sharing their knowledge of the database it seems.  
For the reporting we want it seems we just need to find a person or company that has written these type of reports and purchase them. 


Agile Talent Answered on September 18, 2018.
Add Comment

Make them macadamia/white chocolate cookies, and I am yours.  And Steve is correct, there has *never* been a published Agile schema. Even to consultants.

 The data you need will mostly come from the SIGNOFF, WORKFLOW_PROCESS and CHANGE_HISTORY tables, starting from CHANGE and also pulling in user information from AGILEUSER.  I wrote a simple Change Status Cycle Time report many years ago (by change class and product line), but that was using MSAccess and VB, linking to the Agile database using ODBC. I haven’t even looked at it in over 10 years.

 I do think it would be doable using an PL*SQL script, which would put the information into a table, and that could then be exported using SQL Developer or Toad into a spreadsheet or something else such that it could be formatted as you need. I agree with Steve that there could be lots of different scenarios. and the script would need to handle them all, just in case. Nothing that is rocket science, but to cover the bases. 

 Let me ponder time and effort to do this and discuss some other options with colleagues, and contact me next week, and we can talk more.

Agile Angel Answered on September 19, 2018.
Add Comment

You should be able to get away with just using the CHANGE_HISTORY table here through WORKFLOW_PROCESS is ok too.  The trickiest part is defining the requirements – namely, how do you want cycle count to be affected if the ECO was returned or rejected more than once?  Do you want it just the last time it was submitted compared to the last time it was returned?  Looks at the history tab of an ECO and then use the CHANGE_HISTORY or WORKFLOW_PROCESS tables (which query off of what you see in the history tab).  

This query, for instance, shows me every time ECO1234 was moved from Pending to Submitted and from Submitted to Pending and returned 5 rows due to the Change’s back and forth history.  From here, I’d just want to lock down exactly what I wanted to see as well as some math to subtract two local_dates for the cycle time.  

FROM   agile.workflow_process 
WHERE  change_id = (SELECT id 
                    FROM   agile.change 
                    WHERE  change_number = ‘ECO1234’) 
       AND ( ( state = ‘59745’ 
               AND next_state = ‘59742’ ) 
              OR ( state = ‘59742’ 
                   AND next_state = ‘59745’ ) ) 
ORDER  BY local_date;

Agile Angel Answered on September 19, 2018.

Your state and next state numbers will be different.  The easiest way to get all of your statuses is just simply:

SELECT DISTINCT n2.description AS workflow, 
FROM   agile.workflow_process 
       JOIN agile.nodetable n 
         ON n.id = state 
       JOIN agile.nodetable n2 
         ON n2.id = workflow_id 
ORDER  BY 1, 2;

on September 19, 2018.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.