Generate Invoices as PDF from SQL data

[Kristen]

I want to create an Invoice from SQL data. Currently handled by a 3rd party APP, but need to have control over the invoicing format so want to find the easiest!! way to generate the PDF from SQLdata.

Requirements are that:

Starts on Letter headed page, with Address, Invoice Number, Logo, column headings etc.
Continuation pages to have sub-total at foot and (if possible) carried forwards total top of continuation page

Footing at bottom of last page with breakdown of sales tax etc. and total, These lines must be kept-together

Secondary section with a report of cost-of-sales analysis. This is a more conventional report but needs to be part of the same PDF, be on "blank" paper template, and start on a new page. This is only provided for certain clients.

One area that I have difficulty with is word wrap. The invoice line descriptions may spill-over onto multiple lines, in which case the page breaks, and sub-totals, have to adjust accordingly.

Ideally run off all the invoices in the batch, each to a separate PDF file, but probably OK to do them one-by-one if that's the only option.

I have very little knowledge of SSRS but maybe this is something that it does standing-on-its-head?

Or perhaps there are 3rd party tools that make this easy? or templates that can be set up (in Postscript or somesuch?)

I am confident that my SQL skills can spit-out the data in whatever format is needed - except the bit about being able to predict where page breaks need to fall based on word-wrap of descriptions.

[Kristen]

In case relevant:

We already have the Invoice in an HTML page. However, it currently has no "sense" of any page breaks, so is just a long, "single page", invoice at present.

If I could make the HTML content page-break-aware,and fix the width for the appropriate paper-size, and then output that as a PDF file that would be fairly effortless (rather than starting from scratch in something like SSRS)

[yosiasz]

1. SSRS is super simple if you already have it on premise. there are other programmatic ways to do this but not worth the effort compared to to setting up an SSRS server. does client have it on premise?

2. Who wrote the web server that serves the html? Could ask them to embed "hidden page breaks" html tags you could leverage programmatically.

[Kristen]

@yosiasz

does client have it on premise?

In this case the client is us and "yes I think so", but its certainly not on the SQL box that the APPs I'm looking after are one. But I could install it. That said, we have zero skills and competence on SSRS.

@yosiasz

Who wrote the web server that serves the html?

