Building Your First Report


Sometimes, it's best if you just get a quick drive-by look at someplace new before getting out and walking through all of the buildings, streets, back alleys, and sewers. To that end, let's walk through the process of creating a sample local[7] report using the new Visual Studio 2005 report tools. Nope, you don't need SQL Server (any version) to get this to workbut it won't hurt. You will need a source of data, though. It could be almost any data sourcea flat file, a business data object, or a database tableeven if it comes from Oracle. In this case, I'll use something exotic: the SQL Server sample "AdventureWorks"[8] database.

[7] The ReportViewer control can host either local (client) RDLC reports or address remote (server-hosted) RDL reports.

[8] This database is on the DVD and is also available as a sample database installed with SQL Server 2000 and 2005. The version on the DVD has a number of additional stored procedures referenced in some of the reports.

To set up the sample report application:

1.

Start Visual Studio 2005.

2.

Create a new project. I'm going to stick with a Windows Forms application, but this works about the same in an ASP application. I'm using Visual Basic .NET and have named the initial project prjSampleVSReport.

3.

Your project starts with a blank form (as expected) and a nearly empty project explorer.

4.

Right-click on the project (prjSampleVSReport, in this case) and choose "Add | New Item". Choose "Report" from the Add New Item "installed templates" list. A bit later, I'll show you how to add your own custom reports to this list. Before you click Add, name your report's .RDLC file. I'm going to call this report "ProductByProfit", as shown in Figure 14.5.

Figure 14.5. Adding a new report to the project.


Creating a Report Data Source

In the following few steps, I'll use the DSCW to create a Data Source I can use to populate the report items. I need to build and configure the report before I attempt to "bind" the report to the Form or ASP pageif I don't, Visual Studio won't help us by generating the needed TableAdapter fill method.

1.

I don't have a report wizard to prompt us to set up a source for the report data, so I need to add that to the project one way or another. As you'll see later, there are a number of ways to retrieve the rowset used to populate the report. For this example, I'm going to use the new Visual Studio 2005 Data Source Wizard.

2.

Click on the Data menu and click "Add New Data Source". Yes, this is the same technique I discussed in Chapter 7, "Managing Data Tools and Data Binding," so I won't be showing much more detail here.

3.

For this example, I used the Data Source Configuration Wizard (DSCW) to access a Database table in the AdventureWorks database (the sample database that ships with SQL Server).

4.

Choose the Product (Production) table (under Tables), but choose only a few of the columns: Name, Color, ListPrice, and StandardCost. Before you leave this dialog, I like to rename the DataSet name to a shorter nameit just makes it easier on everyone. I called the DataSet "AWDataSet", as shown in Figure 14.6.

Figure 14.6. Choosing columns from the Products(Production) table.


To "deselect" all of the columns, click on the "Table" icon again. This deselects the table and the selected columns. Next, select just the columns needed.


5.

Click "Finish" when you're finished choosing columns for the report rowset.

Yes, this interface is kinda crippled, as I generally return report rowsets from a server-side JOIN and not just a single table. There are a couple of easy solutions to this problem: Create a View or stored procedure that returns the right SQL project. Remember, later on in this chapter, I'm going to show you how to write your own code to generate report rowsetsincluding those that require a parameter or two to focus the WHERE clause on fewer rows. As it is, this query returns all of the Products (Production) tablenot good.

I still need to make a change to the generated TableAdapter so it returns the right values for this report. I know you can create a table-dump query (as I've just done), but there are very few situations where this makes sense, so let's step into the TableAdapter and tune up the Fill, so it returns the data I need to start withat least, to a limited extent.

6.

From the Project Explorer, choose the AWDataSet.xsd file and double-click to open it. As I discussed (several times) in Chapter 7, I need to customize the query so it returns an expression from the data I've fetched.

7.

Right-click on the "SQL" icon in the Product table property dialog in the TableAdapter XSD designer. Navigate to the query builder and change the SQL to include an expression to return Profit (ListPrice-StandardCost), as shown in Figure 14.7. Click OK in the Query Builder and Finish to complete the changes to the TableAdapter. Behind the scenes, Visual Studio regenerates the strongly typed TableAdapter to incorporate these changes.

Figure 14.7. The altered SQL query to return and sort by the "profit" expression.


At this point, the Data Sources window should show the new expression-based column, as shown in Figure 14.8. Note that the process of adding a new column could be handled directly by the BIDS Data dialog without changing the report row source query.

Figure 14.8. The Solution's Project Explorer showing the new Data Source.


Laying Out the Report

I'm now ready to lay out the report and program the RDLC. No, that does not mean you need to learn XML and RDL so that you can enter the arguments directly, unless you really want to. This process is very much like any other report or RAD development tool you've ever used, but it's generally easierthere are far fewer "controls" to choose from.

1.

Click on the ProductByProfit.rdlc report work surface and lay out the report elements. I'll need a TextBox and a Table report item. Double-click or drag-and-drop these items from the Report Items toolbox (as shown in Figure 14.9) to the report design surface.



Figure 14.9. The Report Items toolbox.


2.

Align the TextBox and Table items as shown in Figure 14.10.

Figure 14.10. Add a TextBox and Table item to the report design surface.


3.

Select the TextBox item and enter the title of the report. Yes, I use TextBox report items to display headings and other information that's not (generally) data-driven. Sure, you can write an expression that feeds any report item in a variety of ways. In this case, I set the Text property of the TextBox report item placed at the top of the report (the report heading) to "Products By Profit".

4.

Use the Report Formatting toolbar (shown in Figure 14.11) to decorate and embellish the TextBox font. In this case, I centered and set the text and background color. The properties for each report item can be set using the Visual Studio IDE's property setter dialog, via right-click custom property pages or via the report-centric toolbars.



Figure 14.11. Setting attributes with the Report Formatting toolbar.


5.

Next, I'll populate the Table report item. The Table report item is described in infinite detail in our Reporting Services book. Suffice it to say that the Table report item is a data repeater that's designed to display data from each row in the report rowset and provide a detail row (at least) and (optionally) a header and footer row that's populated once before the first row and after the last row. Sure, it's possible to set up multiple groups of data, as I'll illustrate (briefly) later.

IMHO

Remember, the best source of in-depth report layout and design information can be found in our Reporting Services book.

6.

Since we'll need an extra column for the Profit values, right-click on the last column header (the top colored border) in the Table report item and choose "Insert Column to the right".

7.

Resize the columns so they fit under the heading TextBox report item. Don't worry about being too precise about alignment at this point. You'll undoubtedly need to resize the columns later once you get a look at the first report prototype.

8.

Open the Data Sources window so it can be used to drag elements to the Table report item. Drag the Product Name icon to the first column, middle section of the Table report item. The Table report item should fill in the column name into the Header cell (top row) and an expression to reference the Name column in the middle (Detail) cell, as shown in Figure 14.12.



Figure 14.12. Populating the Table report item from the Data Source columns list.


9.

Repeat this process for the ProductNumber, Color, and Profit columns. Your report layout should look something like Figure 14.13.

Figure 14.13. The report layout after all columns are placed into the Table item.


10.

Yes, you can embellish the column formatting at this point. You'll probably want to set the formatting for the Profit value cell to "C", center the column headings, or perhaps decorate the header row fonts. Of course, this assumes that the report will always run on a computer where the installed regional currency is the same or where you've set the appropriate language properties of the report item. If this is not the case, you need to set the language property of the report TextBox item. To do so, select the cell or entire row and navigate to the properties page or one of the report-formatting toolbars and select the appropriate options.

Nope, you're not ready to "run" the report yet. You have not hooked up the ReportViewerthat comes next.


Configuring the ReportViewer Control

At this point, it's time to add the ReportViewer control to the Windows Form. Remember, this control interprets the RDLC file I just configured using the Report Designer "layout." It also expects the Data Source to be fully populated, so you need to ensure that the TableAdapter Fill method is executed somewherehopefully before you try to launch the report.

1.

Select the Windows Form tab in your project (Form1.vb [Design]). This exposes the Windows Form1 work surface and the Windows Forms Toolbox. Navigate to the Data section of the toolbox and double-click on the ReportViewer control. This installs the ReportViewer control on the Windows Form, as shown in Figure 14.14. Note that the default ReportViewer is too large to fit the form, so you'll have to make either one larger or the other smaller.

Figure 14.14. Adding the ReportViewer control to the Windows Form.


The ReportViewer control is the host for any report your application will expose to the user. This could be the RDLC report I'm building or an RDL report hosted on a Reporting Services server. Generally, this means the ReportViewer needs to be large enough to view the entire report workspace. You might try docking the ReportViewer in the parent Form, but that opens another can of worms. Let's not go there yet.

When you first access the ReportViewer control, the ReportViewer Tasks pane is opened. This gives you an opportunity to choose the report the ReportViewer control is to host. For this example, I'll simply point to the prjSampleVSReport.ProductsByProfit.rdlc RDLC report I already added to the project. As I discuss later in this chapter, you can also use the ReportViewer to address "server" reportsthose hosted on a Reporting Services 2005 SQL Server.

2.

Once you choose a report, the remaining dialog items in the ReportViewer Tasks are populated, as shown in Figure 14.15. In this case, I don't need to touch any of theseI recommend that you don't.

Figure 14.15. Choosing the report to host.


If you try to choose a report (an RDLC file) that does not have any report items, Visual Studio can't figure out what query needs to be executed to populate the report rowset.


3.

Next, I need to verify that the report Data Source is properly wired. By default, when you choose a report (an RDLC file) that has been populated with report items (as ours has), the Visual Studio report interface inspects the TableAdapter and determines which method needs to be used to populate the report rowset. It then adds a call to the TableAdapter Fill method to the Form1_Load method along with a RefreshReport method invocation, as shown in Figure 14.16.

Figure 14.16. The ReportViewer inserts a Fill method call into Form1_Load.


No, you're probably not going to want to leave this Fill method invocation or the Me.ReportViewer1.RefreshReport call here in Form1_Load. That's because it will (at least) slow down getting the program's initial form loaded and displayed for the first time. You'll probably want to move this generated code to a Button_Click eventbut you'll do that later. You might want to execute the query that constructs the DataTable in another thread to help performance, but that's fodder for another chapter.

Testing the Report

Nope, there is no "Preview" button on the Report Designer as there is in Reporting Services. This means the only way you can preview your report is to run the application that hosts the ReportViewer control. When you do, the code inserted into your project when binding the selected report to the ReportViewer control executes the Fill method and the RefreshReport method that renders the report. Try itclick on the debug "Run" button or press F5. You should now see something like Figure 14.17.

Figure 14.17. The first test run for the Products by Profit report.


Polishing the Report

No, this is not exactly a finished report suitable to show your bossunless you're looking for a new job. Let's step through a number of steps to make it closer to something that your boss might expect of a professional report developer.

1.

Return to the Form1.vb [Design] window and select the ReportViewer control. In the upper-right corner there is a tiny arrowclick on it to open the ReportViewer Tasks dialog. In this dialog, click "Dock in parent container". This permits the report to stretch with the Form as the user makes it larger.

Next, I want the products with the highest profit to be shown in the report first instead of last. This can be accomplished in a couple of ways. One approach is to change the initial query to sort the data on the Profit expression. This puts a bit more load on the data source server but means that the client does not have to do the sort. Of course, the data source server might run the sort more efficiently, as it can leverage existing indexes. On the other hand, once the subset rowset arrives, it's pretty easy for the client to sort (or resort) the rowsetespecially if there are very few rows to sort. Let's sort the data here on the clientright in the report definition. For an interactive report that spans only a few pages, this makes a lot more sense performance-wise. As the rowset increases, it's going to be increasingly more expensive to sort the data, regardless of whether it's done on the client or the server.

2.

Return to the ProductByProfit.rdlc [Design] window (the report layout design surface). Here is where you can change the sort order of one or more report columns and set other expressions to make the report display a more professional appearance. Yes, these sort specifications are persisted in the RDLC report definition.

3.

Click-select the Table report item and right-click to open the Table Properties dialog, shown in Figure 14.18. Note that I've also filled in the Tooltip property. This and the other Table properties can also be set via the traditional Visual Studio Property page, but this dialog makes setting the Table report item properties easier.



Figure 14.18. The Table report item Properties page.


4.

Since I want to change how the rows in the Table Report item are sorted, click on the Sorting tab, which exposes a dialog as shown in Figure 14.19. The Sorting expressions listed here dictate how the data presented in the Table report item is ordered. You can have as many sort expressions as make sense. As I said, these sort operations are carried out by the ReportViewer on the client systemnot by the data source query engine. Choose the column(s) on which to sort the report data rowset. For this example, I chose the Fields!Profit.Value column. I also set the sort Direction property to Descending.

Figure 14.19. Choosing a field for the sort expression.


Note that the expression syntax for the report rowset (exposed by the TableAdapter, in this case) references an individual column as a "field." The DataTable columns are mapped to this Fields collection. The "bang" syntax (which uses ! to delimitate the Fields collection from its members) is a throwback to early Visual Basic DAO Field column syntax. Note that I'm referring to the Value property of the specific Field. In most cases, if you use the expression editor (which I'll show you many times later in this chapter), the Report expression editor UI generates the correct syntax for each column you need to reference.

