display dates with no data using excel DATE table with left outer join

[fishymetrics]

I have read many posts and faqs regarding the dilema of displaying continuous calendar dates even when there is no data returned. I'm trying to work it out in a test report.
Using Cr10
My excel table has dates from 1/1/1999 to 12/31/2025

I linked, using LO join, the date to a "close date" field in a call handling table in a Progress database thru ODBC
Selection was for year to date

CR created this sql

SELECT `DATES_`.`DateTime`
FROM `DATES$` `DATES_`
WHERE (`DATES_`.`DateTime`>=#2011-01-01 00:00:00# AND `DATES_`.`DateTime`<#2011-11-04 00:00:00#)


SELECT "xca_mstr"."xca_cls_date", "xca_mstr"."xca_nbr"
FROM "PUB"."xca_mstr" "xca_mstr"

This works fine.

However, I want to do a selection on the call table where "xca_mstr"."xca_nbr"startswith "CA"
so my Record selection formula is

{DATES_.DateTime} in YearToDate and
{xca_mstr.xca_nbr} startswith "ca"

CR creates the sql

SELECT `DATES_`.`DateTime`
FROM `DATES$` `DATES_`
WHERE (`DATES_`.`DateTime`>=#2011-01-01 00:00:00# AND `DATES_`.`DateTime`<#2011-11-04 00:00:00#)


SELECT "xca_mstr"."xca_cls_date", "xca_mstr"."xca_nbr"
FROM "PUB"."xca_mstr" "xca_mstr"
WHERE "xca_mstr"."xca_nbr" LIKE 'ca%'

And as you guessed it, now my "dates without call data" disappear.

maybe its just late in the day and brain not working but any suggections as to how to handle this?

 

[lbass]

What is the resulting report supposed to look like? Would you be grouping on some field? Which one? Can you provide a little mockup?

-LB

 

[Madawc]

A left-outer link should do the trick. (I assume they can be done between a table and an Excel spreadsheet, though I have never done this.)

If you're selecting on the date receving the left-outer, you will first have to do an IsNull test on the field, otherwise the formula will stop.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[fishymetrics]

sorry for the delayed response, I was fishing

the problem was that the Left Outer join stopped working when I added the selection criteria <{xca_mstr.xca_nbr} startswith "ca"> on the "Righthand" table "xca_mstr".

I think I had wrestled with this before and I was having a "senior moment" last week.
the fix was to change the selection to

{DATES_.DateTime} in DateTime (2011, 01, 01, 00, 00, 00) to DateTime (2011, 01, 21, 00, 00, 00) and
(isnull({xca_mstr.xca_nbr}) or {xca_mstr.xca_nbr} startswith "ca")

Interestingly, the SQL query that CR creates does not change.
So I guess CR just gets all the xca_mstr data from the database, regardless of whether it startswith "ca" and just displays what I asked for?

I also noticed that, because I am using 2 data sources, the SQL query does not include the words "Left Outer Join" as it would when joining tables in the same database.

Thanks LB and Madawc for always offering your help.

 

[fishymetrics]

oh , i need to correct something.

the sql that CR creates DOES change. It drops the WHERE "xca_mstr"."xca_nbr" LIKE 'ca%'

 

[lbass]

You didn't answer my questions.

-LB