Background: Basically, I have to create to report using a cross-tab that displays the number of billed minutes used each day on each telephone within a facility, over a date range.  The rows are each specific phone.

Problem: The problem I am running into is that if there is no activity on a phone within the date range (zero billed minutes), the cross-tab does not see any data and does not display that particular phone at all.  Those who will be using this report demand to see the phones with no activity within the cross-tab displayed with the other phones. 

Is there some way I can remove the date range from the select expert, but instead use it somehow to suppress columns from my cross-tab that don't fall in the date range?  Or maybe is there some other, better way to achieve what I'm trying to do?  Because I keep running into walls every time I try something and nothing seems to work. I had a list of phones with no activity in a subreport elsewhere in the report but that was not acceptable to the user, so I have no choice but to write it this way and I don't even know if it's possible.

Thanks for any help you guys can provide!

Oh sorry about that I must not have been clear.

As long as I have my date range criteria in the select expert, the cross-tab will only show data within my date range.  (Each column in my cross-tab is each day within the date range, btw, in case I wasn't clear about that before.)

This works ALMOST perfectly, except for one major problem.  The user really needs to see phones with no activity in the date range displayed in the cross-tab with the rest of the phones, and that does not work.  The reason for this is the following:

Each individual phone call is stored in the database with a date/time, billed minutes, and the phone it was made on, among other things.  This table is the main table the cross-tab is pulling from.  If the criteria in the select expert is limiting the report to calls only stored with dates within the date range, and there are NO calls made on a phone within the date range, that phone is simply left off the cross-tab because it was never in the result set of the report to begin with.  This is why creating a formula setting the value to zero in the event of a null value does not work (I tried) because it is not a null value but rather, the criteria of the report filtering out the results before the cross-tab ever sees it.

I tried adding "or isnull(date field)" into the criteria, but all that did was add the phones that never had any activity EVER into the cross-tab (which was mainly junk data anyway, which didn't help).  It still ignored those phones that had calls OUTSIDE the date range, but just none WITHIN the date range. 

So the way I'm thinking about this is that the only way to achieve this would be to eliminate the date range criteria from my select expert altogether and find some way to suppress columns from my cross-tab that are outside of the date range.  But I do not think Crystal XI (the version I am developing on) is capable of doing this (or is it?).  I need some type of alternate solution to force my cross-tab to display all phones EVEN IF there is no data in the date range for some phones.

Solutions not involving a cross-tab would be welcome, but not preferred.  I really want to exhaust any and all possibilities to get this to work with a cross-tab because I'm envisioning a hideous, unreadable report if made another way.

Thanks again!

What is your data source type. Usually you can address this problem by either writing a Command for your data or use a view or a stored proc.

Edited by DBlank - 17 Nov 2009 at 7:24am

Could you not just put a formula relating to the phone not showing up in the display string of the cross tab.. stating that if it equals 0 it still shows up.

I think you can solve your problem by changing the link type between the databases. Try a left outer join which will return all the data from the table on the left regardless of whether it has a corresponding link on the right.