Livereport to produce list of most popular documents

Hi all,

I'm wondering if anyone has a LR that will output the number of Fetches/Downloads performed by unique users in a give timeframe. So the output would be similar to:

DOC NAME	Hits in October	Hits in November	Hits in December
Doc1	24	18	2
Doc2	12	16	44
Doc3	16	11	54

All three documents above have the same parent folder.

I have the SQL to pull the documents: select dataid from dtree start with dataid=STARTWITH connect by prior dataid = parentid

And I have one that will provide hits on a per user basis: select name, count(*) "number of hits" from dauditnew,kuaf where id=performerid and (auditstr='Fetch') and dataid in (select dataid
from dtree start with dataid in (39620992) connect by prior dataid=parentid) group by name order by count(*)desc

But I'm looking for something that will break it down by document.

Thanks!

Matt

Find more posts tagged with

Comments

Rinita_Pranaykanti

Hi Matt,

Using Pivot in your SQL you can achieve a bit of what you want.

An sample SQL for you: Please note that the column for months can't be dynamically adjusted as per you date range selection. So maybe you can put all 12 months and for the month outside the range the NULL value is shown.

SELECT *

FROM

(select dtree.name,datename(mm,auditdate)'month',1 'hit'

from dbo.dtree,dbo.dauditnew

where dtree.dataid = dauditnew.dataid

and auditstr='Fetch'

and auditdate >= %1

and auditdate < %2

and dtree.dataid in (select dataid from dbo.dtree where parentid = %3)

and performerid = %4) ps

PIVOT

(

SUM (hit) FOR [month] IN

( June,July,August,September,October,November,December)

) AS pvt

If you dont want the tabular looking result

select dtree.name,datename(mm,auditdate)'month',count(*)