List of all document,versions (SQL 2008 DB)

Hi :

I have created this report to list all documents,version created. I am trying to join it in such a way that instead ofcreatedby column i can display the user login id,name etc. So basically i am trying to get a system usage report which will have ssay username, officelocation, new folders created, new docs uploaded etc as the result to see how users are utilizing the system.Any help please?

SELECT a . name , a . dataid , a . subtype , a . CreatedBy , a . createdate , a . modifydate , b . vercdate

FROM dtree a , dversdata b

WHERE a . dataid = b . docid AND a . versionnum = b . version AND a . dataid in

( SELECT dataid FROM dtree WHERE subtype = 144 ) Order by a . name

Find more posts tagged with

Comments

Mukesh_Giri

Hi:

I createdt this report to have listed all the documents created in a time-period and also i was able to capture the user audit info.

SELECT a . Value1 as "Location" , a . AuditStr as "Audit Action" ,

c . FirstName + ', ' + c . LastName "User Name" , c . OfficeLocation

as "User office Location" FROM DAuditNew a , Dtree b , KUAF c

where a . dataid = b . dataid and a . PerformerID = c . ID and AuditStr = 'create' and b . SubType = 144 and AuditDate >= '2012-03-24 23:59:28.177' and AuditDate <= '2012-04-24 23:59:28.177'

Now i wish to include a column to have count of documents created by each user in the report, is that feasbile??

Also when i add this SQL query to live-report , live report gives me this error , i have selected "Auto Live-Report" already

Livelink Error: Error processing request.

[[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'a'. - SELECT a.Value1 as "Location",a.AuditStr as "Audit Action",c.FirstName + ', ' + c.LastName "User Name", c.OfficeLocation as "User office Location" FROM DAuditNew a, Dtree b ,KUAF cwhere a.dataid=b.dataid and a.PerformerID = c.ID and AuditStr='create' and b.SubType=144 and AuditDate >= '2012-03-24 23:59:28.177' and AuditDate <= '2012-04-24 23:59:28.177']

Appu_Nair

Mukesh,

The query and livereport may be solvable by re-adjusting the real estate but your data may not be correctly aligned for a Group By Clause that is because you have the 'Location' info.Look at some fictitious data for you to see.I left some columns out for clarity.I will show it for 2 users 'Appu' and 'Mukesh' creating 4 items in different locations

Location AuditStr Name

Enterprise:Appu Create Appu

Enterprise:Appu:sub Create Appu

Enterprise:Mukesh Create Mukesh

Enterrprise:Mukesh:sub Create Mukesh

Now with this arrangement the Location is different for all events so even if you made it work it will say count=1 for the 4 rows.If location remains constant it will add thru a group by query.So at best what a group by for this data can show is for a fixed location a count

so something like this would work

SELECT count(a.auditstr),c . FirstName ,a . AuditStr FROM DAuditNew a , Dtree b , KUAF c
where a . dataid = b . dataid and a . PerformerID = c . ID
and AuditStr = 'create' and b. SubType = 144 and
AuditDate >= '2012-03-24 23:59:28.177' and AuditDate <= '2012-04-24 23:59:28.177'
group by c . FirstName,a . AuditStr

The trick for a group by is you want something that is identical on a column so that you can add it up and it changes for some others like in your case the user ,in this case a.auditstr fits that bill

This would not work

SELECT count(a.auditstr),c . FirstName ,a . AuditStr ,a . Value1 FROM DAuditNew a , Dtree b , KUAF c
where a . dataid = b . dataid and a . PerformerID = c . ID
and AuditStr = 'create' and b. SubType = 144 and
AuditDate >= '2012-03-24 23:59:28.177' and AuditDate <= '2012-04-24 23:59:28.177'
group by c . FirstName,a . AuditStr,a . Value1

if you cast that Value1 from NTEXT into something like a string it may work but everytime the location changes for the user the aggregate would change as well or would it I am confused myself...

Anything better you may need Lindsay Davies or people that have a db brain on this.I could be totally wrong

as well.

The cut and paste had the symbol !(bang) in my query window I would type everything giving enough space in the livereport window and possibly the auidtedate could be userinput 1 and 2

Here's what worked in my sqlserver when I cast the value1

SELECT count(a.auditstr),c . FirstName ,a . AuditStr ,convert(nvarchar(max),a . Value1)
FROM DAuditNew a , Dtree b , KUAF c
where a . dataid = b . dataid and a . PerformerID = c . ID
and AuditStr = 'create' and b. SubType = 144 and
AuditDate >= '2012-03-24 23:59:28.177' and AuditDate <= '2012-04-24 23:59:28.177'
group by c . FirstName,a . AuditStr,convert(nvarchar(max),a . Value1)

It printed all the 5 values which came out as 3 and 2 when I was testing with my above query.

That is what I am thinking

Mukesh_Giri

Hey Appu:

Thanks for your time to help on this, thanks for clarifying the concepts.

Here is waht i did and all works in terms of the count and live-report format.

select COUNT(DAuditNew.DataID) "Documents Action Count", KUAF.FirstName + ', ' + KUAF.LastName "User Name",

KUAF.OfficeLocation,DAuditNew.AuditStr from DAuditNew, DTree, KUAF where DAuditNew.DataID = DTree.DataID and DAuditNew.PerformerID = KUAF.ID

and DTree.SubType = 144 AND AuditDate >= %1 and AuditDate <= %2 group by KUAF.FirstName, KUAF.LastName, KUAF.OfficeLocation, DAuditNew.AuditStr

parameters as user input 1&2 and date as the two prompts.

Mukesh