Want to display Wk1, Wk2, Wk3 in crosstab 1

[azoe]

Crystal 11, MS SQL, ODBC, Stored Procedure

The user gets prompted to enter 2 dates for the report.
I would like to take those dates and somehow split it out into weeks to display as column headers in a crosstab like this:
Wk1 Wk2 Wk3 Wk4
10.00 5.00 11.00 8.00

Right now I'm only grouping by user name but I'd like the second group to be these weeks.
It could be split up Monday through Sunday.

I wonder if I'm doing the stored procedure part right. I declare them:
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

Then:
IF ISDATE(@startDateStr) = 1
SET @startDate = CONVERT(datetime, @startDateStr)
ELSE
SET @startDateStr = GETDATE()

IF ISNULL(@endDateStr, '') = '' SELECT @endDateStr = @startDateStr

IF ISDATE(@endDateStr) = 1
SET @endDate = CONVERT(datetime, @endDateStr)
ELSE
SET @endDateStr = GETDATE()


I thought I'd start by trying to use DateDiff("w", {?@StartDateStr}, {?@EndDateStr}) in a formula but Crystal highlighted the start date parameter and said it needed a date.

Any thoughts on what I did wrong - or maybe there is a better way ...
Thanks -

 

[Madawc]

Why aren't the parameters dates, instead of date-strings?

If this isn't avoidable, make sure the date-strings are converted before looking for the date difference. DateDiff("w", Cdate({?@StartDateStr}0, Cdate({?@EndDateStr})) ought to work.

Madawc Williams (East Anglia, UK)

 

[azoe]

To be honest with you - I am not sure. I got help with that from a former co-worker. I thought maybe it allowed for different forms of input by the user (11-12-2005 as opposed to 20051112, etc.) But I don't have a good understanding of it.

Your formula did help me determine the number of weeks though. Now I can continue working on the rest.
Thank you

 

[Madawc]

Glad to be of help. If I were doing it, I'd feel tempted to create a new version in which the parameters were set to dates. In Crystal 10, you get shown an actual calendar and can choose a suitable date.

Madawc Williams (East Anglia, UK)

 

[Madawc]

It's just occurred to me that you'd not want Crystal's date default if you were entering a lot of dates that were months or years away from the current date.

You may also have been given advice by someone who stuck to a method that works without looking for possible better methods.

Check with the people who're going to be using the report. Let them know what Crystal can do, and see whether or not they want it. They may also be interested in graphics, which Crystal produces quite easily.


Madawc Williams (East Anglia, UK)

 

[azoe]

I would like to try setting the parameters to dates. I now have this at the top of the stored procedure:
@StartDate datetime,
@EndDate datetime

I took out the declarations and the If statements. Now when I pick a date from the calendar I see it chooses a time (all zeros). I have to look up if all zeros means it includes the whole day or not.

Am I doing it right so far? I haven't figured out how to group by weeks yet but maybe this will help because I was having a hard time working with dates - that as you said weren't really dates.

We've recently converted from another database and there isn't that many years they can go back to. That may be a good thing since I'm not familiar with whatever issue that would bring up. I think they will like to see some graphics.
Thanks.

 

[Madawc]

Seems fine. In Crystal, parameters can be dates or date-times. You can also give them a default value.

As for integrating with a stored procedure, that's not something I've ever done. I used Stored Procedures with Crystal 8.5 but not so far with Crystal 10, and I've never tried using parameters with them. If you have trouble, try [Search] within this forum, because a lot of good answers are already posted. If that fails, start a new thread.

Madawc Williams (East Anglia, UK)