Need date to appear even though no data for that date 1

[lwanalyst]

I hope someone can help me.

I have a report that lists the # of admits at a hospital per date, but if there are no admits on any particular day, then the date doesn't appear at all. How can I get the date to appear and the # of admits to populate as "0". see example...

01/01/02 10 admits
01/02/02 7 admits
01/03/02 9 admits
01/05/02 7 admits
(there were no admits on 01/04/02, so it doesn't appear)

 

[dgillz]

Crystal cannot report on data that does not exist.

Your best solution - if you can do this - is to force some dummy data in your database, just the date itself with no patient name, etc.

then make your count on patient name, it will count 0 for the date in question and the restof your report can stay as it is. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[IdoMillet]

Create a new CALENDAR table with a record for each day. (side note: you could populate that table with various other useful attributes (day of week, holiday (yes/no) that could support reporting issues later on...)

Now, do an outer join from the CALENDAR table to the ADMISSIONS table and you are all set.

hth,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[kenhamady]

The calendar table will work, but you have to keep adding dates as time goes on. Also, if you need any selection criteria on the admition records the outer join will not help, since those dates won't meet the criteria.

You could create conditional totals for each date in the report, you could use subreports, or you can create dummy sections that only prints when there is a gap between dates. Which approach you use depends on how long a range of days the report will include and how many consecutive empty dates will likely occur.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[lwanalyst]

Thanks, Ken,
I like the "conditional total for each date" idea or "using the dummy sections that only print when there's a gap between dates". Which is easiest? My report is a rolling 30 day time frame that I would run bi-weekly. Do you have sample code for either method?

 

[Guest_imported]

Ken, Most likely a very basic question, but anyway where CR will automatically ask you what date to enter via pop up upon entry to form? Data is then stored in a text box until the next report asks for a new date...Thanks

 

[kenhamady]

Jack,

I don't understand your question.

lwanalyst,

I have examples of conditional totals in the Formulas page on my web site. You would need one for each day in your report. That is 30 formulas each with a grand total. You would need another 30 formulas to calculate the 30 'rolling' days based on today's date or user input. This is a lot of work, but pretty simple work. You don't need to group the report since all of the totals will be grand totals in the report footer.

The conditional sections is a bit more complex, but might be less work if you will only have gaps of a maximum of 2-3 days. You group by day and do your normal subtotals. Then you create a detail section that checks for the number of days between the current record and the next. If the gap is more than 1, this section prints a line with the next date, and a zero. You can have another section below it that prints if the gap is more than 2 and prints 2 days later and a zero. Create as many sections as you think you will need.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[rosemaryl]

Dear All,

I have found that the best solution to the above is to create a stored procedure that generates the days for you. i do this for SQL server all the time

I then group on Date for the main report and do a subreport linked to the date and place it in the group.

It works and it is easy. I just pass a startdate to the procedure and it generates all the remaining days in the year.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

ro's suggestion makes good sense.

You might also consider building a Period table upon which you can join, that way it's always available for reporting off of tables/Views/SP's.

By Period table I mean a table which has entries for things like:

Year,
Quarter,
Month,
Work Week,
Day,
Hour,
Minute,
Period (this is a datetime type)

Perfill the table with whatever range of dates you might need, and then you'll always have a source.

Standard fare in Data Warehouses

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[adam0101]

If you're reporting on a certain date range, you can make a temporary table in the stored procedure and loop through the date range to insert each date into the temp table. Then LEFT JOIN on the temp table.

CREATE PROCEDURE [whatever]
(
@begin_date_selection	varchar(10)		= '1/1/2002',
@end_date_selection	varchar(10)		= '1/31/2002',
)
AS

DECLARE			@from_date		datetime
DECLARE			@to_date		datetime
DECLARE			@inc_date		datetime
	
SET			@from_date		= CONVERT(datetime,@begin_date_selection)
SET			@to_date		= CONVERT(datetime,@end_date_selection)
SET			@inc_date		= CONVERT(datetime,@begin_date_selection)

CREATE TABLE #Dates([TempDate]	int)

WHILE @inc_date <= @to_date
BEGIN
	INSERT	#Dates
	SELECT	@inc_date
	SET @inc_date=DateAdd(d,1,@inc_date)
END

 

[kenhamady]

Remember that left outer joins won't solve the problem when you have any criteria on the outer table. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[lwanalyst]

Hi Ken,

the conditional sections sounds like it will work. The report really never has more than 1-3 day gaps consecutively. Okay, I understand the conditional formula and getting the totals, but how do I calculate the 30 'rolling' days based on today's date?

By the way, you are quite knowledgeable on the subject matter, and have been extremely helpful.

 

[kenhamady]

if you are talking about option 6 (imposter sections) You wouldn't need 30 rolling dates. You would only need 3 dates, one for conditional section. The first one would be:

{DateField} + 1

Since it prints at the beginning of the gap, it will add one day to current records value. The second section woulc use +2 and so on.

Option 4 (conditional totals) could use rolling dates for column headings, and here you would promopt for a start date and then write 30 fromulas that were:

{DateField} + 1
====
{DateField} + 2
====
etc.


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[BobCZ]

I ran into a problem like this. My problem was in certain records, no data would be created in the date field if nothing happened on that day. I solved it by using the IsNull command to check to see if the date field had any data. If not, then make up your own data.

-bob

 

[kenhamady]

Bob, that works if a record exists with a null date. But, usually there are is no record at all to contain the null value.

In otherwords if I have 3 records for 8/1, 8/2 and 8/4. I have threee records with no null values. There is no null value for 8/3 because there is no record for 8/3. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com