Download
1 / 17
170 likes | 174 Views
Access. Chapter 7- Access Custom Reports. Custom Reports. Banded report writer Report, Page(s) and Group(s) sections can have both Headers and Footers Detail Section displays records from underlying table or query Not all sections are needed in each report. Custom Reports.
E N D
Access Chapter 7- Access Custom Reports
Custom Reports • Banded report writer • Report, Page(s) and Group(s) sections can have both Headers and Footers • Detail Section displays records from underlying table or query • Not all sections are needed in each report PgP MIS 342 Access
Custom Reports • Reports have numerous formatting options • Note the page break control • Note useful section properties such as Report Header Section “Force New Page” • Note “Keep Together” property PgP MIS 342 Access
IIf Function • Useful in calculated fields • Prevents divide by zero error • Hide zero values • Display values that meet criteria PgP MIS 342 Access
IIf Function • IIf(expr, truepart, falsepart) • Expr Expression you want to evaluate • truepart Value or expression returned if expr is True • Falsepart Value or expression returned if expr is False PgP MIS 342 Access
IIf Function Example • Northwind.mdb, Orders table • Some orders do not have an entry for Shipped Date • Create a query with an IIf statement to display the message “Not Yet Shipped” for these orders • Display the CustomerID and ShippedDate fields PgP MIS 342 Access
Report Design • Important to practice skills, similar to Forms and Data Access Pages (chapter 8) • Reports allow summarization of potentially huge amounts of data-converting it into useful information for which managers can base decisions upon PgP MIS 342 Access
Report Design Enhancements • Page numbers, total pages • Date or date and time • Titles, subtitles, lines • Conditional formatting (like Excel) to highlight certain values • Sorting and grouping for better organization • Hide duplicate values (Format, Hide Duplicates) PgP MIS 342 Access
Report Design Enhancements- Conditional Formatting • Northwind.mdb, Orders table • Use the CustomerID, OrderDate and ShippedDate fields to create report • Create calculated field to display “Days to Ship” • Set up conditional formatting as shown on next slide(0to5-green;6to10-yellow;>10-red) PgP MIS 342 Access
Report Design Enhancements- Conditional Formatting PgP MIS 342 Access
Report Totals • Sum([fieldname]) • Create group totals • Place in group footer • Create overall total • Place in report footer PgP MIS 342 Access
Report Totals • RunningSum Property (Search in Help) • Over Group to display cumulative total within each group • Over All to display cumulative total for entire report PgP MIS 342 Access
Report Totals Example • Northwind.mdb, Orders,OrderDetails tables • Use the CustomerID, OrderID, ProductID, UnitPrice and Quantity fields, create ExtPrice • Use Report Wizard, group by CustomerID,OrderID, sort by ProductID, use summary options to total UnitPrice (then multiply this by Quantity to get ExtendedPrice) • Create calculated field to display “ExtendedPrice” PgP MIS 342 Access
Report Totals Example Includes group and overall totals PgP MIS 342 Access
Report Totals Example Includes group and overall totals PgP MIS 342 Access
Special Report Features • Examine Date formats • Execute the Mailing Label Wizard • Become familiar with page numbers (sort of a ‘mini-wizard’) • Subreports-to explain certain sections in more detail or to summarize data • Can use SQL statements as report data sources PgP MIS 342 Access
Learn Report Writing • Report writing is a valuable skill • Access is but one report writer, also- • Crystal Reports • SQL Server Reporting Services PgP MIS 342 Access
