Cross-Tab -- columns missing because no data

[SharonKelly]

Hello,

I'm using CR 8.5 and SQL Server 2000.

I'm pulling in data with a stored procedure -- a record set of patient orders over the past year to date that includes order date/time and the Specialty and Division of the ordering doctor.

It seems that my problem SHOULD have a simple answer --

This is a simplified version of what I'm getting, where the numbers represent the total orders ordered by docs in each unit for each month. The report is grouped on Specialty(e.g. Medicine, Peds). The cross-tab report is then in the group footer for specialty and has Division for the row and a function @Month for the column, where the function gives me back the month based on the order's date/time.
@Month =
if Month({Orders.OrderDtm}) = 1
then "Jan"
else if Month({Orders.OrderDtm}) = 2
then "Feb"
... etc.

Medicine
JAN APR MAY AUG

ALLERGY 133 150 166 155
CARDIO 199 60 50 33
GU 0 2 0 0

Peds
JAN FEB MAR MAY JUN

ALLERGY 133 19 66 15 77
NICU 133 60 40 33 0
GI 0 0 0 2 0


What I WANT is to have ALL the months represented in this report, even if the month had no orders for any of the Divisions within the Specialty group. I would always want all months to display. But since the column is grouping on date, if there are no records in the current group satisfying, say, 'order placed in August', then Aug won't show up as a column.

I would appreciate any hints or help that you might be able to provide. I've pored through my Crystal books, and they don't seem to address how to group on missing data, when you want that group to be represented on the report anyway.

Thanks!
Sharon

 

[synapsevampire]

The problem is that you want what you don't have

The best way to handle thsi sort of thing is to take a page from Data Warehousing and create a period table.

This period table (which I have posted SQL Server code to create previously here) contains every day for years into the future.

Now use this as your main table and LO join it to your data, and now you have all days.

There are lots of workarounds and kludges, but you'll be much happier if you address it permanently and eliminate the hardcoding.

-k

 

[bdreed35]

Hey SV, this topic comes up so much you might as well put it in an FAQ.

~Brian

 

[synapsevampire]

Yeah, I reckon so, Brian...

Perhaps better still a basic implementing Business Intelligence booklet would make sense, covering the basics of implementing a reporting system.

But who RTFMs?

-k

 

[SharonKelly]

Thanks for the very quick response! I am brand new to this group, and I did a search on Period Table and got no results. How would I find your previously posted SQL code for building the period table (which I presume I would modify to be periods of months during the interval in which I'm interested)?

Thanks!
Sharon

 

[synapsevampire]

thread149-540531

-k