need help displaying data for single day

[andreadd]

CR2008 / Oracle 11g view.

I have beent asked with writing a timesheet report. I need this report to pull data from a view. the problem that I am having is getting the report to display just a single day. So if i am running the report on thursday may 17th - the data displayed is from 5/11 to 5/17 and then shows up in corresponding date areas on the report - so 5/11 only shows 5/11 data.

so it would look something like this:

employee Friday 5/11 Saturday 5/12 Sunday 5/13
Joe Schmoe start 9:00 start 10:30
end 5:00 end 6:00

on my main report i have a bunch of formulas to get the date/day labels that look like this:

"Saturday" & Date(DateAdd("w", (-DayOfWeek ({mydatetimefield},7 )), {mydatetimefield})) + 1

i wouldnt mind cleaning this up but have been stumbling on it. the above appears to be very stable.

i figured it would be cleanest (and safest) to do the actual data display via subreports that i have placed in a group header on employee - one for each day. while i have no probs digging up the start/end times and employee names, my issue is that the data displayed isnt corresponding to the date. I am not right fully 'getting' how to take that particular dayofweek and telling the subreport that it's job is for that date and only that date. im not very familiar with how dayofweek works of other functions like dateadd. I did get my mitts on LBass' fabulous calendar report but when I tried to add the SQL expression for dayofweek in the subreport, it wouldnt work. got msg about server not being able to gather data. when i looked under date functions, it wasnt a choice. I'm assuming the SQR view is what decides what it can and cant see.

i'd appreciate the help - and thanks in advance

 

[Madawc]

I'm not clear what your problem is. If you've grouped by date, it's possible the software has defaulted to group by week. Right-click on group header or footer in the left-hand margin, select [Change Group] and you should be able to fix it.

Your date display is needlessly complex. Look up ToText: it lets you display dates very neatly.

If possible, avoid subreports. Each occurance is a separate access of the database, so they are very slow at detail-line level.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[andreadd]

thanks for the response - totext I have tried but since the dates are on 1 form side by side I do not know how to take my datetime field then say for dayofweek = 6 please print the date in this label.

I cannot group by week - that would be great but again datetime field and my week runs friday to thursday and all data must appear like a cross tab

any ideas would be greatly appreciated. I think i am stuck with subreports even though i do not want to use them. the time stamps then need to display by a billing code, sometimes its there, sometimes it aint.

 

[andreadd]

maybe i should clrify a bit better. on my report i have 2 groups - one for the company name then the next is for the employee assigned.

since my week is friday to thursday i havent added in a group for date

what i need to do is add into my group a little formula/subreport/ whatever for each day side by side so it looks like a cross tab. i need for each day to display the date assigned for that day. so dayofweek (mydatefield) = 5 then it needs to pull data for just that day, including its date (for my example, 8/9/2012)

what this single area would look like:

Thursday
8/9/2012
________
start: 9:00
lunch start: 12:00
end: 5:00

Special
lunch end 1:00

_________

so imagine one for every day of the week sitting side by side. the "special" is if the employee has to do something during their workday. -dont make sense to me but i have to sort by it when it appears

 

[Madawc]

Sorry, I don't follow this. Can anyone else help?

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[lbass]

You don't have to use a SQL expression for dayofweek--it just makes the report faster--so if you think subreports are the way to go, just use the regular dayofweek() function.

-LB

 

[andreadd]

i ended up having another column added to the view for just date. it comes out as a varchar but i can use cdate on it then I made subreports sorting on specific dayofweek. seems fairly solid but I was hoping to use the sql expression.