Null Values in a Cross Tab

[Cort]

Heya again guy and gals. I've read the keyword search on this topic but don't really understand it all so I'll give you my Example:

CR 8.5

Table 1 - Reps
Rep_Name

Table 2 - Contracts
Contract_Number
Date_Assigned
Rep_Name


OK I join Rep_Name in 1 to Rep_Name in 2 with a left join.

I want a list of contracts assigned within the last full week by day for each rep. If a rep was not assigned a contract in that perior I want a 0 there. What it's doing is giving me the correct information for anyone who has at least one assignment and not giving anyone with no assignemnts as that is null and therefore there is no return.

Current result:
Rep     Mon     Tues     Wed     Th     Fri
bob     1        1        2       0      1
bill    0        0        1       0      0


Desired Result:
Rep     Mon     Tues     Wed     Th     Fri
bob     1        1        2       0      1
bill    0        0        1       0      0
jane    0        0        0       0      0

Any help would be greatly appreciated.

Thank You

 

[Cort]

Bump to see if there might be a little help out there.

Thanks

 

[Katy44]

I'm having the same problem and looking into it. Any ideas would be appreciated!

 

[Katy44]

I've managed to get this working in a very simple report just by messing around with the link direction and options

 

[Cort]

Care to share your link directions and options

 

[Katy44]

Well in your case it would be direction = from reps to contracts and a left outer join. But I can't get this to work in my more complex report.

 

[Cort]

Correct it will work like that as long as you do not specify any criteria from Table 2. Once you try to specify any criteria from Table 2 (such as when it was assigned) it will drop the Reps from Table 1 who do not have an assignment.

 

[Katy44]

Ah, that would explain it, thank you and sorry to clutter up your post with my rubbish!

 

[Katy44]

I'm sorry, still confused - in mine (it's not a crosstab, but surely the principle should be the same), I have table one left joined to table two, so in my opinion I should get all from table one. I put a field from table two in my details section, grouped by a field in table 1 and then added a count of the field in table two. This worked fine, and provided me with zeros where appropriate.

 

[Cort]

Correct that will work. But take your selection criteria and specify that you only want contracts assigned within a specific date parameter it will then return the names with a count of the valid dates but will drop those names without a valid date.

 

[Katy44]

Surely you just have to add an "or isNull(myfield)"?

 

[Katy44]

OK, WHY does that not work?! That's how you would do it in an SQL query

 

[Cort]

Yup. It works in SQL. As to why it does not work in CR I'm not completly clear on. The solution supposedly is either a manual X-Tab (which I hate to make, far too much upkeep) or a stored procedure (which I'm very unfamiliar with)

/sigh

 

[Katy44]

OK, I think I've found the solution....and you're going to hate it!! It's all to do with the way crystal handles nulls and or statements.
If you can add the "or isNull..." to every criteria field, add it BEFORE the other criteria, so
"isNull(myField) or myField = myValue"
STUPID STUPID STUPID but it seems to work

 

[Katy44]

Yes, I've got that working in my actual report now. I knew that where nulls are involved the order of the criteria in the "or" matters, but completely forgot. I have already submitted it to Business Objects, as I consider this a bug - does anyone else agree?
The only other thing I also forgot to do was to make sure my outer joins are passed through all the tables involved in the report.