Using Parameter year in DateTime formula 1

[RenaG]

I am using Crystal 10.

I have a parameter field that is a string and gets a beginning year and ending year from the user. I want to use this string in a subreport in the record selection. Something like this:

{Schedule.App_DtTm} in DateTime (2010, 01, 01, 0, 0, 0) to DateTime (2011, 12, 31, 0, 0, 0)

I need to replace the 2010 with a minimum(?YearRange) and the 2011 with a maximum(?YearRange).

Do you have any suggestions on how to set this up?

TIA!
~RLG

 

[CoSpringsGuy]

Thats an odd approach but I will assume there is a reason you are going that direction .. Try this

{Schedule.App_DtTm} in DateTime (tonumber(minimum({?YearRange})), 01, 01, 0, 0, 0) to DateTime (tonumber(maximum({?YearRange})), 12, 31, 0, 0, 0)

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[briangriffin]

I think it's easier to filter on the year only - this eliminates any chance of dropping records due to datetime fields - for instance, in your example you would not include any appointments scheduled on 12/31/2011. This is becauseDateTime (2011, 12, 31, 0, 0, 0) is midnight of the 31st - the very beginning of the day. You could avoid that by using DateTime (2011, 12, 31, 11, 59, 59), but this is the way I do it:

year({Command.AdmitDate}) in tonumber(minimum({?Year})) to tonumber(maximum({?Year}))

 

[briangriffin]

Actually, you would use:

year({Schedule.App_DtTm}) in tonumber(minimum({?YearRange})) to tonumber(maximum({?YearRange}))

 

[RenaG]

Thanks for the replies!

CoSpringsGuy - the user could want to go back 2 or 3 years. They always want the whole year. I was trying to set it up easier for them.

brian - I like just testing on the year. Good catch on missing data for 12/31! I am running into difficulty of communicating the parameter field from the main report to the subreport where this will be used in the record selection. I have been trying to use a shared variable but can't get it to work. The last thing I tried is:
In the main report

Formula field: fmtStartYear
shared stringVar ClinicVisitStYr := minimum({?YearRange}) ;

In the subreport

Formula field: fmtStartYear
shared stringVar ClinicVisitStYr ;

In the record selection
I tried two different ways

year({Schedule.App_DtTm}) in year({@fmtStartYear}) to year (2011)
and
year({Schedule.App_DtTm}) in year(ClinicVisitStYr) to year (2011)

I tried all kinds of other variations to a theme here but nothing worked.

The error on the first option is: "A date is required here." And it has the formula highlighted.

The error on the second option is: "The ) is missing." And the shared variable is highlighted.

How do I set this up?

Thanks!
~RLG

 

[briangriffin]

Set up the subreport the same way as the main report - create a range parameter (call it YearSub) and use it in the record selection.

In design mode of the main report, right click the subreport and select Change Subreport Links. In the Field(s) to Link To select the parameter from the main report.

In the Subreport Parameter Field to Use, select {?YearSub}. You'll see another parameter titled {?PM-YearSub}. Don't accidentally use that one.

Now the two parameter fields should be in sync, and both reports will filter based on those values.

 

[RenaG]

Hey Brian,

Well that was so much easier than what I was trying to do. I did try, once, to pass the parameter to the subreport but had missed the important step of setting up the parameter in the subreport first. I have been hitting my head on this all day (actually since yesterday afternoon) and you make it seem so easy! Thanks so much.

~RLG