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.