November 16, 2011 16:00 by
ScottIt is important to understand the reporting options available in SQL Server 2008 R2 to provide an appropriate report layout to meet the business requirements and needs of our end users. This post briefly discusses about different report layout types available and explains in detail the steps to create a Tabular Report. It also explains how to sorting and drilldown features to your report.
Different Available Report Layouts are
- Tabular Reports – these reports are defined using table data and organized in to rows and columns. The columns are typically fields in the table. Reporting detail rows can be grouped on various fields, each group can have header, footer , breaks and subtotals.
- Matrix Reports – To summarize the date for analysis we can use the Matrix Reports. It is useful for viewing the aggregated values with two different hierarchies(example time and geography).
- List Reports – List report consist of single rectangular area that repeats for every record or group value in the dataset.
- Chart Reports – these reports provide a visual context for different kinds of data. Some times complex information can be analyzed with very little explanation.
- Composite Reports – You can combine the reports that we discussed above for compelling the advanced reports.
Creating Tabular Reports
1. Open the Report Builder 3.0 , select the Insert Tab and click Table on the ribbon.
As we have not created a data set and data source, it will prompt you to add these objects. Use the DataSource Properties window to enter a name for the new data source as shown below
2. Enter the following query in query designer
1: Select CalendarYear,SalesTerritoryRegion, SalesAmount
2: FROM
3: FactResellerSales as F INNER JOIN DimDate as D ON
4: F.OrderDateKey = D.DateKey INNER JOIN DimSalesTerritory as ST
5: ON F.SalesTerritoryKey = ST.SalesTerritoryKey
6: order by CalendarYear , SalesTerritoryRegion
I am using the AdventureWorksR2 database to create this report.
3. Defining Table Groups – You can drop the database fields to zones to define groups located at the bottom of the designer pane.
In this example we are grouping the rows of this table based on CalendarYear and SalesTerritoryRegion Fileds. To add the CalendarYear field as a group above the detail row, drag and drop this field from the Data Window.
4. Drag the required fields to report designer as shown in the following window. As SalesAmount field is a currency type value and it should be formatted to show the values. To do so right click on the SalesAmount textbox and then choose properties
5. To look at the report that we built so far, you can click the run button in Report builder. Now the report is rendered as shown below
6. Switch back to the design view to add totals to the end of a group with same result. Right-click on the CalendarYear row, and choose Insert Row—>Inside Group – Below
The new row will be added below the group values. Hover the mouse over the new cell below the SalesAmount field and click on the field list icon as shown below
7. To see the changes in report, click on the run button to preview the report
8. Adding Sorting Feature to report – Any group can be sorted by any order using combination of data fields and expressions. For Example to set the sort order for the SalesTerritoryRegion Group click the group name under Row groups and select group properties you will see the following window
You can see the preview result in the following window
Creating Drill Down Reports – All report items and groups have a visibility property that can be set either permanently or conditionally. Common use is to create drill-down reports , where headers are used expand and collapse. Typically (+) or (-) sign will be displayed next to the column header.
To create a drilldown in this example, select CalendarYear Group from the lower part of the designer and click on group properties option then you should be able to get the following dialogue box
Set the display option to hide and select relevant textbox for the CalendarYear group by checking the Display toggle option. Click Ok to save these property changes.