Print Page | Close Window

Subreport Timestamp

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19504
Printed Date: 04 May 2024 at 11:02pm


Topic: Subreport Timestamp
Posted By: nhopp4
Subject: Subreport Timestamp
Date Posted: 06 May 2013 at 6:05am
Hi guys,
I am having an issue with a subreport that is pulling a parameter from the main report for a date range.  When I run the report I show what I want but it also includes all dates going back months in my subreport. I would like it to only show about an hour before the time that the main report shows on my subreport.
 
My timestamp is in datetime format.
 
Any ideas how I can do this?
 
Thanks,
Nick 


Replies:
Posted By: Sastry
Date Posted: 06 May 2013 at 7:19am
Hi

Is this sub report linked to main report ? if so, are you passing parameter value to sub report or linking it with sub report date field ?

Check sub report record selection formula for datetime parameter criteria.

In which section of your main report you placed your sub report.

-------------
Thanks,
Sastry

Posted By: nhopp4
Date Posted: 06 May 2013 at 8:03am
The subreport is linked to the main report through a parameter that looks at the timestamp for its data.
 
In the subreport record selection the datetime is equal to the datetime parameter.
 
I placed the subreport on details 1 section of the main report.
 
Thanks for the reply,
Nick

Posted By: DBlank
Date Posted: 06 May 2013 at 8:42am
i will assume since you are running this sub report on the detail section that you are also linking it on some other primary key field so that only data relevant to that detail would appear.
as for the time you can alter the select criteria in the sub report to use a dateadd function, something like
{table.datefield}>=dateadd('h',-1,{?pm-datetime})

Posted By: nhopp4
Date Posted: 06 May 2013 at 10:11am
DBlank,
That was what I was trying to do but couldnt figure out how to structure it.  When I put in the line in the record selection for the Subreport that you gave me it says that a date is required where my timestamp parameter is.
 
{Reports_Events.Timestamp}>=dateadd('h',-1,{?Pm-Reports_Events.Timestamp})
 
Thanks for the reply!

Posted By: DBlank
Date Posted: 06 May 2013 at 10:16am

check sub report and verify that the param ?Pm-Reports_Events.Timestamp is an actual datetime field type. I have seen some DB's insert datetime stamps as strings/varchar data types.

if it is a string convert it...
{Reports_Events.Timestamp}>=dateadd('h',-1,datetime({?Pm-Reports_Events.Timestamp}))

Posted By: nhopp4
Date Posted: 07 May 2013 at 2:32am
I double checked and the parameter is a date time field in the subreport.

Posted By: nhopp4
Date Posted: 07 May 2013 at 6:05am
Not sure what I changed to make this work but the code works but still not getting what I need. 
{Reports_Events.Timestamp}>=dateadd('h',-1,{?Pm-Reports_Events.Timestamp})
 
When I put this in it gives me any dates above my main report timestamp not just an hour which is all I want.  I want the dates lower than the main report date by an hour.  So if the date was 4/1/2013 1:00:00 PM I only want to see 4/1/2013 1200pm - 1:00pm.
 
Thanks for your help guys I think we are close just need to only see an hour instead of every timestamp before or after the date.

Posted By: DBlank
Date Posted: 07 May 2013 at 7:19am
{Reports_Events.Timestamp} in dateadd('h',-1,{?Pm-Reports_Events.Timestamp}) to {?Pm-Reports_Events.Timestamp} 

Posted By: nhopp4
Date Posted: 07 May 2013 at 8:47am
That worked!  Thanks a lot DBlank!  If you have time how would I make it to also include 15 minutes after if I wanted?
 
Thanks!!!
Nick

Posted By: DBlank
Date Posted: 07 May 2013 at 11:59am
add 15 minutes to the second value using dateadd function
 
{Reports_Events.Timestamp} in dateadd('h',-1,{?Pm-Reports_Events.Timestamp}) to dateadd('n',15,{?Pm-Reports_Events.Timestamp})


Print Page | Close Window