passing a date range to subreports 1

[andreadd]

CR2008 / Oracle view

it's me again with that time sheet report. I have everything running accurately but I am hoping to increase performance.

i have a main form and 7 subreports. these reports are placed in a group footer. each subreport represents a day of the week. Our workweek starts on Friday and goes to Thursday. This report will be run via cViewServer daily.

so on my main form i have the following formula in the record selection:
{mydatetimefield} in ((CurrentDate-DayofWeek(CurrentDate,crFriday)+1) to CurrentDate)

on my subreports I have this in my record selection:
cdate({mydatefield}) = Date(DateAdd("d", (-DayOfWeek (CurrentDate,7 ))+3, CurrentDate))

this is Monday's subreport record selection formula. Now before anyone asks, I did make both the formulas use the same field and it did not make any difference in performance.

what I use for linking the subreport to the main form is 2 formulas placed in all for the employee name and for the client id.

I do not have any date linking going on.

what I am hoping to accomplish is to place something in record selection or link or even group selection that will narrow the number of records the subreport is looking at - even past30days is better than everything. If my current database has 10000 rows and 9 meet the criteria for Monday the way things are currently is the subreport looks at all 10000 rows. I am hoping to cut that down some and have no clue how. I would greatly appreciate any help I can get.

 

[IanWaterman]

I think its beccause this

cdate({mydatefield}) = Date(DateAdd("d", (-DayOfWeek (CurrentDate,7 ))+3, CurrentDate))

Will not parse to database

Replace your subreport dataset with a command , take your report SQL and paste into Command.

where you see field ({mydatefield})

replace with To_date({mydatefield}, 'DD/MM/YYYY')

You can then filter dates in command adding to Where clause

and To_date({mydatefield}, 'DD/MM/YYYY') >= sysdate-30

Ian

 

[andreadd]

this worked wonderfully in the subreports but now my main form isnt displaying the subreports I have no clue what is off.

what I did is go to the subreport and copy my SQL query, then added a command, pasted the query in and added at the end: TO_DATE("mytable"."datefield", 'MM/DD/YYYY') >= sysdate -14 then went to record selection and changed the cdate({mydatefield}) to the date field in the command.

the sub reports on their own are running great.

they are on the main form in a group footer for that date-time field. I figured, ok maybe i need to add a command on the main form and use the same field - so I did that and changed the group even from the date time field to the mydatefield in the command and still nada shows on the main form.

my groups are as follows:
GRP1: a formula to sort department the employee's time belongs to
GRP2: client id
GRP3: employee id
GRP4: mydatefield

on the main form i tried to clear all links between the datasource and the command and link only on the date field and it does not alter the results.

can anyone tell me what im doing wrong? I'd really appreciate it

 

[andreadd]

nevermind me - omg!

the problem was that today is friday. the report thinks first day of work week is friday and only runs for the current work week. the dev database i am working from has nothing entered for today so yup, it did as told, returned no data. sub reports when run individually ask for a date to process.

im going to go smack myself and go back to bed