|
I'm working on a report and running into a problem that I've encountered multiple times in the past but haven't found a solution for.
I have two tables. The first table is a customer master table and the second is an invoice master table. I've linked the two tables together using the customer_id fields. The problem I'm running into is when I filter the invoices by date or some other parameter using the select expert. If there are no invoices that match the parameters, no invoices are displayed, as expected. However, I need to still be able to display values from the fields in the customer master table, such as customer_id, customer_name, etc. in the report header. When no invoices are displayed, I'm unable to display customer information in the report header. I'm selecting the customer_id using a parameter.
Thanks for reading.
|
|
for a single customer invoice I think people often handle it by running the 'invoice' data as a subreport. This way your selection criteria allows for the customer data to still pull into the main report.
If you can create a stored proc as your data source (or a crystal command) you can move the date range criteria out of the WHERE and into the LEFT JOIN
example:
select c.customerid, c.fname, c.lname, i.* from customers c
LEFT JOIN invoices i on i.customerid = c.customerid and i.date between @start and @end
WHERE c.customerid = @customerid
Edited by DBlank - 27 Jul 2015 at 11:21am
|
IP Logged
