Personal Workspace

Hi Forum

I am looking for some help with returning both a count of items which i can do but also the datasize of the objects in the users personal workspace.

I am using the following to return the count

Select -1*d.OwnerID, d.Name,
(Select COUNT(DataID)-1 From DTree Where OwnerID = -1*d.UserID) "Count"
From DTree d, KUAF k
Where SubType = 142
And k.ID = d.UserID
And k.Deleted = 0
Order By d.Name

This works fine for all users but i am having major issues with getting a sum of the datasize objects where i get from DVersData i presume or is there another place this is stored ?

This SQL also works and does bring back count and datasize except it is counting all versions the datasize is ok with this one my problem is i cannot get it to just count most recent versions from DTree.

Select -1*dt.OWNERID "Workspace ID", k.Name,
Count(dt.DataID) "Number of Items",(SUM(dv.DataSize)/1024) "Space Used (KB)"
From KUAF k, DVersData dv, DTree dt
Where k.ID = dv.Owner
And dt.OwnerID = -1*k.ID
And dt.DataID = dv.DocID
And dt.SubType <> 298
Group By k.Name, dt.OwnerID
Order By "Space Used (KB)" DESC

Any suggestions would be greatly appreciated...

Regards, Mike.

Find more posts tagged with

Comments

praveen_nagarajan

Hello Mike,

Can you this query a try and let me if this helps.

Select -1*dt.OWNERID "Workspace ID", k.Name,

Count(dt.DataID) "! Number of Items",(SUM(dv.DataSize)/1024) "Space Used (KB)"

From KUAF k, DVersData dv, DTree dt

Where k.ID = dv.Owner

And dt.OwnerID = -1*k.ID

And dt.DataID = dv.DocID

AND dt.VersionNum = dv.Version

And dt.SubType <> 298

Group By k.Name, dt.OwnerID

Order By "Space Used (KB)" DESC

Best Regards

Praveen

On Thu, Aug 9, 2012 at 7:07 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Personal Workspace Posted bymichael.fennell@fsa.gov.uk (Fennell, Michael)On 08-09-2012 09:31

Hi Forum I am looking for some help with returning both a count of items which i can do but also the datasize of the objects in the users personal workspace. I am using the following to return the count Select -1*d.OwnerID, d.Name,
(Select COUNT(DataID)-1 From DTree Where OwnerID = -1*d.UserID) "Count"
From DTree d, KUAF k
Where SubType = 142
And k.ID = d.UserID
And k.Deleted = 0
Order By d.Name This works fine for all users but i am having major issues with getting a sum of the datasize objects where i get from DVersData i presume or is there another place this is stored ? This SQL also works and does bring back count and datasize except it is counting all versions the datasize is ok with this one my problem is i cannot get it to just count most recent versions from DTree. Select -1*dt.OWNERID "Workspace ID", k.Name,
Count(dt.DataID) "! Number of Items",(SUM(dv.DataSize)/1024) "Space Used (KB)"
From KUAF k, DVersData dv, DTree dt
Where k.ID = dv.Owner
And dt.OwnerID = -1*k.ID
And dt.DataID = dv.DocID
And dt.SubType <> 298
Group By k.Name, dt.OwnerID
Order By "Space Used (KB)" DESC Any suggestions would be greatly appreciated... Regards, Mike.
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

praveen_nagarajan

Hello Mike,

Can you this query a try and let me if this helps.

Select -1*dt.OWNERID "Workspace ID", k.Name,

Count(dt.DataID) "! Number of Items",(SUM(dv.DataSize)/1024) "Space Used (KB)"

From KUAF k, DVersData dv, DTree dt

Where k.ID = dv.Owner

And dt.OwnerID = -1*k.ID

And dt.DataID = dv.DocID

AND dt.VersionNum = dv.Version

And dt.SubType <> 298

Group By k.Name, dt.OwnerID

Order By "Space Used (KB)" DESC

Best Regards

Praveen

On Thu, Aug 9, 2012 at 7:07 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Personal Workspace Posted bymichael.fennell@fsa.gov.uk (Fennell, Michael)On 08-09-2012 09:31

Hi Forum I am looking for some help with returning both a count of items which i can do but also the datasize of the objects in the users personal workspace. I am using the following to return the count Select -1*d.OwnerID, d.Name,
(Select COUNT(DataID)-1 From DTree Where OwnerID = -1*d.UserID) "Count"
From DTree d, KUAF k
Where SubType = 142
And k.ID = d.UserID
And k.Deleted = 0
Order By d.Name This works fine for all users but i am having major issues with getting a sum of the datasize objects where i get from DVersData i presume or is there another place this is stored ? This SQL also works and does bring back count and datasize except it is counting all versions the datasize is ok with this one my problem is i cannot get it to just count most recent versions from DTree. Select -1*dt.OWNERID "Workspace ID", k.Name,
Count(dt.DataID) "! Number of Items",(SUM(dv.DataSize)/1024) "Space Used (KB)"
From KUAF k, DVersData dv, DTree dt
Where k.ID = dv.Owner
And dt.OwnerID = -1*k.ID
And dt.DataID = dv.DocID
And dt.SubType <> 298
Group By k.Name, dt.OwnerID
Order By "Space Used (KB)" DESC Any suggestions would be greatly appreciated... Regards, Mike.
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Michael_Fennell

Hi Praveen

Ok thanks i will give it a try i see what you done and it kind of dawned on me that i could use the dtree version number to tie it to just the version in DVersData, only problem with this is it will only bring back the count for document and not things like shortcuts etc as if it does not have a version then it will not have an entry in DversData.

I might do this via live/web reports as i can just call sub reports from the main one to get the information that way i can just do a count of the workspace properly and also a datasize count.

Regards , Mike.

Manoharan_Venkidusamy

--Manoharan venkidusamy(This mail was sent from my android phone)"eLink Entry: Content Server LiveReports Forum" <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Personal Workspace

Posted by praveen@supaisystems.com (nagarajan, praveen) On 08-10-2012 08:38

Hello Mike,

Can you this query a try and let me if this helps.

Select -1*dt.OWNERID "Workspace ID", k.Name,

Count(dt.DataID) "! Number of Items",(SUM(dv.DataSize)/1024) "Space Used (KB)"

From KUAF k, DVersData dv, DTree dt

Where k.ID = dv.Owner

And dt.OwnerID = -1*k.ID

And dt.DataID = dv.DocID

AND dt.VersionNum = dv.Version

And dt.SubType <> 298

Group By k.Name, dt.OwnerID

Order By "Space Used (KB)" DESC

Best Regards

Praveen

On Thu, Aug 9, 2012 at 7:07 PM, eLink Entry: Content Server LiveReports Forum<livereportsdiscussion@elinkkc.opentext.com> wrote:

Personal Workspace Posted by michael.fennell@fsa.gov.uk (Fennell, Michael) On 08-09-2012 09:31

Hi ForumI am looking for some help with returning both a count of items which i can do but also the datasize of the objects in the users personal workspace.I am using the following to return the countSelect -1*d.OwnerID, d.Name,
(Select COUNT(DataID)-1 From DTree Where OwnerID = -1*d.UserID) "Count"
From DTree d, KUAF k
Where SubType = 142
And k.ID = d.UserID
And k.Deleted = 0
Order By d.NameThis works fine for all users but i am having major issues with getting a sum of the datasize objects where i get from DVersData i presume or is there another place this is stored ?This SQL also works and does bring back count and datasize except it is counting all versions the datasize is ok with this one my problem is i cannot get it to just count most recent versions from DTree.Select -1*dt.OWNERID "Workspace ID", k.Name,
Count(dt.DataID) "! Number of Items",(SUM(dv.DataSize)/1024) "Space Used (KB)"
From KUAF k, DVersData dv, DTree dt
Where k.ID = dv.Owner
And dt.OwnerID = -1*k.ID
And dt.DataID = dv.DocID
And dt.SubType <> 298
Group By k.Name, dt.OwnerID
Order By "Space Used (KB)" DESCAny suggestions would be greatly appreciated...Regards, Mike.
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Personal Workspace
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

The contents of this e-mail and any attachment(s) may contain confidential or privileged information for the intended recipient(s). Unintended recipients are prohibited from taking action on the basis of information in this e-mail and using or disseminating the information, and must notify the sender and delete it from their system. L&T Infotech will not accept responsibility or liability for the accuracy or completeness of, or the presence of any virus or disabling code in this e-mail"