Dynamic export
-
Hi,
I have some data which I break down by categories and export into an Excel file. Each category goes into separate worksheet. Users may create some more categories from time to time. I need to create some routines which will take into account those new categories. Currently I just manually add another "Transform data task" for each new category in my DTS package. Is there a more dynamic and way of doing this?
Thanks.
-
There isn't an easy way I can think of. You could use an Active X task in a parent package to modify the current package and add tasks, but I think adding a transform is the simplest way to do this.
-
Try the other way. Create an excel Macro that connects to your SQL DB, loop thru all the categories and while in each category build a SELECT statement on the fly that plugs the data into a new Sheet.
That's just a hint, the actual code will be priceless!!!!. If you need a hint on the VBA code reply.
-
I will think about this way, but I am afraid this will be very slow as the record set is very large. Worth trying anyway.
I was also considering building a script for the DTS package dynamically in VB file format, but unfortunately putting it back into SQL server is a manual process. You can't just run the script.
-
This could be a solution, but I am not good in Active X area. Do you know about any examples of similar code on the web?
-
I guess I'll never understand why folks try to push data into Excel when the "Get External Data" feature in Excel works so very well...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ditto
Although I use a Auto_Open macro to execute proc and insert data by ordinal columns into one or more sheets formatted according to sql datatype
Far away is close at hand in the images of elsewhere.
Anon. -
Concur... and, if you do it right, don't even need that... the "External Data Ranges" can be setup to "auto-magically" refresh on open. In Excel 2k3 and above, you can even make the external data ranges refresh on a timed basis AND have it "auto-magically" copy adjacent formulas down as the range grows or shrinks. PFM!!!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The dataset is too large to allow everyone query the database to populate their own file. So there has to be just one file to be copied from by all the people. I could possibly populate it and then put into a shared folder, but this is a manual process.
-
The same as above, I can't allow every user to do this and the report should run at a particular time overnight, as it is heavy. I prefer to sleep at this time.
-
Then you can write a stored procedure that figures all that out (provided CATEGORY is used in a normalized fashion) and DTS the results of the stored procedure or put them in a tab delimited file, or whatever. But you've basically told us that your car needs some repairs and haven't even shown us the car yet.
Some example data in the form of INSERT/SELECTs, some table CREATEs to match, a little more info about exactly what you want to do and you'll be surprised
Folks just might be able to help a little more...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I can't allow every user to do this and the report should run at a particular time overnight, as it is heavy Create a workbook with no sheets, insert a module and in Auto_Open sub
create a new workbook
retrieve the data
insert it in worksheet in the created workbook
save and close the created workbook
close original workbook
close excel
Schedule Excel to run at designated time and use the workbook as parameter
Far away is close at hand in the images of elsewhere.
Anon. -
Hi,
This is my query:
Select ContractId ,
Balance,
Charge_Receivables ,
Residual ,
Interest ,
Asset ,
Income,
Charges
From ContractAccount
Where Accountid in (select disticnt Accountid from ContractAccount (nolock))
Accountid defines categories. I want all data, related to the same category to be exported into separate worksheet. I would be good to be able to create another Excel file if the number of categories exceeds the max number of worksheets (255) within the same Excel file.
Thanks.
-
Just one more question... why spreadsheets... why not print reports?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I was asking a similar question: why Excel... why not Analysis Services? It looks like this is the form users are used to use and they want nothing else...
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply