How can I include zero values in Crosstabs?

[FrankInBerlin]

Hi Everybody,

here is the problem:
Two tables

Table 1:
Code Description
1001 aaaa
1002 bbbb
1003 cccc
....
Table 2:
customer Code
1 1001
1 1002
1 1001
2 1003
3 1004
....

Now I want to create a crosstab analysing the the Data for one customer but should show all descriptions no matter if they have been used for this customer or not.
What I want is:

Description Count for Customer 1
aaaa 2
bbbb 1
cccc 0
.... ....

In a regular crosstab I only get the descriptions for counts > 0. But this works in minutes.

I created a workaround using a regular report and a subreport but in that report a subreport is generated for every description. Since my worst case scenario is 20000 descriptions and an average time for generating the subreport is 2 min this can mean 27 Days to run this report.

Can anybody help???

With my best,
Frank

 

[kenhamady]

If you link from table 1 to table 2
and you use a Left Outer Join
and you DONT put an criteria on table 2

Then you should get what you are describing. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[FrankInBerlin]

Dear Ken,

of cause I have to put a criteria on table2.
I only want the information for one customer
(for a certain period, but I did not mention it here to keep it simple).

And even if I choose all customers, I would only get the discriptions used so far. But table1 also contains a lot of codes/descriptions not used yet but with possible use in the future.

The whole point in this report is to compare the data from one month/year with the data of an other period (month/year).

I tried all sorts of linkings, and nothing worked so far.
(the left outer join being the first).

I am afraid that there is not other solution then the time consuming workaround I found so far.
But it is driving me crazy, because what I want is pretty simple!

With my best,

Frank

 

[kenhamady]

The criteria on table2 is why lose records with no matches. I have an article with your options that might help in:

http://www.kenhamady.com/news0208.html

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[JohnEck]

Use a left join and an if ISNULL statement

 

[FrankInBerlin]

Hi Everbody,

thanks for all the input.
You guys are great!

With my best,

Frank

 

[PaulDeshayes]

Hi Frank,

I have the same problem as you have. I have it with cross-tabs, and with graphs.

Did you find a nice solution to your problem?

I tried the left outer join with null-testing, but it didn't work. In fact, even if there is no selection-formula, I won't have all the "customers", but only those having some "descriptions". I worked around with more complex attempts, but none succeeded.

/Paul