MSSQL Server Reporting Services: The Authoring Phase: Overview Part II

Monday Mar 29th 2004 by William Pearson
Share:

MSAS Architect and Reporting Services Consultant Bill Pearson introduces the addition of data, then grouping, sorting and more, within a basic tabular report, as he concludes his overview of the Authoring phase of MSSQL Server 2000 Reporting Services.

About the Series ...

This is the third article of the series MSSQL Server 2000 Reporting Services. The series is designed to introduce MSSQL Server 2000 Reporting Services ("Reporting Services"), with the objective of giving a preview of its features, as well as sharing my conviction in its role as a new paradigm in enterprise reporting. As I advise clients on a more and more frequent basis these days, this is the future in a big way. I hope you will consider my input valuable, and that you will investigate closely the savings and advanced functionality that will soon be available to anyone with an MSSQL Server 2000 (and beyond) license.

Note: In addition to the installation of Microsoft SQL Server 2000 Reporting Services, Version 1.0, together with Microsoft Visual Studio.NET (required to access Report Designer for report creation), Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.

Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 Reporting Services, MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("MSAS"). Any Microsoft Office components that appear within the series are members of the MS Office 2003 suite, but previous versions will work similarly in most instances

Introduction

In our last article, The Authoring Phase: Overview Part I, we began an overview of the first of the main phases of the enterprise reporting life cycle. We introduced the article with observations surrounding the objectives of the MSSQL Server 2000 Reporting Services series, as well as the objectives of the initial phase overview articles, and then discussed the Authoring phase in general. We began an exploration of the steps involved in creating a blank report, mentioning in passing the general ways of creating reports, each of which we will revisit numerous times in later articles.

After introducing the Authoring phase, we began a practice example in which we set out to create a basic tabular report. First, we created the Report Project to house the Report File, which we created next. Within the Report File, we established a Data Connection, and then built a simple SQL query to use against our specified data source, the AdventureWorks2000 sample OLTP database. We then designed the report Layout, and, finally, added data from the dataset resulting from our query.

We are now ready to pick up where we left off, and undertake the remaining steps of our initial walkthrough of the Authoring phase. We will complete our exploration of the general Authoring process, within the remaining activities of the hands-on practice example we began in Part I, rejoining the tabular report as we saved it, and taking the following steps:

  • Perform modifications to the layout of our existing report, RS02_Authoring;
  • Perform Grouping within the report;
  • Modify the underlying Dataset for the report;
  • Filter the Dataset;
  • Add Subtotals and a Total;
  • Set Properties and Formats for illustrative report components;
  • Preview our work, throughout the article, to confirm the effects of our activities.

As we mentioned in the first half of this two-part article, our intent is to perform an overview of Authoring. We will return to various activities we touch upon here, as well as to many of the topics we explore within the subsequent two phase overviews, as we get involved in creating reports to accomplish illustrative business needs. I intend to make this a series on enterprise reporting in the widest sense. I have wanted to do this for years as a BI consultant, but never had a unified, common platform from whence I could show techniques and methods to support robust and creative business intelligence. Before the advent of Reporting Services, we would have had to introduce multiple tools to accomplish sophisticated solutions in many cases, but those scenarios are now a thing of the past.

A new era in enterprise reporting has dawned, as industry and analysts alike will soon proclaim. Stay tuned - it will happen sooner than many appreciate, and the exceptional benefits to analysts and other information consumers will become the new standard.

Returning to the Authoring Phase

At the end of Part I, we previewed our work in building a blank report, and then saved the report file as RS02_Authoring for easy identification. We will reopen the file and resume our exploration of report design procedures, focusing immediately on additional features within a continuing practice example. As you recall, we constructed RS02_Authoring, a simple tabular report from "scratch," using a manual example for our first exposure to authoring, versus a wizard-driven report, to enrich our overview with a far greater number of the aspects of Report Designer. At this stage, we will resume our progress within the layout of the report, and build upon our work to date.

Opening the Report from Part I

Let's first reopen the Report Project, and get to the Report File. We again launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

1.  Click Start.

2.  Navigate to Microsoft Visual Studio .NET 2003 in the Programs group, as appropriate. The equivalent on my PC appears as shown in Illustration 1.


Illustration 1: Getting Started in Microsoft Visual Studio .NET 2003 ...

3.  Click Microsoft Visual Studio .NET 2003 to initialize the application.

The Microsoft Development Environment [Design] opens.

4.  Select File --> Open --> Project from the main menu, as depicted in Illustration 2.


Illustration 2: Selecting a New Project

5.  Navigate to the location of project RS002-1, which we created in Part I (see the Creating the Report Project subtopic in the Creating the Report Project and the Report Files section).

When arriving at the location, we see two related files within the Open Project dialog, RS002-1.rptproj (the Project file), and RS002-1.sln (the Solution file), as shown in Illustration 3.


Illustration 3: Files Related to the Reporting Project

Projects and Solutions are containers in Visual Studio .NET for managing files. The objects that these containers hold are called Items.

A Project is made up of a set of files. Projects generally produce one or more output files when built, with the nature of the files depending on the type of Project. While many of the details surrounding Project file components relate more to Visual Studio .NET than to our current focus within the Report Designer, it is helpful to know that Report Projects (Visual Studio .NET projects that relate to Reporting Services) contain reports, shared data sources, and resources. Projects belong to Solutions, which contain one or more Projects, among other possible components.

NOTE: For more information about Visual Studio .NET and its components / processes, see the Visual Studio .NET Books Online.

6.  Click RS002-1.rptproj to select it.

7.  Click Open.

The Project opens.

The Solution Explorer pane, containing RS02_Authoring.rdl, is depicted in Illustration 4.


Illustration 4: The Report Definition File in the Solution Explorer Pane

RS02_Authoring.rdl is the Report Definition file in which our work from Part I is stored (in Report Definition Language, or "rdl").

8.  Double-click on RS02_Authoring.rdl, which appears within the Solution Explorer pane (the upper right corner of the Development Environment).

The Report Definition file for RS02_Authoring.rdl opens, defaulted to the Layout tab in front. The Design Surface is presented, as depicted in Illustration 5.


Illustration 5: The Layout Tab and Design Surface

The Design Surface of the Layout tab is, we recall, the central "palette" from which we started our blank report in Part I. We will begin here, as we have a few more Layout considerations to explore before moving on with the report.

Performing Grouping and Sorting

Let's look first at an important capability in any reporting application, Grouping. We can group our data by fields or by expressions, within our selection of the table, list, and matrix data regions (we will work with other regions as the series progresses). Groups provide the dual benefit of allowing us to establish sections within our table to meet the business objectives of the report, and to establish header and footer regions, where we can insert subtotals and / or expressions, as we shall see.

Let's add a logical grouping to our report whereby the Products we present are grouped by Category, then by Subcategory, to meet a hypothetical business requirement that has been described by the intended audience. We will proceed by taking the following steps:

1.  Click the Table (at any point inside it) on the Layout tab, so that column and row headings (called "handles" in the Report Designer) appear at the top and left of the Table, as shown in Illustration 6.

Click for larger image

Illustration 6: The Report Definition File in the Solution Explorer Pane

The row handles appear on the left of the table, while the column handles cross the top. A corner handle, at the point where the row and column handles intersect, also exists, to allow us to adjust the table as a whole.

In Part I, we added a column to make a place for a fourth data element we wished to appear, the List Price. We will find that the same right-click action we used at that point comes to our assistance here, offering us the grouping capability we need within the context menu that it affords.

2.  Right-click on the handle of any of the three rows in the table.

The context menu appears.

3.  Select Insert Group from the context menu, as depicted in Illustration 7.


Illustration 7: Select Insert Group

The Grouping and Sorting dialog appears, defaulted to General tab.

4.  Type Category into the Name box.

This will name the Group we are creating.

5.  Click the first row in the Group On box, just beneath the Name box, to the right of the asterisk ("*") symbol.

Clicking this row enables a selector, from which we can select the data element upon which we wish to group.

6.  Select =Fields!Category.Value from the drop down selector.

The General tab of the Grouping and Sorting dialog appears as depicted in Illustration 8.


Illustration 8: The Grouping and Sorting Dialog - General Tab with Our Additions

7.  Click the Sorting tab.

8.  Click the first row in the Sort On box, within the Expression column.

Clicking this row enables a selector, just as we saw with on the General tab, from which we can select the expression upon which we wish to sort.

9.  Select =Fields!Category.Value from the drop down selector.

10.  Leave the Direction value at Ascending, the default.

We have established sorting on Product name, in alphabetical order.

The Sorting tab of the Grouping and Sorting dialog appears as shown in Illustration 9.


Illustration 9: The Grouping and Sorting Dialog - Sorting Tab with Our Additions

11.  Click OK.

The Grouping and Sorting dialog closes, and we are returned to the table, shown in Illustration 10.


Illustration 10: Table, with Group Header and Footer

We note that both a group header and footer have been added to the table (compare it to the "handle" view in Illustration 6, and notice that two new rows have been added to accommodate the header and footer).

Let's add another group for SubCategory, which will be subordinate to the Category group we have created in the last few steps.

12.  Right-click on a row handle once again.

The context menu appears.

13.  Select Insert Group from the context menu.

The Grouping and Sorting dialog appears, defaulted to General tab.

14.  Type SubCategory into the Name box, to name the new group.

15.  Click the first row in the Group On box, just beneath the Name box, to the right of the asterisk ("*") symbol.

16.  Select =Fields!SubCategory.Value from the drop down selector.

The General tab of the Grouping and Sorting dialog appears.

17.  Click the Sorting tab.

18.  Click the first row in the Sort On box, within the Expression column.

19.  Select =Fields!SubCategory.Value from the drop down selector.

20.  Leave the Direction value at Ascending, the default.

21.  Click OK.

The Grouping and Sorting dialog closes, and we are returned to the table. Let's take a look at our progress so far with a preview of the report.

22.  Click the Preview tab, next to the Layout tab

The report generates and presents a preview, similar to that partially shown in Illustration 11.


Illustration 11: Report Preview (Partial View) with Groupings Apparent

We can easily see that the Products are grouped by Category and SubCategory. To proceed with our next steps, we will need to make our data source richer. While we have a "List Price" for the Products, it is unlikely that adding together the list prices of the products we sell would serve a typical business purpose. However, if we could present the total sales of each of the SubCategories and Categories of Products in our report, this would be a "value add" to present to the information consumers.


Modifying the Underlying Dataset


We want to quickly modify our underlying dataset. Some of us are probably not anxious to return to the SQL query we inserted in Part I. Let's take a look at a simpler way to add a table - at least a more visual way - and at the same time, gain a little exposure to another way to design our underlying datasets.


1.  Select the Data tab.


The SQL Query we inserted early in Part I appears. We will add a table in the same way that we might have written the query from the start - and as we will do in many subsequent articles, within which we are looking at specific reporting techniques.


2.  Click the Generic Query Designer button atop the Data tab.


The Generic Query Designer button is shown in Illustration 12.



Illustration 12: The Generic Query Designer Button

The Generic Query Designer loads, and appears as depicted in Illustration 13.


Illustration 13: Generic Query Designer - Initial View

The Generic Query Designer is slightly reminiscent of the design feature in MS Access. This tool will likely become familiar to those of us who do not readily write direct SQL. I like to use it even when I could easily write the query, as it provides a good view of the layout of a data source, and an easy-to-understand visual idea of the relationships between the tables involved.

We will add the Sales Order Detail table, which contains data about transactions that have transpired with our Products. Although we could make this very sophisticated, for purposes of our overview we are really only looking at how to create subtotals and totals, and so we will not belabor the myriad other factors that would surely be involved in generating a production-quality report here, such as date ranges, filters and parameters, and other considerations. For purposes of our overview, we seek only to add a value with which we can practice summarization in the report.

3.  Click the Add Table button atop the Data tab.

The Add Table button is shown in Illustration 14.


Illustration 14: The Add Table Button

The Add Table dialog appears, from whence we can select from any tables appearing in our Data Source (specified in the Data Connection definition we performed in Part I).

4.  Click the Sales Order Detail table in the dialog to select it.

The Add Table dialog, with our selection, appears as shown in Illustration 15.


Illustration 15: Select the Sales Order Detail Table

5.  Click Add to add the selected table.

6.  Click Close to close the dialog and return to the Generic Query Designer.

The Sales Order Detail table appears, complete with an automatic join in place, as shown in Illustration 16.


Illustration 16: Generic Query Designer - with New Table in Place

We might, of course, not want the joins to be placed for us when we add tables, but in this case, there is no need to make any adjustments.

7.  Scroll down the length of the Sales Order Detail table, and click the checkbox to the left of the LineTotal column, as depicted in Illustration 17.


Illustration 17: Selecting a Table Column for Inclusion in the Dataset

This marks the column for inclusion in our dataset. We note that it appears immediately in the Grid pane underneath the Diagram pane.

8.  Click the Run button, shown in Illustration 18, atop the Data tab.


Illustration 18: The Run Button

The modified query executes, and we see the new results dataset appear in the Result pane below the Query pane. We can also easily discern the change in the SQL query, as I used "T's" to name the tables in the original query (a convention that many use within SQL), and left the standard name in place for the column that we added.

The Generic Query Designer, with all the various panes we are seeing, adds a dimensionality and utility that I have yet to see in the enterprise reporting tools that Reporting Services will soon replace in many organizations. The ease with which we can crystallize ideas in this little "lab" makes it highly useful - I sometimes work within it to test ideas when a standard "report" is not even in the cards. The functionality of MSSQL Server 2000's Query Analyzer appears to have been in the mind of the designers of this excellent tool; we will see far more of it in later articles.

Filtering the Dataset

Let's filter the dataset to make it smaller for purposes of reporting.

1.  On the Data tab, click the ellipses ("...") to the right of the Dataset Selector, as shown in Illustration 19.

Click for larger image

Illustration 19: Select the Ellipses ...

The Dataset dialog appears, as shown in Illustration 20.

Click for larger image

Illustration 20: The Dataset Dialog

2.  Click the Filters tab.

3.  Click the first row in the Expressions column.

A selector becomes enabled.

4.  Select the following from the options that are available in the Expressions selector:

=Fields!Category.Value

5.  In the Operator column of the same row, select the "=" ("equal") sign, if not already in place

6.  Select <Expression...> in the Value column of the same row.

The Expression Editor opens.

7.  Type the following word into the Expression list:

Clothing

We are specifying that we wish to filter the dataset to return the Clothing category only. Note that this is a filter of the dataset, and affects reports that rely upon the dataset; it is not a filter of the data source, as we shall see.

The Edit Expression dialog appears as shown in Illustration 21.


Illustration 21: The Edit Expression Dialog

8.  Click OK to accept the expression and close the dialog.

We are returned to the Filters tab of the Dataset dialog, which now appears as shown in Illustration 22.


Illustration 22: Dataset Dialog - Filters Tab

9.  Click OK once again.

The Dataset dialog closes, and we arrive at the Data tab of Report Designer.

10.  Click the Run button to populate the Result pane.

The Result pane is populated by the Dataset. We see not only the Clothing category, but all others from our initial query, as well. This is because, as I mentioned earlier, the filter lies between the Dataset (what we see in the Result pane) and the report.

11.  Click the Preview tab to process the preview of our report.

Processing begins, and data begins populating the report even as processing is occurring. We see that the Clothing category is the only category returned in the report, as partially depicted in Illustration 23.


Illustration 23: Report Preview (Partial View)

Our understanding of the filter operation is confirmed.

12.  Click the Layout tab to return to the Design Surface

Our new data column, Line Total, appears for selection in the Fields pane, as shown in Illustration 24.


Illustration 24: Line Total Joins the Field Selection

We are now ready to add the Line Total field to our report. But first, let's save our work to free up system resources, as well as to avoid any mishaps.

13.  Select File --> Save RS02_Authoring.rdl As ... from the main menu.

14.  Navigate to the location of choice in the Save File As dialog.

15.  Name the file as follows:

RS03_Authoring.rdl

We will add a new data field and a subtotal next.

 

Modifying the Report - Adding a Data Field, Subtotals and a Total

We added a column to the report in our practice in Part I. We need to repeat that action to create a place to put our new Line Total field.

1.  Right-click the gray / blue column handle atop the List Price column.

2.  Select Insert Column to the Right from the context menu that appears.

A new, empty column appears on the far right of the table.

3.  Click Line Total to select it in the Fields pane.

4.  Drag the selection to the Details row (between the header and footer row we created, and in the same row occupied in the other columns).

5.  Drop the selection to place it in the new column.

The new column appears in our table.

6.  Click the Preview tab, once again, to review the results of our work.

Processing begins, and the Preview appears. It includes our new data field, as partially represented in Illustration 25.


Illustration 25: Preview with New Line Total Column (Partial View)

Now that we have a numerical value that makes sense, from a business perspective, to summarize, let's create an aggregate of the Line Total values at the Subcategory and Category levels.

7.  Click the Layout tab, once again, to return to the table layout.

8.  In the third column of the table, second cell from the top, type (or cut and paste) the following:

=Fields!Category.Value & ":   " &Fields!SubCategory.Value

We are simply creating a title, a concatenation of Category and SubCategory, to appear at each "break" in SubCategory group. We are placing this in the Group Header for SubCategory, which serves as a convenient temporary place for such a heading - we will move it later.

9.  Right-click the fifth cell from the top in the Line Totals (the right-most) column (the cell corresponding to the row below the already populated Details row).

10.  Select Expression from the context menu that appears.

The Expression Editor appears once more.

11.  Expand the Datasets item in the Fields list at the left of the dialog.

12.  Expand AdventureWorks2000 immediately under the expanded Datasets.

13.  Select Sum(Line Total) in the items that appear under AdventureWorks2000.

14.  Click Insert to add the Sum(Line Total) item in the Expression list on the right.

15.  Remove the comma after Line Total.Value, together with "AdventureWorks2000" from the expression, so that only the following remains:

=Sum(Fields!LineTotal.Value)

The Edit Expression dialog should appear in the Expression list as shown in Illustration 26.


Illustration 26: The Edit Expression Dialog with Our Expression

NOTE: The above might just as easily have been typed in; this is only an attempt to show that the Expression Editor can be used to create the expression, as well.

16.  Place the same expression in the two cells below the cell we have just populated (that is, the sixth and seventh rows of the Line Totals column).

The expressions can be cut and pasted, typed, or created using the Expression Editor, as we have seen in the above steps. We are simply specifying that we wish to add totals for the Line Totals value, to appear in the footers for the groups SubCategory and Category, as well as in the footer for the report (the bottom row). The expression for the totals is the same because Reporting Services will return the respective totals based upon the context of their placement.

Once the two additional cells are populated, the table should resemble that shown in Illustration 27.


Illustration 27: The Table with our Subtotal and Total Expressions Added

Let's check our work with the built-in Preview function once again.

17.  Click the Preview tab.

Once the report preview has processed (we can see the page counter at the top of the Preview tab - it should reach approximately 405 pages), we will "fast forward to the last page. Here we will be able to see all three totals (the Subcategory and Category subtotals, and the report total), in one convenient place.

The page counter (the Last Page selector button appears circled), is depicted in Illustration 28.


Illustration 28: The Page Counter, with Last Page Button Circled

18.  Click the Last Page button to go to the final page of the report.

19.  Scroll to the bottom of the last page.

The report should resemble that partially depicted in Illustration 29. (You may need to compact the columns on the Layout to condense it to a similar view).


Illustration 29: The Last Page of the Report (Partial, Compacted View)

Now let's clean up the appearance a bit with some formatting.

Formatting and Setting Properties

A point that I constantly make within the context of reporting engagements, as well as enterprise reporting classes and workshops that I give on a recurring basis, is that we are best served by resisting the temptation to format reports until the data is largely in place. This is because formatting is more efficient when performed as a single step; early formatting, such as setting column widths or establishing numerical formats, often has to be reworked as the report evolves, and a myriad of small steps, although easily accomplished at various times on their own, add significant time to the creation of the information product.

Let's perform a few formatting steps here, if only to provide an overview of their accomplishment within the Authoring phase. Keep in mind that we will delve much further into more elaborate concepts (such as conditional formatting) in articles where it makes sense to do so, and that we are only inspecting a sample of features in the overview articles. It should be evident with only casual perusal that the Properties dialogs that we examine (among many others) contain advanced features that meet and exceed those that are available in the enterprise reporting packages that have dominated the market to date.

NOTE: Expect to hear the shrill voices of the marketing arms of the dominant enterprise reporting vendors attempting to trivialize Reporting Services as "a low end solution." In this, as in myriad other features we will cover throughout our series, one can see readily that the capabilities of Reporting Services easily meet, and often exceed, their offerings.

This series will focus on these scenarios in future articles, and show how Reporting Services can match - indeed, overcome - the offerings of the "Big Sisters" in all material respects. My opinion is that, once the marketing arms of these outfits realize that the public is capable of performing revealing functionality comparisons, the next mantra will be that we need their products for "specialized" functions - in addition to Reporting Services. This will be the indicator that the inevitable "boutique" or "add on" play that I have forecast has begun in earnest.

1.  Click the Layout tab to return to the Design Surface.

2.  Right-click the cell containing the Line Total in the Detail row of the table (the middle row, fourth from the top, in the Line Total column).

