products by category report

Description

This example shows how to build a Products by Category Report.

Steps to recreate

Use the Report Builder to create the store_products report based on the store_categories and store_products tables in the InternetDB database.
- On Step 2 click on the the Build Query button and select tables store_categories and store_products. After select field category_name from table store_categories, product_name and price from store_products.
- On Step 3 select category_name as Group fields.
- On Step 4 select Group Left and Above to show group field department_name above the detail fields.
- On Step 5 check Sum for price field and Count for product_namefield.
- On Step 7 uncheck Record Separator, check Current date and time as, select Date and Time, then select category_name in the Row Numbering Reset by property.
Remove the Count_product_name Report Label from the 'Category_name footer' section.
Add 4 columns after column 'Price'.
In the 'Detail' section add the following Report Labels: category_run_total, report_run_total, cat_percent and rep_percent into each new cell.
For the category_run_total Report Label set the Control Source property to price, Data Type to Float, Format $0.00, Function to Sum, and Reset At to Category_name Group.
For the report_run_total Report Label set the Control Source property to price, Data Type to Float, Format to $0.00, Function to Sum and Reset At to Report.
For the cat_percent Report Label set the Control Source property to price, Data Type to Float, Format to 0.00% and Percent At to category_name Group.
For the rep_percent Report Label set the Control Source property to price, Data Type to Float, Format to 0.00% set Percent At property to Report.
For the Price, Sum_price and TotalSum_price Report Labels set the Format property to $0.00
Make cosmetic changes as needed by modifying the text and captions within the HTML.

Database Tables Used

Database: InternetDB

Tables: store_categories, store_products