Help - data retreival issues

[SimonPetherick]

I have a report that includes the following in the selection criteria:

{DATA_VALUES.READING_DATE} > DateTimeValue({?Start Gas Day},Time(06,00,00)) and

if (DateDiff ("d",{?Start Gas Day} ,{?End Gas Day} ) > 92) then
{DATA_VALUES.READING_DATE}<= DateTimeValue({?Start Gas Day}+92,Time(06,00,00)) else
{DATA_VALUES.READING_DATE}<= DateTimeValue({?End Gas Day}+1,Time(06,00,00)) and

if {?Data Type} = &quot;Volume (m3)&quot; then {DATA_POINTS.DTY_ID} in [148.00, 206.00, 245.00, 40.00, 80.00] else
{DATA_POINTS.DTY_ID} in [126.00,240.00,44.00,9.00] and

{SITES.SITE_NAME} = {?Customer}

The report allows the customer to select a customer from a list and also other parameters like data type (energy, volume) and data interval (hourly, daily, monthly). When they select the gas day range, I need to put a limit on the amount of data that they retrieve. The above, displays only the data up to a maximum of 92 days in total. However when I run the report if I've selected a date range over a year, it still looks like its retrieving all the data for that year (although only displaying it for the maximum of 92 days). Is there a way I can stop all the data coming back when the user selects ridiculous date ranges?

Thanks...

 

[synapsevampire]

It's the way that you've constructed the Record Selection Formula, if you check the Database->Show SQL Query you'll see that the record selection formula isn't being passed to the database, rather all rows are returned to Crystal, which is then excluding those that don't match.

I have a FAQ about Optimizing SQL pass through in the Crystal Formulas forum FAQ area.

The key here is to construct the record selection formula one step at a time, and make sure that you test after each step by verifying your criteria is being passed in the Database->Show SQL Query.

To be safe, I'd construct 2 formulas, @startdate and @enddate, and reference those in the record selection formula:

@startdate
if (DateDiff (&quot;d&quot;,{?Start Gas Day} ,{?End Gas Day} ) > 92) then
cdatetime(year({?Start Gas Day}+92),month({?Start Gas Day}+92),day({?Start Gas Day}+92),6,0,0)
else
{?Start Gas Day}

Create the @enddate the same way.

The record selection would look more like this:

(
if {?Data Type} = &quot;Volume (m3)&quot; then {DATA_POINTS.DTY_ID} in [148.00, 206.00, 245.00, 40.00, 80.00] else
{DATA_POINTS.DTY_ID} in [126.00,240.00,44.00,9.00]
)
and
(
{SITES.SITE_NAME} = {?Customer}
)
and
(
{DATA_VALUES.READING_DATE} >= @startdate
and
{DATA_VALUES.READING_DATE} >= @enddate
)

Again, check after each section to ensure that it's getting pass to the database.

Hope this helps.

-k