Deploy, Customize and Schedule Spotlight Reports in SSRS

Spotlight reports are designed in SQL Server Report Definition files (RDL) and stored in the local installation folder of Spotlight client application making them accessible to Spotlight users. By default this path in Spotlight is C:\Program Files (x86)\Quest Software\Spotlight on SQL Server\Plug-ins\Trending\SSRS. Not only Spotlight provides users with the raw RDL files but also provides the pre-built Visual Studio Project file that includes are report files. This allows to load the project file including all Spotlight reports in VS at one time.

Below I’m outlining the steps on how to use Spotlight VS Project file to deploy, schedule and customize existing reports, and add new reports.

A few requirements before we begin:

1. SQL Server Reporting Services 2008 and above is supported.
2. Report Server Administrator privileges.

Deploy Spotlight Reports to SQL Server Reporting Services:

1. Open the Business Intelligence Development in Visual Studio.
2. Select File | Open | Project / Solution option.
3. Select the project file SoSSE Reports.rptproj in the Spotlight Client installation folder: C:\Program Files (x86)\Quest Software\Spotlight on SQL Server Plug-ins\Trending\SSRS folder.
4. Right click SoSSE Reports | Properties entity.
5. Change the Target Server URL to your reporting server.

To confirm the URL string of server check Reporting Services Configuration Manager:

6. Double click Shared Data Sources file | Spotlight Statistics Repository. Edit the Connection String and update the data source and database name if it has changed from the default.
   
7. Right click Spotlight Statistics Repository Reports | Deploy.

8. View Spotlight reports in Report Manager URL:

9. Use Subscribe option to schedule and email reports:

Customize the default Reports:

1. The definition (RDL) files and the project file SoSSE Reports.rptproj are in the Spotlight Client installation folder in the …\Plug-ins\Trending\SSRS folder.
2. Copy the RDL files and project file to a folder outside of the Spotlight Client installation folder.
3. Rename the copied RDL reports to a new filename so clarify it’s the customized version.
4. Use the Business Intelligence Development to open and customize the report.
   For example, I’ve renamed the ‘All SQL Agent Jobs Report’ and customized the report to show top 8 jobs instead of the default 6:

5. Deploy the customized report to SQL Server Reporting Services.

Result of the default report:

Result of the customized report:

Create new reports:

Users can add new reports to the project SoSSE Reports.rptproj and then deploy those reports to SQL Server Reporting Services.

1. Right-click Reports | Add New Report option. Create report based on Spotlight Statistics Repository data source.
   
2. New report name will show under Reports. Right click Spotlight Statistics Repository Reports | Deploy to deploy new list of reports.

3. See new report in SSRS:

4. Copy RDL file of new report from C:\Program Files (x86)\Quest Software\Spotlight on SQL Server\Plug-ins\Trending\SSRS to C:\Program Files (x86)\Quest Software\Spotlight on SQL Server\Plug-ins\Trending\Reports. View new report in Spotlight client:

See Spotlight Reports help page for more details.

Download the latest version of Spotlight from here.