SubReport field has different field type than main report

[dianne2n]

I'm using CR 8.5 trying to report "Hours Available" for a work center and "Hours Scheduled" for that same work center.
In my main report (Hours scheduled) I've created parameter fields for choosing the work center, starting day to report, ending day to report. The two date fields in the Oracle view for my main report are "string" types, so I made my parameter date fields also "string" types. My subreport needs to pull data for the corresponding work center and corresponding dates. I think I'm having a problem because the Work_Day field in the Oracle view for the subreport is "DateTime" type. Any ideas on what I need to change to get this report to work?

 

[lbass]

You need to have the fields be of the same datatype, which will require conversion. You are usually better off converting to a date for sorting purposes, etc. If you need help converting, please supply a sample of your string dates and explain what they mean.

-LB

 

[dianne2n]

Main Report fields:
Work_Center_No (data example = 01418)
Op_Start_Date (data example = 1005-10-07-07.57.12)
Op_Finish_Date (data example = 1005-10-08-15.54.42)
Sub Report fields:
Work_Center_No (data example = 01418)
Work_Day (data example = 2005-11-22.00.00.00)
Are you thinking that I should create a formula field in my subreport that convert the DateTime Work_Day field to a string type?

 

[lbass]

No, I was thinking the reverse. Can I assume that that's a typo where you use 1005 for the year? Try the following to convert your stringdatetimes to an actual datetime:

date(val(left({@string},4)),val(mid({@string},6,2)),val(mid({@string},9,2)))+
time(val(mid({@string},12,2)),val(mid({@string},15,2)),val(mid({@string},18,2)))

-LB

 

[dianne2n]

OK -- I'm making progress. I now have data from my subreport showing in the main report. One last problem:
The subreport needs to show the data for the same date range as the main report. In the main report I have two paramter fields (start_date / end_date) and I go after records with OP_Finish_Date between (start_date) and (End_Date). How do I pass that "between" info to the subreport?

 

[lbass]

In the subreport links screen, add {?start_date} from the main report which will automatically show {?pm-?start date} in the lower left subreport linking area along with the corresponding date field from the subreport in the bottom right (with "select data based on this field" checked). Do the same for {?end_date}. This will cause a record selection formula to appear in your subreport that equates the date field with both start and end date. You need to then go into the subreport record selection formula and change the equal signs to >= and <=.

-LB

 

[dianne2n]

Hooray! We're REALLY close now. One more question:
I'm getting the correct data from the subreport into the main report. However, when I run the report, I'm also getting prompted for the start/end dates that belong to the subreport. Somehow I haven't been able to link the start/end date parameters from the main report into the subreport. They keep disappearing from the links. When I add the start/end date parameters to the "Fields to link to", I can't connect them to the Work_Day field of the subreport (no fields show up in the bottom right-hand drop-down box).

 

[lbass]

That means that the workorder field isn't of the same datatype as the parameter. Are you using datetime for the start and end parameters or only date? If they are date parms, then since workorder is a datetime field, you would have to use a formula {@date} to convert it:

date({table.work_day})

Then link the parms to the formula {@date}.

-LB

 

[dianne2n]

PERFECT !! Thank you EVER so much.