Self Service BI – Microsoft SSRS End user reporting environment

Introducing here a Microsoft® BI ad hoc reporting tool that enables business users to create reports themselves  easily and quickly, as and when needed

SSRS Environment: SQL Server Reporting Services (SSRS) is a server-based report generation software system catered by Microsoft. It can be used to prepare and deliver a variety of interactive and printed reports. It is managed through a web interface. Reporting services features a web services interface to support the development of custom reporting applications, which can be administered by the end user with a

very less technical knowledge. SSRS comes as part of SQL Server and the latest release was with SQL Server 2008 R2.

Architecture: Reports are defined in XML based Report Definition Language (RDL). The standard designer tool for SSRS is Microsoft Visual Studio with Business Intelligence Projects plug in. Reports can be generated in different formats like Excel, DOC, PDF, CSV, XML, various image formats and web archive. Reports can be viewed or embedded using ASP.NET report viewer web control and report viewer windows form control.

Security: SSRS Security is role based and can be assigned on an individual item like report, data source, folder etc. Security roles and rights can be inherited and overloaded.

Report Development by end users

Why end users’ does wants to create report?Information workers throughout an organization need access to business data for timely and effective decision making. With a wide range of skills and expertise, these business users need an intuitive yet powerful tool to explore and find answers to critical questions without deep technical knowledge of corporate data sources. Ad-hoc reporting is used to describe dynamic report creation by the users. Ad hoc Reports are very beneficial in scenarios where large amount of data is being displayed in the report; An Ad hoc report would contain desired as well as undesired data. In such scenarios, the users would like to have control over the choice of data that is to be displayed in the report. Depending on the user’s choice, the report is dynamically generated to display only the desired data.

Report Designer is a part of Business Intelligence Development Studio (BIDS), you will get all the sophisticated features for developing simple as well as complex reports. This requires developer skills and the work can be classified purely as software development. BIDS is a daunting environment for non-developers and it requires a considerable investment on the part of the user in order to become proficient in its use. Also visual studio development studio installation is required for developing reports.

Report Builder, a component of Microsoft SQL Server Reporting Services, allows business

users to create their own reports using a user-friendly model of their data. Report Builder leverages the

Reporting Services platform to bring ad hoc reporting to all end users. It is targeted at information workers and non-developers This group of report developers must not be technical people. It provides easy to use drag and drop functionality in a Microsoft Office-like environment and shields users from the complexity of the underlying data structure by enabling them to interact with report models. A report model is an abstraction layer that sits over the top of a data source and exposes the underlying data using business-oriented language that is more meaningful to the end user.

Report builder users won’t interact with any data sources and data structure, they are provided with enough data structure to answer questions like ‘How many employees are there over 50 years old’

Report Builder features

# A ad-hoc report design tool for Microsoft SQL Server Reporting Services
# Targeted at business users who want to find and share answers to interesting questions
#Driven from a business model of the data, so users do not need to understand the underlying data structures
# Reports built with report templates (textboxes, tables, matrices, charts)
# Finished reports can be saved on the server

Microsoft Report Builder Architecture

Report Builder reports enable users to interactively drill and explore data related to the content of their report leverage all of the viewing and management features of the reporting platform.

• Report Builder reports are published in the Report Definition Language.

• Reports are managed, secured and delivered by the same methods and APIs.

• Report Builder reports can be edited in the Report Designer to take advantage of Report designer’s full capabilities such as nested data regions and expanded expression support,

• Report Builder can be launched from third party applications for integrated reporting.

Report Builder 3.0

This is the release comes with the latest SQL Server 2008 R2. Additional visualizations including maps, sparklines and databars which can help produce new insights well beyond what can be achieved with standard tables and charts. The Report Part Gallery is also included in this release – taking self-service reporting to new heights by enabling users to re-use existing report parts as building blocks for creating new reports in a matter of minutes with a “grab and go” experience. Additionally, users will experience significant performance improvements with enhancements to the ability to use Report Builder in server mode.

How to launch? : Internet explorer -> http://servername/reports . Click on the “Report Builder” button.

Behind the scene – SSRS Report Model

Report Builder reports are built from models that provide a business layer on top of the underlying data source. Report models include:

• Business names for database fields and tables

• Defined relationships between items in the data source

• Logical groupings of model items

The model aids users in navigating and selecting the desired information from the underlying data source. The Report Builder then uses the report model definitions to automatically generate the source query for retrieving the requested data. In addition, model elements can be secured so that users only see data they have permissions to see.


5 thoughts on “Self Service BI – Microsoft SSRS End user reporting environment

  1. Great article!! Do you know of any tool that works with SSRS for adhoc reporting but the end user doesn’t need to download the report builder on their machine? We are llloking for a tool which can work on the web with the application and let the end user designt he adhoc reports there.


    • Yes, if you use SQL Server 2008 SSRS, the report builder that comes with it doesnt require any exclusive download and install. When user click on the button, it will load the app in the browser itself. hope this helps

  2. Crystal Migration Services has mobilized SSRS and made it super easy to use.
    1. Real Ad Hoc, very easy to create and modify reports.
    2. Real mobile, you can create and view SSRS from your mobile devices such as IPAD, IPhone, Android and Surface.
    3. Real SSRS, the reports created are ready to be uploaded to Report Server or SharePoint.
    4. Backend DB can be anything supported by .NET, not limited to SQL Server.


Comment please...

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s