Demonstrating Crystal Reports Functionality

1
Demonstrating Crystal Reports Functionality

• Mauricio Featherman, Ph.D, Tarana Damania, MBA,
  EY
• Washington St. University

2
Training Objectives

• This is a set of slides that amplify topics from
  your Crystal reports (CR) book. By following
  these steps you can use to learn how to use CR.
• You will be able to
• Create reports based on user requirements
• Use the formula, parameter, grouping, filtering
  and charting abilities of crystal reports
• Use the cross-tab functionality of crystal reports

3
Report of Sales By Country Top 7 Drill Down
Enabled
4
Country/Region Sales.rpt

• Xtreme.mdb, Data- Customers table
• Fields-Country, Region, City, Name, sales, email
• Group-by Country AND region-Summarize Last
  Years Sales on both
• Top N- only show top 5 Sales per country, all the
  rest in Others category. Right click the report
  and use the report record sort expert, add a
  sort on city
• Format the email field as a hyperlink, soyou can
  email the top customers, Finish

5
Finished product
6
Drilldown Option

• Right click Details (Section 3)
• Select Hide or Drill-down ok option to hide
  specific customer info)
• Repeat for region header and region footer
• Put all the data for the group footers into the
  group headers (for aesthetics, and to save
  paper)

7
Drilldown finished report
these are email links!
8
Reporting

• Use the chart expert icon and select a bar chart,
  
• click the data tab, on change of country show
  last years sales.
• Format the chart as desired, right-click, add
  data labels, etc.

9
Finished product
10
Employee Productivity

• Requirement
• Sales manager needs an employee productivity
  report
• Status of all orders made by the selected
  employee to customers
• Highlight late orders
• Late gt shipped after 5 days of order date

11
Employee Productivity (contd.)

• Translate Requirement
• all orders Orders table
• an employee Employee table with user input
  to decide on which employee- we want to see the
  sales for each employee (Note any employee can
  sell to any customer)
• by account Customer table with grouping by
  customer
• highlight late orders use conditional
  formatting

12
Creating Employee Productivity.rpt

• Add new form, add the CR viewer control and
  create a new report in Standard form
• Data Xtreme.mdb -gt Select the Customers,
  Employee, Orders tables
• Links - Note auto-links
• Fields from Customers customer name, from
  Orders - Order id, Order Amount, Order Date,
  Ship, From Employees last name,

13
Creating Employee Productivity.rpt (contd.)

• Group by Employee name then group by Customer
  Name in Customer table
• Total Summarize by summing Order Amount per
  account/customer name, click finish

14
Using Parameters

• In Field Explorer, right click on Parameter
  Fields
• Add new parameter called Employee Last name
• Enter prompting text for user
• Select employee to display
• Why? To aid user to input the right choice
• Provide list of employee first names

15
Setting Default Parameter Values

• Click default values then select Employee table
  last Name field and click on gtgt to move all the
  names to the default list
• Note New parameter added to Field Explorer
• Parameter still needs to be added to report
  (verify the absence of the tick mark next to
  parameter name)Select the employee field in the
  report and righ click and choose the
  ReportSelect expert. Click New and select the
  Employee Last name field and choose your
  parameter field as shown
• Change default to is equal to
• Select the ?Employee Last name parameter
• Note the filter is actually a formula (click
  on Show Formulagtgtgt)
• Verify tick mark in field explorer

16
Finished Employee Productivity.rpt
When we run the report, we want to display the
orders that the employee booked
17
Using theFormula Field

• In order to highlight delayed orders
• Calculate difference between Order Date Ship
  date
• All orders shipped later than 5 days after order
  date are DELAYED
• Right click Formula Field, create new formula
  called Late Shipments
• Use the editor
• Double click Orders.Ship Date, insert
  Arithmetic operator Subtract (or just type in
  the minus sign), double click Orders.Order Date
• Test formula for errors using formula check
• Again, absence of tick indicates formula not
  part of report, so add it to the details band

18
Making the formula part of the report

• Right click on Details (Section 3) of report
  and Insert/Field into report after Ship Date
• Insert formula field _at_Late Shipments
• Right click on field and select Highlighting
  expert
• Create a new condition value gt 5 using the
  item editor
• Change font background to bold red
• View report

19
Hierarchical Grouping

• Will discuss report that uses hierarchical
  grouping
• Listing employees under supervisors (who are also
  employees)

20

• What if you wanted to perform table computations
  on many different criteria, such as add up all
  sales for each country and then segment, those
  sales by year and then format and show the data
  in a tabular format? Were talking serious
  looping here.
• CrossTab reports make this incredibly easy!

21
Cross Tabs (Chapter 14)

• When creating new Crystal Report, choose Cross
  Tab not Standard expert
• Want spreadsheet/table showing annual orders by
  country (only for shipped orders)
• i.e. Rows country, columns year, each cell
  Annual Order Total
• Need Customer and Orders table

22
Cross Tabs (contd.)

• Select Customer.Country -gt Click to Add Row
• For column, need to create formula since
  Orders.Order Date needs to be converted to a year
  format
• Click New Formula -gt name Annual Orders

23
Evaluating the Year of an Order

• Under Functions, Date Time, select
  Year(x)
• Under Report Fields, select Orders.Order date
• or
• Just type Year (Orders.Order Date)
• Check for errors

24
Cross Tab (contd.)

• Select _at_Annual Orders and click Add Column
• Select Orders.Order Amount click Add
  Summarized Field
• Select Orders.Shipped click Add Summarized
  Field
• Since only require shipped orders, under Select
  tab, add Orders.Shipped field, set condition to
  is true
• Change format of Year