Parameter values (date) displayed in report detail
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9740
Printed Date: 01 May 2025 at 12:33pm
Topic: Parameter values (date) displayed in report detail
Posted By: Despina
Subject: Parameter values (date) displayed in report detail
Date 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,
|
Replies:
Posted By: Despina
Date 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') |
Posted By: lockwelle
Date 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
|
Posted By: Despina
Date 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? |
Posted By: lockwelle
Date 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
|
Posted By: DBlank
Date 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?
|
Posted By: lockwelle
Date Posted: 21 Apr 2010 at 6:26am
| well, that would change things... |
Posted By: Despina
Date 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,
|
Posted By: DBlank
Date 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 |
Posted By: Despina
Date 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 |
Posted By: Despina
Date Posted: 21 Apr 2010 at 1:40pm
|
Is it possible that it relates to the data type: in the db the date is set to VARCHAR2 where in CR it is set to string?
If so how can I match the datatype in CR?
Regards,
Despina |
Posted By: DBlank
Date Posted: 21 Apr 2010 at 3:32pm
|
I am at a loss as to what you really want to acccomplish here. If you were starting from scratch can you explain what you want your report to do (ignoring all the problems you have had). |
Posted By: Despina
Date Posted: 21 Apr 2010 at 4:29pm
|
Hello, I just want to create a parameter that shows all the values in the database.
For whatever reason some of the date values drop off when I try to create a parameter for the date in CR.
Regards,
Despina |
Posted By: DBlank
Date Posted: 22 Apr 2010 at 3:44am
|
how many total rows of data are in your source?
If you create a dynamic param on a differnt field do all the values appear?
|
Posted By: lockwelle
Date Posted: 22 Apr 2010 at 3:55am
|
I'm sorry, but I am confused. If all you want is all the data in the database between 2 dates, why not ask for the dates. Filling in the missing dates is the issue, but there isn't easy way to do that....Perhaps a subreport where you pass it the current record's date and the next record's date and have the subreport loop and print an empty row message of some sort.
in my mind, a parameter is something that you use to query a database for results...you don't need every date to that...if you do, use a calendar, same basic idea. Now displaying all the dates in a given date range is a different requirement, but it is not a parameter of the report, it is business rule/requiremnt.
so, I think that DBlank's last question was the most apt. What is the report to display and what are the questions that you asking the DB to get the data to be displayed. (yeah, I paraphrased)
|
Posted By: Emir_W
Date Posted: 20 May 2010 at 12:10am
|
hi,
you can check your regional setting in control-panel.(mm/dd/yyyy or dd/mm/yyyy).
hope it help. ------------- Emir W |
Posted By: Despina
Date Posted: 20 May 2010 at 11:46am
|
Hello Firstly thank you to all who tried to assist, There was an issue with the incompatibility of the datatypes between the db and CR. I was able to fix this by calling the db value as a string (through SQL) and then converting it back to a suitable format that appears as a date in CR Regards, Despina
|
Posted By: oks911
Date Posted: 23 Aug 2010 at 10:32pm
|
I manage to solve this with Cdate. {Table.DATE} in CDate(Year({@StartDate}), Month({@StartDate}), day({@StartDate})) to CDate(Year({@EndDate}), Month({@EndDate}), day({@EndDate})) Hope this help. |
Posted By: godwing
Date Posted: 13 Sep 2010 at 4:48pm
|
Hi Despina, This maybe a little bit late but you may try this.. First, try to clear the select expert. Place the DateField in Details section Check if the 3 dates you're looking is displayed in the screen. if yes, try this, Datefield<=?Dateparameter. //note: this is just the same with Datefield>=?Dateparameter and Datefield<=?Dateparameter. Hope it helps, |
Posted By: louisville2k10
Date Posted: 30 Sep 2010 at 3:06am
|
Just curious, but why is this thread in the "tips and tricks" section? It seems like this was more of a technical question... ------------- Thanks for your help! |