12-17-2015 04:38 PM
Hello,
I am first time user onbase running SQL queries, I need some input on how to put this together.
Part 1
I want to get a report of documents that are scanned into the "xxx" scanning queue. What I need to see is how many documents have been scanned for the previous week, broken down per day and per document type and per user.
so far this is what i got but i'm don't think it what suppose to be for above request
select sl.usernum, aq.batchnum, aq.queuename, sl.logdate, aq.batchname,
id.itemtypename, aq.numberdocuments, aq.totaldocuments, aq.totalpages
from hsi.scanninglog sl, hsi.archivedqueue aq, hsi.useraccount, hsi.doctype id
where aq.queuename = 'xxx'
and sl.logdate >=dateadd(day,datediff(day,0,GetDate())- 7,0)
Part 2
We want another report that tells us by day (for the previous week), how many documents went into the "abc" lifecycle, and how many left the "abc" lifecycle.
and for this i got
select wf.statenumto, wf.entrytime, wf.exittime, wf.usernum
from hsi.wflog wf
join hsi.useraccount ua on ua.usernum = wf.usernum
where statenumto = 108
and wf.entrytime >=DATEADD(day,datediff(day,0,GetDate())- 7,0)
greatly appreciate any help
12-18-2015 04:51 AM
Hi Jenny,
Just a quick tip but did you look into using our Report Services module? I see your organization is licensed for this module and it does come with multiple scanning and Workflow reports "out of the box". Even if these don't give you 100% of what you wanted, depending on your access you could see the SQL used in these default reports as a basis to create your own SQL Query or a new report in Report Services. Especially if you choose to export a report template and Import as a new report which you can then modify to your liking.
I hope that may help in some way.
Warm Regards,
Chris
12-18-2015 04:51 AM
Hi Jenny,
Just a quick tip but did you look into using our Report Services module? I see your organization is licensed for this module and it does come with multiple scanning and Workflow reports "out of the box". Even if these don't give you 100% of what you wanted, depending on your access you could see the SQL used in these default reports as a basis to create your own SQL Query or a new report in Report Services. Especially if you choose to export a report template and Import as a new report which you can then modify to your liking.
I hope that may help in some way.
Warm Regards,
Chris
12-18-2015 05:20 AM
Thanks Chris,
I didn't realize about this, I'll look into this further more.
12-18-2015 08:03 AM
12-18-2015 10:24 AM
n/a
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.