Joined: 28 Aug 2013
Online Status: Offline
Posts: 4
Topic: Problem displaying field values Posted: 27 Jul 2015 at 11:04am
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.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 27 Jul 2015 at 11:20am
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
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
