Standard reporting with SSRS

SSRS has been around for quite a long time. It was first released in 2004 as an add-on to SQL Server 2000. Over the years, it has seen many improvements, making it by far the most widely used Microsoft reporting tool.

Despite the multitude of new reporting tools with their fancy and cool features, there is still a huge demand for standard corporate reports, also called canned reports or operational reports, which SSRS is ideal for.

SSRS is a feature-rich, flexible, and scalable reporting platform that can satisfy the needs of everyone from small businesses to fortune 500 companies. The following screenshot shows an SSRS report in the design mode using Visual Studio 2012:

An SSRS project in Visual Studio 2012

SSRS supports dozens of data sources including SQL Server, SQL Azure, SSAS multidimensional models and tabular models, Parallel Data Warehouse, OLE DB, ODBC and, a SharePoint list. It is a powerful report-authoring and management environment that allows the creation of static and parameterized reports. The reports are built using Visual Studio or SQL Server Data Tools for Business Intelligence (SSDT-BI), which provides a drag-and-drop interface as well as wizards that generate a Report Definition Language (RDL) file based on XML. This allows for most of the reports to be built without having to write any code.

There is also a simpler-to-use and more streamlined reporting tool called Report Builder 3.0 that looks and feels more like an Office application than the project-centric development tool SSRS. It is a popular tool that also creates an RDL file that can be used by SSRS. Report Builder has about 80 percent of the features and capabilities of SSRS, so it is geared more toward a person who needs a very easy-to-use tool and is willing to sacrifice some flexibility (nevertheless, the RDL file created in Report Builder can always be opened in SSRS to add any missing functionality).

SSRS offers two modes of installation and operation: Native mode and SharePoint Integrated mode. Native mode provides a standalone report server called Report Manager, which offers report viewing, administration, security, processing, and delivery. SharePoint Integrated mode provides the report server through a SharePoint server and almost all the features that are present in Native mode. However, certain features, such as security and storage, are integrated within SharePoint. Most companies select SharePoint Integrated mode as it provides a unified portal to store and present all documents and reports.

The previously mentioned RDL files are uploaded to the Report Manager or the SharePoint document library. All the reports can be accessed through either of the portals and can be automatically generated and distributed. Users can perform analysis using parameters, filters, drill-down, and drill-through. Reports can be scheduled and distributed via the portal, a file share, e-mail, or a printer, and can be rendered as PDF, Excel, XML, comma delimited text file, various image types (TIFF, BMP, GIF, JPEG, EMF, PNG, WMF), HTML, or Microsoft Word formats. The following screenshot shows an output of an SSRS report in Report Manager:

Running a report in Report Manager