Outer join on crosstab

[Katy44]

Further to the left join issue, does anyone know if this works for crosstab reports?

I have two tables:
Table 1: ID


Table 2:
Table2ID
Table1ID (FK)
NumberToSum

I have a crosstab where I have Table1 ID as rows and then Table2ID as columns. I then sum a formula which takes NumberToSum, and replaces it with 0 if it's null in the cells.

Table 1 is linked to Table 2 using a left join. Can anyone tell me why all the values from Table 1 aren't displayed, with zeros in the cells where there aren't any records in Table2?
There's no selection criteria in the report at all.

 

[Jyme]

I think I might of had a similar issue before...you should provide in your query a way to change your nulls to 0's so they come in your record set...if they arent in your record set ,I think, they wont be shown in the crosstab regardless of your check in crystal..if your using sql server try

isnull(NumberToSum, 0)

I think this is what youre talking about?

-jim

 

[Katy44]

Thanks for your help. This sound like the problem I am having. I'm not sure where to put the null check though - in the record selection? Or somehow in the data source?
(I'm using CR10 and SQL Server 2000)

 

[Katy44]

Thanks, it seems to be working. One of my links was the wrong way round...I feel stupid....!