Generate Invoice Document from SQL
-
Really need some help on this one - i need to generate documents for customers to sign automatically as sales staff enter their data into SQL. These are invoice style documents. I currently have word templates of the invoice documentation, i just need to be able to add the clients names, address etc into the relevant spaces for them to print off and sign.
I have looked at what is possible with PDF's and Word but I am coming up short in finding a solution that is quick and easy.
I am good with TSQL and writing Stored Procs etc and can easily get the data ready - i just need to find a way to populate the templates in the right places and then save a copy for emailing
has anyone done something similar - and can suggest a good way to do this.
-
Reporting Services? That can generate PDF / Word etc
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them. -
thanks for the reply- can SQL reporting services take an existing template like i have or does it generate the whole document on the fly?
-
andyc209 (9/25/2015)
thanks for the reply- can SQL reporting services take an existing template like i have or does it generate the whole document on the fly?You can't load a word template into reporting services, but while a little fiddly at times (depending on what you want to do) it's pretty easy to generate reports, and also to embed reporting services into your apps
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them. -
andyc209 (9/25/2015)
Really need some help on this one - i need to generate documents for customers to sign automatically as sales staff enter their data into SQL. These are invoice style documents. I currently have word templates of the invoice documentation, i just need to be able to add the clients names, address etc into the relevant spaces for them to print off and sign.I have looked at what is possible with PDF's and Word but I am coming up short in finding a solution that is quick and easy.
I am good with TSQL and writing Stored Procs etc and can easily get the data ready - i just need to find a way to populate the templates in the right places and then save a copy for emailing
has anyone done something similar - and can suggest a good way to do this.
How good is your VBA coding? You could put a macro in a Word document that can use ADO to get a recordset back from SQL Server and then populate the document, but you'd then need to know how to write code that can manipulate a Word document using it's object model, and be able to figure out exactly where that information that you retrieved, needs to go. Have you considered that option?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Not thought about that option but may try and find some examples on the web. cheers
-
You might be able to use mail merge in Word to generate the documents, you can merge a csv extract or a table or view for that matter into a template in word.
-
need it to be automatic though - dont think i can trigger it automatically
-
not sure if this would work in your situation, but what about putting placeholders in an existing doc as a template, then saving it as rtf?
store it as a varchar(max), and then you could REPLACE specific placeholders with data driven stuff? write the file to disk, save the extension as .doc and that might work.
it depends on what you are doing to the invoice.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
my quick proof of concept looks like it would work:
i just grabbed an Ms template, saved it, and started combing through the raw text.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
If you can find the code from one of the sadly dead and gone Access <version> Developer's Handbook: DESKTOP, you might find the code for it. If I recall correctly, you grab all the data for the "header"/top of the invoice (customer info), and then stuff the values into bookmarks in your word template. Then for the line items, you use ADO to grab the query results and then convert it to a table, and then format that. You can probably find the book on Amazon really cheap, but getting the CD might be difficult.
-
I've done this before where I took an existing mail merge process that needed to be data driven and automated. You can use a Script Transformation and interact with the Office DLL to create the word docs by code. You may be able to get it to work with a template, but I remember having to build out the doc all through code including text and building out data tables on the word doc. It was a bit frustrating to develop and interact with the Office DLL, but once completed, it worked like a champ and was easy to maintain because it was all contained in one SSIS package.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply