A
Alberto Cricca
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
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