Link to home
Start Free TrialLog in
Avatar of lcallah93
lcallah93Flag for United States of America

asked on

Blank Date Parameters

I have created a report that lists cash deposits by date range.  I currently have a default report scheduled on Crystal Server to run with hard-coded dates for the current fiscal year.  What I would like to do is let the dates default to the current fiscal year if left blank.  

I have created two formulas in the report that calculate the first and last day of the current fiscal year.

Start Date:

BeforeReadingRecords;
if month(currentdate)<6
then date((year(currentdate)-1),07,01)
else date((year(currentdate)),07,01)

End Date:

BeforeReadingRecords;
if month(currentdate)>7
then date((year(currentdate)+1),06,30)
else date((year(currentdate)),06,30)

I then created two formulas to populate the parameters with that date (or so I thought) if the parameters were left blank:

BeforeReadingRecords;
if not hasvalue ({?Start Date})
then {?Start Date}={@Fiscal Year Start Date}

BeforeReadingRecords;
if not hasvalue({?End Date})
then {?End Date}={@Fiscal Year End Date}

Then in my selection criteria I put in the following:

{gifts_full.gifteffdat} in {?Start Date} to {?End Date}

However, I get a 'parameter has no value' error when running the report.

Has anyone done anything like this before and if so, how did you solve the problem?

Thank you!

Lisa
SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mike McCracken
Mike McCracken
You are correct, you can't assign values to the parameters in the report.

What version of Crystal?

mlmcc
SOLUTION
Avatar of Mike McCracken
Mike McCracken
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Kurt Reinhardt
Kurt Reinhardt
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lcallah93
lcallah93
Flag of United States of America image

ASKER

Thank you for checking how the data is pulled.  I did check to see that the figures matched (which they did) but I failed to check on how the records were generated.

Thank you!
Avatar of Mike McCracken
Mike McCracken
Might be worth verifying if the dates are being passed.

Run the report in the designer then
Click DATABASE --> SHOW SQL

If the dates show in the query then they are being passed.

If they don't then try Kurt's method.  If that passes them to the database then you probably should ask this to be reopened and include his comment in the accepted answer

mlmcc
Avatar of lcallah93
lcallah93
Flag of United States of America image

ASKER

The dates are being passed - the query is below.

 SELECT "gifts_full"."gifteffdat", "gifts_full"."giftamount", "gifts_full"."giftdeduct", "gifts_full"."giftnonded", "division"."table_code", "gifts_full"."giftrest", "division"."table_type", "division"."table_val", "solicitations"."sol_val", "solicitations"."sol_code", "gifts_full"."giftsource", "gifts_full"."giftid", "names_VIEW_preferred"."nameformn", "gifts_full_1"."gifteffdat", "names_VIEW_preferred"."namesmashd", "gifts_VIEW_released_for_reporting"."giftkey", "gifts_full"."gifttype", "gifts_full"."giftlook1"
 FROM   ((((("VCU"."dbo"."names_VIEW_preferred" "names_VIEW_preferred" LEFT OUTER JOIN "VCU"."dbo"."gifts_full" "gifts_full" ON "names_VIEW_preferred"."nameid"="gifts_full"."giftid") LEFT OUTER JOIN "VCU"."dbo"."chart_of_accounts" "chart_of_accounts" ON "gifts_full"."giftrest"="chart_of_accounts"."chart_code") LEFT OUTER JOIN "VCU"."dbo"."gifts_full" "gifts_full_1" ON "gifts_full"."giftplgkey"="gifts_full_1"."giftkey") LEFT OUTER JOIN "VCU"."dbo"."gifts_VIEW_released_for_reporting" "gifts_VIEW_released_for_reporting" ON "gifts_full"."giftkey"="gifts_VIEW_released_for_reporting"."giftkey") LEFT OUTER JOIN "VCU"."dbo"."solicitations" "solicitations" ON "gifts_full"."giftsolic"="solicitations"."sol_code") LEFT OUTER JOIN "VCU"."dbo"."division" "division" ON "chart_of_accounts"."chart_div"="division"."table_code"
 WHERE  ("gifts_full"."gifteffdat">={ts '2010-07-01 00:00:00'} AND "gifts_full"."gifteffdat"<{ts '2011-07-01 00:00:00'}) AND ("gifts_full"."gifttype"='b' OR "gifts_full"."gifttype"='g' OR "gifts_full"."gifttype"='y') AND "gifts_VIEW_released_for_reporting"."giftkey" IS  NOT  NULL  AND ("gifts_full"."giftlook1" IS  NULL  OR "gifts_full"."giftlook1"<>'ng')
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image
No data values can satisfy these conditions...

 WHERE  ("gifts_full"."gifteffdat">={ts '2010-07-01 00:00:00'} AND "gifts_full"."gifteffdat"<{ts '2011-07-01 00:00:00'})
Avatar of lcallah93
lcallah93
Flag of United States of America image

ASKER

I am getting all gifts greater than or equal to 7/1/2010 but less than 7/1/2011.  I have to admit that I still confuse the <> symbols - is that not what the equation says?
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image
Apologies; you're right - I didn't pick up the difference in years even reading it several times!!!
Avatar of lcallah93
lcallah93
Flag of United States of America image

ASKER

No worries - it happens to me WAY to much...
Avatar of Mike McCracken
Mike McCracken
When I read you rcomment peter, I had to triple check to ensure the dates really were different and in the correct order.

mlmcc
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image
I suspect CR had the same problem...(:-)