HELP! Issue with date in record selection

[newbie0423]

I have a report that will not let me change the {v_basic_case_rec_data.cr_prdate} to the year 2013. If I try to put a current date in the report doesn't return anything. Below is what I have in the record selection. This report was written by someone who no longer works for the company. I have tried various things to try to get it to run. If I take out the fields that have the datetime 1900,01,01,00,00,00 it works, but I need those fields in the report. I don't know what I'm missing. This has me stumped!!! I am using Crystal XI Thanks in advance for your help!


{v_basic_case_rec_data.fac_mnc} in ["MMC", "WBG"] and
{V_JGG_DL_PacuReport04082010PlusSurg.ToFloorUnit} > DateTime (1900, 01, 01, 00, 00, 00) and
{v_basic_booking_data_ODBC_plusC.pbt_descr} in ["AM Admit", "Inpatient"] and
not ({V_JGG_DL_PacuReport04082010PlusSurg.DISCHDESC} in ["Emergency Department", "Home", "Operating Room", "Outpatient Chemotherapy", "PACU", "Pediatric Floor", "Radiation Oncology", "Transfer To Another Hospital", "Unknown"]) and
{V_JGG_DL_PacuReport04082010PlusSurg.ReportAttempted} > DateTime (1900, 01, 01, 00, 00, 00) and
{v_basic_case_rec_data.cr_prdate} in DateTime (2011, 04, 01, 00, 00, 00) to DateTime (2011, 04, 27, 00, 00, 00) and
{V_JGG_DL_PacuReport04082010PlusSurg.ReadyForDischargeToFloor} > DateTime (1900, 01, 01, 00, 00, 00)

 

[hilfy]

If I take out the fields that have the datetime 1900,01,01,00,00,00 it works, but I need those fields in the report.

Does this mean that you take them out of the report entirely or just out of the selection criteria?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[newbie0423]

Just the selection criteria

 

[hilfy]

Have you checked your data? Do the records with cr_prdate in 2013 have values in the other date fields?

You don't say which database you're connecting to, but I would try running the whole query in a tool like Toad, SQL Server Management Studio, SQL*Plus, etc. To see whether it returns any data there. If it doesn't then the issue is in the data, not in Crystal.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[newbie0423]

I'm afraid I don't have access to any of the tools you mentioned. I am the end user and I only have access to crystal. None of the data returns if I put in 2013, but if I put in 2011 in the cr_prdate then the report returns data.

 

[hilfy]

Ok, here's what I would do, then:

1. Copy your existing report.
2. In the new report, take all of the (1900, 01, 01, 00, 00) date filters out.
3. Take out any groups and ALL of the fields from the report.
4. In the details section, place just the following fields:

Some sort of case ID (a way to get back to this particular record in the database....)
{v_basic_case_rec_data.cr_prdate}
{V_JGG_DL_PacuReport04082010PlusSurg.ToFloorUnit}
{V_JGG_DL_PacuReport04082010PlusSurg.ReportAttempted}
{V_JGG_DL_PacuReport04082010PlusSurg.ReadyForDischargeToFloor}

5. Run the report for cr_prdate is in a period during 2013. Look for any records that have values in ALL 4 date fields. I suspect that you will find that all of the records in the time period are missing values in one or more of the date fields from the V_JGG_DL_PacuReport04082010PlusSurg table. This would be why they're not showing up in your report.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[newbie0423]

Thanks for your time. I'm still not able to get the year 2013 to populate any data.

 

[IanWaterman]

What was the result of the test Dell suggested?

Try File->Report Options-> Check Convert Database Null Values to Default

Does that make a difference?

Ian

 

[newbie0423]

Thanks Ian, I tried what you suggested and I'm still not getting any data back. I tried what Dell instructed me to do, but when I enter any time period for 2013 nothing comes back. I can get data back for 2011 and some of 2012. Anything after 2/2/2012 returns nothing.

 

[hilfy]

The issue is in your data, not in Crystal. This is proven by the fact that you get data sometimes.

Did you do what I suggested with creating a new report that has just the four date fields on it and the same filters as the original report, minus the filters for dates > (1900, 01,01)? This will show you where the date data is causing the issue.

Beyond that, there is nothing else we can tell you to do to correct the report.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[newbie0423]

OK, I understand. Thank you so much for your time.