ReportServer Forum

zach.mathew

Member

Registered: 2015-04-17

Trying to generate report statistics. Is this SQL query correct ?

I'm looking to generate some statistics for the instance of ReportServer we're using. Using version 3.0.6

We're trying to get a list of most run reports within a date range.

select rex.value reportid, rex.reportname, count(1) 
  from (
		select r.name_field reportname, r.id r_id, e.action, e.date_field , e.user_id userid, p.key_field, p.value
		  FROM rs_audit_log_entry e
		 INNER JOIN rs_audit_log_property p
		    ON e.entity_id = p.log_entry_id 
		   and e.date_field >= $P{startdate}
           and e.date_field < to_date($P{enddate} , 'YYYY-MM-DD') + interval '1 day'
		  LEFT OUTER JOIN (
		       select distinct id, name_field 
		         from rs_report_a rra 
		        where name_field is not null order by id) r
		    ON cast(r.id AS varchar(255)) = p.value    
		 where e.action = 'END_REPORT_EXECUTION' 
		   and p.key_field = 'report_id'
		   --and p.value = '4868256'
   	    ) rex
  group by 1, 2
  order by 3 desc

Thanks

Zach

adrian1703

Moderator

Registered: 2021-10-29

Re: Trying to generate report statistics. Is this SQL query correct ?

select rex.value reportid, rex.reportname, count(1) 
  from (
		select r.name_field reportname, r.id r_id, e.action, e.date_field , e.user_id userid, p.key_field, p.value
		  FROM rs_audit_log_entry e
		 INNER JOIN rs_audit_log_property p
		    ON e.entity_id = p.log_entry_id 
		   and e.date_field >= '2020-04-01'::date
           and e.date_field < '2026-04-01'::date
		  LEFT OUTER JOIN (
		       select distinct id, name_field 
		         from rs_report_a rra 
		        where name_field is not null order by id) r
		    ON cast(r.id AS varchar(255)) = p.value    
		 where e.action = 'END_REPORT_EXECUTION' 
		   and p.key_field = 'report_id'
		   --and p.value = '4868256'
   	    ) rex
  group by 1, 2
  order by 3 desc

| Report ID | Report Name                | Count |
| --------- | -------------------------- | ----- |
| 74770     | Top Employees - Dashboard  | 30    |
| 74724     | T_AGG_CUSTOMER - Basis     | 27    |
| 61892     | variante                   | 20    |
| 74910     | CustomerCreditLine         | 15    |
| 75004     | Store Sales                | 15    |
| 200       | Audit Logs                 | 8     |

This sql does work on my postgresdb (schema 4.6.3). So your sql seems to be correct.

Do you want to use this to create a dynamic list report ?

If so here are a couple of thing to be aware of.

* the sql query is gonna be wrapped by reportserver meaning that orderby will likely not work and needs to be configured inside the report.
* using the date parameter can be confusing and is not intuitive - i suggest you to use a text parameter and cast it.

If you have more specific questions feel free to ask and include more information about your usecase.

Kind regards