**************************************************************************** Location : Local or Network Folders Host : FINDITEZ-T460P Path : C:\Users\ken\Documents\Test Cases\en\Samples\Reports\General Business\Employee Sales.rpt **************************************************************************** Report Information Version: 11.0 Author: Copyright © 2004 Business Objects Comments: Report showing employee sales by product type for each quarter. Bar graph showing total quarterly sales for each employee. Drill down on product type to view sales rep's individual product sales. Title: Employee Sales (Quarterly Report) Printer: No Printer Main Body Data Sources Source: Connection #1 Database DLL: crdb_odbc.dll Database Type: ODBC (RDO) Server: Xtreme Sample Database 11.5 Properties: QE_LogonProperties DSN: Xtreme Sample Database 11.5 UseDSNProperties: False QE_SQLDB: True SSO Enabled: False Report Parameters Parameter: Employee ID Data Type: Number Description: Enter Employee ID: List Type: Dynamic Dynamic Field: Orders.Employee ID Default Values: 3 Tables Table: Employee Connection: Connection #1 Used Columns: {Employee.Employee ID} : Number {Employee.Last Name} : String[42] {Employee.First Name} : String[22] Table: Orders Connection: Connection #1 Used Columns: {Orders.Order ID} : Number {Orders.Employee ID} : Number {Orders.Order Date} : Date Table: Orders Detail Connection: Connection #1 Report Alias: Orders_Detail Used Columns: {Orders_Detail.Unit Price} : Number {Orders_Detail.Quantity} : Number Table: Product Connection: Connection #1 Used Columns: {Product.Product ID} : Number {Product.Product Name} : String[102] {Product.Product Type ID} : Number {Product.Product Class} : String[102] Table: Product Type Connection: Connection #1 Report Alias: Product_Type Used Columns: {Product_Type.Product Type Name} : String[102] Table Links Joins: Orders INNER JOIN [Orders Detail] Orders_Detail ON Orders.[Order ID] = Orders_Detail.[Order ID] Orders INNER JOIN Employee ON Orders.[Employee ID] = Employee.[Employee ID] [Orders Detail] Orders_Detail INNER JOIN Product ON Orders_Detail.[Product ID] = Product.[Product ID] Product INNER JOIN [Product Type] Product_Type ON Product.[Product Type ID] = Product_Type.[Product Type ID] Record Selection Formulas Formula: {Orders.Order Date} in Date (2004, 01, 01) to Date (2004, 03, 31) Record Sorting Sort By: @Quarter -- Ascending Order Group #1 Sum of (@amount, Employee.Last Name) -- Descending Order Product.Product Class -- Ascending Order Group #3 Product_Type.Product Type Name -- Ascending Order Group #4 Product.Product Name -- Ascending Order Group #5 Formula Fields Field: amount Formula: {Orders_Detail.Unit Price}*{Orders_Detail.Quantity} Field: product class Formula: if {Product.Product Class}="Accessory" then "Accessories" else if {Product.Product Class}="Bicycle" then "Bicycles" Field: Quarter Formula: if Month({Orders.Order Date}) in [1, 2, 3] then "Quarter 1, " + totext(Year({Orders.Order Date}), 0, "") else if Month({Orders.Order Date}) in [4, 5, 6] then "Quarter 2, " + totext(Year({Orders.Order Date}), 0, "") else if Month({Orders.Order Date}) in [7, 8, 9] then "Quarter 3, " + totext(Year({Orders.Order Date}), 0, "") else if Month({Orders.Order Date}) in [10, 11, 12] then "Quarter 4, " + totext(Year({Orders.Order Date}), 0, "") Field: initial and lastname Formula: {Employee.Last Name} + ", " + {Employee.First Name}[1] Field: EmployeeName Formula: Shared Stringvar EmployeeName := {Employee.First Name} + ' ' + {Employee.Last Name} Summary Fields Field: amount Formula: Sum ({@amount}, {Product.Product Class}) Field: amount Formula: Sum ({@amount}, {Product_Type.Product Type Name}) Field: amount Formula: Sum ({@amount}, {Product.Product Name}) Field: amount Formula: Sum ({@amount}, {Employee.Last Name}) Field: amount Formula: Sum ({@amount}, {@Quarter}) Report Header Section: Section a Fields Field: Field2 Data Source: PrintDate Data Type: Date Format: Date and Time Order: Date Time Date Type: System Default Date Format: YYYY-MM-DD hh:mm:ss AM Field: PrintTime1 Data Source: PrintTime Data Type: Time Format: Date and Time Order: Date Time Date Type: System Default Time Format: MM/DD/YY h:mm:ss AM Text Fields Text Field: Text1 Text: Employee Sales Report Text Field: Text2 Text: Quarterly report Pictures Picture: RO_Xtreme_Logo Size: 279x111 Hyperlink: http://www.businessobjects.com Boxes Box: Box2 Ends in Section: Section1 Size: 416x19 Line Type: Single Line Thickness: 20 Lines Line: Line1 Ends in Section: Section1 Line Type: Single Line Thickness: 30 Section: Section b Fields Field: EmployeeName1 Data Source: {@EmployeeName} Data Type: String SubReports SubReport: Employee Sales Sub.rpt Links: {?Employee ID} => {?Employee ID} Section: Section c Charts Chart: Graph2 Chart Type: Bar Show value(s): Sum of (@amount, Employee.Last Name) Evaluate On: @Quarter Employee.Last Name Page Header Section: Section a Conditional Formulas Condition: EnableSuppress Formula: PageNumber=1 Text Fields Text Field: Text5 Text: Quarterly report Groups Group: Group #1 Grouped By: {@Quarter} Group Header Section: Section a Fields Field: Field3 Data Source: {@Quarter} Data Type: String Text Fields Text Field: Text6 Text: Employee Text Field: Text7 Text: Sale Amount Text Field: Text8 Text: Product Class Group Footer Section: Section a Fields Field: Field14 Data Source: Sum ({@amount}, {@Quarter}) Data Type: Number Tool Tip Formula: 'Grand total of all employees for the quarter' Format: Number Currency Type: Floating Currency Symbol: $ Currency Position: Leading Outside Negative $-123 Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: $-5,555,555. Text Fields Text Field: Text10 Text Field: Text11 Text: {@Quarter} Sales: Group: Group #2 Grouped By: {Employee.Last Name} Group Header Section: Section a Conditional Formulas Condition: EnableSuppress Formula: DrillDownGroupLevel <> 2 Text Fields Text Field: Text3 Text: Employee Text Field: Text4 Text: Sale Amount Text Field: Text15 Text: Product Class Section: Section b Fields Field: Field4 Data Source: {@initial and lastname} Data Type: String Group Footer Section: Section a Fields Field: Field13 Data Source: Sum ({@amount}, {Employee.Last Name}) Data Type: Number Tool Tip Formula: "Employee sales amount for the quarter" Format: Number Currency Type: Floating Currency Symbol: $ Currency Position: Leading Outside Negative $-123 Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: $-5,555,555. Text Fields Text Field: Text9 Group: Group #3 Grouped By: {Product.Product Class} Group Header Section: Section a Conditional Formulas Condition: EnableSuppress Formula: DrillDownGroupLevel <> 3 Text Fields Text Field: Text17 Text: Sale Amount Text Field: Text18 Text: Product Class Text Field: Text19 Text: Product Type Section: Section b Fields Field: Field5 Data Source: {@product class} Data Type: String Field: Field6 Data Source: Sum ({@amount}, {Product.Product Class}) Data Type: Number Tool Tip Formula: 'Employee sales amount for product class for the quarter' Format: Number Currency Type: Floating Currency Symbol: $ Currency Position: Leading Outside Negative $-123 Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: $-5,555,555. Group: Group #4 Grouped By: {Product_Type.Product Type Name} Group Header Section: Section a Conditional Formulas Condition: EnableSuppress Formula: DrillDownGroupLevel <> 4 Text Fields Text Field: Text20 Text: Sale Amount Text Field: Text16 Text: Product Type Section: Section b Fields Field: Field7 Data Source: {Product_Type.Product Type Name} Data Type: String Field: Field8 Data Source: Sum ({@amount}, {Product_Type.Product Type Name}) Data Type: Number Tool Tip Formula: 'Employee sales amount for product type for the quarter' Format: Number Currency Type: Floating Currency Symbol: $ Currency Position: Leading Outside Negative $-123 Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: $-5,555,555. Group: Group #5 Grouped By: {Product.Product Name} Group Header Section: Section a Conditional Formulas Condition: EnableSuppress Formula: DrillDownGroupLevel <> 5 Text Fields Text Field: Text21 Text: Product Name Text Field: Text22 Text: Sale Amount Section: Section b Fields Field: Field9 Data Source: Sum ({@amount}, {Product.Product Name}) Data Type: Number Tool Tip Formula: 'Employee sales amount for product name for the quarter' Format: Number Currency Type: Floating Currency Symbol: $ Currency Position: Leading Outside Negative $-123 Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: $-5,555,555. Field: Field10 Data Source: {Product.Product Name} Data Type: String Detail Section: Section a Fields Field: Field11 Data Source: {Product.Product Name} Data Type: String Field: Field12 Data Source: {@amount} Data Type: Number Format: Number Currency Type: Floating Currency Symbol: $ Currency Position: Leading Outside Negative $-123 Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: $-5,555,555. Page Footer Section: Section a Fields Field: Field15 Data Source: PageNumber Data Type: Number Format: Number Currency Symbol: None Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: -5,555,555 Text Fields Text Field: Text12 Text: Business Objects Technical Support Site Hyperlink: http://support.businessobjects.com Text Field: Text13 Text: Copyright 2004 Business Objects Software Limited. All rights reserved. Business Objects, the Business Objects logo, Crystal Reports, Crystal Enterprise, Crystal Analysis, WebIntelligence, RapidMarts, and BusinessQuery are trademarks or registered trademarks of Business Objects and its affiliates in the United States and/or other countries. Other trademarks are the property of their respective owners. Business Objects owns the following U.S. patents, which may cover products that are offered and sold by Business Objects: 5,555,403, 6,247,008 B1, 6,578,027 B2, 6,490,593 and 6,289,352. Text Field: Text14 Text: Feedback on Samples by E-mail Hyperlink: mailto:crfeedback@businessobjects.com Pictures Picture: Picture2 Size: 150x40 Hyperlink: http://www.businessobjects.com Lines Line: Line2 Ends in Section: Section19 Line Type: Single Line Thickness: 30 Subreport: Employee Sales Sub.rpt Data Sources Source: Connection #1 Database DLL: crdb_odbc.dll Database Type: ODBC (RDO) Server: Xtreme Sample Database 11.5 Properties: QE_LogonProperties DSN: Xtreme Sample Database 11.5 UseDSNProperties: False QE_SQLDB: True SSO Enabled: False Report Parameters Parameter: Employee ID Data Type: Number Description: Enter Login Name: List Type: Static Tables Table: Employee Connection: Connection #1 Used Columns: {Employee.Employee ID} : Number {Employee.Last Name} : String[42] {Employee.First Name} : String[22] Table: Orders Connection: Connection #1 Used Columns: {Orders.Order ID} : Number {Orders.Employee ID} : Number {Orders.Order Date} : Date Table: Orders Detail Connection: Connection #1 Report Alias: Orders_Detail Used Columns: {Orders_Detail.Unit Price} : Number {Orders_Detail.Quantity} : Number Table: Product Connection: Connection #1 Used Columns: {Product.Product ID} : Number {Product.Product Name} : String[102] {Product.Product Type ID} : Number {Product.Product Class} : String[102] Table: Product Type Connection: Connection #1 Report Alias: Product_Type Used Columns: {Product_Type.Product Type Name} : String[102] Table Links Joins: Orders INNER JOIN [Orders Detail] Orders_Detail ON Orders.[Order ID] = Orders_Detail.[Order ID] Orders INNER JOIN Employee ON Orders.[Employee ID] = Employee.[Employee ID] [Orders Detail] Orders_Detail INNER JOIN Product ON Orders_Detail.[Product ID] = Product.[Product ID] Product INNER JOIN [Product Type] Product_Type ON Product.[Product Type ID] = Product_Type.[Product Type ID] Record Selection Formulas Formula: {Orders.Order Date} in Date (2004, 01, 01) to Date (2004, 03, 31) Record Sorting Sort By: @Quarter -- Ascending Order Group #1 Sum of (@amount, Employee.Last Name) -- Descending Order Product.Product Class -- Ascending Order Group #3 Product_Type.Product Type Name -- Ascending Order Group #4 Product.Product Name -- Ascending Order Group #5 Formula Fields Field: amount Formula: {Orders_Detail.Unit Price}*{Orders_Detail.Quantity} Field: product class Formula: if {Product.Product Class}="Accessory" then "Accessories" else if {Product.Product Class}="Bicycle" then "Bicycles" Field: Quarter Formula: if Month({Orders.Order Date}) in [1, 2, 3] then "Quarter 1, " + totext(Year({Orders.Order Date}), 0, "") else if Month({Orders.Order Date}) in [4, 5, 6] then "Quarter 2, " + totext(Year({Orders.Order Date}), 0, "") else if Month({Orders.Order Date}) in [7, 8, 9] then "Quarter 3, " + totext(Year({Orders.Order Date}), 0, "") else if Month({Orders.Order Date}) in [10, 11, 12] then "Quarter 4, " + totext(Year({Orders.Order Date}), 0, "") Field: initial and lastname Formula: {Employee.Last Name} + ", " + {Employee.First Name}[1] Field: determine ranking Formula: //This formula determines the ranking of an employee. Basically the counter //value adds up by 1 for each group section that is printed. If the parameter //?login name is the same as the employees ID then in a variable //called ranking take the value of the counter, otherwise do not change the //ranking value Whileprintingrecords; numbervar counter := counter + 1; numbervar ranking; stringvar EmployeeName; if {?Employee ID} = {Employee.Employee ID} then ( ranking := counter; EmployeeName := {Employee.First Name} + " " + {Employee.Last Name}; ) Field: fetch rank Formula: Whileprintingrecords; numbervar ranking Field: EmployeeName Formula: WhilePrintingRecords; StringVar EmployeeName; Summary Fields Field: amount Formula: Sum ({@amount}, {Product.Product Class}) Field: amount Formula: Sum ({@amount}, {Product_Type.Product Type Name}) Field: amount Formula: Sum ({@amount}, {Product.Product Name}) Field: amount Formula: Sum ({@amount}, {Employee.Last Name}) Field: amount Formula: Sum ({@amount}, {@Quarter}) Report Header Section: Section a Fields Field: Field2 Data Source: PrintDate Data Type: Date Format: Date and Time Order: Date Time Date Type: System Default Date Format: YYYY-MM-DD hh:mm:ss AM Field: PrintTime1 Data Source: PrintTime Data Type: Time Format: Date and Time Order: Date Time Date Type: System Default Time Format: MM/DD/YY h:mm:ss AM Text Fields Text Field: Text1 Text: Employee Sales Report Text Field: Text2 Text: Quarterly report Pictures Picture: Picture1 Size: 279x111 Hyperlink: http://www.businessobjects.com Tool Tip Formula: "http://www.crystaldecisions.com" Boxes Box: Box2 Ends in Section: Section1 Size: 416x19 Line Type: Single Line Thickness: 20 Lines Line: Line1 Ends in Section: Section1 Line Type: Single Line Thickness: 30 Section: Section b Conditional Formulas Condition: EnableSuppress Formula: PageNumber=1 Text Fields Text Field: Text5 Text: Quarterly report Headings Heading: Text14 Text: EmployeeName Heading: Text15 Text: fetch rank Groups Group: Group #1 Grouped By: {@Quarter} Group Header Section: Section a Fields Field: Field3 Data Source: {@Quarter} Data Type: String Text Fields Text Field: Text6 Text: Employee Text Field: Text7 Text: Sale Amount Text Field: Text8 Text: Product Type Group Footer Section: Section a Fields Field: Field14 Data Source: Sum ({@amount}, {@Quarter}) Data Type: Number Format: Number Currency Type: Floating Currency Symbol: $ Currency Position: Leading Outside Negative $-123 Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: $-5,555,555. Field: EmployeeName1 Data Source: {@EmployeeName} Data Type: String Text Fields Text Field: Text10 Text Field: Text11 Text: {@Quarter} Sales: Group: Group #2 Grouped By: {Employee.Last Name} Group Header Section: Section a Fields Field: Field4 Data Source: {@initial and lastname} Data Type: String Field: Field16 Data Source: {@determine ranking} Data Type: String Group Footer Section: Section a Fields Field: Field13 Data Source: Sum ({@amount}, {Employee.Last Name}) Data Type: Number Tool Tip Formula: "Employee sales amount for the quarter" Format: Number Currency Type: Floating Currency Symbol: $ Currency Position: Leading Outside Negative $-123 Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: $-5,555,555. Text Fields Text Field: Text9 Group: Group #3 Grouped By: {Product.Product Class} Group Header Section: Section a Fields Field: Field5 Data Source: {@product class} Data Type: String Field: Field6 Data Source: Sum ({@amount}, {Product.Product Class}) Data Type: Number Format: Number Currency Type: Floating Currency Symbol: $ Currency Position: Leading Outside Negative $-123 Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: $-5,555,555. Group: Group #4 Grouped By: {Product_Type.Product Type Name} Group Header Section: Section a Fields Field: Field7 Data Source: {Product_Type.Product Type Name} Data Type: String Field: Field8 Data Source: Sum ({@amount}, {Product_Type.Product Type Name}) Data Type: Number Format: Number Currency Type: Floating Currency Symbol: $ Currency Position: Leading Outside Negative $-123 Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: $-5,555,555. Group: Group #5 Grouped By: {Product.Product Name} Group Header Section: Section a Fields Field: Field9 Data Source: Sum ({@amount}, {Product.Product Name}) Data Type: Number Format: Number Currency Type: Floating Currency Symbol: $ Currency Position: Leading Outside Negative $-123 Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: $-5,555,555. Field: Field10 Data Source: {Product.Product Name} Data Type: String Detail Section: Section a Fields Field: Field11 Data Source: {Product.Product Name} Data Type: String Field: Field12 Data Source: {@amount} Data Type: Number Format: Number Currency Type: Floating Currency Symbol: $ Currency Position: Leading Outside Negative $-123 Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: $-5,555,555. Field: fetchrank1 Data Source: {@fetch rank} Data Type: Number Format: Number Currency Symbol: None Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: -5,555,555. Report Footer Section: Section a Text Fields Text Field: Text3 Text: {@EmployeeName} you are ranked #{@fetch rank} this Quarter Section: Section b Fields Field: Field15 Data Source: PageNumber Data Type: Number Format: Number Currency Symbol: None Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: -5,555,555 Text Fields Text Field: Text12 Text: Business Objects Technical Support Site Hyperlink: http://support.businessobjects.com Text Field: Text13 Text: Copyright 2004 Business Objects Software Limited. All rights reserved. Business Objects, the Business Objects logo, Crystal Reports, Crystal Enterprise, Crystal Analysis, WebIntelligence, RapidMarts, and BusinessQuery are trademarks or registered trademarks of Business Objects and its affiliates in the United States and/or other countries. Other trademarks are the property of their respective owners. Business Objects owns the following U.S. patents, which may cover products that are offered and sold by Business Objects: 5,555,403, 6,247,008 B1, 6,578,027 B2, 6,490,593 and 6,289,352. Text Field: Text4 Text: Feedback on Samples by E-mail Hyperlink: mailto:crfeedback@businessobjects.com Pictures Picture: Picture2 Size: 150x40 Hyperlink: http://www.businessobjects.com Lines Line: Line2 Ends in Section: Section21 Line Type: Single Line Thickness: 30