nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

reporting need on workroute and attachments

timw86

I would like to run a report for work flows. I would like to show all the active workflows, the current process work item, who is responsible for the current work item. I think most of this information is available from the views in the workroute database, no big deal. I would also like to show all attachments, if there are any, attached to the workflow. Can someone assist in this union or join. I am having difficult finding where this relationship exists. SQL statements or table name/column name would be fine.

tw

Find more posts tagged with

Comments

Migrateduser

Hello, TW.

I presume one of the views you're interesting in using for your report is the ProcessInstanceListView - which shows a number of columns from the ProcessInstance (i.e. workflow process) table, among others. The attachments seem to be located in the ProcessInstance table, specifically in a column named "attachments".

The bad news is that the attachments column is a binary data type (a BLOB field) and, as such, I'm not sure how it is that you can read the value. My guess string streams are stored in this field that consist of an XML fragment that contains the attachments for this workflow process. But I'm not certain about that.

Does your reporting tool require you to use SQL only? Is there any way you can add a Java hook to be invoked by the reporting tool so that you can make API calls? Probably not, but I thought I'd ask.

Best of luck,
Nick2112

timw86

Nick,

You are correct the ProcessInstance.attachments column contains xml data. I used c# to read the column and write the output to a text file. The snippet below shows what is contained in just one of our process instance rows. Within the node <NameValueSet> there is a row which contains the attachment name and doc id.

<ProcessInstancePD>
<ActivityInstanceInformation>
<ActivityInfo ActivityInstanceId="85925" Actor="" Assignees="DOCCONTROL"/>
<ActivityInfo ActivityInstanceId="85923" Actor="__process" Assignees="approver"/>
</ActivityInstanceInformation>
<NameValueSet>
<NameValueStruct Name="Cpt008" Type="String" Value="!V3!WORKSITEMP!C!D$783!"/>
</NameValueSet>
</ProcessInstancePD>

For our reporting needs, this is going to create additional work. ( I am still assuming that this is the only place to query this information! ). I can think of multiple ways to build the reports. Here are my initial thoughts:

1) I could use jsp or .net web pages technology. I would format a report in html. I would have access to a high level language at runtime aka report time to query this information. Within the code, I would.... query sql data.... stream xml data into memory or io.... join the two data sources logically and display.

2) I could use java or .net technology in batch mode using a scheduler to query this information and populate custom sql data tables. I would then be able to perform all reporting via sql. This would allow Crystal, Web Page, or any other tool to access this information.

I will keep the forum posted as I work through this task.

TW

--

Migrateduser

TW,

Excellent work! And thanks for sharing your approach and the XML snippet with the forum viewers. This is very interesting indeed. Yes, please keep us posted with regards to which approach works better for you.

Thanks,
Nick

Migrateduser

TimW,

Say, would you be willing to share your C# code with our friends on DevNet? I bet many others out there would like to see what you've done. Only if you have the time and if it's convenient.

Take care,
Nick2112

The world is my country, all mankind are my brethren, and to do good is my religion. - Thomas Paine