That's in-house, so we're in control of that. I did some Googling today and that suggests that leveraging the CSS in the current web pages may convert readily to PDF (using a variety of available solutions which piggy-back on WebKit and thus do a thorough job of rendering. Out in-house weakness, via that route, is that we've never tried to do any CSS for Print as we've never needed anything other than screen-based output. Might be easier to outsource that Design work - rather than try to learn SSRS (given that we are wedded to our HTML engine)

(I had assumed that HTML might be the wrong starting point for a paper format with page-break sensing, but maybe not. One of the HTML "solutions" I've seen on StackOverflow was to put a sub-total on EVERY line of the invoice, "hidden" via CSS, and then make the relevant sub-total Visible at the point of a page break. Seems bizarre to me as a solution, but its WELL within our skill set, and we've done more horrific work-arounds than that before!

[yosiasz]

well, I would not recommend installing SSRS on the server that has the db for the app. we have a dedicated BI server that has out DW so we installed SSRS on that .

anyways, SSRS is not that complicated really. worth implementing long term. then you can even implement (like we do) automation to send pdf to users or even let users subscribe to reports etc.

[Kristen]

@yosiasz

let users subscribe to reports

We like the way that our Reports engine works via HTML/browser, and I have disliked the way that SSRS does things (in a development sense, the output can look fantastic), but that might be worth debating to see if the reasons we choose are still valid. If you have a reasonable knowledge (or more!!) of SSRS I could start a fresh topic with a brief description of what we think the benefit of our approach is and you could give the SSRS counter argument?

[yosiasz]

bring it on!

[Kristen]

@yosiasz

bring it on!

OK, here's The Thread

[Kristen]

OK, I started messing around with SSRS and viewing some tutorials online ...

Let's say I have an invoice with a Header (Client's Name, Address etc) and then the Invoice Lines - likely to be 1-to-100 lines per Invoice.

My current method would be to create a stored procedure that output two resultsets, one for the Headers and the second for the Items, both ordered by Invoice Header ID and then they would be output "synchronised" by the reporting engine.

I don't really want (as the Tutorial showed) a single query with the Header and Item Data on every result-row - because that would repeat the Header Data on every row of the Items on an Invoice.

So perhaps I have the possibility to have a Resulset of Invoice Headers and then some sort of sub-query to get the Invoice Items for each Invoice ...

... or output the Invoice Items, sorted by Invoice Header ID, and then some sort of Grouping so that when the Invoice Header ID (on the Invoice Item) changes a separate query gets the Invoice Header info

Or something else altogether??

I could do with some guidance so that I start off in the right direction please

[yosiasz]

woow! can you please post a doodle of what your report would look like?
grouping would take care of that for you. SSRS grouping is very powerful and you have total control over it.
pretty much you have total control over the whole report in so many different ways.

You could even have subreports embedded in a report!

[Kristen]

These are an old version, back then some columns just "truncated" and I need to actually output the full text - so assume that the Invoice will be more spread out (vertically) and likely to be multi-page and will need sub-totals for the page.

Each invoice (starting on a new page) also has a summary report (also starting on a new page), That's pretty straightforward - some Grouping, but easy enough to adjust the sequencing of output for that using SQL, and doesn't matter where page breaks fall on that. Need Invoice and Summary Report interwoven/collated, and one PDF file per Client (a client can have multiple invoices, or just one).

I suppose at a pinch we could do the report one client at a time, to get individual PDF files, or better still run it "unattended" to create PDF files on Disk ready to be reviewed and sent out (as Email attachments). If it was unattended it wouldn't matter if it could only do one client at a time, I could obviously control the individual one-by-one, for an unattended batch job, by some sort of Queue in SQL

Needs letter headed "logo" header for the first page of each Invoice.

[Kristen]

OK, this is how far I have got.

Created a simplified Invoice Header report (and in the WHERE clause hard-wired the InvocieID for now ) :

Then created an Invoice Items report. Hardwired the InvoiceHeaderID for that too ...

Both reports worked OK on their own.

Then added a Sub-Report to the Invoice Header:

It then wouldn't run anymore, got this error:

So I re-edited each column to use a FIRST() function.

Not sure why that is necessary, but I haven't tried running this on more than one InvoiceHeader record, and also have not looked into how I "Tie" the InvoiceItems subreport to the parent InvoiceHeader record (they share an FKey ID of course ...)

So then the fields looked like this:

and when I ran it, apart from my very mediocre formatting!!, I got something resembling the data I want (I put some Grouping in, not needed for the Invoice itself, so more an example of the accompanying Analysis Report)

Am I on the right track, or are there better ways of starting-out to do this?

[yosiasz]

Very nice, you are on the right track! If you want to you could have only one data source to feed one report also.

[Kristen]

@yosiasz

you could have only one data source to feed one report also

As in:

SELECT H.Company, H.Address1, H.Address2, ..., H.PostCode, H.Country
       , I.ItemNo, I.Staff, I.CaseRef, I.Narrative, I.Amount
FROM InvoiceHeader AS H
    JOIN InvoiceItems AS I
        ON I.InvoiceID = H.InvoiceID

??

If so definitely don't want to do that, the amount of repeated data on each row would be a huge penalty.
But maybe you were meaning something else?

In my "stuff" I can do:

SELECT InvoiceID, Company, Address1, Address2, ..., PostCode, Country
FROM InvoiceHeader
WHERE ... some criteria ...
ORDER BY InvoiceID

SELECT ItemNo, Staff, CaseRef, Narrative, Amount
FROM InvoiceItem
WHERE ... some criteria ...
ORDER BY InvoiceID
         , InvoiceItem

and I can "sync" my two CMS Template - in this case on [InvoiceID] - works well, provided they are both sorted in the same order!

Probably other ways of achieving the same thing.

So, my current need-to-know is:

What's the best SSRS mechanism for doing:

Order Header
Order Items (sub report or somesuch)
Tax SubTotal and Grand Total of Invoice

then another sub-report (or somesuch) for the breakdown analysis of the invoice items

Keeping in mind that I would like to output each invoice, and its analysis sub-report, to a separate PDF file

[yosiasz]

@Kristen

What's the best SSRS mechanism for doing:

Order Header

Add a table/matrix using wizard (uncheck collapse) which should have its own dataset. each dataset should point to a sproc that has @companyid as parm.

Order Items (sub report or somesuch)

Add a table/matrix using wizard (uncheck collapse) which should have its own dataset. each dataset should point to a sproc that has @companyid as parm.

Tax SubTotal and Grand Total of Invoice

These usually come right out of the box when you are using the wizards, or you can right click a total and select Add Total

then another sub-report (or somesuch) for the breakdown analysis of the invoice items
Same here also. Add a table/matrix using wizard (uncheck collapse) which should have its own dataset. each dataset should point to a sproc that has @companyid as parm.

[yosiasz]

notice the company drop down. of course this you can hide when sending to customer when you automate things

[Kristen]

I've moved a field from Group heading to Detail row, it took with it its formatting (fair enough), but I can't find any way to easily reset it. (Actually took a while to figure out what was happening because its heading Formatting was White-font-on-Blue-background, and where I pasted it too it got a White-background, so it became while-on-white - couldn't understand, for a while! when if I clicked it in designer the field name was there, but the moment I clicked-off it disappeared!)

In Word I can click on a word, with correct formatting, and use the Format Painter Tool to "apply" that formatting somewhere else.

I cannot see how to take the formatting from a "good" column, on the detail row, and apply that elsewhere - e.g. to ALL the cells in that row. Only way I have found is to edit the properties of the field (or all/several multi-fields on the row) ... but there are LOTS of properties, plus I can't see the properties of an adjacent (correctly formatted) field at the same time (to compare / copy&paste), can I?

Seems like a lot of effort ... perhaps there is a "Style" I could apply to all fields in the Detail Row, and use that on All Reports, so if it needed to change I could do that consistently across the board?

[Kristen]

Here's an example of why I haven't liked SSRS in the past (unless you tell me this is a Newbie error and there is a better way)

So I have my Invoice Header with first sub-report (Invoice Items) and second sub-report (Invoice Analysis).

I want to add a page break between first and second sub-reports so (naively) I changed the Properties on Tablix of second sub-report and added "Page break before".

Did Preview on the master report and ... I got the page break I wanted but it was AFTER the Page Heading on the second sub-report. Fair enough (although I couldn't find a way to create a page break BETWEEN the two sub-reports, but that's not the issue here).

So I removed that page break and instead on added a PAGE BREAK AFTER to the First sub-report.

Pressed refresh in the Preview (I already had open) of the Master Report. Nope, no change. Had to cancel that, go back to design and re-run the Preview - and then I got the change.

All our stuff correctly handles the cache in that instance. Any change [e.g. to meta data / properties / etc] causes all cached objects to be deleted, and whenever anything "uses" a cached object it is rebuilt just-in-time if it does not already exist.

[jeffw8713]

@Kristen

All our stuff correctly handles the cache in that instance. Any change [e.g. to meta data / properties / etc] causes all cached objects to be deleted, and whenever anything "uses" a cached object it is rebuilt just-in-time if it does not already exist

What you are seeing here is just the development tool (SSDT) and not something you would see once the report has been published. I believe the reason for this is so that you can quickly see minor changes to the formatting without having to rerun the queries to get the data...but some changes don't appear unless you actually refresh.

Yes - it can be annoying at times but much better than having to rerun everything because you expanded one field and changed from left-aligned to center-aligned.

[Kristen]

Thanks, but I would have thought it was worthwhile for MS to include that in the SSDT - hours spend by DEVs around the world having to fiddle with This & That when the Preview could just refresh with latest Sub-Report layout.

I don't know if the following things are possible, but I think they should be!

Default Labels for Columns. I don't want to have to type "Order No" for my [OrderNo] or [Order_No] column every time I use it on a report; plus I'd like the Labels for a column, across all reports, to be consistent. Similarly, if I decide I want to change that to "Order #" I want that to change everywhere (except where I have expressly overridden the default).

Same with Width. I've set up a width for my CaseNo on the report. I'd actually like it to be auto width (maybe that is possible? I only found Auto Height) and then I ran the report on some recently imported data, which has slightly different Case Refs from the new company, and they don't fit So I've had to change all the places I have used that column (today that's not a problem, I only have a couple of reports once I have a bigger portfolio I will want to be able to manage consistency. Auto changing them might well be a formatting No-No -
things will get bumped off the side of the page (although that all works find in HTML / Browsers of course) so I would like to be able to either use "Default Column's Width" [and optionally override that for "fixed width" where it is important) or "Tell me all the reports where this column is used so I can check/fix them"

All this might just be my lack of knowledge as a Newbie.