Hi, I have a problem where I have to show invoice header information such as invoice date, invoice number etc, and then on the same row display charge lines of the invoice of certain types if they exist.
The invoice and the charge lines of the the invoices are linked by an unique invoiceNo and I need to display invoiceCharge.chargeType = 'Labour', so that on my report, I should have something like:
Invoice Date Invoice No Labour Charges
01/01/13 00001 2 Men Charge
2 Men Overtime
04/02/13 00002 4 Men Charge
06/05/13 00003
08/11/13 00004 2 Men Charge
* Invoice 00001 has 2 charge lines of type labour and invoice 00003 doesn't have any charge lines of type labour
The issue is that there are other charge lines on the invoice that should not display in the Labour Charges column and if there are no charges for Labour the invoice still needs to display (e.g. invoice 00003). I have in the select expert, isnull({invoiceCharge.invoiceNo}) or (not isnull({invoiceCharge.invoiceNo}) and {invoiceCharge.Chargetype} = 'Labour') and I have {invoice.invoiceNo} -> {invoiceCharge.invoiceNo} linked using left outer. Also, I have the data displayed using a cross tab report with {invoice.invoiceDate}, {invoice.invoiceNo} and {invoiceCharge.ChargeDesc} as rows.
This works fine for invoices without any charge lines or invoices with charge lines of type 'labour', however, it doesn't work with invoices with all charge lines of other types and I can see that they are being filtered out using the select expert but I'm not sure how I can get them to show properly without showing other invoice charge lines?
|