Issue with Left outerjoin

[raccess]

Hi,

I have one report in which I’m using two tables Region & SalesSummary. Region table contains department structure and SalesSummary table contains summarized 12 months sales data with department ID.

Now in my report I want to display all regions (group of departments) irrespective of their data value in SalesSummary. If there are no data in SalesSummary then it should display 0 but all values from Region table should appear.

I have put left outer join between these two tables but if I don’t have any data in SalesSummary it’s not showing up in report. How can I solve this problem?

Here is my table structure,

Region Tbl

RegionID
RegionName
DeptFrom
DeptTo
DeptID


SalesSummary Tbl

FiscalYear
DeptID
Period1
Period2
‘
‘
‘
Period12

I’m using CR10, Oracle & Business View to join tables.

In report I need all records from Region table and if no data exist on SalesSummary then it should put zero, how I can achieve this? Here if I don’t use Fiscal Year then I’m getting data but if I used Fiscal Year as input parameter I’m not getting blank records.

Thanks in Advance for your help,
-R

 

[MrBillSC]

In your report's selection formula are you selecting on a field in the SalesSummary table? This might cause the problem.
MrBill

 

[lbass]

Bill is right. If you select on the right hand table, it will make the join act like an equal join. Another approach would be to remove the select and create a formula for the detail section that limits the data to what you are interested in:

if isnull({Sales.Date}) or
not({Sales.Date} in date({?fiscalyear}-1, 07, 01) to date({?fiscalyear},06,30)) then 0 else
{Sales.Amt} //example using July - June fiscal year with parm

-LB

 

[raccess]

Thanks this is what i was looking for.

Thank You