Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.sqlserver.dts,microsoft.public.sqlserver.programming,microsoft.public.excel.programming
|
|||
|
|||
DTS: SQLServer to Excel in unattended server environment
Hi all,
migration of SQLServer data to Excel in unattended environment has been discussed many times on this and some other related newsgroups. Nevertheless, I didn't find a suitable answer to my needs. I've been asked to create summary reports starting from item/waybill data. Item table contain the following attributes: IT_ITEM_CODE IT_ITEM_DESC IT_BRAND_CODE Waybill table contains the following attributes: WB_CUST_CODE WB_ITEM_CODE WB_DATE_ISSUED WB_QTY WB_TOTAL_AMOUNT Stats are produced on demand. Users must select: - customer code - years interval (ex.: <1999, 2004) Users may select: - item/brand code - months interval (ex.: <Apr, Giu or <Jan, Aug) Quantity and amount data are aggregated on an <item code, delivery year basis. Therefore export queries will look like: SELECT IT_ITEM_CODE, IT_ITEM_DESC, YEAR (WB_DATE_ISSUED), SUM (WB_QTY), SUM(WB_TOTAL_AMOUNT) FROM ITEMS INNER JOIN WAYBILLS ON (IT_ITEM_CODE = WB_ITEM_CODE) WHERE WB_CUST_CODE = ? AND YEAR (WB_DATE_ISSUED) BETWEEN ? AND ? GROUP WB_ITEM_CODE, IT_ITEM_DESC, YEAR (WB_DATE_ISSUED) or (apply brand code filter) SELECT IT_ITEM_CODE, IT_ITEM_DESC, YEAR (WB_DATE_ISSUED), SUM (WB_QTY), SUM(WB_TOTAL_AMOUNT) FROM ITEMS INNER JOIN WAYBILLS ON (IT_ITEM_CODE = WB_ITEM_CODE) WHERE WB_CUST_CODE = ? AND YEAR (WB_DATE_ISSUED) BETWEEN ? AND ? AND IT_BRAND_CODE = ? GROUP WB_ITEM_CODE, IT_ITEM_DESC, YEAR (WB_DATE_ISSUED) (etc.) where ? stands for user selections. Typical report (single customer) will look like: CODE ITEM_DESC 2004 2003 1994 ----- ----------------- ---------- ---------- ---------- 00001 DESC000000001 1000 1100 650 $10,000.00 $10,500.00 $ 530.00 -4.76% 10.00% - 00002 DESC000000002 1000 1100 650 $10,000.00 $10,500.00 $ 530.00 -4.76% 10.00% - where column data are (for each item): - quantity (for year X) - total amount (for year X) - percentual difference between total amount (year X and year X - 1) Report file names contain an explicit reference to the creation date/time (so they always change) I'm trying to avoid using both ADODB and Office automation This is the solution I'm working on: 1) create a data pump action to dynamically export SQL query result to a text file 2) process output text file and create final output (text file) 3) pump text data to SQL server (temporary table) 4) create Excel worksheet (ADOX) 5) pump data from SQL server to Excel That's the only solution I found out, but it's definitely a little bit too complex. As I'm new to DTS/Excel programming, I'd appreciate if someone would address to me to a suitable (and easier) solution. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SQL Server -- Bulk Insert from Excel to SQL Server | Excel Discussion (Misc queries) | |||
Excel - MS sqlserver 2000 connection | Excel Discussion (Misc queries) | |||
Excel 2003 Error Cannot Locate the Internet Server or Proxy Server | Excel Discussion (Misc queries) | |||
Excel Front End , SqlServer Backend in the web | Excel Programming | |||
Unattended Macro | Excel Programming |