3.  Select Properties from the context menu that appears, as shown in Illustration 30.


Illustration 30: Select Properties from the Context Menu

The Textbox Properties dialog appears.

4.  Select the Standard radio button, as appropriate, on the right side of the dialog.

5.  Select Number in the left list box underneath the radio button.

6.  Select the following from the number format options that appear in the right list box:

1,234.00

The Textbox Properties dialog appears as depicted in Illustration 31.


Illustration 31: The Textbox Properties Dialog for Line Total - Detail

While we will not be using the Name box in our practice in this article, we will see in subsequent sessions how we can name each component of the report for numerous purposes, and can reuse formats and other properties both within other reports as well as programmatically - all with great ease and efficiency.

7.  Click OK.

8.  Select the Properties dialog for the Line Total subtotal in the SubCategory Footer (created when we grouped on Product SubCategory earlier), the cell below the Line Total - Detail cell we formatted above.

9.  Select Standard: Number and the associated format exactly as we did for the previous cell.

10.  Click the Advanced button at bottom left in the dialog.

The Advanced Textbox Properties dialog appears, defaulted at the General tab.

11.  Click the Font tab.

12.  In the Weight selector, select Semi-bold.

13.  In the Decorations selector, select Overline.

The Advanced Textbox Properties dialog appears as depicted in Illustration 32.


Illustration 32: The Advanced Textbox Properties Dialog for Line Total - SubCategory Footer

14.  Click OK to close the Properties dialogs.

15.  Select the Properties dialog for the Line Total subtotal in the Category Footer (created when we grouped on Product Category earlier), the cell below the Line Total - SubCategory Subtotal cell we formatted above.

16.  Select Standard: Number and the associated format exactly as we did for the previous cell.

17.  Click the Advanced button at bottom left in the dialog.

The Advanced Textbox Properties dialog appears, defaulted at the General tab.

18.  Click the Font tab.

19.  In the Weight selector, select Semi-bold.

20.  In the Decorations selector, select Overline.

21.  Click OK to close the Properties dialogs.



Property settings are the same as for the previous textbox. Let's make them slightly different for the Report Total, so as to make its identity readily apparent to the information consumer.



22.  Select the Properties dialog for the Line Total total in the Report Footer (a standard feature of the default table layout), the cell below the Line Total -Category Subtotal cell we formatted above.



23.  Select the Standard radio button, as appropriate, on the right side of the dialog.



24.  Select Currency in the left list box underneath the radio button.



25.  Select the following (default) from the number format options that appear in the right list box:



$1,234.12


The Textbox Properties dialog appears as depicted in Illustration 33.




Illustration 33: The Textbox Properties Dialog for Line Total - Report Footer

26.  Click the Advanced button at bottom left in the dialog.

The Advanced Textbox Properties dialog appears, defaulted at the General tab.

27.  Click the Font tab.

28.  In the Weight selector, select Bold.

29.  In the Decorations selector, select Underline.

The Advanced Textbox Properties dialog appears as depicted in Illustration 34.


Illustration 34: The Advanced Textbox Properties Dialog for Line Total - SubCategory Footer

30.  Click OK to close the Properties dialogs.

Now let's take a look at an alternative means of working with properties, as we handle the formats for our column headings.

31.  Click the row handle (the blue / gray area to the left) for the top row in the table layout.

The Properties Window for the row should appear in the bottom right corner of the development environment. If it does not, click the Properties Window icon (see Illustration 35) in the top toolbar to cause it to appear.


Illustration 35: The Properties Window Icon

32.  Expand the Font item, located in the top Appearance section of the Properties Window, by clicking the "+" sign to the left of the Font label.

Font expands to reveal the settings of the components that make up its settings summary line.

33.  Click the selector to the right of the FontWeight property to enable it.

34.  Select Bold.

35.  Click the selector to the right of the TextAlign property to enable it.

36.  Select Center to center the Category heading.

37.  Click the selector to the right of the TextDecoration property to enable it.

38.  Select Underline to underline the column headings.

The Appearance section of the Properties Window appears as shown in Illustration 36.


Illustration 36: The Appearances Section of the Properties Window - Our Settings Circled

The Properties Window provides an excellent option for handling the formatting and other properties of rows, columns and the overall report.

39.  Click the third cell from the top in the Product column, where we have placed the concatenated "label" we created earlier for the "breaks" between the various SubCategories in the report.

