Crystal Reports XI problem left outer joining tables

[FJS4]

I am currently working on creating a report in CRW XI, but am stuck on a snag in getting all of my data show. I am new to CRW, but have been working extensively on this problem over the last few days, and help would be greatly appreciated. The problem is as follows:

I am trying to create a report that will show the $ values for 4 different status levels [target, qualified, proposal and submitted]. This grouping is actually a subset of a larger list of status levels, but I used the "is one of" feature of the select expert to control for just those 4. The status levels come from a minor table called "Status" and are "status.description". The $ figures are located in the main table "bid_subj" and are "bid_subj.est_net_revenue_amt".

My problem occurs when I try to generate a table and bar graph that will compare the $ values of all 4 groups side by side. If the sample taken has data for all 4 categories, then the report generates perfectly. However, when I control for certain parameters, the data will sometimes only have records for entries that belong to one or two of the status levels. When this occurs, the report generated excludes the missing status levels, but for comparison puposes, I need them there. The table looks like this:

Target= $$$$
Submitted= $$$$

But I need it to look like:

Target= $$$$
Submitted= $$$$
Proposal= 0
Qualified= 0

I tried to use a left outer join on the tables to correct this problem, but it doesn't change anything, and in certain instances, the report failed because I enforced from the minor table. If anyone knows how to work through this issue, advice would be greatly appreciated. Thanks. If it's useful, the relevant SQL for the report is:

FROM ((`BID_SUBE` `BID_SUBE` INNER JOIN `BID_SUBJ` `BID_SUBJ` ON `BID_SUBE`.`SUBJ_ID`=`BID_SUBJ`.`SUBJ_ID`) LEFT OUTER JOIN `STATUS` `STATUS` ON `BID_SUBJ`.`STATUS`=`STATUS`.`STATUS`) INNER JOIN `BUSINESS_LINE` `BUSINESS_LINE` ON `BID_SUBJ`.`BUSINESS_LINE`=`BUSINESS_LINE`.`BUSINESS_LINE`
WHERE (`BID_SUBJ`.`STATUS`=0 OR `BID_SUBJ`.`STATUS`=1 OR `BID_SUBJ`.`STATUS`=4 OR `BID_SUBJ`.`STATUS`=5) AND (`BID_SUBJ`.`SBU`='Army- PEH' OR (`BID_SUBJ`.`SBU`>='Army- PEH' AND `BID_SUBJ`.`SBU`<='Other') OR `BID_SUBJ`.`SBU`='Civil-HCM' OR `BID_SUBJ`.`SBU`='DHS-HQ' OR `BID_SUBJ`.`SBU`='DoD' OR `BID_SUBJ`.`SBU`='NSA- Intel' OR `BID_SUBJ`.`SBU`='Other')

 

[hilfy]

You would have to start from the status table and then link from there to your other tables in order to get all of your status values. Or you can able do a RIGHT outer join to the Status table.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[FJS4]

I've tried multiple variations of linking from the status values, but I keep getting an error message that reads:

Database Connector Error: 'DAO Error Code: 0xce0
Source: DAO.Database
Description: Join expression not supported'

It's either that or the join doesn't modify anything. Could it have to do with settings in my database itself? I'm using Access.

 

[dgillz]

What is a DAO database? Are you using an ODBC connection?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[FJS4]

I'm actually not 100% sure. In Database Expert, I chose the "Create New Connection" drop down and then the "Database Files" drop down, from which I chose my database, which was in a WinAwardLAN folder. Is this at all helpful? How would I confirm whether or not I am using ODBC? Sorry, I'm pretty new to this.

 

[hilfy]

It sounds like you're connecting to a file-based database. That means that you may be somewhat limited in what you can do based on what indexes are available on the tables. What is the file extension on your database?

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.