**************************************************************************** Location : Local or Network Folders Host : FINDITEZ-T460P Path : C:\Users\ken\Documents\Test Cases\en\Samples\Reports\Financial\Sorted Variance Analysis Report.rpt **************************************************************************** Report Information Version: 11.0 Author: Business Objects Comments: This report shows the variances betweent he actual and budgeted amounts for an account Title: Sorted Variance Analysis 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: End Date Data Type: DateTime Description: End Date? List Type: Static List Values: 2005-02-14 11:24:28 AM 2005-02-14 12:03:53 PM Default Values: 2005-02-28 11:24:28 AM Tables Table: Account Connection: Connection #1 Used Columns: {Account.Account Number} : String[18] {Account.Account Type ID} : Number {Account.Account Name} : String[102] Table: Account Type Connection: Connection #1 Report Alias: Account_Type Used Columns: {Account_Type.Account Type} : String[42] Table: Command Connection: Connection #1 SQL Command: SELECT Sum(Amount) AS Year_Budget, [Account Number] FROM [Monthly Account Budgets] WHERE Year=Year({?End Date}) and Month <= Month({?End Date}) GROUP BY [Account Number]; Parameters: End Date Used Columns: {Command.Year_Budget} : Number {Command.Account Number} : String[18] Table: Journal Entry Connection: Connection #1 Report Alias: Journal_Entry Used Columns: {Journal_Entry.Journal Entry ID} : Number {Journal_Entry.Account Number} : String[18] {Journal_Entry.Date} : DateTime {Journal_Entry.Amount} : Number {Journal_Entry.Debit Or Credit} : String[22] Table: Command_1 Connection: Connection #1 SQL Command: SELECT [Amount], [Account Number] FROM [Monthly Account Budgets] WHERE Year=Year({?End Date}) and Month = Month({?End Date}) Parameters: End Date Used Columns: {Command_1.Amount} : Number {Command_1.Account Number} : String[18] Table: Journal Entry Type Connection: Connection #1 Report Alias: Journal_Entry_Type Used Columns: {Journal_Entry_Type.Journal Entry Type} : String[102] Table Links Joins: [Journal Entry Type] Journal_Entry_Type INNER JOIN [Journal Entry] Journal_Entry ON Journal_Entry_Type.[Journal Entry Type ID] = Journal_Entry.[Journal Entry Type ID] Command INNER JOIN Account ON Command.[Account Number] = Account.[Account Number] Command_1 INNER JOIN Account ON Command_1.[Account Number] = Account.[Account Number] Account INNER JOIN [Account Type] Account_Type ON Account.[Account Type ID] = Account_Type.[Account Type ID] Account INNER JOIN [Journal Entry] Journal_Entry ON Account.[Account Number] = Journal_Entry.[Account Number] Record Selection Formulas Formula: {Journal_Entry_Type.Journal Entry Type} <> "closing entries" and {Journal_Entry.Date} <= {?End Date} and {Journal_Entry.Date} >= dateserial(year({?End Date}),1,1) and {Account_Type.Account Type} in ["Expense", "Revenue"] Record Sorting Sort By: Account_Type.Account Type -- Original Order Group #1 Account.Account Name -- Ascending Order Group #2 Formula Fields Field: YTD_balance Formula: if {Journal_Entry.Debit Or Credit} = 'Credit' then ( {Journal_Entry.Amount} * -1; ) else ( {Journal_Entry.Amount}; ) Field: YTD_Variance Formula: if {Account_Type.Account Type} = 'Revenue' and {Account.Account Name} <> 'Sales Returns' then ( {Command.Year_Budget} + Sum ({@YTD_balance}, {Account.Account Name}) ) else ( {Command.Year_Budget} - Sum ({@YTD_balance}, {Account.Account Name}) ) Field: month_variance Formula: if {Account_Type.Account Type} = 'Revenue' and {Account.Account Name} <> 'Sales Returns' then ( {Command_1.Amount} + Sum ({@month_balance}, {Account.Account Name}) ) else ( {Command_1.Amount} - Sum ({@month_balance}, {Account.Account Name}) ) Field: month Formula: monthname(month({?End Date})) Field: month_balance Formula: if month({Journal_Entry.Date}) = month({?End Date}) then ( if {Journal_Entry.Debit Or Credit} = 'Credit' then ( {Journal_Entry.Amount} * -1; ) else ( {Journal_Entry.Amount}; ) ) Summary Fields Field: month_balance Formula: Sum ({@month_balance}, {Account.Account Name}) Field: YTD_balance Formula: Sum ({@YTD_balance}, {Account.Account Name}) Page Header Section: Section a Fields Field: PrintDate2 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 Field: month1 Data Source: {@month} Data Type: String Text Fields Text Field: Text13 Text: Actual Text Field: Text14 Text: YTD Text Field: Text15 Text: Variance Text Field: Text20 Text: Variance Text Field: Text19 Text: Budget Text Field: Text18 Text: Actual Text Field: Text11 Text: Budget Text Field: Text3 Text: Xtreme Mountain Bikes Text Field: Text5 Text: For the Months Ending {?End Date} Text Field: Text4 Text: Variance Analysis Text Field: Text1 Text: Variance Analysis Text Field: Text2 Text: How to create a Variance Analysis Report Pictures Picture: RO_Xtreme_Logo Size: 279x111 Hyperlink: http://www.businessobjects.com Boxes Box: Box1 Ends in Section: PageHeaderSection1 Size: 416x19 Line Type: Single Line Thickness: 20 Lines Line: Line1 Ends in Section: PageHeaderSection1 Line Type: Single Line Thickness: 30 Line: Line4 Ends in Section: PageHeaderSection1 Line Type: Single Line Thickness: 30 Groups Group: Group #1 Grouped By: {Account_Type.Account Type} Group Header Section: Section a Fields Field: GroupNameAccountType1 Data Source: GroupName ({Account_Type.Account Type}) Data Type: String Group: Group #2 Grouped By: {Account.Account Name} Group Footer Section: Section a Fields Field: YearBudget1 Data Source: {Command.Year_Budget} 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: Amount1 Data Source: {Command_1.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: GroupNameAccountNumber1 Data Source: GroupName ({Account.Account Name}) Data Type: String Field: YTDVariance1 Data Source: {@YTD_Variance} 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: monthvariance1 Data Source: {@month_variance} 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: Sumofmonthbalance1 Data Source: Sum ({@month_balance}, {Account.Account 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: True Number Style: $(5,555,555). Field: SumofYTDbalance1 Data Source: Sum ({@YTD_balance}, {Account.Account 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: True Number Style: $(5,555,555). Detail Section: Section a Fields Field: YTDbalance1 Data Source: {@YTD_balance} 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: monthbalance1 Data Source: {@month_balance} 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: JournalEntryID1 Data Source: {Journal_Entry.Journal Entry ID} Data Type: Number Format: Number Currency Symbol: None Suppress if Zero: False Leading Zero: True Reverse Sign: False Number Style: -5555555 Field: AccountNumber1 Data Source: {Journal_Entry.Account Number} Data Type: String Field: Date1 Data Source: {Journal_Entry.Date} Data Type: DateTime Format: Date and Time Order: Date Only Date Type: Custom DateTime Format: M/D/YY Page Footer Section: Section a Fields Field: PageNumber1 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: Text6 Text: Feedback on Samples by E-mail Hyperlink: mailto:crfeedback@businessobjects.com Text Field: Text7 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: Text8 Text: Business Objects Technical Support Site Hyperlink: http://support.businessobjects.com Pictures Picture: Picture3 Size: 150x40 Hyperlink: http://www.businessobjects.com Lines Line: Line3 Ends in Section: PageFooterSection1 Line Type: Single Line Thickness: 30