Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: date range parameter Post Reply Post New Topic
Author Message
Drdre01
Newbie
Newbie


Joined: 13 Dec 2014
Online Status: Offline
Posts: 2
Quote Drdre01 Replybullet Topic: date range parameter
    Posted: 27 Jun 2015 at 12:46pm
I have a store proc with the following date which feed 3 different reports
1) Daily
2) Weekly
3)Monthly
but the problem is that when the 3 reports are displayed they all return the accurate data but in the display .It displays the date range for a day rather than a day for daily,month for monthly and week for weekly
SQL
CREATE PROCEDURE [dbo].[xxx} (
   @p_StartDate DateTime = NULL,
   @p_EndDate DateTime = NULL,
   @p_UserTeam VarChar(100) = NULL
) AS
/*

*/
SET NOCOUNT ON

IF @p_StartDate IS NULL
   SET @p_StartDate = CAST(CONVERT(Char(8), GETDATE()-1, 112) AS DateTime)

IF @p_EndDate IS NULL
   SET @p_EndDate = CAST(CONVERT(Char(8), GETDATE()-1, 112) AS DateTime)

-- Make certain that the SP is pulling the entire end day's data only if a time is not already passed in
IF DATEPART(hh,@p_EndDate) = 0
     SET @p_EndDate = CAST(CONVERT(Char(8), @p_EndDate, 112)+ ' 23:59:59' AS DateTime)   

Present formula to implement the date range in crystal
'Test for dateonly state - Start time = 12:00:00 AM and End time = 11:59:59 PM. Otherwise display the time as well.
Dim dateonly as boolean

dateonly=True

Dim startHour as Number
Dim startMinute as Number
Dim startSecond as Number
Dim endHour as Number
Dim endMinute as Number
Dim endSecond as Number

startHour=Hour ({pr_HVDB_rpt_Teams_Individual;1.StartDate})
startMinute=Minute ({pr_HVDB_rpt_Teams_Individual;1.StartDate})
startSecond=Second ({pr_HVDB_rpt_Teams_Individual;1.StartDate})

endHour = Hour ({pr_HVDB_rpt_Teams_Individual;1.EndDate})
endMinute = Minute ({pr_HVDB_rpt_Teams_Individual;1.EndDate})
endSecond = Second ({pr_HVDB_rpt_Teams_Individual;1.EndDate})


If startHour <> 0 or startMinute <> 0 or startSecond <> 0 or endHour <> 23 or endMinute <> 59 or endSecond <> 59 Then
    dateonly = False
End If

If dateonly = True then
    Formula=ToText({pr_HVDB_rpt_Teams_Individual;1.StartDate},"MM/dd/yyyy") & " through "& ToText ({pr_HVDB_rpt_Teams_Individual;1.EndDate}, "MM/dd/yyyy")
Else
   Formula=ToText({pr_HVDB_rpt_Teams_Individual;1.StartDate},"MM/dd/yyyy") & " " & ToText({pr_HVDB_rpt_Teams_Individual;1.StartDate},"hh:mm:ss tt") & " through "& ToText ({pr_HVDB_rpt_Teams_Individual;1.EndDate}, "MM/dd/yyyy") & " " & ToText ({pr_HVDB_rpt_Teams_Individual;1.EndDate}, "hh:mm:ss tt")
End If                            
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Jun 2015 at 4:00am
sorry but I am not sure what you are asking exactly.
IP IP Logged
Drdre01
Newbie
Newbie


Joined: 13 Dec 2014
Online Status: Offline
Posts: 2
Quote Drdre01 Replybullet Posted: 29 Jun 2015 at 5:13am
The question is based on the sql syntax and the code in crystal how can I modify it to give me what I want.
I said its one code feeding the 3 reports but I want when the the report is ran it displays daily for daily and that's what it's doing but when i run the weekly /monthly it still shows the daily range e,g 06/01/2015 through 06/01/2015
I want the weekly to be like
06/01/2015 through 06/07/2015
& monthly to be
06/01/2015 through 06/30/2015

but presently all three reports are returning accurate data but the header display is showing
06/01/2015 through 06/01/2015 for all 3 reports
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Jun 2015 at 5:52am
so you are setting the 'correct' @p_StartDate and @p_EndDate values in your sql otherwise your data set would be wrong.
So just set your result to include these as part of your output and then drop them into your header.
IP IP Logged
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.047 seconds.