Parameter by Month

[drinker]

I would like to have a parameter for the customer to use with this report and be able to select the month. Currently I have a date range for them to choose from. but I was wondering if it would be possible to select a month / Year , March - May etc..
{CUSTOMER_ORDER.ORDER_DATE} = {?Month}


Crystal 8.5

 

[satinsilhouette]

You would have to create a formula that lays out the month for the year with a between date

if month({datefield}) = 10 then "October"
else if
month(datefield}) = 9 then "September"
else if

etc.

Then create a manual parameter that equals months, January, February, etc.

In the record selection then make the formula field equal to the parameter field.

Thanks so much!
satinsilhouette

 

[lbass]

You could set up a {?month} as a number parameter and then use a record selection formula of:

month({table.date}) = {?month}

Even better, create a SQL expression {%month}:

month(`table`.`date`)

Or if that doesn't work with your datasource try:

{fn month(`table`.`date`)}

Then set the record selection formula to:

{%month} = {?month}

The SQL expression will pass to the SQL query, while the formula won't.

You probably should also make sure you are only working with one year or else also set up a year parameter.

-LB

 

[synapsevampire]

I doubt that your users will find two parameters easier than a single date parameter, but if you insist, I would leverage the month/year parameters in a conventional where clause rather than using a SQL Expression as you're more likely to leverage an index on the database used for the date field:

(
{CUSTOMER_ORDER.ORDER_DATE} >= cdate({?MyYearParameter},{?MonthParameter},1)
and
{CUSTOMER_ORDER.ORDER_DATE} <= dateserial({?MyYearParameter},{?MonthParameter}+1,1)-1
)

Either method will pass the SQL to the database.

-k