Reports with Microsoft Report Builder and SQL Server Reporting Services (SSRS)

Data is the important essence of today’s modern digital organization.  Understanding data generated by stakeholders help the organization to take important decisions such as sales, marketing, and operational efficiencies. To understand it better consider an example, if one knows customers are coming more from India and very much less from the USA. It will be vital creating a marketing strategy to invest more in the USA, resulting in increasing revenue. Such type of understanding is possible only if modern organizations have the capability to visualize and understand their data in real time.

My previous employers had Microsoft Dynamics CRM system in place which provided reporting capability quite handy. One was able to create advanced search criteria with related records and get much of their information. As CRM systems are becoming more and more viable for startups and mid businesses, they will be able to use such capability to infer their data out of the box.

For solutions which don’t have reporting/advanced search features available out of the box, it’s important to create reports directly from their structured relational database. SQL Server Reporting Services (SSRS) is one such capability framework provided by Microsoft to create reports. Using Report Builder tool integrated with reporting services is like an icing making reports delivery faster.

Report builder provides report capability based on Table/Matrix, Chart, and Map format. Reports can be deployed directly on a web portal. SQL server administrator can define the level of access for each user to view different reports and data based on organization needs.

For reports created in this blog, I am using Northwind open source database. One can download it from the web and carry out their practice. I have created two reports from Northwind:

  1. The chart to show the distribution of customers over different products.
  2. Record of all orders with product information.

For all the SQL enthusiastic below query was executed as part of the report.

SELECT
       [Northwind].[dbo].[Orders].[CustomerID]
      ,[Northwind].[dbo].[Orders].[EmployeeID]
      ,[Northwind].[dbo].[Orders].[OrderDate]
      ,[Northwind].[dbo].[Orders].[RequiredDate]
      ,[Northwind].[dbo].[Orders].[ShippedDate]
      ,[Northwind].[dbo].[Orders].[ShipVia]
      ,[Northwind].[dbo].[Orders].[Freight]
      ,[Northwind].[dbo].[Orders].[ShipName]
      ,[Northwind].[dbo].[Orders].[ShipAddress]
      ,[Northwind].[dbo].[Orders].[ShipCity]
      ,[Northwind].[dbo].[Orders].[ShipRegion]
      ,[Northwind].[dbo].[Orders].[ShipPostalCode]
      ,[Northwind].[dbo].[Orders].[ShipCountry]
      ,[Northwind].[dbo].[Products].[ProductName]
FROM [Northwind].[dbo].[Orders]
INNER JOIN [Northwind].[dbo].[Order Details]
ON Orders.OrderID=[Order Details].OrderID
INNER JOIN [Northwind].[dbo].[Products]
ON Products.ProductID=[Order Details].ProductID

Report Builder provides convenient wizards to manage all the format of data. Once reports are finalized one can directly upload manually or publish automatically to be made available for users. Below is the screenshot of the finished available reports for the users.

 

 

One can directly export available reports in different formats and use them for any submission. These reports are dynamic and not based on design time. Hence these reports can be used anytime to carry out any required operational analysis.

Please find sources for download:

  • Chart report showing distribution of customers over different products: Download Source Code(Link) Download Report (Link)
  • Table report showing a record of all orders with product information: Download Source Code (Link) Download Report (Link)

I consider report builder quite a good report design tool for any business and deploying them in SSRS allows easy access for end users. Give it a try and get in touch for any clarification.

Leave a Reply

Your email address will not be published. Required fields are marked *