Maybe some of you don't know this, but to view Report Definition Language reports (RDL) , you aren't forced to use SQL Server Reporting Services and a report server running on the machine.
With the latest version of SSMS you are able to view any report, just have in mind 2 things when creating your report to be able to use it on SSMS:
- Use an embedded Dataset for your report and point the server name to (local)
- Add the database name to your objects if you want a specific db, otherwise, SSMS will use the current db or the master as default.
You can create RDL reports (among other projects) with the free tool SQL Server Data Tools (SSDT) who is the replacement for the old Business Development Integration Studio(BIDS).
Once you have your report created, it is just matter of importing it to SSMS to use it. I will show you how to do it.
Add the report to SSMS
For this example I am using one of my open source reports for server monitoring.
First, connect to your server using SSMS and right click on the server or database, then select Reports>Custom Reports...
Locate the report file on your windows explorer. Note: If you want another name for the report, rename the file on this point, then select it and click OPEN.
A Warning will appear for any custom report you add, and again I tell you: Always review the source of any custom code before using it to avoid malicious executions against your servers, also test them in a non-prod server before. As advice: trust no one.
If you trust on the source, then ignore the warning and click RUN.
After that, the custom report will appear, note that the connection has been changed to the current server where the report is being executed.
BE CAREFUL: Report runs with the credentials of the user connected to the SSMS at that point, so have this in mind for sensitive information or object permissions.
After that, the report will be available for you to execute it at any time.
To Delete it
Reports are not "imported" to SSMS, so if you want to delete it, just delete the rdl file on the windows explorer, after that if you select it again in SSMS it will prompt to delete it from recent list.
