CR2013 - Crosstab Column Issue - How to display a pre-specified list of columns?

Hi! I wasn't sure how to phrase the title.

I have a monthly cross tab report that contains a list of 800 Toll free numbers as the column headers, and the amount of calls that came in by date for the row. (Attached picture of a finished report).

Here's my dilemma: Our client would like to view the usage for every toll free that they have with us, even if there's no timestamp or CallsIn for that month. I have the record selection set up to pull all records matching those toll free numbers, but of course Crystal won't include any records that have no data. The attached image should have 2-3 DNIS in between that did not have usage this month.

Is there a way that I can have Crystal Reports display the list of toll free numbers, and THEN look at the timestamp & callsIn fields? I've already ensured the 'suppress empty columns' box is not checked.

Here's my record selection formula, if it helps:
{iDNISStat.DNIS} in ["Array Of Toll Free #s Here"]  and {iDNISStat.Timestamp} in LastFullMonth

Cross Tab expert set up:
Columns: DNIS, Rows: timestamp, Summarize: CallsIn

Thank you!!
cr2013-Crosstab-DNIS-example.png
lavitaSenior VPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vastoCommented:
You need to have the full list of pones in one table and left join it with the data for the calls ( which will be in another table). Then you will see at least one line per phone , even for those without calling data and cross table will show all reports. You also need to exclude the timestamp check from the record selection formula  ({iDNISStat.Timestamp} in LastFullMonth), because it converts the join from left join to inner join.
The best way to handle this is using a command which looks like this:
select .... from PhoneList pl LEFT JOIN PhoneData pd ON pl.PhoneID=pd.PhoneID and pd.Timestamp between {?DateFrom} and {?DateT0}
WHERE pl.PhoneID in (....)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike McCrackenSenior ConsultantCommented:
Agree.  If you don't have a table with the phone numbers.  Does the DNIS table have all the phone numbers if you ignore the time frame?

If so you can use it for the PhoneList table vasto mentions above.

mlmcc
lavitaSenior VPAuthor Commented:
Thanks to the both of you! I'm still getting use to the idea of needing a table for *everything* in Crystal Reports.

If anyone in the future has this issue/sees this post, here's how I solved it:
Originally I only had 'TableA', and an array of data for column headers.

1) Saved the desired column headers in an Excel 2003 Spreadsheet (NEW TableA)
2) Connected to the table, added in the call data database. (TableB [was TableA])
3) I linked the two fields with 'LEFT OUTER JOIN' (Not Enforced). TableA Field -> TableB Field
4) Since I wanted only records for the past month, as well as null values, my record selection formula is:
IsNull({TableB.Timestamp}) or {TableB.Timestamp} in LastFullMonth

Then I simply added the fields to the CrossTab expert, and suppressed empty rows.

For whatever reason, I could not get the changes to work on the existing report, but when I made a new report under the exact same conditions it worked perfectly.

Thanks again! You guys rock!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.