ASP.NET 2.0 Reporting Revisited: Part I

Creating Drill-through Reports Using the ReportViewer

asp:Feature

LANGUAGES: C# | VB.NET

ASP.NET VERSIONS: 2.0

 

ASP.NET 2.0 Reporting Revisited: Part I

Creating Drill-through Reports Using the ReportViewer

 

By Dennis E. Rehm

 

This article continues the ASP.NET reporting I described in ASP.NET 2.0 Reporting. That article described how to create two subreports within a report without using SQL Server Reporting Services. Because I received positive feedback from that article and because there are not many complete explanations to do anything but basic reports using the Microsoft Report Designer and ReportViewer packaged with Visual Studio we re going to further explore the world of Microsoft reporting.

 

This time we re going to create a summary report in a Web page and enable it so we can click on a total and generate a detail report to show what makes up that summary number. To do this, we re going to use the Visual Studio Report Designer and its control, the ReportViewer. No third-party products are required. If you went through the steps to build the subreports in the first article, you ll be able to skip some of the set-up steps here. If not, don t worry everything you need to create the drill-through report is here.

 

Note: You would create a report like this when you have a high-level report that you don t want to clutter with a lot of details, but you want to make the details available without sending the user to another report.

 

First we need the right development environment. (This article describes the process using Visual Studio 2005, but the same process will work with Visual Studio 2008. The great benefit of using Visual Studio 2008 is that reports can be printed directly from the ReportViewer with the addition of an ActiveX component on the client. In Visual Studio 2005, the report must be exported as a PDF or XLS to be printed.) The Visual Studio Report Designer and ReportViewer are included in all versions of Visual Studio 2005, except Express. (If you have Visual Web Developer 2005 Express, you can download the Report Designer and ReportViewer as part of a feature pack for SQL Server 2005 at http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en. Scroll to the bottom of the page and look for SQLServer2005_ReportAddin.msi.)

 

We ll first create the summary report, then create two detail reports. When we ve built the reports, we ll link them together. We ll create the reports using the AdventureWorks OLTP database. (If you don t have this database, the AdventureWorks database can be downloaded from CodePlex at http://www.codeplex.com/MSFTDBProdSamples. Installation instructions are included.)

 

The process of creating interlinked reports requires meticulous attention to detail. If you do something wrong, your only indication may be the message An error has occurred during report processing. There are few diagnostics to help you pinpoint and resolve the problem. If you encounter this message, please review each step carefully. In the worst case, simply delete the objects and build them again.

 

We ll be using the following eight tables from the AdventureWorks database: SalesOrderHeader, SalesOrderDetail, Customer, Individual, Product, ProductCategory, ProductSubcategory, and SalesTerritory. See Figure 1 for the data model. Note that the Individual table is a subset of the Customer table and that SalesTerritory is a foreign key for both Customer and SalesOrderHeader.

 


Figure 1: The data model for the SalesOrderHeader, SalesOrderDetail, Customer, Individual, Product, ProductCategory, ProductSubcategory, and SalesTerritory tables.

 

The Project

Marketing has requested an order summary report that shows each product within its category and subcategory. Orders should be grouped by US and non-US customers and, within these groupings, separated out as orders from individuals or from other sources. We re going to build that report together. Then we ll create different detail reports that will show different information, depending on whether the user clicked on individual or reseller orders. When the user clicks on a total order amount in the summary report, a detail report will automatically open using the drill-through capabilities of the ReportViewer.

 

Creating the Summary Dataset

First we need to create a dataset with the data required for the summary report. To access the data in the AdventureWorks database, we need a SQL Server login. I named my login ReportApp, gave it the password reportapp, and assigned it the db_owner role in the AdventureWorks database. If you don t have this login, go ahead and create it. (This is not how you would structure database permissions in a production system, but it will allow us to focus on building the report rather than setting up security for this example.)

 

Go to the Solution Explorer and right-click in the project. Select Add New Item from the menu. Select Dataset from the Add New Item window and name it OrderSummaryRpt.xsd; click the Add button.

 

If you don t already have an App_Code folder in your project, Visual Studio will ask you to create one. Your OrderSummaryRpt dataset will be placed in the App_Code folder.

 

The TableAdapter Configuration Wizard will open and ask you to select a Connection (see Figure 2). If you previously created a connection to the AdventureWorks database using the ReportApp login, you can use that connection and skip to the next paragraph. Otherwise, click the New Connection button. The Add Connection window will open. Enter or select the server where you installed the AdventureWorks database. Select the radiobutton for SQL Server Authentication. Enter The SQL Server login and password you created. In my case, I used ReportApp as the user name and reportapp as the password. Check the Save my password checkbox. Select or enter a database name radiobutton. Select the AdventureWorks database in the dropdown list. You can click the Test Connection button to be certain, but if you can see the AdventureWorks database in the dropdown list, you ve successfully made a connection to the server. (Your window should look like Figure 3.) Click the OK button. You are now back in the TableAdapter Configuration Wizard with your new connection selected.

 


Figure 2: Select a connection.

 


Figure 3: The Add Connection dialog box.

 

Click the Next button. If this is a new connection for this application, you ll be asked to save the connection string to the configuration file. You should do so. The default name is fine.

 

Next, choose a command type. Select the radiobutton by Use SQL statements (see Figure 4). For this example we ll use SQL statements to retrieve the data. In a production system, I prefer stored procedures, which can be granted permissions independent of the underlying tables (using SQL statements is simpler for our purposes here). Click the Next button.

 


Figure 4: Choose a command type.

 

If you are familiar with the data model and the SQL statement is short, you can type the SQL statement. Otherwise, click the Query Builder button and build the SQL statement graphically. The SQL statement in Figure 5 needs a little explanation.

 

SELECT Subcat.ProductCategoryID, Cat.Name AS CategoryName,

   Prod.ProductSubcategoryID, Subcat.Name AS SubcategoryName,

 Det.ProductID, Prod.Name AS ProductName,

 CASE WHEN Terr.CountryRegionCode = 'US'

   THEN 'US' ELSE 'Non' END AS CountryRegionCode,

 CASE WHEN Cust.CustomerType = 'I'

   THEN 'Individual' ELSE 'Other' END AS CustomerType,

 SUM(Det.LineTotal) AS TotalOrderAmt

FROM Sales.SalesOrderHeader Hdr

INNER JOIN Sales.SalesOrderDetail Det

   ON Hdr.SalesOrderID = Det.SalesOrderID

INNER JOIN Production.Product Prod

   ON Det.ProductID = Prod.ProductID

INNER JOIN Production.ProductSubcategory Subcat

   ON Prod.ProductSubcategoryID = Subcat.ProductSubcategoryID

INNER JOIN Production.ProductCategory Cat

   ON Subcat.ProductCategoryID = Cat.ProductCategoryID

INNER JOIN Sales.SalesTerritory Terr

   ON Hdr.TerritoryID = Terr.TerritoryID

INNER JOIN Sales.Customer Cust

   ON Hdr.CustomerID = Cust.CustomerID

GROUP BY Subcat.ProductCategoryID, Cat.Name,

   Prod.ProductSubcategoryID, Subcat.Name,

Det.ProductID, Prod.NAME,

 CASE WHEN Terr.CountryRegionCode = 'US'

   THEN 'US' ELSE 'Non' END, Cust.CustomerType

ORDER BY CategoryName, SubcategoryName,

   ProductName, CustomerType

Figure 5: The summary report SQL statement.

 

Because the AdventureWorks database is normalized, we need to go to different tables to get different pieces of information, such as the name of the Category, Subcategory, and Product. And, because all three tables (Product, ProductCategory, and ProductSubcategory) have a Name column, I ve given each an alias by concatenating the name of the table before Name. This makes the result set easier to manage.

 

Marketing wants the report to be broken down by US and non-US sales. The territory information is more detailed than that. Using the CASE statement to manipulate the data on a row by row basis allows us to group the orders the way marketing wants them. The CountryRegionCode column in the database is nvarchar(3). To avoid extra work in this example, I ve set the values in the CASE statement to be no longer than three characters. This will save us work later in the drill-through reports.

 

Marketing also wants the order totals broken out by individual and other (reseller) orders. The customer type is I for an individual; we ll put everything else into Other . We use another CASE statement to convert those codes to descriptive values for our report.

 

Because we want order amount totals, we ll do a GROUP BY of the other columns and SUM the amounts. (We could return all the detail information and let the report do the totaling, but this approach allows the database server to do the work and uses less network bandwidth to deliver the result set. It meets our needs.)

 

When the SQL statement is complete, click the Advanced Options button and uncheck the Generate Insert, Update and Delete statements box (see Figure 6). Because this is a report, no data modification statements will be required. Click the OK button to close that window, then click the Next button.

 


Figure 6: The Advanced Options dialog box.

 

The Choose Methods to Generate window is fine as it is. Click the Next button. The wizard will display a summary of the items to be generated. Click the Finish button. Your dataset should look like the one in Figure 7. Save and close your dataset. You ve now created the summary dataset that will retrieve the data to drive the report.

 


Figure 7: The summary report dataset.

 

Creating the Summary Report

We ll place all our reports in a separate folder within the project. If you don t have a Reports folder, create one by right-clicking the project in the Solution Explorer, selecting New Folder from the pop-up menu, and naming it Reports.

 

Right-click this new Reports folder and select Add New Item. Select Report from the installed templates and name it OrderSummary.rdlc. Click the Add button. The Report Designer will open and you should see OrderSummary.rdlc in the Reports folder in the Solution Explorer. If not, close the Report Designer, then drag the report and drop it in the Reports folder. Then double-click it to open it again in the Report Designer.

 

The Report Designer has a panel labeled Website Data Sources. If it s not displayed for you, go to the menu at the top of the window and select the Data menu option. Under that is a single menu item: Show Data Sources. Select it and the Website Data Sources panel will be displayed. If it s not docked, you can dock it (the lower left corner is its usual home). Our OrderSummaryRpt dataset is displayed as a Website Data Source. Expand DataTable1 and you can see the data elements we selected in our SQL statement (see Figure 8).

 


Figure 8: Our dataset in the Website Data Sources.

 

We re going to create the summary report using a matrix report control, also known as a crosstab. This allows the number of columns to expand based on the source data. Drag the matrix control from the Report Items toolbox and drop it on the report in the Report Designer. The matrix has four cells. One is labeled Rows, one is labeled Columns, one is labeled Data, and the remaining cell is blank.

 

Matrix controls are very easy to populate. Simply drag a column from the Website Data Sources panel and drop it on the cell where you want it. We ll do that in a moment. First, let s review our report format. We want to see Category, Subcategory, and Product in the Rows cell; we want the US and Non groupings in the Columns cell, and we want Amount in the Data cell.

 

Now drag the Category column from the Website Data Sources panel under OrderSummaryRpt and DataTable1. Drop it on the cell labeled Rows. Type Product Information in the cell just above where you dropped the Category column. Next, we want to add Subcategory in the rows. Right-click the matrix column header above the Product Information label you just added. If for any reason you do not see the column headers, click the matrix control again to select it. The control must be selected for the headers to appear. When you right-click the header, you ll see a pop-up menu. Select Add Row Group. You should see the Grouping and Sorting Properties window (see Figure 9).

 


Figure 9: Adding a Row Group.

 

Rename the Name from matrix1_RowGroup2 to matrix1_SubcategoryName. In the Group on textbox click under Expression and a dropdown list will appear. Select =Fields!SubcategoryName.Value. Click the dropdown list under Parent group and select =Fields!CategoryName.Value. Click the checkbox by Group header should remain visible while scrolling, then click the OK button.

 

We ll repeat this process to add Product. Right-click the column header above the Subcategory cell you just created. Select Add Row Group and open the Grouping and Sorting Properties window. Change the Name to matrix1_ProductName, click under Expression and select =Fields!ProductName.Value as the Group on value, select =Fields!SubcategoryName.Value under Parent group, and check the checkbox by Group header should remain visible while scrolling. Click the OK button.

 

Let s go back and enhance the grouping for our first column. Right-click the cell that contains =Fields!CategoryName.Value. Select Edit Group from the pop-up menu. Note the Name and Group on Expression that were set for us. Click the checkbox by Group header should remain visible while scrolling and click the OK button. This completes the setup of data to define the rows.

 

Marketing has asked to see the totals for US and non-US orders. These will be columns of data. Because we are using a matrix control, a separate column will be created automatically for each distinct value when we run the report. Drag the CountryRegionCode column from the Website Data Sources panel under OrderSummaryRpt and DataTable1 and drop it on the cell labeled Columns. We need only set this one cell. If you remember, we used a CASE statement to set the value in the CountryRegionCode column to US or Non . Therefore, our report will have separate columns when we run it.

 

Within the US and non-US orders, we want to show orders by individuals and orders from other sources. These will be columns within the US and non-US columns. Right-click the column header above =Fields!CountryRegionCode.Value and select Add Column Group from the pop-up menu. The Grouping and Sorting Properties window will open. Change the Name to matrix1_CustomerType, then click under Expression and select =Fields!CustomerType.Value as the Group on value, select =Fields!CountryRegionCode.Value under Parent group, and check the checkbox by Group header should remain visible while scrolling. Click the OK button.

 

We want to enhance the Territory column. Right-click on the cell that contains =Fields!CountryRegionCode.Value. Select Edit Group from the pop-up menu. Note the Name and Group on Expression that were set for us. Click the checkbox by Group header should remain visible while scrolling and click the OK button. This completes the setup of data that we want to define the columns.

 

Finally, we want to define the actual data for the report: the dollar amounts of the orders. Drag the TotalOrderAmt column from the Website Data Sources panel under OrderSummaryRpt and DataTable1 and drop it on the cell labeled Data. The Report Designer generates the value =Sum(Fields!TotalOrderAmt.Value). This means the value will be a total order amount for a given Product and Individual or Other within CountryRegionCode. Because we did a GROUP BY in our SQL statement, we ve already done this totaling and the report won t need to do it (but it can and would do totaling if needed).

 

At this point the structure of the summary report is complete. Let s make some cosmetic changes. Select all the cells in the report by clicking on one and Ctrl-clicking each of the others. Then change the font size to 8 pt. Click on the cell containing =Sum(Fields!TotalOrderAmt.Value) and set the Format property to c . (The Properties panel is generally on the lower right side of the Visual Studio window.) This will display the order amount in a currency format. Click the cell containing =Fields!CountryRegionCode.Value and set it to center the column value in the cell. Click the cell containing =Fields!CustomerType.Value and set the cell alignment to right. Our report is ready to test.

 

Your report should look similar to Figure 10. Because information about the connection to the database is stored in the web.config file, we won t be able to preview the report here in the Report Designer. We ll display the report as part of a Web page in the next step. For now, save the report and close the Report Designer.

 


Figure 10: The summary report.

 

Displaying the Report

Open the Web page that will contain the report. I m going to use Default.aspx. Place a label on the page and name it lblMsg. It should contain no text (we ll use this label to display messages later).

 

Drag a ReportViewer control from the Data section of the Toolbox and drop it on the Web page. The first time you use a ReportViewer in your application, your web.config file will be modified to include references to the ReportViewer assemblies and provider. This is done automatically.

 

The ReportViewer SmartTag will be open and you can select a report to associate with the ReportViewer. Select the OrderSummary report in the Choose Report dropdown list. When you select the OrderSummary report, you ll see an ObjectDataSource created on the Web page and a Choose Data Sources item will be added to the SmartTag. The ObjectDataSource has an ID of ObjectDataSource1. We want to give it a more meaningful ID. Click on the ObjectDataSource and in the Properties panel change its ID to objSummary.

 

Go back to the ReportViewer SmartTag and click on it. Select Choose Data Sources. A window opens to allow us to map the Report Data Sources to Data Source Instances on the Web page. Note that our Report Data Source has a name that is the name of the dataset (OrderSummaryRpt) with the name of the datatable concatenated to it (OrderSummaryRpt_DataTable1). See Figure 8 and note the names of the dataset and datatable. This will be used later in our report. However, now that we renamed our ObjectDataSource, the mapping is incorrect. Click the SmartTag to open it and click on Data Source Instances. Choose objSummary from the dropdown list. Click the OK button and the mapping is complete.

 

You can make the ReportViewer control wider and taller as needed for the report. See Figure 11 for my Web page with the controls on it. Set the report Web page as the Start Page. We are now ready to test the basic report. Run the Web application and go to the Web page with the report. Because the SQL statement for our OrderSummaryRpt dataset has no criteria, it will be executed when the page is requested. You should see the Web page with the report populated with data. See Figure 12 for an example.

 


Figure 11: The Web page with a ReportViewer.

 


Figure 12: The initial summary report.

 

If you have errors connecting to the database, open web.config and verify your connection string. Be sure to use your server name and the User ID and Password you have for the AdventureWorks database:

 

 

   connectionString="Data Source=YourServerName;

        Initial Catalog=AdventureWorks;

        Integrated Security=False;

        User ID=ReportApp;Password=reportapp"

   providerName="System.Data.SqlClient"/>

 

You must have the basic report working in order to continue. When you have your report displaying in the Web page, you ll notice that the Product Name is wrapping within its column. Make it wider until there is no wrapping. When everything looks good, we re ready to tackle the first drill-through detail report.

 

Creating the Detail Dataset for Individuals

Again, we start with a dataset to retrieve the required data. This time we ll retrieve data about orders for a single Product purchased by individuals. (Later we ll do the orders purchased by others.)

 

Create a dataset in the App_Code folder named OrderDetailIndRpt.xsd. The SQL will retrieve the orders from individuals for a single Product ID and either US or Non orders. This is done in the WHERE clause with the @ProductID and @CountryRegionCode parameter. This report will display both quantity and amount, whereas the summary report displayed only an amount (see Figure 13).

 

SELECT Cat.Name AS CategoryName, Subcat.Name AS SubcategoryName,

 Det.ProductID, Prod.Name AS ProductName,

 Terr.CountryRegionCode,

 Terr.NAME AS TerritoryName, Cust.CustomerID,

 Ind.Demographics.value('

   declare namespace IND=

       "http://schemas.microsoft.com/sqlserver/2004/07/

       adventure-works/IndividualSurvey";

   data(/IND:IndividualSurvey[1]/IND:DateFirstPurchase)',

        'datetime') AS DateFirstPurchase,

 Ind.Demographics.value('

    declare namespace IND=

       "http://schemas.microsoft.com/sqlserver/2004/07/

       adventure-works/IndividualSurvey";

   data(/IND:IndividualSurvey[1]/IND:YearlyIncome)',

        'nvarchar(max)') AS SalaryRange,

 Det.OrderQty, Det.LineTotal

FROM Sales.SalesOrderHeader Hdr

INNER JOIN Sales.SalesOrderDetail Det

       ON Hdr.SalesOrderID = Det.SalesOrderID

INNER JOIN Production.Product Prod

       ON Det.ProductID = Prod.ProductID

INNER JOIN Production.ProductSubcategory Subcat

       ON Prod.ProductSubcategoryID = Subcat.ProductSubcategoryID

INNER JOIN Production.ProductCategory Cat

       ON Subcat.ProductCategoryID = Cat.ProductCategoryID

INNER JOIN Sales.SalesTerritory Terr

       ON Hdr.TerritoryID = Terr.TerritoryID

INNER JOIN Sales.Customer Cust

       ON Hdr.CustomerID = Cust.CustomerID

INNER JOIN Sales.Individual Ind

       ON Cust.CustomerID = Ind.CustomerID

WHERE Prod.ProductID = @ProductID

 AND Cust.CustomerType = 'I'

 AND (Terr.CountryRegionCode = @CountryRegionCode OR

   (Terr.CountryRegionCode <> 'US' AND @CountryRegionCode <> 'US'))

ORDER BY CountryRegionCode, TerritoryName, Cust.CustomerID

Figure 13: The SQL statement for sales to individuals.

 

Note: If you cut and paste from the SQL script in Figure 13, you ll need to combine the line that begins with http:// with the following line that ends with /IndividualSurvey ;. The lines are formatted here to fit in the printed columns.

 

This is a long SQL statement. Much of it is similar to the SQL statement for our earlier summary report. We ve added the Individual table because marketing wants to see some special information for orders from individuals. That information is stored in the Demographics column, which is a column of datatype xml. Furthermore, this is a typed xml column. Without digressing into the details, you can see the format of the SQL statement we use to retrieve the DateFirstPurchase and YearlyIncome from within that column. (Other demographic information like BirthDate, MaritalStatus, Gender, and Education is also stored in this column.) In the WHERE clause we check the CountryRegionCode. We want to match US or anything else.

 

Remember to go into the Advanced Options and uncheck Generate Insert, Update and Delete statements. If you re having problems creating this dataset, follow the step-by-step instructions in the Creating the Summary Dataset section. When you re finished, the dataset will be displayed. Note the symbol of a key by CustomerID. We don t want that primary key. Click on the CustomerID column and right-click the key beside it. Select Delete key from the pop-up menu to delete it. (The Customer ID is not a primary key for this dataset.) The completed dataset should look like the one in Figure 14.

 


Figure 14: The Individual Sales dataset.

 

Creating the Individual Orders Report

Now we can create the report that will be the Individual Detail Orders report. We ll drill through the OrderSummary report to this report. Add a report named OrderDetailInd to the Reports folder. (See the Creating the Summary Report section for a more detailed description of this process.) Building this report will take a few minutes. Save periodically as you walk through the following steps. Review Figure 15 to see the final report; it will help you visualize what we are building.

 


Figure 15: The Individual Sales report.

 

Drag a table control to the report. Make sure it s in the upper-left corner. By default it has three columns. Drag the CountryRegionCode, TerritoryName, and DateFirstPurchase columns from the OrderDetailIndRpt dataset in the Website Data Sources to the columns in the Detail row in the table control on the report. Use the edges of the column headings to make the column widths smaller.

 

Because we have more than three columns of data, we need to add some columns to the report table. Right-click the header cell at the top of the column containing DateFirstPurchase and select Insert Column to the Right. Do this two more times so you now have a total of six columns in your report. Now drag SalaryRange, OrderQty, and LineTotal to the Detail row of the three new columns. This information will make up the bulk of our report.

 

Notice that the Report Designer generated column headings for us. They are good, but not exactly what we want. Change Country Region Code to Country/Region , Territory Name to Territory , Date First Purchase to First Purchase , and Line Total to Total Amt . Check to make sure the first four columns have the heading and data left-aligned; the last two column headings and data should be right-aligned.

 

Our detail report will be for a single product. While that data won t change on a row by row basis, we do want to show the product information. Click on a column label, such as Country/Region. Go to the row descriptor for the column headings on the left side of the table. Right-click the row descriptor. You should see a pop-up menu. Select Insert Row Above. You should see a new row above the column labels. Select the first two cells in this new row, right-click inside your selection and select Merge cells from the pop-up menu. The two cells are combined into one larger cell. Do the same with cells three and four and then with cells five and six. You now have three larger cells in the row above the column labels instead of six.

 

Drag to the left-most large cell CategoryName from the OrderDetailIndRpt dataset in the Website Data Sources. Drag to the middle large cell SubcategoryName and drag to the right-most large cell ProductName. At this point we have the values in the cells. We want to add a label. Right-click the large cell with CategoryName. Select Expression from the pop-up menu. The Edit Expression window will open. Currently the expression is =First(Fields!CategoryName.Value). Insert a label by changing the expression to = Category: & First(Fields!CategoryName.Value). Click the OK button when you are done. Change the expression for the Subcategory Name by inserting Subcategory: & right after the equals sign. Finally, change the expression for the Product Name by inserting Product: & after the equals sign. Now the cells will display the label and the value.

 

We d like to see some totals for the report. Drag OrderQty to the cell in the Footer below =Fields!OrderQty.Value. When you drop it, you ll see the Report Designer has generated the expression =Sum(Fields!OrderQty.Value). That s exactly what we want. Drag LineTotal to the cell in the Footer below =Fields!LineTotal.Value. That completes the cell layout of our report. Now we want to make some cosmetic changes.

 

I used an 8 pt. font for my report. Select each table cell one by one and set the font to be 8 pt. I also made bold the merged cells with CategoryName, SubcategoryName, and ProductName. Click on each of the three cells and click the Bold button on the toolbar. Select the two amount cells. Set the Format property in the Properties panel to c . This will display the amounts in the currency format. Click on the First Purchase Date cell and set the Format property to d . This will display the date in the common month/day/year format and suppress the time part of the datetime. When you re done, the result should look similar to Figure 15.

 

Now we need to build the linkage between the summary report and this detail report. Right-click in the Report Designer, but not in the report. Select Report Parameters from the pop-up menu. Click the Add button to add a report parameter for the first parameter expected by the detail individual orders SQL statement, which we named @ProductID. The name of the report parameter does not need to match the SQL parameter, but it makes everything clearer if it does. We ll name our report parameter ProductID. The datatypes of the SQL parameter and report parameter need to match. In this case, the ProductID in the database is an integer, so our parameter needs to be an integer. Click the Add button again. Define a second parameter named CountryRegionCode. Its datatype is a string. Your report parameters should look like Figure 16. Click the OK button, save the report, and close it.

 


Figure 16: Defining the report parameters.

 

That s as far as we ll go in this installment. In Part II we ll link our two reports together with drill-through capabilities. Then we ll create the report for resellers, create the linkages, and have our full drill-through capabilities. Until then, have fun creating great reports for your users.

 

Files accompanying this article are available for download.

 

Dennis E. Rehm has been developing software using different languages and tools for 30 years. He s still looking for the silver bullet. He has his own company, Applied Computing, where he does teaching, consulting, and software construction for government, education, and Fortune 500 businesses. You can reach Dennis at mailto:[email protected]net.

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish