Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]
This walkthrough shows how to author a basic report using Report Designer Preview. The scenario shows you how to create a tabular report with nested row groups, adjacent column groups, and totals.
Warning
Only install October Community Technical Preview (CTP) on a test server, and use it only to try out new features or verify that existing reports run on the new server architecture. Be sure to use copies of report definition files or report server databases. Databases and report server content will be upgraded automatically when you use them on a October Community Technical Preview report server. However, you will not necessarily be able to upgrade them again in subsequent CTP releases.
Prerequisites
Before you start this walkthrough, verify that all prerequisites are met:
- You have installed SQL Server 2008 Reporting Services October Community Technical Preview and the AdventureWorks OLTP sample database.
For more information, see Initial Installation (Reporting Services) and Considerations for Installing SQL Server Samples and Sample Databases. - To publish a report, you must configure the test report server for viewing reports and you must have permissions to publish a report to the report server. For more information, Tutorial: Setting Permissions in Reporting Services.
Step 1: Open Report Designer Preview
On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to Reporting Services, and click Report Designer Preview.
The Report Designer stand-alone application opens in Design Report view. The empty design surface appears with the message Click here to create a data source and data set for your report.
Step 2: Add a table and create a data source
On the Insert tab, click Table.
The Data Source Properties dialog box opens.
Type a name for the data source.
Verify that the Embedded connection option is selected.
In the Type drop-down list, select Microsoft SQL Server.
Type a connection string or click the Edit button to open the Connect to SQL Server dialog box.
In the Connect to SQL Server dialog box, in the Server host name text box, enter the name of the server where you have installed the AdventureWorks database.
In Authentication, choose Use Windows Authentication or select SQL Server Authentication and type a user name and password that has permissions to access the AdventureWorks database.
In the Database section, type or select AdventureWorks.
Click Next.
The Query Editor dialog box opens.
There are four ways to enter a query:
- Open. Click the Open button and browse to a .sql or .txt file that contains the query.
- Import from another report. Click the Import From Another Report button and browse to an .rdl report file. All report dataset definitions from that report file are imported into this report..
- Copy and paste an existing Transact-SQL query. Copy the query text, and from the toolbar, click Paste.
- Type a Transact-SQL query directly into the query pane.
Step 3: Create a report dataset
This walkthrough uses inventory data from the AdventureWorks OLTP database. Paste the following query into the query pane:
SELECT p.[ProductID] ,pc.Name, pm.Name as Model ,ps.Name as Subcategory, p.Color, CASE WHEN p.Color = 'White' THEN 1 WHEN p.Color = 'Black' THEN 2 WHEN p.Color = 'Blue' THEN 3 WHEN p.Color = 'Yellow' THEN 4 ELSE 5 END As ColorSortOrder, p.Size, CASE WHEN p.Size = 'S' THEN 1 WHEN p.Size = 'M' THEN 2 WHEN p.Size = 'L' THEN 3 WHEN p.Size = 'XL' THEN 4 ELSE 5 END As SizeSortOrder, Quantity,p.StandardCost,ListPrice FROM [AdventureWorks].[Production].[ProductInventory] ppi JOIN Production.Product p ON ppi.ProductID = p.ProductID JOIN Production.ProductSubcategory ps ON ps.ProductSubcategoryID = p.ProductSubcategoryID JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID JOIN Production.ProductModel pm ON pm.ProductModelID = p.ProductModelID WHERE pc.Name = 'Clothing'On the toolbar, click the green triangle arrow to run the query. The result set appears in the result grid below the query pane.
Click Finish.
The dataset and its fields collection appear in the Data pane.
A table appears on the report design surface. The table has one shaded row for column headers and one unshaded row for data. If you do not see a table on the design surface, review the first instruction in Step 2: Add a table.
Data source credentials are not saved as part of a report. If you are not using Windows authentication for a data source, you must enter SQL Server credentials every time that you open a saved report or import datasets from another report.
Add Data to the Data Region
Add fields from the report dataset to the data region.
Step 4: Add fields to the table row
In the Data pane, from the DataSet1 field collection, drag Model to the first unshaded cell on the data row in the table.
Several things happen when you add a field to a table:
The text box is selected, as indicated by a dark outline, and the data region row handles and column handles appear.
A field collection icon that represents all the fields in the dataset appears on the right side of the text box. Click this icon to see other fields in the field collection. You can select a different field for the text box.
Note
If you do not see the icon, hover the mouse over the text box where you dropped the field.
The data region is automatically bound to the dataset to which the field belongs.
The table row is automatically bound to the details group, which is all the data, filtered and sorted, in the report dataset.
The selected textbox shows an orange bracket just inside the left border of the text box, indicating the active group. The row icon displays three lines, indicating that this is the details group.
The Row Groups pane displays (DetailsGroup), the only group in the row group hierarchy.
The text box displays the text
[Model]. This is display text for the underlying expression=Fields!Model.Value.The column header text box is automatically filled in with the name of the field, Model.
Hover over the next empty cell in the details row, click the fields icon, and click
Quantity.- The data text box displays the text
[Quantity].
- The data text box displays the text
Set the sort order for the data rows. In the Row Groups task pane, right-click (DetailsGroup) and choose Edit Group. The Group dialog box opens. Note that there is no group expression defined for the details group.
Click Sorting. Click the Add button. In Sort by column, select
[Quantity]from the drop-down list. In the Order column, select Z to A. Click OK.The details group will be sorted from the highest quantity to the lowest quantity.
Drag StandardCost from the Dataset1 fields collection to the next unshaded cell in the table and set the currency format for the StandardCost column.
In the textbox, right-click the cell and choose Properties. The Text Box Properties dialog box opens.
Click Number. In the Category pane, click Currency. Examples of currency formats appear.
From the Decimal places spin box, select 0.
The text box format property is set for currency with zero decimal places. Click OK.
Add a row number. Right-click the text box that displays
[Model]. Hover over Insert Column, then click Left.A new column is added on the left of the Model column.
Right-click in the newly added text box and click Expression. The Edit Expression dialog box opens. This is a simple text entry dialog box for writing expressions. Paste the following expression in the expression pane:
=RowNumber(Nothing)Click OK. The text box displays the text
<Expr>. This display text appears whenever the expression for a text box is something more than a simple reference.Set the column headers to bold. Click the row handle for the column headers, then click the Bold button in the Font section of the Home tab.
Save your report. Beside the Jewel, click Save and enter a file name. Click OK.
Preview the report
Preview the report.
Step 5: Preview the report
On the Home tab, click Preview.
You should see three columns. The first row displays column headers, the next 35 rows show the default data group sorted by highest quantity to lowest quantity, with row numbers in the first column.
Add Row Groups
Create two row groups for Subcategory and Model.
Step 6: Add a group and aggregates to the group task panes
On the Views tab, click Design. The report design surface is displayed.
From the Data pane, drag Subcategory to the Row Groups task pane. Drop it above the (DetailsGroup) node to create a parent-child group.
Several things happen when you add a field to the task pane:
- A new group is automatically created. The unique group name defaults to Subcategory. The group expression is automatically set to
=Fields!Subcategory.Value, which displays as[Subcategory]. - A new column appears on the left side of the table, separated from the other table columns by a dotted line. The new area is the data region's row group definition area.
- In the new column, a row group cell appears for the detail rows from the report dataset. Cells in this row are in the scope of the group named Subcategory.
- The display text for the spanning cell is [
Subcategory]. This is the row group cell. From the shortcut menu for this cell, you can add totals for the group, add additional rows and columns, change the group properties including the group expressions, delete the group, add groups in relation to this group, set the data region properties, change the group spanning cell text by changing the expression text, or set the text box properties.
- A new group is automatically created. The unique group name defaults to Subcategory. The group expression is automatically set to
Set the Sort expression for the Subcategory group. In the Row Groups task pane, right-click Subcategory, and then Edit Group. The Group dialog box opens.
In the left pane, click Sorting. Click the Add button. In Sort by, from the drop-down list, choose [
Subcategory].In the Order column, accept the default A to Z.Click OK.
You have set the sort expression for the group to sort by Subcategory alphabetically in ascending order.
From the Data pane, drag Model to the Row Groups task pane. Drop it between the details group and the subcategory group.
The table adds a new column to the right of the previously added column. The display text
[Model]appears in the group cell. Cells in this row are in the scope of the group named Model.Delete the original Model column in the body section of the table now that the table has a group defined for it. Right-click column handle, and click Delete Columns.
Delete the RowNumber column to simplify the report display.
(Optional) Preview the result. The report shows four columns: a Subcategory group, a Model group, and values for Quantity and Standard Cost for the row groups.
Add Row Totals
Add Totals for the row groups.
Step 7: Add totals at the bottom for rows
Right-click in the row group cell for Subcategory, hover over Add Total, and click After.
The default aggregate Sum for Quantity and Standard Cost is added to the last row as a grand total.
Right-click in the row group cell for Model, hover over Add Total, and click After.
The default aggregate Sum for Quantity and Standard Cost is added to a new row, added outside the Model group but inside the parent group Subcategory. This value shows the total for each unique value in the Model group.
Set the Format property for the entire column for Standard Cost. Click the column header for Standard Cost to select the entire column. In the Property grid, click in the text box next to Format, and type C0. You have set the currency format for all the text boxes in the column to use zero decimal places.
On the toolbar, click Preview Report to preview the results.
Add Adjacent Column Groups
Create a column group for Size and one for Color.
Step 8: Add adjacent column groups
Click in the table to select the data region. The Group pane displays the groups defined for the table.
From the Data pane, drag Size to the Column Groups task pane.
Several things happen when you add the field to the task pane:
- A new group was automatically created. The unique group name defaults to Size. The group expression is automatically set to
=Fields!Size.Value, which displays as[Size]. - A new row appears at the top of the table, separated from the other table rows by a dotted line. The new area is the data region's column groups section.
- In the new row, a cell spans the two columns that existed in the data section of the table to display the column headers for
[Size].
- A new group was automatically created. The unique group name defaults to Size. The group expression is automatically set to
Click in the spanning cell for the Size column group. On the toolbar, click Bold and Center.
In the Column Groups task pane, right-click Size, and then Edit Group. Click Sorting. Set the sort expression for this group to the field [SizeSortOrder]. Click OK.
Add an adjacent group based on Color. Right-click in the spanning cell for the Size column group, hover on Add Group, then click Adjacent Right. The Group dialog box opens.
In the Group name section, change the name from Group1 to Color.
In the Group Expression section, from the drop-down list, choose
=Fields!Color.Value.A new adjacent column group is added as a peer to the existing column group.
In the Column Groups task pane, right-click Color, and then Edit Group. Click Sorting. Set the sort expression for this group to the field [ColorSortOrder]. Click OK.
Drag Quantity to all the three cells that are members of the row groups for Subcategory and Model.
Preview the results.
The report shows the following columns:
- Four columns for size: S, M, L, XL
- Five columns for color: White, Black, Blue, Yellow, Multi
Add Column Totals
Add totals for inventory in the column groups. Place the totals side by side so you can verify the inventory counts are correct.
Step 7: Add a total for Quantity beside each column group
Right-click the column group cell for Size, hover over Add Total, and click After.
The default aggregates [Sum(Quantity)] and [Sum(Standard Cost)] are added as the grand total in a new column. For this example, delete the Standard Cost column by right-clicking the column header, and clicking Delete Columns.
Right-click the column group cell for Color, hover over Add Total, and click Before.
The default aggregate
[Sum(Quantity)]is added in a new column before the first column in the Size column group as the grand total.On the toolbar, click Preview Report to preview the results.
(Optional) Because the independent inventory totals for Size and Color are side-by-side, you can verify that the counts are the same and that no inventory is missing.
Publish the report
To preview the report in SQL Server 2008 Reporting Services, you must enter values for deployment properties.
Step 8: Publish the report
On the Jewel tab, click Publish.
In Report Server URL, accept the default
https://localhost/reportserveror type the name of a report server with the October CTP release of SQL Server 2008 Reporting Services installed.In the Report folder, accept the default root level folder or type the name of an existing folder on the report server.
In Report name, type the name of the report. Click OK.
The report is published to the report server.
Deployment settings are not preserved as part of a report. You must enter deployment settings every time that you restart the Report Designer Preview application.
Browse to the location of the server that you entered in Step 2.
Report Manager displays the name of the report.
Click the report name, The browser shows the rendered report.
You should see three columns. The first row displays column headers, the next 35 rows show the default data group sorted by highest quantity to lowest quantity, with row numbers in the first column.
Note If you have problems viewing the report, check the Prerequisite section.
You have completed the walkthrough for creating a tabular report with row groups, row totals, column groups, and column totals.
Troubleshooting
Check the Report Server log for errors. For more information, see Troubleshooting (Reporting Services).
See Also
Other Resources
Working with the Data Window (Reporting Services)
Grouping Data in a Tablix Data Region (Reporting Services)