Parameters Options 1

[brxmas]

CR 9
SQL server 2000

I created a 'Crystal' report which uses parameters to select correct data.

1) Parameter ?Period (number)
2) Parameter ?start_date (date)
3) Parameter ?end_date (date)

I want the user to have an option to select by Period or Date Range. When I enter start date and end date and click ok (without putting any number in Period parameter) it gives me a error 'You must enter a value for this field'. If I blank it out I get 0 results.

What is the best way to get results either by Period or Date Range? At the end of a period users will run a report by Period # and during the Period it's by Date Range. If it's a period report they want to see the Period # in the report not a date range and visa versa.

Thanks in advance for your help.

brxmas

 

[lbass]

Try creating a string parameter with options of "period" or "range". Then use a record selection formula like:

if {?periodorrange} = "period" then
{table.period} = {?period} else
if {?periodorrange} = "range" then
{table.date} >= {?start} and
{table.date} <= {?end}

-LB

 

[MisterMo]

Or you could set the Period to be the driving one with a default value of 0.

if {?period} <> 0 then
{table.period} = {?period}
else
{table.date} >= {?start} and
{table.date} <= {?end}

you should give default values to the date range as well.




Mo

 

[brxmas]

I'm getting the same data for Period Last Year as Period Current Year?

This is what my output from the report looks like, it's comparing 2005 P?? to 2004 P?? data:

Current Revenue Last Year Revenue
235 217

Thanks in advance.

 

[lbass]

And your question is? The above should work even if you are cutting across years.

-LB

 

[brxmas]

Nevermind, I had to put some code in my formula for Last Year. I really appreciate your help. Thanks again.

BC

 

[brxmas]

ok, what about YTD....

Current is the Current Period (P8 2005)
Last Year is the same Period Last Year (P8 2004)

Current YTD is P1 2005 - P8 2005
Last Year YTD is P1 2004 - P8 2004

Current Last Year Current YTD Last Year YTD
235 217 1426 1217

 

[brxmas]

Before I needed a way to get data using one parameter either by Period or DateRange this is the code I used:

Report Selection
{mc_period_dates.period_year} >= {?Year} -1 AND
{mc_period_dates.period} <= {?Period}

My field Current Period:
IF {mc_period_dates.period} = {?Period} AND
{mc_period_dates.period_year} = {?Year} THEN
{mc_mb_commission_summary.daily_ad_cost_sum}

Current YTD:
IF {mc_period_dates.period} <= {?Period} AND
{mc_period_dates.period_year} = {?Year} THEN
{mc_mb_commission_summary.daily_ad_cost_sum}

 

[lbass]

Again, you haven't identified what your question is. Are you trying to create a report with the same layout--current, last year, current YTD, last year YTD using either a date range or a period? If so, use a record selection formula like:

(
if {?periodorrange} = "period" then
{table.period} = {?period} and
{mc_period_dates.period_year} = {?Year}
else
if {?periodorrange} = "range" then
{table.date} >= date(year({?start}),01,01) and
{table.date} <= {?end}
)

Then {@current} would be:

if {?periodorrange} = "period" then
(
if {table.period} = {?period} and
{mc_period_dates.period_year} = {?Year} then
{mc_mb_commission_summary.daily_ad_cost_sum}
)
else
if {?periodorrange} = "range" then
(
if {table.date} >= {?start} and
{table.date} <= {?end} then
{mc_mb_commission_summary.daily_ad_cost_sum}
)

//{@current YTD}:
if {?periodorrange} = "period" then
(
if {table.period} <= {?period} and
{mc_period_dates.period_year} = {?Year} then
{mc_mb_commission_summary.daily_ad_cost_sum}
)
else
if {?periodorrange} = "range" then
(
if {table.date} >= date(year({?start}),01,01) and
{table.date} <= {?end} then
{mc_mb_commission_summary.daily_ad_cost_sum}
)

//{@Last Year}:
if {?periodorrange} = "period" then
(
if {table.period} = {?period} and
{mc_period_dates.period_year} = {?Year}-1 then
{mc_mb_commission_summary.daily_ad_cost_sum}
)
else
if {?periodorrange} = "range" then
(
if {table.date} >= dateadd("yyyy",-1,{?start}) and
{table.date} <= dateadd("yyyy",-1,{?end}) then
{mc_mb_commission_summary.daily_ad_cost_sum}
)

//{@Last Year YTD}:
if {?periodorrange} = "period" then
(
if {table.period} <= {?period} and
{mc_period_dates.period_year} = {?Year}-1 then
{mc_mb_commission_summary.daily_ad_cost_sum}
)
else
if {?periodorrange} = "range" then
(
if {table.date} >= date(year({?start})-1,01,01) and
{table.date} <= dateadd("yyyy",-1,{?end}) then
{mc_mb_commission_summary.daily_ad_cost_sum}
)

-LB