Using Report Building to create reports for SSRS.

SQL Server Reporting Services (SSRS) Report Builder 3.0 is a report creation tool developed by Microsoft. I have only recently started using Report Builder, but have been pleasantly surprised on how quickly I could pick up some of the more advanced features. In this post I will discuss the application’s functionalities that I have found useful and the pros and cons of using Report Builder.

First off, I will give you a brief overview of the different features in the Report Builder workspace. After creating a new report your workspace will look similar to the image above. The first step in creating a report is to add a data source. You can add a data source by right clicking on Data Sources then opening ‘Add Data Source’ in the Report Data section. Selection 1 in the above image shows the Data Source section.

In Report Builder there are shared and embedded data sources.  I strongly recommend to always use shared data sources. Shared data sources can be used by multiple reports; whereas, embedded data sources have the database name and credentials stored in the report. If you need to rename a database or change credentials, using a shared data source you can update it for all reports at once.

After creating your data source you need to create a dataset. sYou do this by right clicking on Data Sets and then selecting ‘Add Data Set’.  Selection 2 in the figure above shows the data set selection. You then complete the wizard and select the data from the data source you want to use in your data set.

After configuring your data source and data set, you can present your data by using one of the data visualization or data regions wizards. Report Builder 3.0 uses the same familiar ribbon interface used by Microsoft Office. The Insert tab of the ribbon interface contains all the different data wizards you can use to group and visualize your data.  Selection 3 in the figure above shows the three basic data regions:

1. Table Wizard helps you display data in a row and column format. A table only grows vertically downward.
2. Matrix Wizard helps you display data in a column/row format just like a table, but a matrix can grow both vertically and horizontally.
3. List Wizard helps you display data in a list.  Unlike the matrix and table wizard, a List supports a free-layout that can include multiple tables or matrices vertically ordered.

After selecting one of the wizards, you are asked to select the data set you wish to use. After which you can drag and drop the different columns of your data set into the wizard. Once you complete the wizard, a table will appear in the workspace with reference to the dataset you have selected. Even though the wizards provide an easy mechanism to present information, they don’t provide me with the necessary customization and control I require. So instead of using a wizard, I prefer to insert a table and populate the fields manually by dragging columns from my dataset into the cells of my table.

Report Builder allows you to directly modify the SQL queries of data sets. This is especially useful when you already have created the necessary query and you just need to plug it into the query designer to retrieve the required tables and columns from your data source.  You can then drag and drop the query results into your table.

Sometimes you have cases in which your report requires the user to input certain parameters before generating the report. You can create those parameters by right clicking on ‘Parameters’ and selecting ‘Add Parameters’. As shown in selection 4 of the above image. Another better method of doing this is to edit the SQL query directly and adding ‘@variable’ to the query where needed. Report Builder automatically detects this and creates a parameter entry for it, you can then use in the report.

Another useful feature of Report Builder is the right hand properties pane. Selection 5 shows the property pane. You can enable this pane by going to the View tab in the ribbons interface and checking the properties check box. The properties pane has saved me a lot of time when authoring reports. This is because it allows me to edit all properties of an object selected directly, instead of right clicking and going through the properties dialogue window.

I will end this blog post by pointing out some of the pros and cons of Report Builder that I have come across while authoring reports:

1. One major positive feature of Report Builder 3.0 in my opinion is the ability to have shared data sources and data sets. This feature drastically saves time and allows multiple reports to share the same data. For example, create shared data sets for all parameters that will be used in multiple reports.
2. A second major positive feature of Report Builder 3.0 in my opinion is the ability to edit and modify the SQL of any data set. This allows for superior control and more advanced data collection.
3. One drawback of Report Builder 3.0 is how Microsoft implements the Query Editor. The query editor makes it difficult to debug your custom SQL query since it automatically rolls back to the last working query when it encounters an error in the current query. Due to the Query Editor doing this you can’t go back and try to fix the current SQL error without retyping it. To avoid this issue, use SQL Management Studio to write your initial queries.