Joined: 07 Jun 2010
Online Status: Offline
Posts: 40
Topic: Selection Expert in Cross Tab Report Posted: 04 Apr 2014 at 4:22pm
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 DateInvoice NoLabour 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?
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