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

lavita
lavita used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011
Commented:
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 (....)
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
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 VP

Author

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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial