In this article we will take a look at the functionality provided by the Report Builder Client. The Report Builder Client is a "self-service" tool to provide capability for building ad-hoc reports by business users, with automatic interactive drilldown capabilities.
Business users create reports based on business terms and semantics which they are familiar with - they use Models which are business definitions over the raw data sources.
(This article is intended at providing an overview. With an abundance of new technologies, it can be difficult to keep pace with every item in different areas. Given that, having a possible solution in the back of one's mind helps with solutions in real life scenarios and that's the intention of this article.)
SQL Server 2005 includes SQL Server Reporting Services as an enterprise level Reporting System.
SQL Report Designer allows developers to create and publish report layouts based on raw data sources.
SQL Reporting Services provides the Report Builder Client which is targeted towards "power users" or "business users". This group would include users who understand the business and have a requirement to build their own ad-hoc reports based on a business model of the data. This set of users is different from the developer or administrator user who typically develops reports, generally for use by other end users, directly using the Report Designer in the SQL Reporting Server, to setup reports based on the raw data sources.
The Report Builder Client is available as a "click-once" Windows Forms Application which is typically deployed from Report Server. When a business user accesses this Report Builder Client, it gets deployed via the browser to the user's workstation.
Features
Models
- The Report Builder allows design of report layouts based on Models instead of direct data sources.
Models provide business names for tables (referred to as Entities) and fields and include defined relationships. Models also provide a logical grouping of items included within the model. In other words, Model could be defined as a business representation of tables.
- Models can be designed specifically or auto-generated using the Model Designer.
- Models contain Entities (analogous to Tables), Fields and Roles (relationships between entities)
- The user of the Report Builder Client can select an Entity to base the report on and if any fields are selected from a model, it becomes the Primary Entity.
Drill-Down Reporting
- Interactive drill-down based on the defined relationships is automatically available in these reports.
- The Model Designer allows the configuration of Drill-through reports, Query Generation and Security.
- Relationships between entities are referred to as "Roles".
Report Layout
The Report Builder allows selection of various layouts, such as Table, Chart, Matrix (cross-tab).
The Report Builder also allows creation of calculated fields in the Model Designer as well as the Report Builder. Calculated Fields created in the Report Builder are available only within that report layout whereas calculated fields created within the Model Designer are available to all reports using that Model.
Finished Report Layouts
Report Layouts created using the Report Builder are saved in Report Definition Language (.RDL) format.
The Report Builder cannot import reports created using Report Designer. However, reports created using the Report Builder can be manipulated using the Report Designer.
NOTE: The Report Builder client requires .Net 2.0 installed and prompts for it's installation, if it is not available.
Conclusion
The Report Builder is a tool to empower the business users in the enterprise allowing them to take care of their own reporting requirements. The business users are provided a business view of the underlying data structure and the Report Builder facilitates the creation of reports based on Models and Roles.
Disclaimer
This article is for purely educational purposes and is a compilation of notes, material and understanding on this subject. Any resemblance to other material is an un-intentional coincidence and should not be misconstrued as malicious, slanderous, or any anything else hereof.