SQL Server Reporting Services -
Lessons Learned In Development and Deployment
By Bill Jones, Jr.

Printer Friendly Version

Bill Jones

Purpose

The primary goal of this article is to communicate from "tech to tech" so that developers will be able to quickly build on what we have already learned.  We walk through creating a report from the Reporting Services sample database to provide a framework for sharing what has been learned.

We make liberal use of screen shots so this report can be digested without using a computer. However, the maximum value is gained by walking through the examples provided on your own machine. We end the report with a "grab bag" of any technical tips we failed to cover in the various examples.



Conventions

Screen shots - While this is a picture book, screen shots are never more than the active window, and often present just the region of interest.

Code Snippets - are presented as follows:

Session("ReportID") = <report ID integer goes here>

Response.Redirect("ViewReport.aspx")

Tech Tips: Information that is particularly important or hard won is presented in this fashion so as to call attention to it. Hopefully, no one who follows will have to learn these items for themselves.

Common Reporting Services Deployment Diagram

SQL Server 2000 Reporting Services relies on several major components to function correctly. This diagram shows the interrelation of the various parts required to develop, deploy and serve up a report using Reporting Services (RS). Report Manager (RM) is used to administer the RS web service and can be used to serve up reports from a folder / list interface. RM is actually a web application that calls the RS web service.

Client Workstation: This is the desktop or laptop used to present reports. These machines access the IIS Server hosting the production web site and as shown here, activate particular reports through the application user interfaces. For example, in our application, the client can select a particular report entry form a list, click on a link and the report can be served up as a PDF download or an HTML page depending on web.config settings.

Tech Tip: Heavily formatted reports produce significantly smaller file sizes in PDF than in HTML.

IIS Server: These machines host the production web sites for applications. The Report View pages use the Report.vb class to interact with the Reporting Services web service. Either HTML or PDF can be rendered depending on web.config settings.

The Report.vb class encapsulates the interaction between the report Viewer ASPX pages and Reporting Services. The class is application independent.

IIS, Reporting Services and Database Server: This machine hosts SQL Reporting Services and SQL Server 2000.  The Reporting Services web service and the Report Manager Web application both run here. This is the server targeted by Visual Studio.NET for deployment in the properties of a report project. It is often the server linked into a report project as a web service provider; although in our View Pages the web service provider is a "web.config" setting.

Database Server: This machine hosts the SQL Server 2000 databases required to persist application information. Reports can be drawn from SQL Server, ODBC or Oracle data sources.

Development Workstation: This represents the programmer's workstation. At least the Report Design tools are installed here. If the machine hosts IIS, then a development Report Server may also be installed. This machine can preview and print reports as part of the development cycle. Unfortunately, the preview tool is a bit more precise than either HTML or PDF rendering.

Tech Tip: Make sure the Report Project properties are set to never overwrite data sources. Also uncheck the deployment option in the Configuration Manager so that RDL files are not pushed to the report server every time a report viewer project in the same solution is run in debug.

Visual Sourcesafe Server: By professional convention, all source code must reside in a repository. Sourcesafe works as well for RDL and RDS files as it does for any other file type hosted in Visual Studio.NET. Source code and report formats should be posted "early and often".

Installation

Rather than catalog all the installation issues we waded through with an unstable development workstation, let us suggest that you use the Google index into the newsgroup if you have an install problem. We have had good success installing on clean development boxes. We have had good results on installing on our servers. We even got the "2nd hop" installation working under Kerberos - that's where RS resides on one box and the RS databases reside on another box. The issue is getting RS to pass along your authentication on the "2nd hop" to either the RS support database server or to your data sources on yet other instances of SQL Server 2000.

We installed the beta edition on several development workstations and at least two servers without incident. We even managed to get Reporting Services and Report Manager running on an IIS server while hosting the Reporting databases in SQL Server 2000 on another machine. Judging from the traffic on the newsgroup, that is no small feat. Our performance experience with that split, or "2nd hop", installation scenario recommends against it. We were taking 30 seconds or more to render reports from our applications that were rendering in 5 seconds or less when previewed in Visual Studio. By putting Reporting Services on the same box with SQL Server 2000, as strongly encouraged by the Microsoft licensing policy, we got a dramatic increase in performance. After a JIT compile, our web application now renders reports at least as quickly as the Preview in Visual Studio.

Again, we had several successful installs. We even had some very successful upgrades from beta to the production release, except for one. One of our development workstations failed to start Reporting Services on the beta install. We figured out that the web service would not support an underscore in the machine name, so we changed the machine name. That and some serious research into rsConfig.exe and rsActivate.exe got the beta going, but ended up making the upgrade to the production bits impossible. Seems the installer uses the machine name to generate user IDs like IWAM_machinename and IUSR_machinename. We got out of synch on those by renaming the machine. RS must also have a reliable ASPNET User ID defined. Microsoft Knowledgebase articles 306005 and 325093 are excellent resources for getting your ASPNET user ID back.

Now that we have consolidated all our RS activity onto one box, life will be less complicated. However, this document configures data sources like we did for the "2nd hop" scenario, as it works well for the consolidated scenario also.

Books on Line (BOL), Wizards and Other Training Wheels

The Books on Line (BOL) that comes with Reporting Services are excellent. Use them. After you think you know what you are doing, use them some more. Here's the link to the new BOL - also available for download:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPORTAL/HTM/rs_gts_portal_3vqd.asp

BOL is by far the best place to dive in. The last two major sections in the BOL are the How-To followed by the Samples and Walkthroughs. Install ALL the report samples somewhere and then go through ALL the Walkthroughs. Sample Reports are not installed by default. You have to select the option. After installation is complete, navigate to http://yourserver/ReportServer and test the web service. The rather stark page should end with something like:

Microsoft SQL Server Reporting Services Version 8.00.743.00

Next, go back to Windows and navigate down to the Reporting Services\Scripts directory. If you took the default path, it will be under MSSQL in your SQL Server directory. Looking in Scripts you find a RaedMe.htm and a couple of scripts. Follow the instructions in the read me file and publish the sample reports. Finally, navigate to http://yourserver/ReportManager. If you see a folder named "Sample Reports" with the "!NEW" indicator by it - your install is complete and Reporting Services is running "hot, straight and normal". Go through all the sample reports. Go ahead, we'll wait.

Are you done? Once you are, you will have a very good idea of what this tool can do. After that initial introduction, you can settle into a little cherry picking and go after specific solutions to specific problems. Don't forget to try the wizards. They can show you some tips and tricks you might otherwise miss.

The BOL How-To section is targeted to solve very specific problems. We don't go back as often as we should, but we do get some serious questions answered there. The Developer Samples have some things you can safely overlook at the beginning, but do go running back after you have begun to find your way around the product. The newsgroup, microsoft.public.sqlserver.reportingsvcs, has been invaluable. It is now cross-referenced on Google at

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=microsoft.public.sqlserver.reportingsvcs 

The newsgroup is always a good first stop for thorny questions. We've posted a couple of questions that didn't get much response, but that mostly centered on the installation horrors we had with an unstable development workstation. Usually you get a quick answer to get you back on track.


Create a Report

Since the best way to learn is by doing, we're going to step through attaching to a data source and using it to create a report. Along the way, we will discuss what to do and what not to do while using Reporting Services (RS). Bear in mind this is all based on what works for us. To borrow a phase from Jerry Pournelle, "Your mileage may vary. "

 Since you installed the Sample Reports, you also installed the sample database AdventureWorks2000. Rumor has it that NorthWind and Pubs are dead. Long live AdventureWorks2000. Let's have a moment of silence, please. Are we really ready to give up either NorthWind or Pubs? What about the "2000" in the name of the new demo database? Does that mean it will change with every new release of SQL Server? Oh well, to quote that great philosopher Marley Grey of Microsoft, "Change is good. "

Figure 1 - Enterprise Manager Local Databases

Jumping on the bandwagon, we will use the AdventureWorks2000 sample database as the source for our report. We will assume that like all good developers, you've dropped everything onto your development machine. Nothing is quite like owning the sand box where you play. Notice in figure 1 that we have the RS support databases living on the same server as our data. This may become an unusual production scenario as security considerations argue against keeping our data on a box running IIS. If you're running against a database across your net, then substitute your server name for our "localhost" in the following examples.

Create a Reports Project

Assuming your Reporting Services install went well, let's jump right into our first project. Visual Studio.NET is our window into the wonderful world of Reporting Services, so open that up. Notice the "Business Intelligence" entries that have been added to your New Project dialogue.

Figure 2 - Visual Studio New Project dialogue

You will want to come back here and use the wizard at your first opportunity. It is an excellent way to get a look at how reports can be designed to achieve various common effects such as grouping, summing or sorting. In this case, we're going to roll our own so we can introduce conditions that allow us to meet our objectives. We have named our new project "rsLessons" and we've chosen the "Report Project" template. By the way, see the tips and tricks section for a way to add your own templates (sort of) to this display.

Data Sources

Moving right along, now let's create a shared data source. We start by right clicking on "Shared Data Sources" folder shown in figure 3. You can click "Add a New Data Source" or you can click "Add".

Figure 3 - Shared Data Sources

Either way you're going to end up the same place. If you click "Add", it will just take you another click to get there. So let's click "Add a New Data Source" and bring up the ever popular connection wizard, Data Link Properties.

As you would expect, we enter "localhost" for the server, keep the "use Integrated Windows security" default - and all the other defaults for that matter - and then select our new favorite demo database, AdventureWorks2000. If "Test Connection" doesn't work, you really need to fix that before moving on.

Figure 5 - Connection Wizard

Looking back at your Solutions pane, you will now see "AdventureWorks2000.rds" displayed under the Shared Data Sources folder. If that name is too wordy for you, now is the time to rename it because in the next step we are going to put it to use.

Add a Report to the New Project

To add a report, right click on the "Reports" folder shown in figure 3. Your choices are "Add New Report", "Add", "Import" and "Properties". "Add New..." takes you to the report wizard, which is a good place to go, but not just now. "Import" lets you import reports from Microsoft Access. We are probably limited to Access XP and Access 2003 imports, since that is the only Office formats we can export. We also expect any third party tools, such as the Hitachi Crystal Reports importer to plug in here. "Properties" is presented for consistency, since all it exposes is the folder name.

Tech Tip: Hitachi is not trumpeting their Crystal conversion capability on their web site, but you can find a reference to it here: http://www.hitachiconsulting.com/supportingDocs/CaseStudies/SO-MS%20RDL%20Generator-electronic.pdf?GXHC_JSESSIONID=-1642622448127049876&

By the process of elimination we arrive at our menu choice, "Add". Actually we want to click "Add" and then "Add New Item" to get to figure 6.

Figure 6 - Add New Item . . . Report

As you can see, we select the "Report" item and we enter a name, "Lessons.rdl". This gets a blank report added to our project and brings us up in the Data panel in the Report Designer as shown in figure 7 in the next section.

Data

Now we are ready to add a dataset, but before we do that we need to add a couple of stored procedures. Assuming you know how to do that, we will just put the T-SQL code here for you to run against your AdventureWorks2000 database. You can do this through the Server Explorer panel, SQL Query Analyzer or with Enterprise "Mangler", your choice. However, the more we use the Server Explorer, the more we like it.

CREATE PROCEDURE uspGetSalesDtlByOrder

@SalesOrderID as int

AS

SELECT

D.SalesOrderID, D.LineNumber, P.ProductNumber, P. [Name] AS ProductName,

D.OrderQty, D.UnitPrice, D.UnitPriceDiscount, D.LineTotal

FROM

dbo.SalesOrderDetail D

INNER JOIN dbo.Product P ON D.ProductID = P.ProductID

WHERE D.SalesOrderID = @SalesOrderID

ORDER BY D.LineNumber

CREATE PROCEDURE uspGetSalesHdrByCust

@CustomerID as int

AS

SELECT

S. [Name] AS StoreName, O.CustomerID, O.SalesOrderID,

O.SubTotal, O.TaxAmt, O.Freight, O.OrderDate, O.DueDate,

O.SalesOrderNumber, O.TotalDue

FROM

dbo.Store S

INNER JOIN dbo.SalesOrderHeader O ON S.CustomerID = O.CustomerID

WHERE O.CustomerID = @CustomerID

ORDER BY O.OrderDate

Since we'll be living in the report designer for the next little bit, how about a quick tour using figure 7? Notice you have three tabs: Data, Layout and Preview. They function pretty much as they are named. We define datasets and view data grids here in the Data tab. Note that there can be multiple datasets defined for a single report. We use the Layout tab to drop controls and fields onto our report design surface.

We use the Preview tab to see how we're doing. Since we can preview without a Reporting web service specified or available, the Preview must use bits on our development box to render reports. That's interesting since the only way we know to render reports in production is using the web service. Wonder if we'll ever be able to use something like the DLL that produces that "Preview" capability to directly run the rendering engine in our Windows applications?

Figure 7 - New Dataset

Click on the Dataset drop down and take your only choice, "<New Dataset>". Once a dataset has been defined, you can edit it by selecting it in the drop down list and clicking on the three dots "..." next to the control. That brings up the Dataset dialogue shown in figure 8. We change the Name to "dsSalesHdr" or something that tells you this is the source for sales headers. We leave the Data source as is for now, although we will be back to click on those "..." next to the data source shortly. The command type comes up "Text" but we select "StoredProcedure" and then click "OK", which drops us back on the Data tab.

Figure 8 - Dataset definition dialogue

Figure 9 shows us dropping down the Stored Procedure selections, but it does NOT show the little trick we use to wade through the unending list of "dt" stored procedures in this database. Type "usp" in the combo box and then click to drop down the list. That jumps right to our two stored procedures, one for sales header and one for sales detail.

Figure 9 - Select Sales Header Stored Procedure

Now comes one of the nifty parts. This little gem figures out all by itself that our SPROC has a parameter. When we click on the red "BANG!" character just to the left of the label "Stored procedure", we get the pop up shown in figure 10, shortened here to save pixels (or trees). Notice how it just knows the parameter name? Do you suppose it can figure out the data type too? Before we check that out, let's type in 3 and click the OK button and fill our grid with data. We're using 3 here because we just know that gets us lots of rows. If your grid "doth not runneth over", we'll wait while you catch up.

Figure 10 - Dataset Parameters

Once you have all that glorious data, click on those three dots next to the dataset name "dsSalesHdr" to bring up the dialogue we saw in figure 8. This time click on the Parameters tab. Notice that our SPROC parameter is being satisfied with "=Parameters!CustomerID.Value". The "Parameters!" part tells us the Report object holds a collection of parameters. We can use that same nomenclature to access the parameters collection in expression inside a report. We still have an open question - what about data type? Let's make one more stop. Close this dialogue and click on the Report menu item. Take the first selection, Report Parameters, to see figure 11.

Figure 11 - Report Parameters data types

We're just looking at the part of the dialogue we need. We have not defined anything for a report yet. All we have done is connect up to an SPROC, provide a parameter and click OK to fill a grid. The report designer has been keeping up and it knows if our SPROC needs a parm it's going to have to come from somewhere, so it defined an integer Report parm. Now that's handy.

Tech Tip: Reporting Services has editions that correlate with the SQL Server edition you use to store the Reporting databases. We are using SQL Server Developer Edition here, so we loaded the Developer Edition of Reporting Services.

Figure 12 - Dataset Credentials

One more point needs to be made before we move on to the Layout tab. What if you can't access your database using Windows Integrated Security? Refer back to figure 7. Click on the three dots "..." next to your Dataset name in the combo control. That brings up the Dataset edit panel figure 8.  

Remember the ellipse button next to the Data source combo control? If you click that you get to a Data source edit panel. Since we are using a shared data source we need to click the "Edit..." button near the bottom of the Data Source panel to bring up the Shared Data Source panel. You can also get here by opening the Shared Data Source directly in the Solution Explorer.

One more click to select the Credentials tab gets us to the display shown in figure 12. We are using Windows Authentication, so that option is active. To provide a User name and Password, select the "Use specific..." option as is often he case with application databases. You have a choice to prompt for credentials. You can even select No credentials, but that is definitely not the politically correct thing to do these days unless you really believe the world is a friendly place.

Did you notice we had to click through a Data Source dialogue to get to the Shared Data Source dialogue we needed? As you probably guessed, we can define individual data sources for specific datasets if we wish. We do not recommend this approach. It hides your server path and complicates future maintenance. Coding specific data sources for specific datasets means you have to touch each dataset to use another server. That becomes a really big deal when you have ten or twelve sub-reports associated with a main report.

To recap, we have defined a data source, the Adventure Works 2000 sample database on the local host, where we are running SQL Server Developer Edition. We used our shared data source to create a dataset to retrieve sales header information via a stored procedure. We know everything is working because when we clicked "BANG!" after specifying the SPROC, we got lots of data.

Figure 13 - Dataset Fields

The payoff is shown here in figure 13. All this work hooking up to a dataset rewards us with a list of Fields, suitable for dragging and dropping onto our reports in the Layout tab.

Layout

Figure 14 - Layout

The Layout tab is where we live while designing a report. Notice the combo control at the top of the Fields panel that shows the dataset name "dsSalesHdr". When you have multiple datasets associated with a report, you control which fields list is displayed by selecting form this control. In figure 14, we see a divider labeled "Body". When we select "Headers" and "Footers", they are shown using a similar convention.

As you would expect, the dots represent the grid. The default is a bit coarse for me, so let's change it. You can select Report Properties from the Report menu or you can right click in the beige area outside the report body grid and select "Properties" from the pop up menu.

Figure 15 -Report General Properties

General properties tab comes up by default. Put your name in the Author box. Describe the report in the Description box. We have found changing Grid spacing to 0.0625 provides excellent control placement and alignment on the report and is coarse enough to be supported well by HTML, PDF and TIFF when rendering reports. The header and footer check boxes become enabled when you turn on headers and footers, just as you would expect.

Figure 16 - Report Layout Properties

Set the page size and the margins on the Properties Layout tab. The 1 inch default margins are a little generous for our taste.

Tech Tip: To print landscape, swap the Page width and Page height on this panel.

Figure 17 - Format Date Code Property

The Code Property provides a way to embed VB.NET functions directly in your report. You can type into Custom Code text box, but we don't recommend it. Attach a console project to your solution. Develop your functions in a module and test by using Console.WriteLine to return function results. Copy the tested function and paste it into your Report Code Property. Preface any calls to your functions with "Code. " to let the rendering engine know how to resolve the call. To format a date, you could use a call in a text box expression like this:

=Code.FormatDate(OrderDate)

The References Property allows you to establish linkage with .NET Assemblies and Classes. Judging from newsgroup traffic, successful deployment of assemblies and classes to the Report Server gets complicated by security issues. The Data Output Property controls XML data output options for the report. Now you know everything we know about Data Output.

So now you have put your name on the report, set your grid spacing to 0.0625 and changed all the one inch margins to a half an inch. Don't forget to stretch your report surface to seven and half inches to match your new margins. Add the code snippet in figure 17 to your Report Code Property as shown and we are ready to create a report.

Let's get started. Use the Report menu to turn on Page Header and Page Footer. Drag a text box from the toolbox to the Page Header.  Enter a title into the text box and set the font size to 14 and bold it. Your report should look something like this:

Figure 18 - Report Title

Now drop another text box in the lower right corner of the Page Footer. Right click in the text box and select "Expression..." to bring up the Edit Expression dialogue.

Figure 19 - Edit Expression

Type ="Page " & and then select PageNumber under Globals and click the Insert button. Enter & " of " & and then select TotalPages and click the Insert button again. After you click OK, right justify the text box and set the font size to 8. Unfortunately while you can use Globals in headers and footers, getting data fields cannot be done easily or consistently as it can in the Access Report Writer.

Next, drag a List control from the toolbox onto the report. Set properties as follows:

1.       Name: lstSalesHdr

2.       DataSetName: dsSalesHdr

3.       NoRows: No sales for specified customer.

Bring up the Fields panel and drag StoreName, SalesOrderNumber, DueDate and TotalDue to the list control. We can get away without a title on the StoreName, but we will need something for the other fields. There are two ways to approach this. We can enter the title as a string expression in the field's text box. For example, we could use the expression: ="Sales Order Number" & Fields!SalesOrderNumber.Value

That is a quick and easy solution but it does have one downside. Only one font setting can be applied to a text box at a time, so the title would have the same characteristics as the data. Obviously we can enter field labels in separate text boxes. That way we can use different font settings for titles and accentuate the data. Another consideration is that putting titles and data on the same row can cause some line up issues if the fonts are different sizes.

Now let's get a little fancy. We will use font size 10 - bold for data and font size 8 - normal for titles. To jazz it up a bit we'll put a solid border on the left side of both the titles and the data. We'll also put a solid border on the top of the titles.

Select all four of the fields you dropped on the list control and make them bold. Right click on the due date field, choose expression and enter the following to format the due date:

������� =Code.FormatDate(Fields!DueDate.Value)

Figure 20 - Layout of Sales Header List

Next, select the Total Due field, right click and choose properties. Under standard formats, set "Currency". See figure 20 for the layout to this point.

COMPOSE A Sub-Report

Since creating a sub-report is pretty much the same as creating a report, we are going to get a little wordy here and cut back on the pictures. Start by right clicking on the reports folder and Add / Add New Item. Select the Report template and name it "subrptDetail".

That brings us to the Data panel. Click the Dataset combo and choose "<New Dataset...>". Make the Name "dsSalesDtl" and set the Command type to "Stored procedure".  Click in the "Stored procedure" control that is added to the Data band. Enter "usp" to find our procedures in the list and then click the pull-down arrow. Take the "uspGetSalesDtlByOrder" SPROC. Click the "BANG!" and enter the value 8408 for the @SalesOrderID parameter. If you don't see lots of rows, we'll wait while you catch up.

Bring up Report Properties to set the author to your name, report description to "Sales Detail sub-report" and grid spacing to 0.0625. We can leave the rest of the report properties set to default values. The page width is at the default of six and a half inches. We'll leave it set to that value because the width of a sub-report is determined here and we want to make sure we can print within the seven and a half inch width of the main report.

Now drop a table control on the sub-report. Notice we get three rows marked Header, Detail and Footer. We also get three columns. We need to add some columns, but first we need to make more room on the report design surface. Drag the vertical column dividers to make each of the three default columns narrower. Right click on a column heading and insert two columns to either the right or the left, your choice. Be careful here. If you exceed our magic six and a half inch width, the report surface expands to accommodate your efforts. Now drag the Line Number field to the first column in the Detail row. Notice how the column header gets filled in automagically. Drag Product Name to the second column and Order Quantity to the third column. Fill out the row with Unit Price in the fourth column and Line Total in the fifth. We're taking a chance by not including the Unit Price Discount field, but this is a training exercise. Use the text box properties to set both Unit Price and Line Total to a currency format.

Right click in the left border of the table on the Header symbol and select "Insert Row Above" from the context menu. We will use this extra header row as a label. Select all the cells in this new header row. Then right-click and pick "Merge Cells". Enter "Order Detail" and then center justify it. While you're at it, set the font size to 8 and make it bold. Select the column header row and set the font size to 8, but leave the weight normal. Select the detail row and set the font to bold.

Now you're going to like this. Drag the Line Total field into the last column but in the footer row this time. Notice how it drops in as =Sum(Fields!LineTotal.Value)? Bold the Line Total footer cell and then size up all the rows and columns to your satisfaction. Finally, use text box properties to set the standard currency format.

Now select all the cells by clicking in the upper left cell, the Order Detail title, and shift-clicking in the lower right cell, the Line Total footer. Now go to Properties and click the "+" next to Border Style. Make the Left and Top borders Solid. Now select the first column and set the Left Border Style to None. Select the top row, the Order Detail cell, and set the background to light gray. With the table selected, set the RepeatHeader property to true. The table is selected when the control name at the top of the Properties panel is table1. Finally, move the table to the top of the layout grid and make sure it fills the entire six and a half inch width available. Your sub-report layout should resemble figure 21.

Figure 21 - Layout of Sales Detail

We glossed over it until this point, but one of the primary ways to gauge our progress is by using the Preview panel after each series of formatting changes or field additions.

Preview

Preview is as good as it gets. You can render, view and print a report in Preview.

Figure 22 - Preview sub-report

When you actually render the report using the Reporting Services web service, you may get slightly different results in HTML or PDF format than you see in the IDE. Look at figure 22 to see our sub-report in Preview.

Using a Sub-Report

Click on the IDE tab Lessons.rdl [Design]. Drag a Rectangle control from the toolbox and drop it on the list control. Use Properties to set the width to six and half inches and the name to something useful like "rctSrDtl". We started out naming everything, but got that out of our system before we finished our first report. Now we name container controls because we find ourselves going back to them. When you drop a field onto a report, the tool will generate a name corresponding to the field name. Unless you rename them, label text boxes are added with names like textbox1, textbox2, etc.

In a complicated report, you might need to choose a name in drop down list at the top of the Properties panel to get the control selected so you can move it or set some additional properties. We have also found it more successful to drop sub-reports into rectangle containers rather than dropping them directly on the primary report or list container. Resizing on render works better that way.

Figure 22 - Sub-report Properties

To get back to the sub-report, drag "subrptDetail.rdl" from the Solution Explorer to drop on the rectangle we just added to the list control. No, we have no idea why it drops in as a three inch square. We have never allowed a sub-report to take up three inches of vertical space in a report design. So let's resize it. Bump the sub-report to the upper left corner of the rectangle. Arrow keys seem to work best moving it. Shift-arrows resize controls. Make the sub-report six and a half inches wide and an inch high. Center it on the page.

Right click the sub-report and select "properties" from the context menu. On the General tab, set Name to srDetail. Click the Parameters tab and set SalesOrderID Parameter Name. Set the Parameter Value to =Fields!SalesOrderID.Value as shown in figure 22.

So, what happens here? The list control iterates through the sales header filling the text boxes bound to data fields. As each row is processed the sub-report is fired. The Sales Order ID is passed from the list control down to the sub-report. The sub-report is then served back up to be displayed in the list control on the main report.

The proof is in the viewing or previewing in this case, so preview your report. Click on the button to the right of the printer icon to execute "Print Preview". Now you can page through your report and see how it formats in something that understands page breaks like PDF or TIFF.

If you are not happy with the page break behavior, add a rectangle control to the list. Drag all the report elements into this new rectangle and set its PageBreakAtEnd property to true. You will have to tweak your reports to get the results you desire. Page Break is missing from the List control Group dialogue. This is an oversight (can you say bug?) as the RDL will support it. Refer to Tips and Tricks for more information on how to get the List control to break on group change.

Tech Tip: In the Solution Explorer, right-click on a report definition file (.rdl) and select "View Code" to access the XML.

If you are going to be viewing a report in HTML, test it that way. If PDF is your primary publishing format, you can safely work in Preview, but always check your report in published format with your Report Viewer ASPX Page before deployment.

The controls circled in red at the top of figure 23 are the Print, Print Preview and Export controls. You must select Print Preview to enable the Print control. These controls all work in Preview inside the Visual Studio.NET IDE. You can use the export to get a pretty good indication of how your report will render in HTML, PDF or other supported formats by exporting to a file and then opening it on your workstation.

Note that pagination is unreliable until you click on Print Preview. Specified page breaks, such as the one we coded before each sales order, will be displayed but overflow pagination may not render correctly in basic Preview.

Figure 23 - Preview finished report


Deploy from Visual Studio.NET

If you installed Report Server locally, you can use the example shown here to deploy your reports and data source. If you are using Report Server on another host, substitute your server name for the "localhost" in the TargetServerURL "http://localhost/ReportServer" in the report Project Property Pages shown in figure 24. Change the TargerFolder to "Lessons Learned" and set OverwriteDataSources to true.

Figure 24 - Report Project Property Pages

Once these settings are made, you are ready to deploy. Select the report and the sub-report; right-click and choose "Deploy". After a brief hesitation you should be rewarded with the following lines in your Visual Studio.NET Output window:

------ Build started: Project: rsLessons, Configuration: Debug ------

Build complete -- 0 errors, 0 warnings

------ Deploy started: Project: rsLessons, Configuration: Debug ------

Deploying to http://localhost/ReportServer?%2fLessons+Learned

Deploying data source '/Lessons Learned/AdventureWorks2000'.

Deploying report 'Lessons'.

Deploying report 'subrptDetail'.

Deploy complete -- 0 errors, 0 warnings

---------------------- Done ----------------------

�� Build: 1 succeeded, 0 failed, 0 skipped

�� Deploy: 1 succeeded, 0 failed, 0 skipped

Notice that the data source is deployed even though we didn't select it. When working with production data sources that can present a problem as some settings have to be made in Report Manager after the initial deployment. Go back to the Report Project Property Pages and reset OverWriteDataSources to False.

Figure 25 - Report Manager... Lessons Learned Folder

Figure 26 - Hide Sub-Report

Click on the edit icon in the detail list to bring up the page show in figure 26. Click on the "Hide in list view" check box and click the "Apply" button. Do the same with the data source and when we next show the Lessons Learned folder we will see a display similar to figure 27. To get back to the data source and the sub-report, click on "Show Details" as that view ignores the "Hide" setting and reveals all files published in the selected folder as in figure 25.

Figure 27 - Lessons Learned Folder with data source and sub-report hidden

Conclusion of Create a Report

Tips and Tricks

1.       Use the wizard to generate reports to see how grouping and sorting are done. Look at the source code of the Report Manager to see how it interacts with the Reporting Services.

2.       If you deploy a report or a data source and cannot see it in the Report Manager, your user ID probably does not have sufficient permission to see the deployed items. You can always log into Report Manger as Admin for the box it is running on. Once in, you can go to Site Settings (the link is in the upper right corner of the page), follow the Configure site-wide security link, click on New Role Assignment in the tool bar and then set up your new Group and User IDs.

3.       "If the report credentials are specified (not integrated security) connection to the data sources are re-used among users. " from the RS newsgroup. Now you know everything we do about "re-used connections".

4.       You can Add / Edit or Delete items in the field list. You can even hook a report data element to some OTHER column in your SQL -- but that should NEVER be done. It would be impossible to maintain. However, this is can be a report saving feature if you get DBA'd.

5.       When using a "text" data source, you are thrown into the Generic Query Designer. The icon on the tool bar with a pencil in it lets you toggle between the default text designer and the Visual Design Tool (VDT). The VDT is excellent, but it will reformat your T-SQL.

6.       HEADERS and FIELDS: You can reference a text box (hidden or not) with an expression like =ReportItems!textbox3.value from headers or footers -- BUT NOT FIELDS. The text box has to render on the same page as the header / footer. This "work around" is just about useless. Unfortunately, there is no fix in sight.

7.       When lining up textboxes in a row - different font sizes can generate small but visible line up issues if you format with a visible border.

8.       STYLE GUIDE: 8 pt titles seem to work nicely with 9 pt bold data (as long as titles are not on the same row -- see above). Using some (say top and left) solid borders gives a form like feel to a report. Be careful to line up sub reports with elements on the main report. Use solid borders during design for line-up (subs and mains especially) even if you plan to remove them for production.

9.       To reset the ASPNET user, refer to KB article 306005 (Note: Article 325093 is also an excellent resource).

10.    Report Server IIS must be set to DISALLOW anonymous login to get NT credentials passed from web page to RS -- otherwise you get IUSR_machinename sent in.

11.    As long as you have known credentials, you should be able to use rs.Credentials = New System.Net.NetworkCredential(UserID, Pwd, Domain) to pass credentials into an instance of the Report.vb class (See code below).

12.    When you're banging around among multiple RS servers REMEMBER - the Reports Project properties wants the URL to the report server WITHOUT the ReportingService.asmx (i.e. http://localhost/ReportServer) whereas the URL references inside the app need a fully qualified path to the service (i.e. http://localhost/ReportServer/ReportingService.asmx).

13.    If you edit a Shared Data source to point to a different database -- once you deploy the changed data source, all the reports that use that data source will point to the new database WITHOUT re-deploying the reports.

14.    TRUSTED SQL CONNECTION ERROR- Always test new UID/PWD combos using SQL Query or something.  Tried to connect using a service account so many times that we generated a security lock up on the account. Finally got Report Manager going with correct credentials ("stored securely in the report server") and "Use as Windows credentials. . . " checked on appropriate data source, once we knew what constituted valid credentials.

15.    Data View returns rows -- Report Preview does NOT (or vice versa): Click on the "Edit Selected Dataset (. . . ) button to the right of the dataset name on the Data View panel. Select the parameters tab. Make sure no hard coded values are associated with your parms. Should point to something like "=Parameters!ReportID.Value". Defaults did NOT show up in Report / Report Parameters menu selection and defaults did NOT appear in the parameter input boxes at the top of the Preview Panel. The defaults DID show up unexpectedly in the parameter input boxes at the top of the Report Manager page.

16.    If you change from one Shared Data Source to another for a report, make sure you ALSO change any sub reports to the new Shared Data Source.

17.    Newsgroup posting by John H. Miller (MSFT): You can somewhat approximate template behavior by creating specific reports you'd like to have your team use as starters and placing them C:\ProgramFiles\Microsoft SQL Server\80\Tools\ReportDesigner\~ProjectItems\~Report-Project directory. Those report "templates" will then show up in Report Designer's Add New Item dialog along with the Report Wizard, Report, and Data Source templates when you go to add a new report to a project.

