July 11, 2014 at 3:12 pm
Maybe this has already been asked and answered but if it is in this forum I did not find it.
The invoice.rdl design is as follows
- Tablix 1 for Invoice Header (Invoice Number, BillTo, ShipTo, Invoice date, etc.)
- Tablix 2 for Invoice Detail (list of items)
An invoice selection parameter allows selection of several invoice numbers. That part works. The parameter is set to allow multiple values obtained from a dataset.
A second dataset retrieves the invoice's master info ... WHERE InvoiceID IN (@InvList).
A third dataset retrieves the invoice's detail WHERE InvoiceID IN (@InvList).
I am using SQL Server datasets, not FetchXML.
Works great if a single invoice is selected.
However, instead of rendering a batch of invoices, one single invoice is generated like this:
Tablix 1:
Invoice Number, BillTo, ShipTo, Invoice date FOR THE FIRST INVOICE
Invoice Number, BillTo, ShipTo, Invoice date FOR THE SECOND INVOICE
Invoice Number, BillTo, ShipTo, Invoice date FOR THE THIRD INVOICE
Followed by
Tablix 2
Invoice Details FOR THE FIRST INVOICE
Invoice Details FOR THE SECOND INVOICE
Invoice Details FOR THE THIRD INVOICE
Instead of
Tablix 1:
Invoice Number, BillTo, ShipTo, Invoice date FOR THE FIRST INVOICE
Tablix 2
Invoice Details FOR THE FIRST INVOICE
PAGE BREAK
Tablix 1:
Invoice Number, BillTo, ShipTo, Invoice date FOR THE SECOND INVOICE
Tablix 2
Invoice Details FOR THE SECOND INVOICE
and so on.
There must be a better way of handling than creating an Invoice Group in the .rdl design form.
Right ?
What is the "trick" /hack / technique / flash of genius to force SSRS to render the set of individual invoices ?
July 16, 2014 at 7:44 am
Use a subreport within the invoice to hold the invoice lines.
1. Report (1) - This is primarily the invoice header dataset. Include in a tablix of your choice. Within the group (or detail) properties choose to have a page break between each instance. A tablix gather data from more than one dataset. [you can use lookup functions for some work arounds].
2. Report (2) - This is the report detail with the invoice lines. Create a parameter to hold the invoice id and filter the dataset to only show the lines for the selected invoice number.
3. Back on report 1 add another row to the invoice header tablix and include a subreport (Report 2). Pass the invoice number from report 1 to the parameter in report 2.
4. Format as necessary.
Fitz
July 17, 2014 at 1:54 pm
Thank you for your suggestion.
I'll work on it when I get back from vacation next week.
Update - tried it - solved my problem.
Regards
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply