Using original work queue collection with added status and detail columns for reporting

[JayBorden]

I have what I assume is a standard use case, but BP performance for the reporting piece is taking far too long. Below are the key points to the process, and wondering what I can do differently: 1. Loading a text file into a collection with 7 columns of data needed to book transaction 2. Loading that collection into a work queue so 5 resources can book the transactions, updating a tag and status with data that will need to be reported with the original 7 columns 3. Create an output report with all the original 7 columns plus the status and my tag data items When I get the collection of the completed or exception items, it gives me just the IDs. When I get data for each item using the ID, I get the single row of the work queue collection and the status and tag data I need. I can append the single row collection for the new columns I need, and then add that new row with the added fields into a "Report" collection with all the other output rows, but when I am dealing with 20,000 transactions it is taking hours to process (Guessing it is since I need to build out the collection through ByVal functions with large amounts of data). Is there an easier way to do it by hitting the BP database or something I am missing?

[Denis__Dennehy]

This sounds like a topic needing a 1 or 2 hour design discussion rather than one i could even begin to understand and discuss on the Forum. I would recommend you reach out to the experienced consultancy resource that is enabling your COE.

[Sankar_GaneshKo]

May be the below scrip help you to make the better report. we can directly take it from BP database. Drill through the sub queries to get more details. SELECT * FROM ( Select Flow, status from ( select Q.name as ""WQ"", BQ.keyvalue as ""KeyValue"", BQ.STATUS, BQ.exceptionreasonvarchar, BQ.exceptionreasontag,dateadd(Hour,-7,BQ.loaded) LoadedTime,dateadd(Hour,-7,BQ.finished) FinishedTime, CAST((BQ.finished -BQ.loaded) as time(0)) 'Handling Time' from BPAWorkQueueItem BQ inner join [BPAWorkQueue] Q on Q.id = bq.queueid Where BQ.loaded > convert(datetime,convert(int, GETDATE()-1)) and BQ.loaded < dateadd(Hour,7,getdate()) ) a ) AS SourceTable PIVOT(Count(status) FOR [status] IN([Success], [Failed] )) AS PivotTable Order by WQ

[JayBorden]

Thank you for the help, we followed a similar path to our solution. We were able to do a sql call for the exact data needed (we did a call passing in work queue and tag, which is returning status, tags, and item data collection). The item data collection of the original fields comes back as xml which we can easily parse in a code block while building out the final report collection. We'll build it into a stored procedure and use a process to handle the DB connection/authentication/call the SP.