Active TopicsMemberlistSearchHelpRegisterLogin

Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks
Message Icon Topic: Parameter values (date) displayed in report detail Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Despina
Newbie
Newbie


Joined: 30 Mar 2010
Location: Australia
Online Status: Offline
Posts: 31
Quote Despina Replybullet Topic: Parameter values (date) displayed in report detail
    Posted: 19 Apr 2010 at 12:51pm

Hello,

 

I am trying to create a crystal report that has a date range parameter (i.e. date is between A to B) to extract audit data from a system.  The problem is that I need to display the date values returned in the details of the report.

 

I need to display all date values (not just min and max) however I am not sure how to achieve this.

 

I would really appreciate any help as I have not been able to find a solution to date.

 

Regards,
Despina

 Cry
IP IP Logged
Despina
Newbie
Newbie


Joined: 30 Mar 2010
Location: Australia
Online Status: Offline
Posts: 31
Quote Despina Replybullet Posted: 19 Apr 2010 at 1:56pm
I have tried this formular but I get the message:
'A number currency, amount, boolen, date, time, date-time or string is required here
 
 
for what I have highlighted in bold.
 
I am still not sure that this formular will give me what I want
 
Regards,
Despina

Global StringVar DateRange := '';
   
 DateRange := ToText({?Date}, ' dd-MM-yyyy')& ' to ' &
                        ToText({?Date}, ' dd-MM-yyyy')
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 20 Apr 2010 at 3:34am
given what you have written, I am confused by what you are after.
 
if I had 2 parameters, say start and stop, then I would add a logic to the 'report/selection formulas/Record' that is something like:
{table.field} >= {?start} AND {table.field}<={?stop}
 
you could use an if statement, but this will return true if the record is in the date range and false if it isn't.  False will exclude the record from the report.
 
HTH
 
IP IP Logged
Despina
Newbie
Newbie


Joined: 30 Mar 2010
Location: Australia
Online Status: Offline
Posts: 31
Quote Despina Replybullet Posted: 20 Apr 2010 at 3:22pm
Thankyou HTH
 
I tried using 

{Command.EVENT_DATE} >= {?dateselection} AND {Command.EVENT_DATE} <={?dateselection}

and it helps to distingiish which records fall withn the date range entered in the parameter however it only shows a TRUE or FALSE value next to each record.

Is there a way of taking it a step further to only disply the records that return the TRUE value and also show the 'valid date next to each record?

 
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 21 Apr 2010 at 3:24am
ok, as written, it will only return a true or false. if you are to write a formula that returns a value, you want something like:

if {Command.EVENT_DATE} >= {?dateselection} AND {Command.EVENT_DATE} <={?dateselection} then

  totext({Command.EVENT_DATE}, "MM/dd/yyyy")
else
  ""
 
HTH
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Apr 2010 at 4:05am
Lockwelle, I am reading this as a need to fill in missing dates from row to row in case a date was skipped in the source.
Data as is:
1-1-10
1-4-10
 
Desired result
1-1-10
1-2-10
1-3-10
1-4-10
 
Is this accurate Despina?
 
 


Edited by DBlank - 21 Apr 2010 at 4:05am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 21 Apr 2010 at 6:26am
well, that would change things...
IP IP Logged
Despina
Newbie
Newbie


Joined: 30 Mar 2010
Location: Australia
Online Status: Offline
Posts: 31
Quote Despina Replybullet Posted: 21 Apr 2010 at 12:54pm

Hello,

 

Firstly sorry for the confusion....

Yes, I am after the following result;

 

1-1-10

1-2-10

1-3-10

1-4-10

 

The reason for this is that I have tried to create a simple dynamic date parameter and activated it through select expert however when I do I loose some of the date values in the database when I use 'is between' or 'is one of'

 

For example db has 3 values:

 

 

14/08/2007

25/01/2007

25/11/2009

 

The parameter only displays:

14/08/2007

25/11/2009

 

So I am looking for another way to bring across the values in the report data so they are all included.

I would also obviously like to have ALL dates included in the selection parameter when a user refreshes a report. 

 

I am not sure how to best achieve this......or why I am loosing the option to select 25/01/2007 when I try to refresh the report

 

  

Also I am not sure if the issue (of the missing date in the parameter) is caused by my SQL as I have also tried to convert a string value into a date format using:

 

TO_CHAR(to_date(substr(ts.SYSLASTUPDATED,1,8),'YYYY/MM/DD'), 'dd/mm/yyyy') as Last_Action_Date,

 

Original format of string was:  200911250439051

New format: 25/11/2009

 

I don’t suspect this is why as I have tried to create a state parameter and I only get 2 options when I should be getting 5.

 

Regards,
Despina

 

 

 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Apr 2010 at 1:19pm
try and change your re-format formula to use MM/dd/yyyy
Crystal does not like dd/MM/yyyy
IP IP Logged
Despina
Newbie
Newbie


Joined: 30 Mar 2010
Location: Australia
Online Status: Offline
Posts: 31
Quote Despina Replybullet Posted: 21 Apr 2010 at 1:28pm
Hello,
 
recoreated report with date format as mm/dd/yyyy but I still can not get 25/01/2007 to appear in the parameter selection.....?
 
Regards,
Despina
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.032 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.