Setting the Color Column Background Property

To make it easier for the report user to visualize the product color, you might want to set the background color of the "color" cell to a matching color. Okay, this is a bit silly, but it's an easy way to illustrate how to set expressions on specific report item properties. Keep in mind that these customizations often don't "cross-render" very well. That is, they might look cool in a Windows Forms application, but when rendered in PDF, they look like the stuff left on the carpet by your new puppy after she's eaten the cat food (again).

1.

Select the Table report item "color" cell and press F4 to open the Properties dialog for this cell. Notice that the "cell" is exposed as a TextBox report item hosted in the Table report item. Navigate to the BackgroundColor property. The default setting is "Transparent", which simply means "no color" (kinda like "null", but not really). As discussed in depth in our Reporting Services book, "Transparent" means that the BackgroundColor tag is not generated and the color of the item takes on the color of what's behind it.

2.

I'm going to set this property using an expression, so click on the property and choose "<Expression...>" from the list of available colors. This opens the Edit Expression dialog shown in Figure 14.20.

Figure 14.20. Editing the BackgroundColor property expression.


Using the Expression Editor

The Edit Expression dialog exposes an easy way to construct expressions for most of the report item properties. As your report-development skills get honed, you'll visit this dialog quite frequently. In this case, I replaced the existing expression ("Transparent") with a reference to the Color Field returned by the query. Because the cell whose property I'm editing is in the "details" row of the Table report item, the ReportViewer control interprets these expressions at runtime on a row-by-row basis. Other property expressions can be applied to the entire report, just the header, just the footer, or other specific elements.

Think of the expression as the value in an assignment statement such as:

TextBox. BackgroundColor=Fields!Color.Value


I encourage you to explore the other constants, Globals, Parameters, DataSets, Operators, and Common Functions. This will help you understand what can be selected as component building blocks to build your expression. Sure, the expression can be fairly complexbut be careful. Although the code is syntax checked, it's not "precompiled," so reference errors won't show up until runtime, when the report needs to interpret the expression and actually access the elements.

If you jump the gun and try to test the report at this point, you'll immediately discover that you're not done polishing. Consider what happens when you use a black font on a black backgroundit's kinda black. Because of this, you need to add another expression to the cell Color property to set the font color to white when the color is "dark," like black or blue. I'm going to let you figure out how to get to the right property expression, but I'll give you a hint as to the expression in Figure 14.21.

Figure 14.21. Setting the Color property expression.


The IIF construct is widely used in report expressions but can be problematic if you aren't careful. It simply returns the first value if the expression is True and the second if the expression is False. In this case, I've doubled up the expression, so it reads "If the column Color data value is black, use white (as the font color), else if the column Color is blue, use whiteif it's neither, use black". Make sense? I discuss how to construct complex expressions in the Reporting Services bookincluding how to build "code-behind" modules that can return values using far more complex code.

It's entirely possible to make changes to the RDLC report definition via the report layout designer while the application is being executed. No, these changes are not applied until the ReportViewer recompiles the RDLC when first executed.


Buffing the First Coat of Polish and Retesting

Before testing the report, I made a few more changes to further polish the report. You have enough instruction at this point to make these changes yourself:

  • Set the background color of the Table report item header row to an attractive color.

  • Set the header row TextBox and Color cell font bold property to True.

  • Resize the initial Form so it's large enough to display the report.

  • Resize the Table report item columns to more closely match the size of the data returned.

Once these changes are made, re-run your application and preview the new report. If you've been following along, your report should look like Figure 14.22. This report looks like it's about ready to send to your boss. No, this process did not take that long, so you might want to pretend you're working hard on making the report right before turning it inif the boss thinks the process is too easy, he might think he can do it himself.

Note that this report has been "zoomed" to 75% by selecting the zoom level drop-down at runtime. This option permits you to see a report rendered based on the size of the target page width, the whole page, or stepped zoom levels from 500% to 25%.

Figure 14.22. The polished report zoomed to 75%.





Hitchhiker's Guide to Visual Studio and SQL Server(c) Best Practice Architectures and Examples
Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)
ISBN: 0321243625
EAN: 2147483647
Year: 2006
Pages: 227

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net