40.  Drag the label to the left, and drop it in the first cell of the same row (actually the Subcategories group header).

41.  Right-click the cell.

42.  Select Properties from the context menu that appears.

43.  Click the Advanced button on the Textbook Properties dialog that appears.

44.  Click the Font tab on the Advanced Textbook Properties dialog that appears.

45.  Set Size at 11 pt.

46.  Set Style at Italic.

47.  Set Weight at Bold.

The Advanced Textbook Properties dialog appears as depicted in Illustration 37.


Illustration 37: The Advanced Textbook Properties Dialog for the SubCategory Label

48.  Click OK to accept settings and close the Properties dialogs.

49.  Select the cell immediately below the newly formatted label (the fourth cell in the first column).

The selection appears as shown in Illustration 38

Click for larger image

Illustration 38: Select the Category - Detail Cell

50.  In the Properties Window, Appearance section, expand the Visibility property.

51.  Select True in the selector to the right of the Hidden property.

The Visibility property setting of the Properties Window, Appearance section appears as shown in Illustration 39

Click for larger image

Illustration 39: Setting Cell Visibility to Hidden

52.  Click the cell in the Detail Row of the Subcategory column (shown in Illustration 40), immediately to the right of the cell for which we just set the Visibility property.

53.  Set the Hidden property for this cell to True, as we did with the previous cell.

We have now hidden redundant data fields that clutter the report. Let's add SubCategory and Category total labels.

54.  Click the fifth cell down in the first (Category) column.

55.  In the Properties pane that appears in the bottom right corner of the development environment (yours may be in a different place; if it does not appear, click the Properties button to call it), type the following into the Value box, in the Data section of the Properties pane:

=Fields!SubCategory.Value & "  SubCategory Total:"

56.  Expand Font in the Appearance section of the Properties pane.

57.  Click the Font tab on the Advanced Textbox Properties dialog that appears.

58.  Select Bold in the Weight property selector.

The affected sections of the Properties pane appear as shown in Illustration 40.


Illustration 40: Modifications in the Properties Pane

59.  Click the sixth cell down in the first (Category) column.

60.  In the Properties pane type the following into the Value box, in the Data section of the Properties pane:

=Fields!Category.Value & "  Category Total:"

61.  Expand Font in the Appearance section of the Properties pane.

62.  Click the Font tab on the Advanced Textbox Properties dialog that appears.

63.  Select Bold in the Weight property selector.

Let's add a Report Total row label as the last modification of this overview.

64.  Right-click the bottom cell in the first (Category) column.

65.  Select Properties from the context menu that appears.

66.  Type the following into the Value box of the Textbox Properties dialog that appears next:

Report Total:

67.  Click the Advanced button.

68.  Click the Font tab on the Advanced Textbox Properties dialog that appears.

69.  Select Bold in the Weight property selector.

70.  Click OK to accept settings and close the Properties dialogs.

Let's take a look at the effects of our work within formats and other properties.

71.  Click the Preview tab.

72.  Once the report preview has processed, click the Last Page button once more.

The Preview of our report, at the bottom of the last page, appears similar to that partially depicted in Illustration 40.


Illustration 41: Preview - Last Page of Report (Partial View)

Note that some of the format features may not be reflected as clearly in the on-line preview as in paper printouts, and that various displays can also have an impact. In addition, column widths might require adjustment to make the report more compact. This is easily accomplished with a quick return visit to the Layout tab.

There are a multitude of additional formatting and properties features, many of which we will encounter as we journey through the MSSQL Server 2000 Reporting Services series together. For now, we will conclude our efforts and save our work.

73.  Select File --> Save RS02_Authoring.rdl As ...

74.  Type the following into the File Name box of the Save File As dialog:

RS03_Authoring.rdl

75.  Click File --> Exit, when desired, to leave Visual Studio 2003 .NET.

Summary and Conclusion ...

In this article, we picked up where we left off in Part I of our Reporting Services Authoring phase overview. We undertook the remaining steps of our initial walkthrough of the Authoring phase, and completed many steps within our original practice example. Rejoining the tabular report from Part I, we made numerous modifications and enhancements to the layout of the report, and then filtered the dataset. We next performed grouping and sorting within the table, and then added subtotals and a total, using the group and report footers as points of placement. Finally, we set illustrative formatting and other properties, to introduce the huge population of options for increasing usefulness and appearance of a report within Reporting Services.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved