This post is part of the series: “Professional SSRS 2016 Book Preview Posts” which are excerpts for my Wrox Press book: Professional SQL Server 2016 Reporting Services and Mobile Reports. Each of the posts is a condensed version of the material covered in a corresponding chapter from the book. The goal for this posts is to provide useful and meaningful information you can use. For more comprehensive details, I refer readers to the rather lengthy book itself. Note that large portions are copied directly from the book manuscript that may refer to figures and screen capture images that have been removed for brevity.
- Introducing SQL Server Data Tools for Visual Studio
- Sample reports projects and exercises
- Using the graphical Query Designer
- Understanding query groups and table joins
- Understanding report data flow
- Understanding report groups
- Grasping expression basics
- Utilizing group sorting and visibility
This chapter introduces and explains one of the most fundamental and essential concepts in SSRS report design. All of the data regions—tables, matrices, lists, and visual controls like charts—rely on groups. You will get started with the sample report project in SSDT that contains several completed examples. You will learn the differences between grouping and aggregating rows in queries and performing grouping on datasets within a report region. We explain the flow of data through the report along with the opportunities to filter, group, and aggregate values at multiple points along this path. You will add groups and related header tiles and footer totals to groups in a table and then a matrix. Then you’ll see how multiple groups for hierarchies of grouped results make for effective reporting.
Another important topic, essential to effective report design, is the use of expressions to define groups and properties. We introduce aggregate functions and aggregate scope, which will be expanded in Chapter 7. Finally, you will learn to design a table report with multi-level drill- down navigation so users can explore details within summary groups as needed.
SQL SERVER DATA TOOLS
So far, you have been working with Report Builder, the simplest of the two report design tools. Before moving into the next report design topic, you should become familiar with the other report designer: SQL Server Data Tools (SSDT) for Visual Studio. This report tool was created primarily with the IT professional in mind. An earlier version of the Visual Studio report design tool was once called Business Intelligence Development Studio (BIDS).
Honestly, most of the report design features in both Report Builder and SSDT are the same. Conversely, subtle differences and some capabilities in SSDT don’t exist in Report Builder. The tool you choose to use to accomplish your day-to-day report design work will likely depend on your organizational role and the complexity of the report design solutions. Before making a final decision, you should learn to use both tools, and then decide which one is right for you and your project.
NOTE After several years of experience, I have changed my approach to teaching people to use both design tools. Even so, it is continually a quandary for instructors and authors to develop the best instructional method. In classes, and in previous editions of this book, I gave instructions using one tool, and then
call–out some of the differences along the way. Because there are so many subtle differences, this approach can confuse new users. Consequently, if you really want to learn serious report design, educate yourself on both tools, one at a time; and then make your choice.
Getting Started
Before you get started using SSDT, decide where you prefer to store the project files. As you’ll see when you create a new project, the default project file path is in your Windows Documents library. If you are new to Visual Studio, I recommend you use the default project path for this quick introductory walkthrough. I like to keep mine in a folder named Projects in my OneDrive, or in a folder named Projects right off the root of the main storage drive. Whatever your choice, decide now, and create a folder named Projects in your preferred storage location.
The following numbered steps are not a complete exercise, but rather a few simple instructions to help you create a new project. The project and report names you use in this little practice run are not critical so just use some names that make sense to you. You will quickly move on and not use this project and report in future exercises.
1. The first thing is to open SQL Server Data Tools 2015. Depending on your version of Windows, either use the SQL Server 2016 program group, or just type the name of the program until you see it in the search results.
TIP Because SQL Server Data Tools now installs as a separate setup package from the link provided in the SQL Server Installation Center, it may not appear under the SQL Server 2016 program group. This may change over time with sub- sequent version upgrades, or if you had upgraded from a previous version of SQL Server. On my Windows 2012 development server, which has a fresh install, SQL Server Data Tools 2015 shows up in the Apps list under the letter “S.”
2. Run SQL Server Data Tools 2015.
NOTE Running Visual Studio 2015 is exactly the same as running SQL Server Data Tools 2015. Keep in mind that Visual Studio 2015 was the current version of Visual Studio at the time of publication. Newer versions of Visual Studio will work with appropriate updates to the SSDT add-in.
3. After the Visual Studio shell opens, from the File menu, choose New ➪ Project….
4. The New Project dialog opens. From the Templates pane on the left, expand Business
Intelligence and choose Reporting Services, as shown in Figure 6-1.
FIGURE 6-1: Choosing Reporting Services from the New Project dialog.
5. Within the project templates list in the middle of the dialog, select Report Server Project.
6. At the bottom of the window, Enter the name for the project as My first report project
NOTE This should be a short name including spaces and descriptive text, which will create a folder and file names. I usually use a name that briefly describes
the purpose of the project and that I will recognize later. The project name I just recommended is only to get you started and give you some experience with new project setup. As I mentioned earlier, you will soon leave this project behind and then open an existing project I have prepared for you.
7. Note the default project folder path. If you prefer, use the Browse button to navigate to a
Projects folder you created earlier to complete the path in the Location box.
The Solution name will be the same as the Project name unless you change it. In larger,
8. If the project won’t be managed as part of a larger solution, just leave the names the same.
When you’re finished making changes, click the OK button.
9. With a report project open and the Solution Explorer visible on the right-side of the SSDT/ Visual Studio main window, right-click the Reports folder and choose Add ➪ Existing Item….
10. From the Add New dialog, select Report and then give the report file a name. This should normally be a friendly name with spaces and mixed case. When you’re ready, click the Add button.
When the Report Designer opens, notice there are different window panes containing controls and properties docked to both sides of the main window. These are labeled in Figure 6-2, and the key that follows explains the labels. The four tool windows used for reports design include, on the left, the Report Data and Toolbox windows arranged as tabs, and, on the right, the Solution Explorer and Properties windows arranged as docked windows, one above the other with a sliding separation bar. You will see additional windows that you can ignore or hide if you prefer.
1. Report Formatting toolbars
Set properties for the currently selected object or objects in the Report Designer when in Design view. Standard properties include Font Name, Font Size, Weight, Italic, Underline, Foreground color, Background color, Alignment, List styles, and Indentation.
FIGURE 6-2: Window panes in Report Designer.
2. Report Designer view selection tabs
Switch between Design and Preview.
3. Report Data window
The Toolbox window is also displayed in this area. Switch windows using the selection tabs at the bottom of the window pane.
4. Solution Explorer window
Navigate the solution, projects, and project files. Right-click to set project properties, add new items, and perform actions.
5. Report Designer window
This is the main design canvas used to design reports and set properties for data regions and report items.
6. Grouping window
Add, remove, and set properties for row and column groups.
7. Properties window
Displays and manages all properties for the selected object or group of selected objects.
8. Tabbed documents selection
In the default view, windows in this pane are selected using these tabs. The Report Data and Toolbox are used for report design, and other windows are available. Windows can be added with the View menu. Use the window pane toolbar to hide, move, pin, and auto-hide windows.
9. Sliding window separator
In the default view, windows in this pane are docked with a movable separator bar. The Solution Explorer and Properties windows are used for report design and solution management. Use the window pane toolbar to hide, move, pin, and auto-hide windows.
The best way to get started with Visual Studio is to create a test project and spend some time familiarizing yourself with the interface. You can dock, hide, and show windows so that you have what you need at your fingertips, as well as remove the ones you don’t need. If you are new to Visual Studio, don’t go crazy moving things around and closing windows until you are familiar with these tools enough to set things back.
Each window has a familiar pushpin icon that will either “pin” the window in place, or allow it to auto-hide when the mouse moves away from it. Use the auto-hide feature and resize tool windows rather than closing windows you’ll need again. If you close or hide a window using the right-click menu or drop-down arrow in the window header, you can get it back using the View menu. Some
of these windows are only available on the View menu when certain items are selected in the design interface. For example, if you were to close the Report Data window, the option to display it again is listed on the View menu only when you set focus to the report in the designer.
In the next section you will be using a sample solution that contains a set of existing projects pro- vided for you. If prompted to save your changes to this new project, answer “Yes,” but you’re not actually going to use that project any more.
Getting Started with Sample Reports Projects
As mentioned in Chapter 3, copies of all the reports used throughout the book are provided on this book’s website at www.wrox.co . These are organized into two projects contained in a Visual Studio solution in the folders you extract with the book download sample files.
1. On the File menu, select Open ➪ Project / Solution….
2. Use the Open File dialog to navigate to the location where you extracted the book sample files. Locate and open the folder named Wrox SSRS 2016 Report Solution
3. Open the file named Wrox SSRS 2016 Report Solution.sln. SSDT opens with the solution and related projects listed in the Object Explorer on the right side of the report design interface. Figure 6-3 shows SSDT open with a report open in the Design window.
The solution contains multiple projects. Use the Solution Explorer window on the right side of the main window to expand and collapse each project in order to access the files it contains. Reports and shared data sources are also added in Solution Explorer.
Generally, you should use shared data sources as a best practice. The only reason typically not to use them is when you want a report to be completely self-contained. But otherwise, having one place to manage data source connection information makes a lot of sense when these objects all get shipped off to the report server and are making important things happen out there in Business User Land.
FIGURE 6-3: A report in the Design window.
Shared datasets have advantages under very specific circumstances and are required when designing mobile reports. Therefore, you will use them in chapters 17, 18, 19, and 20. As a default choice, for most paginated reports, I generally recommend using embedded datasets.
The Wrox SSRS 2016 Exercises project contains finished copies of all the exercises found at the end of each chapter. The Wrox SSRS 2016 Samples project includes many topic-specific examples, each prefixed with the chapter number. For the chapter samples, it is important to realize that explicit instructions are not provided for every single click and menu selection. Because you have already mastered the essentials getting started creating a new report, data source, and dataset, there is no need to repeat those steps. Some unfinished reports are provided with steps completed up to
the task or topic that you can finish. You will be instructed as to which report to open. In some of cases, you’ll examine a completed report to see how it works.
To follow along, open a copy of the sample report, rather than making changes to the original, so you can go back and start over if necessary. The best way to do that is to copy and paste the report in the Solution Explorer.
TIP Whenever you use the copy/paste method to back up a report, ensure that you save your changes first, because this technique actually makes a copy of the last saved file, rather than the version of the report sitting in memory that may have unsaved changes.
This step is pretty simple, but not entirely intuitive at first. My preferred method is to select the report in Solution Explorer (not the report file name as if renaming the file, but just click once to select the report), and then use Ctrl+C (copy) and Ctrl+V (paste). If you right-click to Copy, then
you must right-click the project to Paste. I typically remove the Copy of file prefix and then describe the backup state in parentheses at the end of the filename. You can see this pattern in the sample project.
TIP Making regular backups provides peace of mind by ensuring that you don’t lose your work. There are three common backup methods for reports: using a version control system such as TFS, making routine copies of the report files in the project folder to a different storage location, and keeping a secondary report project in the solution. The latter allows you to use the earlier-mentioned copy- and-paste backup method.
If you are disciplined about using integrated version control, then it can provide a safety net to prevent catastrophic loss. However, it doesn’t help with the typical cadence of report design. When working on a challenging report, I often create and keep one or more backup copies of the report I’m working on. This provides a way to experiment with different techniques without messing up a
working design. If something goes wrong, rather than trying to untangle it, you can just revert to an earlier copy. After testing to make sure the design is working, you can delete the backup copies.
Graphical Query Designer
The reason there are different query tools is that report developers have varied levels of experience with T-SQL. Therefore, individuals develop strong preferences. When I began my career with databases, at first I relied on graphical query design tools. Now I find it easier to hand-write queries rather than using the graphical Query Designer. These helper tools exist for good reason, and they work well. If you are not a T-SQL aficionado, you should learn how to use the graphical query design tool, but it shouldn’t always be your central experience. With a little practice, many find it easier to hand-code simple queries, rather than relying on the Query Designer.
When using SQL Server as a data source, the dataset in Query Designer is more elaborate than in Report Builder. This graphical Query Designer is actually borrowed from the SQL Server client tools, and is also available in SQL Server Management Studio (SSMS).
Open the Sample Report
If you don’t already have the Wrox SSRS 2016 Report Solution open in SSDT, open it now.
1. In the Object Explorer, expand the Wrox SSRS 2016 Samples project.
2. Expand the Reports folder.
3. In the Object Explorer, click once to select the Ch06 – Sales Summary (query completed report.
TIP There is a subtle difference between selecting an object and selecting the name of the object. For example, when you select a report in Object Explorer, the entire line is highlighted. If you click once and then click again, the name
of the report is selected. The same behavior applies to textboxes in the designer where clicking twice will select the text within the object, rather than the object itself. If this happens, click outside the object and then click it once.
4. Press Ctrl+C and then Ctrl+V to make a copy of the report. The new report should appear in the Object Explorer preceded by the text Copy of in the filename.
5. Double-click the new report copy to open it in the designer.
6. Expand the Datasets node in the Report Data window.
7. Right-click the SalesSummaryMonthProductRegion dataset and select Query… to open the graphical Query Designer.
8. Use this finished query to familiarize yourself with the query design interface as you read the following description about designing a query with this tool. When you’re finished, cancel out of the Query Designer and don’t save changes.
9. Optionally, create a new dataset in this report and follow these steps to duplicate this query.
Save your changes, and then switch back to the original query to check the results.
To design a query in SSDT, create a dataset just like you did previously using Report Builder. Click the Query Designer… button to open the graphical Query Designer. You add tables to the query by using the right-most icon on the toolbar. In the example shown in Figure 6-4 is the end result after completing the following steps. Please note that the following six steps are for reference and are explained in greater detailer in the subsequent steps.
FIGURE 6-4: Tables added to Query Designer.
1. Add the Date Sales, and Product tables.
2. Verify that the join lines were added by the query designer.
3. Use the check boxes to select the fields you see in the example: MonthNumber, MonthName ProductCategory ProductSubcategory ProductName, OrderQuantity, and SalesAmount.
4. Click the Use Group By button on the toolbar.
5. For the OrderQuantity and SalesAmount fields, use the drop-down list to change the Group
By column selection to Sum
6. For the OrderQuantity and SalesAmount fields, copy and paste the field names (in the
“Column” column) to the Alias column.
The designer has four panes separated vertically. The top pane shows the tables that were added to the query, joins, and selected columns. The second pane lets you rename columns, group, aggregate, filter the results, and apply parameters. The query text pane shows the T-SQL script generated by
the designer, and the results pane shows the rows returned by the executed query. Test and run the query by clicking the red exclamation mark icon on the toolbar.
Grouping Query Results
Figure 6-4 shows the Group By button on the toolbar (second from the right). This option adds every column to the GROUP BY clause in the query, and displays the text “Group By” in the seventh column of the fields list pane.
Any numeric columns that should be aggregated (rather than grouped in the query) must have the Group By option changed. Figure 6-5 shows the OrderQuantity and SalesAmount columns changed from Group By to Sum. When you do this, a column alias is created for each aggregated column using placeholder names like Expr1. These should be changed to something that makes more sense. You can actually use the original column name for the alias. To differentiate the alias
from the original column name, the original names have been appended with the word Total, so the alias names are OrderQuantityTotal and SalesAmountTotal
FIGURE 6-5: Column names modified.
Despite the desire to filter data by the Year, that column doesn’t need to be returned in the query results. You can see that the Year column has been included from the Date table and the Output checkbox has been unchecked. To add a parameter, type the parameter name you want to use in the Filter cell preceded by an equal sign (=) and an ampersand (@). In the graphical query designer, a column that is used only for filtering is excluded from the GROUP BY list by changing the selection to “Where.” In this case, the designer references the Year column in a HAVING clause to implement filtering.
Query Joins and Join Types
Because these tables have relationships defined in the database, the tables are automatically joined
on the key columns. In the example, the Sales table is joined to the Date table using the key column in the Date table named TheDate, and the OrderDate column in the Sales table. Likewise, Sales and Product tables are joined on the respective ProductKey columns.
The designer applies inner joins by default, meaning that there must be matching records on both sides of the join. The Date table contains one record for every day in the years 2005 through 2014, but there are not orders for every date, so the inner join will only return date information for exist- ing orders. An outer join returns all records from a specified table on one side of the join, and then all the matching records from the table on the other side.
Changing the join type is simple. Right-click the diamond on the line between the two tables and select all rows from the table on the outer side. In this case, you right-click the join between Date and Sales tables, and then choose “Select All rows from Date.” The join will be designated either LEFT OUTER RIGHT OUTER, depending on the order they were added in the Query Designer.
Execute the query by clicking the exclamation mark icon in the toolbar. When prompted for the
Year parameter, type 2013, and then click OK. Notice in the results pane, a row for December,
2013, for which there are no existing orders. This is the effect of the outer join.
Report Data Flow
You will find that most objects within the Report Designer have many properties and features, and many of them are only used when necessary. Standard objects you will use in the reports all the time have properties that you probably won’t use in most reports, but they provide tremendous flexibility and opportunities to address unique business requirements using creative design techniques.
Some of these properties are depicted in Figure 6-6. You can see (moving left to right) that after query results are processed in a dataset query, an optional filter can be applied after the results are produced. Likewise, conditional filters can be applied as the data enters any data region. After that, the results can be sorted before they are presented to the first group expression. Each data region handles grouping a little differently, but the core concepts are the same. Some data regions support multiple levels of grouping.
FIGURE 6-6: Using Report Designer properties and features.
REPORT GROUPS
Report groups are one of the most important concepts in fundamental report design. Whether your reports will visualize data in a table, matrix, or any type of chart, you will define group expressions either implicitly by dragging and dropping fields in the designer, or explicitly by writing
the expression. Table and list reports do not require a group to be defined, but have limited utility without any groups. Beginning with a table, let’s a take a look at how groups work.
1. In SSDT, if you have any unsaved work you want to keep, save that report.
2. From the Object Explorer on the right, open the report named Ch06 – Sales Summary with Groups
3. You can see that the table is bound to the dataset named
SalesSummaryMonthProductRegion
4. In the Row Groups list below the designer window, use the down arrow button next to the ProductCategory group to choose Group Properties… to open the properties dialog for that group, as shown in Figure 6-7.
FIGURE 6-7: The Group Properties dialog.
It’s probably no surprise that the “Group expression” refers to the ProductCategory field (see Figure 6-8), but there are several optional properties and features associated with groups. For example, page breaks can be managed for each group, so a new page is inserted before or after the grouped field value changes.
5. Use the page list on the left to switch to the Page Breaks page in the Group Properties dialog shown in Figure 6-9.
FIGURE 6-8: ProductCategory field as a group expression.
FIGURE 6-9: Managing page breaks.
6. Check the first box to set a page break between each instance of a group.
7. Leave the other checkboxes unchecked.
8. Click the OK button to accept the group changes and close the Group Properties dialog.
There’s no need to inspect the ProductSubcategory group because it’s set to group by the ProductSubcategory field. But, what about the Details) group? This group isn’t normally set to group records for a field by default, so let’s find out if someone might have changed it.
9. Edit the group properties for the Details) group and open the Group Properties dialog.
Hey, look at that! The (Details group has been changed and set up to group by the ProductName field. Why would this make a difference when that field is already being grouped within the detail rows returned by the query? It seems senseless to explicitly group by the lowest-level value that is being returned in the query results.
10. Now check the behavior in the Table Designer. Hover the mouse pointer over and
re-select the two numeric fields for the detail cells in the Order Quantity and Sales Amount columns. You see that the designer applies the SUM function. This is because of the group definition in that Detail group.
The first two groups are set up for drill-down navigation by hiding levels that are expanded with toggle items. With any level collapsed, it is important that the value displayed on this line be an aggregated total representing all the hidden details. This will only happen if an aggregate function (such as SUM) has been applied in each expression on the detail row.
Adding Totals to a Table or Matrix Report
The table, matrix, and list data regions are all based on a common object called the Tablix, and many design techniques are similar for each of these data regions. Each of these objects provides dif- ferent layout options, but the fundamental concept of grouping is the same.
In a table, adding a total to a row group adds a new row that applies an aggregate function (for example, SUM) to all the members of that group. The same applies to a total added to a matrix col- umn group. By adding a total row or column, you’re actually adding a total that applies to the par– ent of the group. Consider this example. Suppose columns are grouped by quarter and then by year. If you were to add a total to the Quarter column group, the total would be for all the quarters add- ing into the year. This means that a total applied to the topmost group will always return the grand total for all records in the data region. This is evident in the example shown in Figure 6-10 where the table data region is grouped and totaled by Subcategory.
FIGURE 6-10: Grouped table data region with totals.
Defining a total for a group at a lower level would create a subtotal break. Totals can be placed before after group values. Adding totals before a row group shows the total above the group in a heading row, and adding the total after shows the total below the group in a footer row.
Subsequently, for a column group in a matrix data region, inserting a total before the group places totals to the left of the group. Adding totals after the group inserts a total column to the right of the group.
Groups, headers, footers, and totals are all related design elements that can take a simple report to the next level and provide significant value. Groups are an essential design concept, and a number of more advanced capabilities have been added as Reporting Services has evolved through newer versions. At the group level, you can now conditionally control things like page breaks and page numbers.
As you continue to review the completed Ch06 – Sales Summary (query completed report, use the following steps to see how the report was be designed.
1. Switch back to Design view.
2. Take a look at the SalesAmount column. The heading label Sales Total was changed from the original field name to make it more readable.
3. The last column was added by right-clicking the header of the column to its left and choosing the option to add a new, blank column. The column label was changed to Avg Sales.
4. In the detail row, right-click the Avg Sales textbox and choose Expression….
5. Review the expression that was added: =AVG(Fields!SalesAmount.Value . Before you click OK to complete the expression, select and copy this text to the clipboard.
6. Right-click the textbox in the Total row of the new column and choose Expression….
7. Review this expression that was added by pasting the expression you copied from the detail cell. Click the OK button.
8. Preview and test the report.
Expression Basics
Looking at the last few examples, when you create a field reference in the designer by dragging
and dropping or selecting from the field list, you will see a field or expression placeholder in square brackets, such as ProductCategory] [SUM(OrderQuantity)]. What you see in the designer
is actually a simplified version of the expression that is stored in the report definition. To view
the actual expression, right-click the placeholder text and choose Expression…. This opens the Expression Editor, showing the entire expression. Expressions always begin with an equal sign and contain a full object reference. Table 6-1 shows these two examples.
TABLE 6-1: Expression Placeholders
| PLAC E H O LDE R | E XPRESSION |
| ProductCategory | =Fields!ProductCategory.Value |
| [SUM(OrderQuantity) | =SUM(Fields!OrderQuantity.Value) |
In this chapter’s exercise, you will see that expressions can include multiple functions and objects. When used in a data region bound to a dataset, an expression simply references a field or fields in that dataset, but can also reference different datasets, as you will see in more advanced examples in the following chapters. Expressions are the real magic behind both simple and complex report designs. This chapter introduces several features and capabilities that are demonstrated in greater detail in the chapters that follow.
Introducing Aggregate Functions and Totals
When you drop a numeric field into a group or table footer cell, an expression is added applying the SUM( aggregate function. The designer assumes that you will want to sum these values, but this function can be replaced with one of several others. Reporting Services supports several aggregate functions, similar to those supported by the T-SQL query language. For now, let’s just consider the concept of basic aggregation using the SUM function.
NOTE You learn about all the functions in Chapter 7, “Advanced Design
Techniques.”
When an aggregate function expression is used in a group detail, header, or footer row, the scope of the current data region or group is assumed. For example, suppose a table contains two nested groups based on the Category and Subcategory fields. If you were to drag the SalesAmount field into the Subcategory group footer, the SUM(SalesAmount expression would return the sum of all SalesAmount values within the scope of each distinct Subcategory group range.
Sorting
You have a few options to sort data in a report, and the best choice will depend on a few factors. Other features (like grouping) require data to be sorted and may negate the capability to sort data after it is grouped. Be mindful that with a large result set, sorting is a costly operation that will add to the overall report execution time. Options may include the following:
➤ Sort Records in the Query—Typically, if records will be presented in a particular order, the most efficient method is to sort the data in the query before it gets to the report. This will aid in grouping as effectively as possible. If other sorting options will be used within the report, presorting in the query may be wasted cycles.
Table Interactive Sorting—Normally used in ungrouped table reports, this feature can be applied to any or all columns of the table. Interactive sorting is applied to the textbox in the table heading for each column. Clicking the sort icon displayed in the column header will
re-order rows the records displayed in that table. Clicking again toggles between ascending or descending order. In a table or matrix data region with groups, interactive sorting can be applied to the rows within a group.
Group Sorting—Every group has an optional Sorting expression. There are times when you may need to group on one set of field values and sort on a different set of corresponding val- ues. This would make sense only when the grouping and sorting fields are distinct within the same set of value ranges.
An example of group sorting can be found in the sample report Ch06 – Orders Matrix Group
Sort. The design of this report is quite simple, and Figure 6-11 shows this in the SSDT designer.
FIGURE 6-11: Design of sample report.
This report began with two datasets. The main query returns the MonthNumber, MonthName Country, and OrderQuantity columns. Take a look at the Year parameter properties and you’ll see that the YearList dataset query is used to populate the parameter list. Edit the SalesSummaryByMonthCountry dataset and you’ll see that the Year parameter is used to filter the results in the query’s WHERE clause. The matrix is added to the report body. The MonthName
field is dragged and dropped onto the Rows header, the Country field is dropped onto the Columns
header, and the OrderQuantity field is dropped in the Values cell to create the expression
=SUM(Fields!OrderQuantity.Value .
If you were to preview the report at this point in the design process, it would look like Figure 6-12. Out of the box, the rows and column groups are naturally sorted alphabetically.
This is probably fine for the country names, but obviously not for the months, which are in alpha- betic order, but not sorted chronologically. This is the reason the MonthNumber column has been included in the query. To fix the group order, you switch to Design view and edit the row group.
Figure 6-13 shows the Sorting page of the Group Properties dialog for the row group.
FIGURE 6-12: Previewing the report.
FIGURE 6-13: Sorting page of the Group Properties dialog.
Changing the “Sort by” expression allows you to display one field and sort by another, as you can see with the report previewed in Figure 6-14.
FIGURE 6-14: Displaying by one field and sorting by another.
…EXERCISE…
