LiveReport For Document Count Within A Folder

Does anyone have an example of a livereport that will list a count of documents within a parent folder and it's child folders? Any help would be greatly appreciated.

Regards,

Marlyn Norris

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

All comments

Damion

Marlyn
Something like this might work for you
select dt.parentid, pt.Name, count(dt.subtype) as [Doc Count]
from DTree dt
left join DTree pt
on dt.ParentID = pt.DataID
where dt.ParentID in (select dt.DataID
from DTree dt
left join DTreeAncestors anc
on dt.DataID = anc.DataID
where anc.AncestorID = [Top level folder]
and dt.SubType = 0)
and dt.SubType = 144
group by dt.ParentID, pt.Name

Put the top level folder ID in and see how you go

Norris_Marlyn

Damion,

Is this query for SQL or Oracle? When I execute it on my Content Server 16.2.8 version running SQL 2016 it states the following error message: Content Server Error: Error processing request
Error executing query. Refer to the Content Server logs for more detail.

Any clue why this would not execute?

Regards,

Marlyn Norris

Damion

Hi Marlyn
I'll admit I hadn't tried that query as a LiveReport. I was running it from SQL Management Studio. Yes, it's an MSSQL query.
I've now thrown it in to a LiveReport and it works on a 16.2.9 system.
Did you replace the placeholder with a folder ID or make it a parameter for input on execution? That's the only thing I can think that would make it not run.
If I get these sorts of errors in a LiveReport, I modify the URL that is in the address bar to include &debugsql=true then refresh the page. That will output the actual SQL that is trying to run with parameters etc filled in. That can then be taken over the SQL Management Studio and run. The error messages from there are more helpful.

John Simon

You can also check the "Show Verbose Database Error Messages" in your Livereport and when it fails it will give you more information (rather than just "Refer to the logs for more info").
Cheers...

GregPetti

@Damion said:

...

If I get these sorts of errors in a LiveReport, I modify the URL that is in the address bar to include &debugsql=true then refresh the page. That will output the actual SQL that is trying to run with parameters etc filled in. That can then be taken over the SQL Management Studio and run. The error messages from there are more helpful.

This parameter setting is the same as using the "Save and View Source" option from the LiveReport. It is particularly useful if you have complex templates and parameters in your LiveReport as it shows you all of the SQL that gets constructed ready for submission.

For error messages, John's point about: "Show Verbose Database Error Messages" is important. This was introduced to allow developers to obscure messages that typically revealed all of the SQL being executed on an error (for security reasons).

One final, additional point is that if you don't have "Use Cursor Fetch Mode" selected you will also get a very terse message, regardless of the verbose setting.
Greg