Month Parameter in Date Prompt 2

[shenay921]

Hi,

I am using Crystal 8.5 with an ODBC to a SQL server. The report I am creating will run within a software program that only allows certain parameters but will let the crystal report prompt for its own parameters (just not so nice looking)

I have a monthly report. I want the user to select the month and have the report run for that full month. How do I set up the parameter? The date range will also need to pass to a subreport. This report will be used every month for every year -- so I wasn't sure how leap years were accounted for.

The user does not want the option of selecting the start and end dates as I had it. Just a full month. The program would let them select the start date but regardless of the Nov 1 or Nov 2 or Nov 15, they would still get the full month of November for that year.

Thanks for any help or suggestions!

 

[synapsevampire]

if the default is ALWAYS for the current year, then create a numeric parameter.

Then in the Report->Edit Selection Formula, use:

(
{table.date} >= cdate(year(currendate),{?MonthParameter},1)
and
{table.date} < dateserial(year(currendate),{?MonthParameter}-1,1)
)

-k

 

[shenay921]

Hi K,

Thanks for the help with this one.

1. So if my current Selection Formula is:

{appt.start_datetime} in {?StartDate} to {?StopDate} and
{appt.apptstatus_id} in [1, 3] and
{appt.resunit_id} = {?ResUnitID}

is the {?MonthParameter} in your recommendation equivalent to the {?StartDate} parameter prompt? Or is there a completely different parameter I am to create.

2. It should always be for the current year -- however what if in January 2006 they want to look at December 2005 -- will this cause a complication?

Thanks again!

 

[shenay921]

Hi K,

Ok, I got the parameter to work using:

{appt.resunit_id} = {?ResUnitID} and
{appt.apptstatus_id} in [1, 3] and
{appt.start_datetime} >= cdate(year(currentdate),(Month ({?StartDate})),1) and
{appt.start_datetime} < dateserial(year(currentdate),(Month ({?StartDate}))+1,1)

What is the concern about the year?

Thank you!

 

[lbass]

If you don't want the report to be limited to a certain year, you could set up a date parameter where you tell the user to select any date in the month they want to report on. Then you could set up the record selection formula like this:

{table.date} in date(year({?date}),month({?date}),01) to dateserial(year({?date}), month({?date})+1, 01)-1

Create the same parameter in the subreport, add the same record selection formula to the subreport record selection formula, and then link the subreport to the main report on the parameter field, using the dropdown list to select {?date} for the subreport field, not the default {?pm-?date}.

-LB

 

[synapsevampire]

Yes, in lieu of your date parameter prompt.

If we assume the current year, then of course it will use the current year...

You can code around this and do lots of things, such as.

Create a default value of 0 for the month, if the user does not change the month, it can assume last month, so the record selection would be:

if {?MyMonthParameter} <> 0 then
(
{table.date} >= cdate(year(currendate),{?MonthParameter},1)
and
{table.date} < dateserial(year(currendate),{?MonthParameter}-1,1)
)
else
if {?MyMonthParameter} = 0 then
{table.date} in lastfullmonth

Obviously you can change the =0 formula to be anything.

You could also code that if the current month is january, then look at last year, but again, you need to determine your requirements BEFORE you start coidng.

Since your first post stated that you just want to pass a month, that's what I did, now you're changing it. I suggest you figure out your requirements and THEN post.

-k

 

[shenay921]

Works like a charm lbass -- thanks!

I appreciate the help from both!

 

[synapsevampire]

Why did you state that you wanted a month parameter if you were going to use a date parameter?

....

 

[shenay921]

Hi K,

I'm sorry if I wasn't clearer in my first post. I wanted a month parameter but the application that runs the report only allows a StartDate -- so it was some kind of combination of the two. And I didn't have it completely clear in my head until I read the recommendations.

Ultimately after reading yours and Lbass' posts and experimenting with your recommendations I got something that would work. I appreciate your help to get me on that path.

Thanks!