cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shera276 Regular Visitor
Regular Visitor

Report Server - Report Usage Statistics

Hi - PowerBI Service allows you to view report usage via the Admin Portal and in the service.  Is this possible in Report Server.  We are moving to report server and it would be nice to see report usage metrics.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Report Server - Report Usage Statistics

@shera276 The Power BI Report Server sits on a SQL instance (the same as normal SSRS) you have access to all the capabilities and information stored in the database. the view "dbo.ExecutionLog3" will provide you the metrics that you are looking for.


Near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Report Server - Report Usage Statistics

@shera276 The Power BI Report Server sits on a SQL instance (the same as normal SSRS) you have access to all the capabilities and information stored in the database. the view "dbo.ExecutionLog3" will provide you the metrics that you are looking for.


Near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

hernan_russy Frequent Visitor
Frequent Visitor

Re: Report Server - Report Usage Statistics

Hi @shera276: I have the same question. Did you get the required audit info from this view?

Thanks

Super User
Super User

Re: Report Server - Report Usage Statistics

@hernan_russy The report server won't mimc the same metrics that you see in Power BI in that view. It will essentially provide you with who or what process executed the report. I have not tested this completely with Power BI Desktop executions, but my understanding is that this will be tracked in the same fashion.


Near SE WI? Join our PUG Milwaukee Brew City PUG
shera276 Regular Visitor
Regular Visitor

Re: Report Server - Report Usage Statistics

Hi @hernan_russy - Until this is available in Report Server, I created reports based on the following suggestion by another PowerBI users in the community:

 

'The Power BI Report Server sits on a SQL instance (the same as normal SSRS) you have access to all the capabilities and information stored in the database. the view "dbo.ExecutionLog3" will provide you the metrics that you are looking for.'

 

So I connected to the dbo.ExecutionLog3 view from our SQL database and was able to create some usage stats.

Highlighted
DaveW Regular Visitor
Regular Visitor

Re: Report Server - Report Usage Statistics

It would be good to have Usage Statistics function in Report Server

I've created the same process, I pull the data from the log files into a table on a daily basis via a SP

Then I can create reports in SSRS or Power BI by date by user, or even which reports have the longest rendering time

SELECT 
Substring (el2.username,4,20) as Logs_User, 
el2.ReportPath as Logs_ReportPath,
CONVERT(varchar,el2.TimeStart,111) as Logs_Date, 
el2.TimeStart as Logs_TimeStart, 
el2.TimeEnd as Logs_TimeEnd, 
el2.[Status] as Logs_Status

FROM Portal.dbo.ExecutionLog2 el2  
--Portal is the name of my Report Server

where ReportPath not like '/02%' and Status = 'rsSuccess' and ReportPath != 'Unknown'
	and CONVERT(varchar,el2.TimeStart,111) = CONVERT(varchar,getdate()-1,111)
	and username not like 'NT SERVICE%'
	and Format  not like 'E%' 	and ByteCount != 0

hope this helps

 

cheers,

Dave

bjarkigu Visitor
Visitor

Re: Report Server - Report Usage Statistics

Hi DaveW

 

Thanks for your input. We are building a small repository of PBI Report Server and this has been helpful. I am in the process of using your script and merging that together with view of users and their access and all reports. Could you please explain your choice of restrictions in the script. I can see that the Where ByteCount != 0 eliminates about 95% of all rows from the original LOG table. Are these all irrelevant for measuring usage? 

Thanks again, 

Bjarki

DaveW Regular Visitor
Regular Visitor

Re: Report Server - Report Usage Statistics

Hey Bjarki,

good to hear it's helped.  I just opened a PBI report with slicers, before I selected any slicer options I looked at the Log and I could see multiple rows, the only row where ByteCount != 0 there was another field: ItemAction with value: ConceptualSchema

So you could also filter on this.

When I then selected some slicer options in the PBI report and looked at the Log, I could see more rows but all had ByteCount = 0 and ItemAction = QueryData.  So I would either use the ByteCount != 0 or the ItemAction field = ConceptualSchema in your report filter. 

I'd suggest to do some testing with opening various reports with multiple functions and work out the triggers that you need to filter

enjoy... Dave

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 45 members 929 guests
Please welcome our newest community members: