Preparing Your Data for Reports

Note: Before creating any reports in ASP.NET Report Maker, read this section to learn how to prepare your data first. After you prepare the data, connect to the database using ASP.NET Report Maker, then right click the database pane or click [Edit] and then choose [Add Report (Detail/Summary)] or [Add Report (Crosstab)] to create a report.

1. Make sure ALL data are available using Views

The first thing to consider in preparing your data is to make ALL data to be displayed available in the source table.

In ASP.NET Report Maker, each report is based on a single source table (or view), you need to make sure all data are available in the source table. If you have foreign keys in your table and you want to display data from the referenced table, you should use view (or "query" in MS Access) of the your database and create joins to combine data from the tables first.

Note: If your database does not support views (for example, MySQL 4), you can use Custom View in ASP.NET Report Maker. However, you should always use queries or views features of your database whenever possible. (See Using Custom View)

Example

In the tutorial of Crosstab Report, we create a query in the Access database first, you can see that the relationships are as follows:

The main data is actually stored in the Order Details table, but we obtain the following information using the joins between tables:

Company name - Use the OrderID field in the Order Details table to trace back to the Orders table, then use the CustomerID in the Orders table to trace back to the Customers table to get the CompanyName.

Product name - Use the ProductID field in the Order Details table to trace back to the Products table to get the ProductName.

Category name - Use the ProductID field in the Order Details table to trace back to the Products table to get the ProductName, then use the CatergoryID in the Products table to trace back to the Categories table to get the CategoryName.

With the company name, product name and category name present in one single source - the "Orders By Product" query, you can create reports and use them to group your data. Otherwise, you can only group by OrderID or ProductID which are only integers and make the report much less readable.

Notes:

1. Make sure the data referenced by the foreign keys are unique or the grouping will be wrong. For example, if you have 2 different CustomerID in your orders table but they both have the same company name, these 2 companies will become indistinguishable in the view; if you group the data by customer name, the result will be wrong.
   
   
2. To enhance performance, only select the data you want to use when you create the views. Do not use asterisk(*) to select all fields (unless you really need to use all of them). Do not select binary fields as they are not supported.

2. Filtering Data

The second thing to consider is filtering of the data. Always retrieve the least amount of data from the database for best performance.

For example, if your data ranges across 3 years and you only need to see one year data in your report each time, you can create a report and select the year at runtime (Run-time refers to when you run the generated script. Run-time filtering is well supported for users to see partial data that they are interested in), but this would mean that the ASP.NET script will need to handle 3 times the data needed to show initially. There is performance penalty if the size of your data is large and it will take a longer time for the report page to show. You have 2 alternatives:

1. Use Extended Filter for the field and choose a default year, or
2. Create 3 views in your database for each year.

then your report for a particular year will load faster.