SQL Query - List most used report

Hi all,

I have a table structure like this:

ReportLogID, UserID, ReportID, ReportName, Viewed

The table stores which users have run which reports in our system.

I would like to run a SQL query that lists the reports (grouped by ReportID) and shows me and the number of times it has been run.

I would also like to choose the start and end date that I want to run the query for (this is the Viewed field).

I can do this in Crystal Reports (where I am comfortable!), but I am not sure what to do in SQL.

Any hep would be great.

Thanks,

Tom
tom_optimumAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
the starter is easy:
 select ReportID, count(*), min(Viewed), max(Viewed)
from yourtable
group by ReportID

Open in new window

you may want more data/columns, in which case you want to read up this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

The below query will let you know that which report run how many times.

SELECT
	t2.Name AS ReportName, ReportID,InstanceName,COUNT(1) counts
	FROM dbo.ExecutionLog t1
    JOIN dbo.Catalog t2
    ON t1.ReportID = t2.ItemID
	GROUP BY t2.Name , ReportID,InstanceName

Open in new window

0
 
tom_optimumAuthor Commented:
Great - thanks.

So, I am using this:

SELECT ReportID, ReportName, count(*), min(Viewed), max(Viewed)
FROM tblReportViewLog
GROUP BY ReportID, ReportName

Open in new window

What would I need to do so I can type in the start and end date instead of min and max?

Thanks,

Tom
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Vikas GargConnect With a Mentor Business Intelligence DeveloperCommented:
Hello,

try this for your local table

SELECT ReportName,Count(ReportID) Counts FROM YourTable
	WHERE Viewed BETWEEN DATE1 AND DATE2
	group by ReportName

Open in new window

0
 
tom_optimumAuthor Commented:
Great - thanks.

Here is what I used int he end
-- Reports run over all time
SELECT ReportID, ReportName, count(*), min(Viewed), max(Viewed)
FROM tblReportViewLog
GROUP BY ReportID, ReportName

-- Reports run between two dates
SELECT ReportID, ReportName, Count(ReportID) Counts FROM tblReportViewLog
WHERE Viewed BETWEEN '2013-09-01' AND '2014-07-29'
GROUP BY ReportID, ReportName

Open in new window

Thanks for you help.

Tom
0
 
tom_optimumAuthor Commented:
Great help guys.

Thanks,

Tom
0
All Courses

From novice to tech pro — start learning today.