Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parameters Options 1

Status
Not open for further replies.

brxmas

Programmer
Feb 9, 2004
69
US
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

 
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
 
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
 
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.
 
And your question is? The above should work even if you are cutting across years.

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

BC
 
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
 
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}
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top