It still ignored those phones that had calls OUTSIDE the date range, but just none WITHIN the date range. 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). 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. 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 table is the main table the cross-tab is pulling from. The reason for this is the following:Įach individual phone call is stored in the database with a date/time, billed minutes, and the phone it was made on, among other things. 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. This works ALMOST perfectly, except for one major problem. (Each column in my cross-tab is each day within the date range, btw, in case I wasn't clear about that before.) Oh sorry about that I must not have been clear.Īs long as I have my date range criteria in the select expert, the cross-tab will only show data within my date range. Thanks for any help you guys can provide!
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. 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. Those who will be using this report demand to see the phones with no activity within the cross-tab displayed with the other phones.
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.
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.