Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to display all records in a crosstab?

Status
Not open for further replies.

beacon5

Programmer
Dec 29, 2008
74
US
Hi everybody,

[Crystal Reports 8.5]

I created a summary report which has a Microsoft Access 2003 database as the datasource, and have inserted a crosstab in the Report Footer. In the database, there's a table called tblCallLog and another table called tblProblem, which I've added to the report, where tblProblem is Left Outer Joined to tblCallLog on the Problem field (this is a text field).

I've added the field Technician to the Column of the crosstab and the Problem field from tblProblem to the Row. I've added a count of the Problem field from tblCallLog as the Summary.

When I run the report, I expected that, because I Left Joined tblProblem to tblCallLog, that all the records in tblProblem would display in the crosstab and would show a zero for the count if none of the technicians had logged their call by that problem type. However, this is not the case and it's just excluding those problems altogether.

What am I missing to make this work as I intend, as in the example below where 'Hardware' is zero for all three techs (currently 'Hardware' won't show at all on the crosstab)?
Code:
               Tech1     Tech2     Tech3
Password           2         7         4
Software           1         0         3
Hardware           0         0         0

Thanks,
beacon
 
You would need to do a left join FROM Problem TO Call Log, with no selection criteria on the Call Log table--but this doesn't guarantee all technicians would show up. This assumes that the Hardward field is in the Problem table, and the Technician is in the Call Log table.

-LB
 
The join is currently a left join FROM tblProblem TO tblCallLog...that's the issue, it appears to be setup properly, but it's not still not displaying all of the Problems.

Hardware is a record in the Problem field of the tblProblem and technician is a field in the tblCallLog.

There are enough calls and problem types that the technicians should always show up.
 
Thanks for your help LB, but I'm going to cut my losses and just create a manual crosstab.

I hate that I can't get this figured out, but I have to finish this report today, so I can't spend any more time troubleshooting it or asking for help.

Thanks again,
beacon
 
If you added any selection criteria on the calllog table it would effective undo the left join.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top