Other Options besides Cycle Time Report for seeing duration from Submitted back to Pending
Hi,
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,
Laura
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.
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.
Thanks,
Laura
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.
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.
SELECT *
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;
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,
state,
n.description
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;