CODEWITHSUNDEEP
sqlsql-serverexcel
Rick
Rick

Reputation: 407

How to automate data sending to excel

I've been doing some research on how to have sql server create invoices or reports automatically given a specific time, similar to a scheduled job. I want to be able to use an excel sheet which has the invoice template and update my sql server update the fields with the given data (Customer Name, account Id, sales tax, etc). I came across SSRS which does reports, but I dont know if that will do data processing for me. The type of processing I need is simple, gallons used * price/gallon * sales tax. I have very little experience with sql server, from what I know, its not very convenient to these algorithms in sql so I rather have excel do it for me. So again, is there a way to have sql run an automated query that updates an excel spread sheet? I have imported data to excel via csv file and vice versa, but the thing that is confusing me in this case is that my excel spreadsheet itself is an invoice template with a company logo so I can't just import data to the template. Does anyone have any suggestions? Thanks.

Upvotes: 1

Views: 4413

Answers (3)

ssis_ssiSucks
ssis_ssiSucks

Reputation: 1506

The easiest method would be to re-create the template within an SSRS report (embedding the company logo in the report, for example), create a subscription for the report, and specify Excel as the output format for the subscription.

The calculations you describe are very simple. If your resultsets are small, embedding the calculations in the report may prove less challenging. However, if you're working with large datasets, it's best to include the calculations in the query executing on the database server. If you can figure out how to write the query for your report to begin with, figuring out simple calculations won't be difficult.

Upvotes: 1

Allan Chua
Allan Chua

Reputation: 10175

you could use this application tier:

DATABASE MANAGEMENT SYSTEM
           |
           V
     PROVIDE DATA TO
           |
           V
    YOUR APPLICATION
           |
           V
       WOULD USE
           |
           V
[OPEN XML SDK 2.0 For excel] -  http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/06/02/4730.aspx
           |
           V
  TO GENERATE EXCEL FILES 
that contains data that was provided 
     by your DBMS to
    your application

[1]:

Upvotes: 1

praveen
praveen

Reputation: 12271

I think you should go with SSRS reports . Report server in SSRS can handle the report processing and Scheduling and delivering of files .U can get the reports in most of the formats(Excel ,Word ,pdf etc ) .You can go through the below link to understand how to schedule and deliver your reports http://msdn.microsoft.com/en-us/library/ms156297(v=sql.105).aspx

Regarding the logic such as gallons=*price/gallon*sales . These logics can easily be applied to the columns in ssrs reports using expressions such as

For the column gallons used, the expression will be 

=(Fields!price.Value/Fields!gallons.Value)*Fields!Sales.Value

Upvotes: 1

Related Questions