How to display date in crystal report even though there are zero orders ?

[swenri]

Hi,
I’ve a report that picks up orders between yesterday 6:00 AM to today 6:00 AM. The problem I‘m having is when I don’t have any orders between this time period in the crystal report it is not displaying the time ie ; the fromdate and todate. How do I make sure that though I have zero orders for time period,I display the fromdate and todate in the crystal report.Please see the code that I have highlighted. This is really urgent.Thank you
-----------------------------------------------------------------------------------------------


USE tempDB



IF OBJECT_ID(N'tempdb..#Result1', N'U')
IS NOT NULL
DROP TABLE #Result1;

GO




USE [DatamartDB2]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MMC_SP_FN_NutritionConsult]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MMC_SP_FN_NutritionConsult]
GO

USE [DatamartDB2]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[MMC_SP_FN_NutritionConsult]

AS


BEGIN

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

PRINT 'FOOD AND NUTRITION NUTRITION CONSULT ORDERS'
DECLARE @Location varchar(255)

BEGIN


Declare @FromDate datetime
Declare @ToDate datetime
Declare @RptTimeFrameout VARCHAR(50)

SET @RptTimeFrameout='1'
if @RptTimeFrameout='1' -- Daily
Begin

select @FromDate = CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '06:00:00 AM')
select @ToDate = CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'06:01:00 AM')



End



SELECT DISTINCT

"BLSession_Extended"."LastName" + "BLSession_Extended"."firstname") AS PtName,






@FromDate AS Fromdate,
@ToDate AS Todate INTO #Result1





FROM ((("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVProblems" "BVProblems"
ON "BLSession_Extended"."sessionID"="BVProblems"."sessionid"
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLOrdersLog" "BLOrdersLog"
ON "BLSession_Extended"."sessionID"="BLOrdersLog"."sessionID"


)
INNER JOIN "DatamartDB2"."dbo"."BLPatient_Location" "BLPatient_Location"
ON "BLSession_Extended"."sessionID"="BLPatient_Location"."sessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Times" "MO_Times"
ON "BLSession_Extended"."sessionID"="MO_Times"."SessionID")
INNER JOIN "DatamartDB2"."dbo"."bllocation" "bllocation"
ON "BLPatient_Location"."location_num"="bllocation"."location_num"

LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics"
ON "BLSession_Extended"."sessionID"="MO_Demographics"."sessionID"
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUserFreeText" "BLUserFreeText"
ON ("BVProblems"."sessionid"="BLUserFreeText"."sessionID"
AND "BVProblems"."AtomID"="BLUserFreeText"."AtomID"
AND "BVProblems"."RowID" = "BLUserFreeText"."RowID"
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVActions" "BVActions"
ON "BLSession_Extended"."sessionID"="BVActions"."sessionid"
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUser_names_Extended" "BLUser_names_Extended"
ON "BVActions"."orderedSigner"="BLUser_names_Extended"."UserID"

WHERE "BLSession_Extended"."FacilityID"=0


AND "BVActions"."orderedTime" >= @FromDate
AND "BVActions"."orderedTime" <=@ToDate

AND "BVActions"."IPR_Display"='Order Food and Nutrition Consult'
AND "BVActions".OrderType = 'RegularOrder'
AND "BVActions"."CancelledSig" IS NULL



SELECT rlt1.*
FROM #Result1 rlt1
where ROOM in

(SELECT TOP 1 ROOM
from #Result1 rlt2
where rlt1.mrn=rlt2.mrn
order by ROOM desc
)

END

END

SET NOCOUNT OFF

SET ANSI_NULLS OFF




GO

GO
GRANT EXECUTE ON [dbo].[MMC_SP_FN_NutritionConsult] TO [Public]


IF OBJECT_ID(N'tempdb..#Result1', N'U')
IS NOT NULL
DROP TABLE #Result1;

 

[kenhamady]

When you report off of an SP it should create parameters in the report that correspond to the SP parameters. If you place these objects on the report it should show you the dates that you used for the parameters, even when you have no data.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[swenri]

Thank you for the reply Ken. But, it did not work fro me . Can you please explain me in detailed as this is urgent.

Thank you,

 

[kenhamady]

OK, I see I missed the part where you assign the values in the SQL. This means that CR won't create the parameters. And, since there are no records in the SQL results you won't be able to pull anything from the SQL. Your options are:

1) Create a UNION that appends one more record. In this record give it a blank name and the same start and end dates. Then you will always have at least one record in your results.

2) Create two formulas in the report that are:
CurrentDate

CurrentDate -1

And use these to display the range. Just note that these can't be tied to the Query, so if you change the value for ReportTimeFrame on the fly, the report won't know that. Unfortunately, there is no way to tell the report what your time frame variable is when there is no data returned by the SP.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[swenri]

Thank you Ken I did the same as you had suggested , but I want it to show dateand time as 6:00 AM to 6:00 AM next day.

It is showing currect date and time like 11/05/2013 12:38:35 PM and 11/04/2013 12:38:35 PM.

Can you please let me know how to do ? Thank you for all the help.