Crystal Reports 8.0-Need dates with no data to show quantity 0

[ShelDoyal]

I've created a report that pulls numbers of tickets based on day and category. I have charts showing this data; however, if there were no tickets for that day for a particular category, the date is skipped on the chart. I need days without tickets to show as zero. I've tried using cross-tab reports, but that presents some other problems, not the least of which is it's a massive report and I don't want to recreate the whole thing. Anyone have some magic code to resolve my problem?

 

[synapsevampire]

The issue is that you want to show data where none exists.

The best solution, and one consistent with Data Warehousing, is to create a periods table, apply your date filtering to this table, and do a Left Outer to your data table.

-k

 

[ShelDoyal]

Here's were I reveal my ignorance...how? I have read only access to the database where I pull data from. I would need to create a table outside the database where the rest of the data is stored. Right? And what's a period table? I've had very little "official" training...it's been learn as you go.

 

[ShelDoyal]

I've had a period table added to the database. The dates are in DateTime format with time set as 12:00 AM. When I do a left join on the period table, I only get records from the second table showing records with Open_Time (a DateTime field) have the time as 12:00 AM. The DBA said I'd have to go into the SQL statement and modify it to format the DateTime field as a Date. He suggested I truncate the field. I'm not sure how to format the trucate function (haven't worked with SQL much). Haven't been able to find much information in the help files, online, or in the book I have.

My current SQL statement reads:

SELECT
SPR_DATES."DATESTAMP",
SPR_INCIDENTS."INCIDENT_ID", SPR_INCIDENTS."OPEN_TIME", SPR_INCIDENTS."PRODUCT_TYPE"
FROM
"SCTR"."SPR_DATES" SPR_DATES,
"SCTR"."SPR_INCIDENTS" SPR_INCIDENTS
WHERE
SPR_DATES."DATESTAMP" = SPR_INCIDENTS."OPEN_TIME" (+)


I'm working in Crystal Reports version 8.0.1.0

Any advise would be welcome. Also, any direction on the best book or resource to find syntax and function information for SQL would be great. The book I have is very basic.