All documents created by a user in a specific parent folder Live Report In CS10 Oracle DB

I am trying to create a Live Report for all documents created by a user in a specific parent folder In CS10 Oracle DB. Any help would be appreciated.

Regards,

Marlyn Norris

Find more posts tagged with

Comments

CBancroft

Yu can use something like this as a starting point. It will list all objects ion a folder and its child folders.

SELECT * FROM DTree d START WITH d.DataId = %1 CONNECT BY PRIOR DataId = ParentId

add a NodeId input for %1 and make is a user input parameter. That will be your beginning folder

Simply add another condition to limit the results by specific user. That could be ownerid or created by, for example.

From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>
Sent: Friday, January 11, 2019 2:01 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: All documents created by a user in a specific parent folder Live Report In CS10 Oracle DB

All documents created by a user in a specific parent folder Live Report In CS10 Oracle DB

Posted bymarlyn.norris@nassco.com (Norris, Marlyn) On 01/11/2019 01:59 PM

I am trying to create a Live Report for all documents created by a user in a specific parent folder In CS10 Oracle DB. Any help would be appreciated.

Regards,

Marlyn Norris

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	My Support

Marlyn Norris

Chris thanks for the information. I did notice when I run the live report in CS10 it only returns results back for the user home directory. How should this report be modified to display all results of documents under a folder?

Regards,

Marlyn Norris

appuGmail

Perhaps its the LR format that confuses it

SELECT * FROM DTree d START WITH d.DataId = %1 CONNECT BY PRIOR DataId = ParentId

The %1 should a Folder DataID.You have such a thing in LR its NodeID or ContainerID

then the second change I would introduce to Chris's query as

SELECT * FROM DTree d WHERE CreatedBy= %2 AND START WITH d.DataId = %1 CONNECT BY PRIOR DataId = ParentId

The %2 I would put UserID as the parameter. In English, we can read that query as From that DTree table look for items underneath a particular folder(%1)

that was created by a user(CreatedBy %2 is the KUAF.ID that created the Objects)

PS: It is best not to rely on the LR interface, what usually people do is use a Database tool SQL developer or TOAD connect to LL Database.

Model the query if possible get a copy of the schema, free to you after an NDA. If the query works in the tool but not in LR here's a simple hack that we all use

In the LR make a deliberate error SELECT * FROM DTree d START WITH d.DataId = %1 CONNECT BY PRIOR DataId = ParentId OhMyMy

When the LR runs it will print the error albeit fill in the Bind Params so you have a guideline on where it went wrong. I also did not have an Oracle database so do your own research.

Ciao,

..... appnair appunair( Livelink Rocks )

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

On Fri, Jan 11, 2019 at 3:31 PM eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

RE All documents created by a user in a specific parent folder Live Report In CS10 Oracle DB Posted bymarlyn.norris@nassco.com (Norris, Marlyn)On 01/11/2019 04:28 PM

Chris thanks for the information. I did notice when I run the live report in CS10 it only returns results back for the user home directory. How should this report be modified to display all results of documents under a folder? Regards,Marlyn Norris
[To post a comment, use the normal reply function]
Topic: All documents created by a user in a specific parent folder Live Report In CS10 Oracle DB
Forum: Content Server LiveReports Forum
Content Server: My Support

Marlyn Norris

I tried using that query and get a error message from the live report execution. Here is what I have...

Query:

SELECT * FROM DTree d WHERE CreatedBy = %2 AND START WITH d.DataId = 20709770 CONNECT BY PRIOR DataId = ParentId

Error:

Content Server Error: Error processing request.

[ORA-00936: missing expression - SELECT * FROM DTree d WHERE CreatedBy = :A1 AND START WITH d.DataId = 20709770 CONNECT BY PRIOR DataId = ParentId]

Regards,

Marlyn Norris

appuGmail

the AND in my query is not needed

Ciao,

..... appnair appunair( Livelink Rocks )

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

On Fri, Jan 11, 2019 at 5:06 PM eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

[EXTERNAL] - Re RE All documents created by a user in a specific parent folder Live Report In CS10 Oracle DB Posted bymarlyn.norris@nassco.com (Norris, Marlyn)On 01/11/2019 06:02 PM

I tried using that query and get a error message from the live report execution. Here is what I have...Query:SELECT * FROM DTree d WHERE CreatedBy = %2 AND START WITH d.DataId = 20709770 CONNECT BY PRIOR DataId = ParentIdError:Content Server Error: Error processing request.[ORA-00936: missing expression - SELECT * FROM DTree d WHERE CreatedBy = :A1 AND START WITH d.DataId = 20709770 CONNECT BY PRIOR DataId = ParentId] Regards,Marlyn Norris
[To post a comment, use the normal reply function]
Topic: All documents created by a user in a specific parent folder Live Report In CS10 Oracle DB
Forum: Content Server LiveReports Forum
Content Server: My Support

Marlyn Norris

Krishna,

That worked like a charm my friend. Much appreciatd to both you and Chris for the help on this.

Thanks!

Marlyn Norris