In a business intelligence solution, we often need to generate reports that showcase data in different ways. In most cases, it is not possible to standardize all reporting needs beforehand. This means that users need the ability to create new reports on demand, with different parameters and filters. These are known as freeform report scenarios which let you design your report layout before actually executing it with the final dataset. This blog provides an overview of freeform reporting and its benefits with two specific SSRS examples.
What is Freeform Reporting?
A freeform report is a new feature in SQL Server Reporting Services. Freeform report scenarios are designed using a visual designer, just like any other report. The difference is that the report layout is not executed, but rather saved as a report design. This design is then used to generate reports on demand, with different parameters and filters. Freeform report scenarios are a great solution if you need to create a standardized report but also want to allow users to modify it, as they would with a word processor.
Steps to Create Freeform Report
In this section, we’ll walk through the process of creating a freeform report with SSRS. There are three major steps to creating a freeform report: Create the report layout, define parameters, define filters As mentioned earlier, freeform reports are created like other reports – you use the Report Designer to create the report layout. These steps are illustrated below:
- Define a Dataset for your report.
- Go to Report Properties set paper size : Letter and margins : 0.25 in and then draw table
- Set header and footer properties:
a. Header Row Properties -> Keep Group After : After and Repeat on new page – true
b. Footer Row Properties -> Keep Group After : Before and Repeat on new page – true - Insert a “Table” control to the report. This iterates the rows in your Dataset.
- Remove the header row.
- Remove the two default columns from the table leaving a single row with a single column.
- Expand the table to the width of your layout and make it as tall as you will need for your “free form” layout.
- Remove the default TextBox inside the table cell.
- Insert a “Rectangle” control into the empty table cell. It will by default “dock” to match the width/height of the table cell.
Create your desired layout by dragging the fields from your DataSet (TextBoxes, etc) into the Rectangle.
Once you have the report layout ready, you can define parameters and filters, enabling users to create different report variations. When the report parameters and filters have been defined, you can create the actual reports.
Testing Freeform Report
Once the report has been designed, you can test different scenarios to see what variations you can get. The easiest scenario to test is to change the filter values and see how the report changes. For example, change the filter on the Region field and see how the report changes. You can also change the value of other fields to trigger a different report layout.
Conclusion
Freeform reporting is a great tool for business users who want to create their own reports. These reports created using a visual designer that is familiar to most users. Using SSRS (SQL Server Reporting Services) or the ReportViewer control in Visual Studio developers can create freeform reports from scratch. You can set the report layout and define parameters and filters that users can use to generate the report on demand.