Crystal reports returns zeros when using a date range

Question

I am creating a crystal report that will display a number of counts. This works fine so long as I'm not using date parameters to narrow the results. The moment I try to use dates, my report returns 0 for all counts.

The report uses a command object with the following SQL (obfuscated, but syntactically the same).

SELECT 
(SELECT COUNT(*) FROM REPORT_ WHERE DATE_SUBMITTED IS NOT NULL) AS SUBMITTED,
(SELECT COUNT(*) FROM REPORT_ WHERE DATE_RECEIVED BETWEEN {?StartDate} AND {?EndDate}) AS TOTALRECEIVED,
(SELECT COUNT(*) FROM REPORT_ WHERE DATE_RECEIVED BETWEEN {?StartDate} AND {?EndDate} AND STATUS_ LIKE 'A1') AS A1,
(SELECT COUNT(*) FROM REPORT_ WHERE DATE_RECEIVED BETWEEN {?StartDate} AND {?EndDate} AND STATUS_ LIKE 'A2') AS A2,
(SELECT COUNT(*) FROM REPORT_ WHERE DATE_RECEIVED BETWEEN {?StartDate} AND {?EndDate} AND STATUS_ LIKE 'A3') AS A3,
(SELECT COUNT(*) FROM REPORT_ WHERE DATE_RECEIVED BETWEEN {?StartDate} AND {?EndDate} AND STATUS_ LIKE 'A4') AS A4,
(SELECT COUNT(*) FROM REPORT_ WHERE DATE_RECEIVED BETWEEN {?StartDate} AND {?EndDate} AND STATUS_ LIKE 'A5') AS A5,
(SELECT COUNT(*) FROM REPORT_ WHERE DATE_RECEIVED BETWEEN {?StartDate} AND {?EndDate} AND STATUS_ LIKE 'OTHER') AS OTHER

If I replace the parameters with static values in the command itself, it runs perfectly... But any attemps I've made to use date parameters causes it to fail. I think that it might be that the crystal report is providing a date in a format is intelligable to the ms sql server. I've tried casting, but nothing seems to work. How can I get my date parameters to work?

Chronicide Chronicide · Accepted Answer · 2012-08-02T15:14:19

I finally worked out how to resolve this... it was human error. when prompted to input new values for the parameters, it somehow decided to ask for the end date first, then the start date. I was putting the wrong values in, which resolved to a null range of dates. I reorded the parameter request, and put the correct values in, and it worked.

I'm a bit ashamed that it took me so long to work out, but I figured that I would post this, inthe off chance someone has come across a similar issue.

Crystal reports returns zeros when using a date range

1 Answers