Date Parameter doesn't return data from 2012. Stops at 12/31/2011

[SkipWhitten]

Hi,
I have a report that includes a date parameter (equal to ot greater than) that won't return data that has a date in next year (2012). I did not create this but the end users tell me that this happens every December and that the previous consultant would tweak it and they'ld be good for the next 12 months. I would appreciate any guidance and I thank you in advance.



Skip

Skip Whitten
IT Consultant

http://www.skipwhitten.info

http://www.biocompatibles.com

 

[Turkbear]

Hi,
Show us the selection formula that uses the parameter and also confirm that the parameter is a Date type.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SkipWhitten]

The parameter is string. Here is the formula to switch it around.
if length ({tsoSalesOrder.userFld1}) = 10
then {tsoSalesOrder.userFld1}[6 to 7]+ "/" + {tsoSalesOrder.userFld1}[9 to 10] + "/" + {tsoSalesOrder.userFld1}[3 to 4]
else {tsoSalesOrder.userFld1}
The date displays like this after: 12/13/11.

Here is the 'Select'
not ({tsoSalesOrder.Status} in [4]) and
{tsoSalesOrder.CompanyID} = "BIO" and
{tsoSalesOrder.DfltPromDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.DfltShipDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.Expiration} >= DateTime (2011, 12, 01, 00, 00, 00) and
{@Seed Arrival Date} >= {?Seed Arrival Date}

Thanks,

Skip

Skip Whitten
IT Consultant

http://www.skipwhitten.info

http://www.biocompatibles.com

 

[RTag]

I would start with checking the report for hard-coded 2011 or 2012.
The easiest way I know is to export the report to Report Definition file.
Menu: File->Export->Export Report and select Report Definition (TXT). This will export all formulas in a flat text file and will allow you to search easier.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[SkipWhitten]

Crystal Report Professional v10.0 (32-bit) - Report Definition

1.0 File Information

Report File:
Version: 10.2

2.0 Record Sort Fields
A - {tsoSalesOrder.Expiration}
A - {tsoSalesOrder.UserFld1}

3.0 Group Sort Fields

4.0 Formulas

4.1 Record Selection Formula
not ({tsoSalesOrder.Status} in [4]) and
{tsoSalesOrder.CompanyID} = "BIO" and
{tsoSalesOrder.DfltPromDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.DfltShipDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.Expiration} >= DateTime (2011, 12, 01, 00, 00, 00) and
{@Seed Arrival Date} >= {?Seed Arrival Date}

4.2 Group Selection Formula


4.3 Other Formulas

Name: {@l1}
Formula: if {?Item Types}={SALES_ORDER_LINE_0.IT1} then {SALES_ORDER_LINE_0.SO1}
else ""

Name: {@l2}
Formula: if {?Item Types}={SALES_ORDER_LINE_0.IT2} then {SALES_ORDER_LINE_0.SO2}
else ""

Name: {@l4}
Formula: if {?Item Types}={SALES_ORDER_LINE_0.IT4} then {SALES_ORDER_LINE_0.SO4}
else ""

Name: {@l3}
Formula: if {?Item Types}={SALES_ORDER_LINE_0.IT3} then {SALES_ORDER_LINE_0.SO3}
else ""

Name: {@Sales Order Status}
Formula: if {tsoSalesOrder.Status}= 3 then "CANCELLED" else ""

Name: {@Seed Company}
Formula: If {tarCustClass.CustClassID} like "BRY*" then "BRACHY" else {tarNationalAcct.NationalAcctID}

Name: {@Seed Arrival Date}
Formula: if length ({tsoSalesOrder.userFld1}) = 10
then {tsoSalesOrder.userFld1}[6 to 7]+ "/" + {tsoSalesOrder.userFld1}[9 to 10] + "/" + {tsoSalesOrder.userFld1}[3 to 4]
else {tsoSalesOrder.userFld1}

5.0 Sectional Information

5.1 Page Header Section
Visible, Keep Together

Patient Name
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Seed Company
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Ship To Facility
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Seed Arrive
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Products(s)
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Isotope
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Make Date
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Ship Date
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Due to Fac.
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Case Date
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Seed Total
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Seed Arrival Report - Sorted by Seed Arrival, Make Date, Ship Date
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



{tsoSalesOrder.CompanyID}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{?Seed Arrival Date}
String, Hidden, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


Vendor Order #
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



5.2 Page Footer Section
Visible, New Page After, Keep Together, Print At Bottom of Page

PageNofM
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


PrintTime
Time, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Time Base: 12 Hour, AM/PM Type: AM/PM After, Hour Type: Numeric No Leading Zero, Minute Type: Numeric, Second Type: Numeric, AM String: 'AM', PM String: 'PM', Hour Minute Separator: ':', Minute Second Separator: ':'


PrintDate
Date, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Windows Default Type: Use Windows Short Date, Date Order: Month Day Year, Year Type: Long, Month Type: Numeric Month, Day Type: Numeric Day, Leading Day Type: None, First Separator: '/', Second Separator: '/', Leading Day Separator: ''


Print Date:
String, Visible, Left Alignment, Top Alignment,
Keep Together



Filename
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


5.3 Report Header Section
Hidden, New Page Before

5.4 Report Footer Section
Visible, New Page After

5.5 Group Header Section #1
Visible

GroupName ({@Seed Arrival Date})
String, Visible, Right Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


DistinctCount ({tsoSalesOrder.SOKey}, {@Seed Arrival Date})
Numeric, Visible, Horizontal Centre Alignment, Top Alignment,
Keep Together, Expand,
Bracketed, 0 Decimal Places, Rounding: 1,
Thousands Symbol: ',', Decimal Symbol: '.'


5.6 Group Footer Section #1
Hidden

5.7 Group Header Section #2
Hidden

GroupName ({tsoSalesOrder.Expiration}, "daily")
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


5.8 Group Footer Section #2
Hidden

5.9 Group Header Section #3
Hidden

GroupName ({tsoSalesOrder.DfltShipDate}, "daily")
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


5.10 Group Footer Section #3
Hidden

5.11 Group Header Section #4
Hidden

5.12 Group Footer Section #4
Visible

{@l1}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{@l2}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{@l4}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tsoSalesOrder.Expiration}
Date Time, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Date Time Order: Date Only, Separator: ' '


{tsoSalesOrder.DfltShipDate}
Date Time, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Date Time Order: Date Only, Separator: ' '


{tsoSalesOrder.DfltPromDate}
Date Time, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Date Time Order: Date Only, Separator: ' '


{tsoSalesOrder.DfltRequestDate}
Date Time, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Date Time Order: Date Only, Separator: ' '


{tsoSalesOrder.UserFld4}
String, Visible, Horizontal Centre Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tsoSalesOrder.UserFld3}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tarCustomer.CustName}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tsoSalesOrder.CustPONo}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tarNationalAcct.NationalAcctID}
String, Hidden, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{@l3}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{@Sales Order Status}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tsoSalesOrder.SOKey}
Numeric, Hidden, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting,
Leading Minus, 0 Decimal Places, Rounding: 1,
Thousands Symbol: ',', Decimal Symbol: '.'


{tsoSalesOrder.UserFld1}
String, Hidden, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tarCustClass.CustClassID}
String, Hidden, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap


{tarCustClass.CustClassName}
String, Hidden, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap


{@Seed Company}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap


{tsoSalesOrder.UserFld2}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap


GroupName ({tsoSOLine.UserFld2})
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap


{@Seed Arrival Date}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


5.13 Group Header Section #5
Hidden

5.14 Group Footer Section #5
Hidden

5.15 Details Section
Hidden

Subsection.1
Visible, Keep Together

{tsoSOLineDist.QtyOrd}
Numeric, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting,
Leading Minus, 2 Decimal Places, Rounding: 0.01,
Thousands Symbol: ',', Decimal Symbol: '.'


{tsoSOLine.SOLineNo}
Numeric, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting,
Leading Minus, 0 Decimal Places, Rounding: 1,
Thousands Symbol: ',', Decimal Symbol: '.'


{tsoSOLine.UserFld2}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap




Skip Whitten
IT Consultant

http://www.skipwhitten.info

http://www.biocompatibles.com

 

[RTag]

I guess you already tried to change DateTime (2011, 12, 01, 00, 00, 00) to DateTime (2012, 12, 01, 00, 00, 00)
in the select formula:

not ({tsoSalesOrder.Status} in [4]) and
{tsoSalesOrder.CompanyID} = "BIO" and
{tsoSalesOrder.DfltPromDate} >= DateTime (2012, 12, 01, 00, 00, 00) and
{tsoSalesOrder.DfltShipDate} >= DateTime (2012, 12, 01, 00, 00, 00) and
{tsoSalesOrder.Expiration} >= DateTime (2012, 12, 01, 00, 00, 00) and
{@Seed Arrival Date} >= {?Seed Arrival Date}

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[IanWaterman]

To Debug I would remove one line of your select and rerun report repeat until correct data for 2012 arrives eg

Here is the 'Select'
not ({tsoSalesOrder.Status} in [4]) and
{tsoSalesOrder.CompanyID} = "BIO" and
{tsoSalesOrder.DfltPromDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.DfltShipDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.Expiration} >= DateTime (2011, 12, 01, 00, 00, 00)

then

Here is the 'Select'
not ({tsoSalesOrder.Status} in [4]) and
{tsoSalesOrder.CompanyID} = "BIO" and
{tsoSalesOrder.DfltPromDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.DfltShipDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{@Seed Arrival Date} >= {?Seed Arrival Date}

etc

Also place filter fields details of report and see what is being returned.

Ian

 

[lbass]

You might want to replace the 2011 in the selection formula with a number parameter {?Year}. If your fiscal year starts 12/1 and the current fiscal year is FY 2012, you might want to substitute {?FiscalYear}-1 for 2011.

Also, although it is not the basis of your problem, I would require users to enter the full year to avoid confusion of year and month and also standardize the entry to yyyy/MM/dd. It looks like you are also allowing different lengths. I'm wondering what the content of that field looks like before you try to standardize it by formula.

-LB