**************************************************************************** Location : Local or Network Folders Host : FINDITEZ-T460P Path : C:\Users\ken\Documents\Test Cases\SSRS\Sales With OnDemand Sub.rpt **************************************************************************** Report Information Version: 11.0 Author: © 2002 Crystal Decisions, Inc. 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_ado.dll Database Name: Xtreme115DB Database Type: OLE DB (ADO) Server: CRMIGRATION\SQLExpress Properties: QE_LogonProperties Application Intent: READWRITE Auto Translate: -1 Connect Timeout: 15 Data Source: CRMIGRATION\SQLExpress DataTypeCompatibility: 0 General Timeout: 0 Initial Catalog: Xtreme115DB Integrated Security: True Locale Identifier: 4105 MARS Connection: 0 OLE DB Services: -5 Provider: SQLNCLI11 Tag with column collation when possible: 0 Trust Server Certificate: 0 Use DSN Default Properties: False Use Encryption for Data: 0 QE_SQLDB: True SSO Enabled: False Report Parameters Parameter: Login Name Data Type: String Description: Enter Login Name List Type: Static List Values: Anne Dodsworth Michael Suyama Robert King Janet Leverling Nancy Davolio Margaret Peacock Default Values: Anne Dodsworth Michael Suyama Robert King Janet Leverling Nancy Davolio Margaret Peacock Tables Table: Xtreme115DB.dbo.Employee Connection: Connection #1 Used Columns: {Employee.Employee ID} : Number {Employee.Last Name} : String[42] {Employee.First Name} : String[22] Table: Xtreme115DB.dbo.Orders Connection: Connection #1 Used Columns: {Orders.Order ID} : Number {Orders.Employee ID} : Number {Orders.Order Date} : Date Table: Xtreme115DB.dbo.Orders Detail Connection: Connection #1 Report Alias: Orders_Detail Used Columns: {Orders_Detail.Unit Price} : Number {Orders_Detail.Quantity} : Number Table: Xtreme115DB.dbo.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: Xtreme115DB.dbo.Product Type Connection: Connection #1 Report Alias: Product_Type Used Columns: {Product_Type.Product Type Name} : String[102] Table Links Joins: Xtreme115DB.dbo.Orders INNER JOIN [Xtreme115DB.dbo.Orders Detail] Orders_Detail ON Xtreme115DB.dbo.Orders.[Order ID] = Orders_Detail.[Order ID] Xtreme115DB.dbo.Orders INNER JOIN Xtreme115DB.dbo.Employee ON Xtreme115DB.dbo.Orders.[Employee ID] = Xtreme115DB.dbo.Employee.[Employee ID] [Xtreme115DB.dbo.Orders Detail] Orders_Detail INNER JOIN Xtreme115DB.dbo.Product ON Orders_Detail.[Product ID] = Xtreme115DB.dbo.Product.[Product ID] Xtreme115DB.dbo.Product INNER JOIN [Xtreme115DB.dbo.Product Type] Product_Type ON Xtreme115DB.dbo.Product.[Product Type ID] = Product_Type.[Product Type ID] Record Selection Formulas Formula: {Orders.Order Date} in Date (2003, 01, 01) to Date (2003, 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] 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: Field1 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: Field2 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: Employee Sales Quarterly report Pictures Picture: Picture4 Size: 286x107 Lines Line: Line1 Ends in Section: Section1 Line Type: Single Line Thickness: 30 Section: Section b Charts Chart: Graph1 Chart Type: Bar Show value(s): Sum of (@amount, Employee.Last Name) Evaluate On: @Quarter Employee.Last Name Page Header Section: Section a Fields Field: Field3 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: Field4 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: Text5 Text: Employee Sales Quarterly report Groups Group: Group #1 Grouped By: {@Quarter} Group Header Section: Section a Fields Field: Field5 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 Text Field: Text15 Text: Sales by month Boxes Box: Box1 Ends in Section: Section6 Size: 711x20 Line Type: None Line Thickness: 20 Group Footer Section: Section a Fields Field: Field16 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. 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: Field6 Data Source: {@initial and lastname} Data Type: String SubReports SubReport: subsales.rpt Links: {Employee.Last Name} => {?Pm-Employee.Last Name} {Employee.First Name} => {?Pm-Employee.First Name} Tool Tip Formula: "Subreports can now be specified as RealTime the time of creation in the Insert Subreport dialog box" Group Footer Section: Section a Fields Field: Field15 Data Source: Sum ({@amount}, {Employee.Last 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. Text Fields Text Field: Text9 Group: Group #3 Grouped By: {Product.Product Class} Group Header Section: Section a Fields Field: Field7 Data Source: {@product class} Data Type: String Field: Field8 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: Field9 Data Source: {Product_Type.Product Type Name} Data Type: String Field: Field10 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: Field11 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: Field12 Data Source: {Product.Product Name} Data Type: String Detail Section: Section a Fields Field: Field13 Data Source: {Product.Product Name} Data Type: String Field: Field14 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: Field17 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: Text3 Text: Crystal Care Support Site Hyperlink: http://support.crystaldecisions.com Text Field: Text4 Text: © Copyright 2002 Crystal Decisions, Inc. All Rights Reserved. Text Field: Text12 Text: Feedback on Samples by E-mail Hyperlink: mailto:crfeedback@crystaldecisions.com Pictures Picture: Picture2 Size: 179x50 Hyperlink: http://www.crystaldecisions.com Lines Line: Line2 Ends in Section: Section20 Line Type: Single Line Thickness: 30 Subreport: subsales.rpt Data Sources Source: Connection #1 Database DLL: crdb_ado.dll Database Name: Xtreme115DB Database Type: OLE DB (ADO) Server: CRMIGRATION\SQLExpress Properties: QE_LogonProperties Application Intent: READWRITE Auto Translate: -1 Connect Timeout: 15 Data Source: CRMIGRATION\SQLExpress DataTypeCompatibility: 0 General Timeout: 0 Initial Catalog: Xtreme115DB Integrated Security: True Locale Identifier: 4105 MARS Connection: 0 OLE DB Services: -5 Provider: SQLNCLI11 Tag with column collation when possible: 0 Trust Server Certificate: 0 Use DSN Default Properties: False Use Encryption for Data: 0 QE_SQLDB: True SSO Enabled: False Report Parameters Parameter: Pm-Employee.Last Name Data Type: String Description: Pm-Employee.Last Name List Type: Static Default Values: King Parameter: Pm-Employee.First Name Data Type: String Description: Pm-Employee.First Name List Type: Static Default Values: Robert Tables Table: Xtreme115DB.dbo.Employee Connection: Connection #1 Used Columns: {Employee.Employee ID} : Number {Employee.Last Name} : String[42] {Employee.First Name} : String[22] Table: Xtreme115DB.dbo.Orders Connection: Connection #1 Used Columns: {Orders.Order ID} : Number {Orders.Order Amount} : Number {Orders.Employee ID} : Number {Orders.Order Date} : Date Table: Xtreme115DB.dbo.Orders Detail Connection: Connection #1 Report Alias: Orders_Detail Used Columns: {Orders_Detail.Unit Price} : Number {Orders_Detail.Quantity} : Number Table: Xtreme115DB.dbo.Product Connection: Connection #1 Used Columns: {Product.Product ID} : Number {Product.Product Class} : String[102] Table Links Joins: Xtreme115DB.dbo.Orders INNER JOIN Xtreme115DB.dbo.Employee ON Xtreme115DB.dbo.Orders.[Employee ID] = Xtreme115DB.dbo.Employee.[Employee ID] Xtreme115DB.dbo.Orders INNER JOIN [Xtreme115DB.dbo.Orders Detail] Orders_Detail ON Xtreme115DB.dbo.Orders.[Order ID] = Orders_Detail.[Order ID] [Xtreme115DB.dbo.Orders Detail] Orders_Detail INNER JOIN Xtreme115DB.dbo.Product ON Orders_Detail.[Product ID] = Xtreme115DB.dbo.Product.[Product ID] Record Selection Formulas Formula: {Orders.Order Date} in Date (2003, 01, 01) to Date (2003, 03, 31) and {Orders.Order Amount}>0 and {Employee.Last Name} = {?Pm-Employee.Last Name} and {Employee.First Name} = {?Pm-Employee.First Name} Record Sorting Sort By: @MyName -- Ascending Order Group #1 Orders.Order Date -- Ascending Order Group #2 Orders.Order Date -- Ascending Order Group #2 Orders.Order Date -- Ascending Order Group #2 Formula Fields Field: MyDate Formula: //Define Variables StringVar mm; StringVar dd; StringVar yy; //Initialize Variables mm := ""; dd := ""; yy := ""; //Stuff Variables With Values if Month(Today) < 10 then mm := "0" + ToText (Month (Today),0) else mm := ToText (Month (Today),0); if Day(Today) < 10 then dd := "0" + ToText (Day (Today),0) else dd := ToText (Day (Today),0); yy := ToText (Year (Today) - 1900,0); //Return this string "Print Date: " + mm + "/" + dd + "/" + yy //TrevorS Field: MyName Formula: {Employee.First Name} +" " + {Employee.Last Name} Field: header Formula: {@MyMonth}+" Sales" Field: MyMonth Formula: Numbervar x:=Month({Orders.Order Date}); if x=1 then "January" else if x=2 then "February" else if x=3 then "March" else if x=4 then "April" else if x= 5 then "May" else if x=6 then "June" else if x=7 then"July" else if x= 8 then "August" else if x=9 then "September" else if x=10 then "October" else if x=11 then"November" else if x=12 then "December" Field: MyWeekly Formula: "Week of "+GroupName ({Orders.Order Date}, "weekly") Field: Accessory Price Formula: if {Product.Product Class}="Accessory" then {Orders_Detail.Unit Price}*{Orders_Detail.Quantity} Field: Bicycle Price Formula: if {Product.Product Class}="Bicycle" then {Orders_Detail.Unit Price}*{Orders_Detail.Quantity} Field: Indiv Total Formula: {@Accessory Price}+{@Bicycle Price} Field: totals Formula: {@MyMonth}+" Totals:" Summary Fields Field: Accessory Price Formula: Sum ({@Accessory Price}, {Orders.Order Date}, "daily") Field: Bicycle Price Formula: Sum ({@Bicycle Price}, {Orders.Order Date}, "daily") Field: Indiv Total Formula: Sum ({@Indiv Total}, {Orders.Order Date}, "daily") Field: Accessory Price Formula: Sum ({@Accessory Price}, {Orders.Order Date}, "weekly") Field: Bicycle Price Formula: Sum ({@Bicycle Price}, {Orders.Order Date}, "weekly") Field: Indiv Total Formula: Sum ({@Indiv Total}, {Orders.Order Date}, "weekly") Field: Accessory Price Formula: Sum ({@Accessory Price}, {Orders.Order Date}, "monthly") Field: Bicycle Price Formula: Sum ({@Bicycle Price}, {Orders.Order Date}, "monthly") Field: Indiv Total Formula: Sum ({@Indiv Total}, {Orders.Order Date}, "monthly") Field: Accessory Price Formula: Sum ({@Accessory Price}, {@MyName}) Field: Bicycle Price Formula: Sum ({@Bicycle Price}, {@MyName}) Field: Indiv Total Formula: Sum ({@Indiv Total}, {@MyName}) Field: Accessory Price Formula: Sum ({@Accessory Price}) Field: Bicycle Price Formula: Sum ({@Bicycle Price}) Field: Indiv Total Formula: Sum ({@Indiv Total}) Report Header Section: Section b Fields Field: Field1 Data Source: {@MyDate} Data Type: String Text Fields Text Field: Text1 Text: Monthly Sales Revenue by Employee Text Field: Text2 Text: Product Pictures Picture: Picture4 Size: 286x107 Lines Line: Line1 Ends in Section: Section2 Line Type: Single Line Thickness: 30 Groups Group: Group #1 Grouped By: {@MyName} Group Header Section: Section a Fields Field: Field2 Data Source: {@MyName} Data Type: String Group Footer Section: Section a Text Fields Text Field: Text7 Text: Totals for {@MyName} Text Field: Text8 Text: Accessories Text Field: Text9 Text: Bicycles Text Field: Text10 Text: Individual Total Section: Section b Fields Field: Field20 Data Source: Sum ({@Accessory Price}, {@MyName}) 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: Field21 Data Source: Sum ({@Bicycle Price}, {@MyName}) 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: Field22 Data Source: Sum ({@Indiv Total}, {@MyName}) 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 #2 Grouped By: {Orders.Order Date} Group Header Section: Section a Fields Field: Field3 Data Source: {@header} Data Type: String Text Fields Text Field: Text3 Text: Accessories Text Field: Text4 Text: Bicycles Text Field: Text5 Text: Individual Total Group Footer Section: Section a Fields Field: Field16 Data Source: {@totals} Data Type: String Field: Field17 Data Source: Sum ({@Accessory Price}, {Orders.Order Date}, "monthly") 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: Field18 Data Source: Sum ({@Bicycle Price}, {Orders.Order Date}, "monthly") 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: Field19 Data Source: Sum ({@Indiv Total}, {Orders.Order Date}, "monthly") 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 #3 Grouped By: {Orders.Order Date} Group Header Section: Section a Fields Field: Field4 Data Source: {@MyWeekly} Data Type: String Group Footer Section: Section a Fields Field: Field13 Data Source: Sum ({@Accessory Price}, {Orders.Order Date}, "weekly") 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: Field14 Data Source: Sum ({@Bicycle Price}, {Orders.Order Date}, "weekly") 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: Field15 Data Source: Sum ({@Indiv Total}, {Orders.Order Date}, "weekly") 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. Text Fields Text Field: Text6 Text: Weekly Total Group: Group #4 Grouped By: {Orders.Order Date} Group Header Section: Section a Fields Field: Field5 Data Source: GroupName ({Orders.Order Date}, "daily") Data Type: String Group Footer Section: Section a Fields Field: Field9 Data Source: {Orders.Order Date} Data Type: Date Format: Date and Time Order: Date Time Date Type: System Default Date Format: YYYY-MM-DD hh:mm:ss AM Field: Field10 Data Source: Sum ({@Accessory Price}, {Orders.Order Date}, "daily") 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: Field11 Data Source: Sum ({@Bicycle Price}, {Orders.Order Date}, "daily") 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: Field12 Data Source: Sum ({@Indiv Total}, {Orders.Order Date}, "daily") 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. Detail Section: Section a Fields Field: Field6 Data Source: {@Accessory Price} 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: Field7 Data Source: {@Bicycle Price} 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: Field8 Data Source: {@Indiv Total} 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. Report Footer Section: Section a Fields Field: Field23 Data Source: Sum ({@Accessory Price}) 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: Field24 Data Source: Sum ({@Bicycle Price}) 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: Field25 Data Source: Sum ({@Indiv Total}) 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. Text Fields Text Field: Text11 Text: Grand Totals: Text Field: Text12 Text: Accessories: Text Field: Text13 Text: Bicycles: Text Field: Text14 Text: Total: