Displaying "zero total" records

[BryanLane]

I am using version 11.5.8.826.

I have a list of customers that I would like to see on a report just showing total sales for the fiscal year to date (9/1 to 8/31).

The list has 49 customers, but only 38 of them have sales so far this fiscal year. I entered the 49 customer numbers into the selection formula and got the correct list of customers when I had just the Customer Master (CusMas) table included in the report. When I linked in the Sales History table (LinPrm) with a left outer join based on Customer Number and selected records with an Invoice Date greater than or equal to 09/01/2010 I only get 38 unique customer numbers that have sales FYTD.

How do I get the report to list all 49 customers? I thought the left outer join would do that. But obviously I was not right.



"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

You can't add selection criteria on the right hand table. You could omit the date criteria and then in the report, create a conditional formula to return the fiscal year to date data, e.g.,

if {table.date} in date({?Fiscalyear}-1,9,1) to currentdate then
{table.sales}

Another approach would be to use a command as your sole datasource, where you can use a left join in the "From" clause which would allow the left join to be maintained while still applying the selection criteria, like this:

select customer.name, saleshist.sales
from customer
left outer join sales on
customer.customerid = saleshist.customerid and
saleshist.invoicedt >= {?StartDate} and
saleshist.invoicedt < {?EndDate}

You can hard code the dates or add parameters on the right of the command box or use functions specific to your datasource, like currdate for Oracle.

-LB

 

[BryanLane]

Thank you again LB,

Took me a bit to get back here, I took of my "Crystal hat" and put on my "Payroll hat" so that everyone would get money and keep them somewhat happy.

I used the datasource way of doing it. Another one down, now on to the next challenge.

Thanks again,
Bryan

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein