What Is Report Builder?

Report Builder is a report creation tool from Microsoft enabling you to develop simple to complex reports and dashboards based on data from a variety of data sources such as SQL Server, Azure, Oracle, DB2, Teradata databases, SAP, XML,ODBC sources.

Report Builder implements the same Report Designer that is provided by SQL Server Reporting Services (SSRS) and the SQL Server Data Tools (SSDT) development tool.

SSDT is a Visual Studio development environment typically used by SSRS report authors who publish reports for user populations - they are likely to be creating many reports and require version control and project based work flow environments.

Report Builder offers the same designer as SSDT, but  allows you to create reports without having to download or learn Visual Studio (SSDT) so is more suited to those who need to develop ad-hoc reports for their own use. That said Report Builder can be used to publish reports to an SSRS Server where other users can access and execute them.

Reports created with SSDT can be opened and modified in Report Builder and reports created in Report Builder can be opened and accessed in SSDT.

The following screen shot shows one of the reports included with the Codeplex sample SSRS project displayed in a Report Builder session.

The report included a Map region with a Matrix region alongside it. You will see that the cursor was hovering over the CA region of the map of USA at the time the screen shot was taken and the tooltip shows the total revenue for California (£6,314,255). This figure is also seen in the table (matrix) to the right.

What Benefits Does Report Builder Offer?

Here are a few of the key benefits:

• Non technical staff can develop reports

• Powerful visuals are enabled through a variety of supported report regions: maps, gauges, indicators, charts

• A library of report items can be created and reused by many: tables, matrices, charts, images

• Reports can be for personal use or published for shared use

In many deployments of Report Builder technical staff will have set up shared data sets and report parts for users to access. This will give users:

• Access to the raw data that they need to report on without the need to learn about the underlying relational database structures and the SQL query language – Shared Data Sets are made available via an SSRS Server  to report writers

• The ability to add impressive looking components to reports without needing to understand how to configure and customise them – Shared Report Parts are made available via an SSRS Server to report writers.

This all means that the report writer can focus on the story being presented by the report by plugging in the relevant components, already bound to the required business data sets.

But of course the report writer can, if they wish:

• Take advantage of the full design capabilities of Report Builder

• Build their own custom datasets if they are familiar with the source data and understand how to write SQL SELECT statements to retrieve the required data

• Create and configure complex charts, gauges, maps, tables, matrices, lists, etc.

How Are Report Builder Reports Shared?

Reports designed using Report Builder can be saved locally or centrally:

• Locally on the author’s local computer and kept as personal reports

• Centrally on a network share for other users, equipped with Report Builder, to access

• Centrally on a SQL Server Reporting Services (SSRS) Server for users with or without Report Builder to access

Making Reports Available to Users Without Report Builder

If a report author wishes to make a report available to a population of users who do not have access to Report Builder that is not a problem if there is an SSRS Server available.

From Report Builder the report author can choose an SSRS site to save the report to. They can then navigate to a folder to file the report under.

Users who have Report Builder will be able to open files from the SSRS site.

Users who do not have Report Builder will be able to access the published reports via a web tool called Report Manager (which is shipped with SSRS) or via Sharepoint if the organisation has integrated SSRS with a SharePoint deployment. Either way, no locally installed software is required on the users computer to access and execute the reports.

The screen shot below shows the SQL Server 2016 SSRS Report Manager:

Report Builder Report Parts

To make it as easy as possible for non technical users to create reports customised to their own needs, Report Parts can be published to an SSRS Server and made available to report authors to drop in to their own reports. These report parts will include all required data sets so the author need know nothing about the underlying data or report region structure.

Report Parts can be published from Report Builder by the original report author:

The Publish Report Parts option then allows you to select which parts of your report you wish to publish:

If a non-technical user wishes to include any of the published parts in their reports all they need to do is insert a chosen Report Part from the Report Parts Gallery. The following example shows the Report Parts button on the far left of the Insert ribbon, and the gallery of available Report Parts on the far right:

The above screen shot shows a map region available in the right hand Report Part Gallery. This has been added to the report by simply selecting it from the gallery.

The report is then immediately available for running.

What Can We Get From Report Builder?

Report Builder is a fully functional Report Writing tool. It enables you to:

• Use the Report Builder ribbon to quickly add items your reports, launch table, chart, and map wizards, and format report data.

• Add data from built-in data providers by using query designers that help you specify which data to include in your report.

• Create and use report parameters and other interactive features that enable your report readers to customize data and vary report presentation.

• Create expressions from built-in fields, collections, operators, and functions.

• Open reports directly from a report server.

• Preview reports that use local or published shared data sources and shared datasets.

• Preview reports in HTML or print format.

• Export reports to other file formats such as Microsoft Excel.

• Save your report and related items to a SharePoint library, a report server, or your local computer.

These are just some of the features available, but when used in combination with SSRS automated report execution, report subscriptions, and embedding into other applications can also be achieved.

If you would like to learn more about the capabilities of Report Builder or SSRS and SSDT then why not emal us at info@ptr.co.uk or take a look at these courses form our Business Intelligence Training Portfolio:

Writing Reports with Microsoft Report Builder SSRS Level 1

Writing Reports with Microsoft Report Builder SSRS Level 2