[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • Last Modified:

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
0
lcallah93
Asked:
lcallah93
  • 4
  • 4
  • 4
  • +1
3 Solutions
 
peter57rCommented:
I don't believe you can assign values to parameter fields.  If I am wrong, and you CAN, then the issue with your code is that you need to control the calculaton sequence using..


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

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


However if I am correct that you can't assign a value then you can do this.
(This assumes that you don't need the fiscal start and end dates for any other reason.)

@GetDates

BeforeReadingRecords;
Datevar mystart;
Datevar myend;
// get default start (fiscal start)

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

//get default end  (fiscal end)
if month(currentdate)>7
then myend:=date((year(currentdate)+1),06,30)
else myend:=date((year(currentdate)),06,30);

//set actual start and end using parameters if present
if hasvalue ({?Start Date}) then Mystart := {?Start Date};
if hasvalue({?End Date}) then MyEnd :={?End Date};
""


The selection criteria woud then use

Datevar mystart;
Datevar myend;
{gifts_full.gifteffdat} between  mystart and myend

0
 
mlmccCommented:
You are correct, you can't assign values to the parameters in the report.

What version of Crystal?

mlmcc
0
 
mlmccCommented:
This seems to be CR2008 or later

In the selection formula you can manipulate rather than calling the formulas
Just use peter's formulas as code in the selection formula

Datevar Mystart ;
Datevar Myend;

if hasvalue ({?Start Date}) then
     Mystart := {?Start Date}
Else if month(currentdate) < 6 then
     Mystart:= date((year(currentdate)-1),07,01)
else
    Mystart:= date((year(currentdate)),07,01);
if hasvalue({?End Date}) then
    MyEnd :={?End Date}
Else if month(currentdate) > 7 then
    MyEnd :=date((year(currentdate)+1),06,30)
else
    MyEnd :=date((year(currentdate)),06,30);

{gifts_full.gifteffdat} between  Mystart and MyEnd


mlmcc
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Kurt ReinhardtCommented:
Agree with mlmcc.  You cannot assign parameter values with formulas, but as he said, you don't need to.  You can account for the values in the record selection criteria.  That being said, there are two issues with the code above:

1)  Between isn't a recognized keyword.  The correct syntax would be
{gifts_full.gifteffdat} in Mystart to MyEnd 

Open in new window


2)  Even though the syntax works, it won't pass to the database for processing.  This means you'll get every possible record back from the database first and then apply the filter only after the records have already been returned (I checked).

I've attached a sample report written in Crystal Reports 2008 against the Xtreme Sample Database 11.5.  It uses the following code which does pass the criteria to the database for processing:

//The following syntax works AND passes the record selection criteria to the database for processing
//In the following example, blanks Start End Date parameters default to the appropriate Fiscal Year values

{gifts_full.gifteffdat} in

(
If
  HasValue({?Start Date})
Then
  {?Start Date}
Else If
  Not HasValue({?Start Date})
Then
  If
    month(currentdate) < 6
  Then
    date((year(currentdate)-1),07,01)
  Else
    date((year(currentdate)),07,01)
)

to

(
If
  HasValue({?End Date})
Then
  {?End Date}
Else If
  Not HasValue({?End Date})
Then
  If
    month(currentdate) > 7
  Then
    date((year(currentdate)+1),06,30)
  Else
    date((year(currentdate)),06,30)
)

Open in new window


~Kurt
CR2008---Default-Values-for-Opti.rpt
0
 
lcallah93Author Commented:
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!
0
 
mlmccCommented:
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
0
 
lcallah93Author Commented:
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')
0
 
peter57rCommented:
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'})
0
 
lcallah93Author Commented:
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?
0
 
peter57rCommented:
Apologies; you're right - I didn't pick up the difference in years even reading it several times!!!
0
 
lcallah93Author Commented:
No worries - it happens to me WAY to much...
0
 
mlmccCommented:
When I read you rcomment peter, I had to triple check to ensure the dates really were different and in the correct order.

mlmcc
0
 
peter57rCommented:
I suspect CR had the same problem...(:-)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now