:::: MENU ::::

Posts Categorized / Reporting Services SSRS

  • Oct 03 / 2013
  • 0
dbDigger, Monitoring and Analysis, Reporting Services SSRS, T-SQL Scripts

Scripts for SSRS reports usage analysis

Here are some handy and useful scripts that may be used to analyze the report usage of your SSRS report server. All these are select scripts and are using NOLOCK hint. So feel free to add filters and customize according to your specific requirements.
To get the last Time a Report was generated we have following script. Further filters may be applied if required.

 SELECT DISTINCT C.NAME AS [Report Name]  
   ,MAX(EL.TIMESTART) AS [LAST START TIME]  
 FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK) EL  
 INNER JOIN REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID = C.ITEMID  
 GROUP BY C.NAME  
 ORDER BY C.NAME  

If you want to analyze that how many reports were viewed on a specific day then we have following script. It generates report usage Stats by Date. It is advisable to insert the name of the account that runs SSRS. The reason is that if you have a report that is run off of cache or has subscription, the counts will show here. So if you want to see reports that have been run by users only then filter out the account that runs the SSRS service.

SELECT CONVERT(VARCHAR(25), TIMESTART, 101) AS [SPECIFIC DATE]  
   ,COUNT(*) AS [Gneerated Number]  
 FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK)  
 --WHERE USERNAME NOT IN  
 GROUP BY CONVERT(VARCHAR(25), TIMESTART, 101)  
 ORDER BY CONVERT(VARCHAR(25), TIMESTART, 101) DESC  

Get the report server usage during the hours.

SELECT DATEPART(HOUR, TIMESTART) AS HOUR  
   ,COUNT(*) AS [Gneerated Number]  
 FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK)  
 --WHERE USERNAME NOT IN  
 GROUP BY DATEPART(HOUR, TIMESTART)  
 ORDER BY DATEPART(HOUR, TIMESTART)  

To get the reports generated by users we have following script.

SELECT EL.USERNAME  
   ,C.NAME  
   ,COUNT(1) AS [Gneerated Number]  
 FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK) EL  
 INNER JOIN REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID = C.ITEMID  
 GROUP BY EL.USERNAME  
   ,C.NAME  
 ORDER BY EL.USERNAME  
   ,C.NAME  
  • Sep 24 / 2013
  • 0
dbDigger, Reporting Services SSRS, T-SQL Scripts

Get scheduled job name for specific SSRS report subscription

I was required to rerun some failed SSRS subscriptions. As we know that SSRS subscriptions automatically create scheduled jobs in SQL Server agent. The problem is that job names are not readable and it is some UID. So first of all i got the list of failed subscriptions from execution logs and then i was required to get job name for each failed subscription so that it may be executed manually. For this purpose i found following piece of code and it worked fine for me. In following example i got the job name for a report named MyReport.

SELECT b.NAME AS JobName,e.NAME  ,e.path  ,d.description  ,a.SubscriptionID  
   ,laststatus  ,eventtype  ,LastRunTime  ,date_created  ,date_modified  
 FROM ReportServer.dbo.ReportSchedule a  
 JOIN msdb.dbo.sysjobs b ON a.ScheduleID = b.NAME  
 JOIN ReportServer.dbo.ReportSchedule c ON b.NAME = c.ScheduleID  
 JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID  
 JOIN ReportServer.dbo.CATALOG e ON d.report_oid = e.itemid  
 WHERE e.NAME = 'MyReport'  
 and b.description = 'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.'   

Above piece of code may be found in full context here.

  • Sep 11 / 2013
  • 0
dbDigger, Reporting Services SSRS

SSRS 2008 R2 configuration files

Various SSRS settings can be seen and modified in the provided configuration files. We should backup these files and log any changes made to these. Following is the list of major SSRS 2008 R2 log files along with their path on disk

  • RSReportServer.config: It stores the configuration settings for Report Manager, the Report Server Web service features as well as background processing. Click here to get its details on BOL.
  • RSSrvPolicy.config: It stores the security policies for code access of the server extensions. Click here to read about using Reporting Services security policy files.
  • RSMgrPolicy.config: It stores the security policies for code access of report manager. Click here to read about using Reporting Services security policy files.
  • Web.config for the Report Server Web service: Includes only the ASP.Net related settings.
  • Web.config for Report Manager: Includes only the ASP.Net related settings.
  • ReportingServicesService.exe.config: It stores the configuration settings related to trace levels and logging options for the Report Server service.Click here to get its details on BOL.
  • RSReportDesigner.config: It stores configuration settings for Report Designer. Click here to get its details on BOL.
  • RSPreviewPolicy.config: It stores the code access security policies for the server extensions used during report preview. Click here to read about using Reporting Services security policy files.
  • Registry setting: It stores configuration state and other settings used to uninstall Reporting Services. If you are troubleshooting an installation or configuration problem, you can view these settings to get information about how the report server is configured. However these settings should not be modified directly.
  • Nov 28 / 2008
  • 2
dbDigger, Performance Tunning and Optimization, Reporting Services SSRS

Difference between .RDL and .RDLC

Reports that you create in SQL Server 2005 Reporting Services (saved as .rdl files) can be converted to the client report definition (.rdlc) file format used by the ReportViewer controls. This is called an RDL-to-RDLC conversion.
Reports that you create for ReportViewer controls (saved as .rdlc files) can be converted to the report definition (.rdl) file format used in SQL Server Reporting Services. This is called an RDLC-to-RDL conversion.
.RDL files are for Reporting Services and the .RDLC files are for the VS.NET ReportViewer Component. The “C” in .RDLC simply stands for Client and that is the main difference.
The .RDLC files do not store any query specific information (or in other words, how to get your data).

Consult us to explore the Databases. Contact us