18.    Newsgroup posting by Robert M Bruckner (MSFT): Just drag & drop a numeric field from the Fields list into the Table Group1 Footer and it should automatically create an expression =Sum(Fields!NumberField.Value). Since the Sum is placed in the Table Group1 Footer it will be automatically "scoped" to the data contained in Group1. If you want a running total, then the expression you need is =RunningValue(Fields!Cal.Value, Sum, Nothing). This would create a running total that would never reset to 0 (because the scope argument of RunningValue is set to Nothing which means never reset).

19.    RS v1 works with Framework v1.1 so it MUST be installed and running before you put Whidbey (ASP.NET 2.0) on a machine. Suspect you will need to continue developing reports in VS 2003, but we would expect to be able to use the RS web service just fine from ASP 2. Not sure what to expect writing to the API though.

20.    Newsgroup posting by Robert M Bruckner (MSFT): Regarding expressions - basically you can use any object / function from the .NET framework which is available within the following namespaces:

a.        System

b.       System.Convert

c.        System.Math

d.       Microsoft.VisualBasic

21.    Here's the link for the new (since beta) approach to securing and hooking up an assembly to a report RDL: http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp?frame=true

22.    From the new BOL, here's a link into using Assemblies with your reports: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_rdl_6d0i.asp

23.    And here's the link to the new BOL -- soon to be available for download: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPORTAL/HTM/rs_gts_portal_3vqd.asp

24.    And here's an example of a custom data provider: http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=b8468707-56ef-4864-ac51-d83fc3273fe5

25.    From Newsgroup posting by Bruce Johnson [MSFT] {If} you need to add a page break before/after the List's group expression. Because of a bug, the List Grouping / Sorting dialogue is missing the checkboxes to set the PageBreakAtEnd and PageBreakAtStart properties. You need to hand edit the RDL to work around this problem. Be sure to backup the RDL before you make these changes. . . .

Keys are of the RDL to examine: [View RDL "Code" in the IDE {BJ}]

���� <List Name="list1">

������ <Style />

������ <Grouping Name="list1_Details_Group">

�������� <GroupExpressions>

���������� <GroupExpression>=Fields!Country.Value</GroupExpression>

�������� </GroupExpressions>

�������� <PageBreakAtEnd>true</PageBreakAtEnd> 

������ </Grouping>

26.    To calculate AGE, use this Text Box expression:

=IIF(Fields!DOB.Value Is Nothing, "",

CDate(Fields!DOB.Value).ToShortDateString & " (" & DateDiff("yyyy", CDate(Fields!DOB.Value ), DateTime.Today) & ")")

27.    Got an error using a Fields ref as parm to a sub-report. Used the expression option (finally) to pick up the desired value out of a dataset and things got better REAL quick. The error message mentioned "reference to an undefined field". It only took an hour or two to realize THAT was bogus, and to just start pulling sub-reports out of sub-reports to get down to basics. The initial sub-report rendered correctly when it was previewed, so that should have pointed me back to the main report sooner. Oh well. If this was easy they wouldn't need us.

28.    If a sub report is defined wider than the space allocated on the primary report -- sub report width is used. This can cause extra pages, with or without data.

29.    To allow Internet access of RS, from a post on the newsgroup by Parker Jones: allow anonymous access to the Report Server Virtual Directory rather than the Reports Virtual Directory. That should eliminate your login requests. Add IUSR_MachineName as a browser role to the desired reports.

30.    Google is cross-referencing the newsgroup at: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=microsoft.public.sqlserver.reportingsvcs

31.    From a post by Bruce Johnson [Msft] - You can determine the installed versions as follows:

Report Server

Navigate to the Report Server using http://<servername>/reportserver. The

Version number will be displayed on the web page.

Report Designer

Help Menu: About Microsoft Development Environment. Selecting Microsoft

SQL Server Report Designer in the "Installed Products" area will display

the version number.

Version Number

Beta 1 Something earlier that Beta 2 (We couldn't find the number).

Beta 2 8.00.673.01

RTM 8.00.743.00

32.    In the Reporting Services Books On Lone (BOL) go to the following content heading (in beta docs) to see code from which we derived our Report.vb class:

Reporting Services Programming

Building Applications Using the Web Service and the .NET Framework

Setting the URL Property of the Web Service

About the Author Bill Jones Jr. is a Software Architect with MetaLogix, Inc. based in Charlotte North Carolina. MetaLogix (www.Meta-Logix.net) specializes in SharePoint, .Net and workflow applications. He has primary responsibility for the AgendaCentral product (www.AgendaCentral.com).   He is also the founder and president of the Enterprise Developers Guild, Charlotte's official Microsoft .Net User Group.

Download the Report.vb Class used in this article