In this tutorial we will show you how to create a Crosstab Report in ASP.NET Report Maker. We will use the demo database for demonstration.
As explained in Preparing Your Data for Reports, ASP.NET Report Maker can create a report query from a single table, but an individual table may not contains data suitable as the data source for a crosstab query. If you need more than one table to get the result you want, which is almost always the case, you must design a view specifically for crosstab presentation.
In this example, we use the view "orders by products" to generate a crosstab report that shows quarterly sales by product for different years.
Steps to Create the Report
1. Loading ASP.NET Report Maker
Open ASP.NET Report Maker and connect to the demo database.
2. Adding a report
Right click [Reports] on the database pane then click [Add Report (Crosstab)], or click [Edit] in the main menu and then select [Add Report (Crosstab)].
The Crosstab Report Setup form will show up, enter the report name, for example, we use "Quarterly Orders by Product". Select the View "Orders By Products" as source table.
Click the [Row Headings] tab, since we want to group the order items by category name and then by product name, we select "CategoryName" and then "ProductName" as row headings. We also want to see the summary for each category so we check the [Show summary] checkbox for the field "CategoryName".
Click the [Column Headings] tab, since we show the order date (quarterly) as column headings, we select "OrderDate" as [Field] and select "Quarter" as [Interval].
When we prepare the data by creating the view, we can filter the orders to a particular year by using a WHERE clause, in that case, the report will be for that particular year only.
In this example, however, we did not filter the data in the view, the data includes orders for more than one years. If your group by "Quarter", orders in the same quarter but different years will be grouped together. That is usually not what you want, so we enable the [Year filter] feature, ASP.NET Report Maker can create a combobox of the years according to the data and display data for a year at a time.
Now click the [Value] tab. We want to see the total order amount by product, so we select "Amount" as the [Field] and select "SUM" as [Summary value].
Click [Include row sums] to let the report calculation sum of the each row, that is, the total amount of the quarters.
Since there are quite a few products, the report will not be displayed in one single page (depends on the [Record per page] setting), we select [Show page summary] to make the report show the summary of the orders displayed in the current page only. Moreover, we check [Show grand summary] so we'll always see the summary of all orders too.
Click [OK] to finish.
The report will be added in database pane. Field level setup will be copied from the source table.
3. ASP.NET Script Generation
Go to the [Generate] tab, click the [Generate] button and ASP.NET Report Maker will generate the required ASP.NET scripts automatically.
4. Running the ASP.NET Application
Browse the generated site, click "Quarterly Orders by Product" in the menu to run the report. Note that: