Nested Loop in SQL Query

Hi,
I'm probably missing something obvious (either that or doing this totally wrong).
I'm trying to use a nested loop to generate the following results:
Unit Day1 Day2 Day3 Day4 Day5
Name1 25 45 89 54 76
Name2 48 54 81 74 98
What I have so far is this:
WHILE @.FCount < @.TotalFoodUnits
BEGIN
SELECT
(SELECT Unit FROM tbl_acc_FoodVenues WHERE UnitID = (@.FCount + 1)) AS Unit

WHILE @.FDCount < @.Days
BEGIN
SELECT
(SELECT FdRevenue_a FROM tbl_acc_aud_SportsAudits WHERE AudDate = DATEADD(day, @.FDCount, @.pdStartDate)) AS Rev
SET @.FDCount = @.FDCount + 1
END
SET @.FCount = @.FCount + 1
END
Any suggestions pleaseThis won't do what you want. It looks like you need to do a pivot-table type query that aggregates your data. With loops, you're just going to generate a whole bunch of separate result sets. Doing a pivot type table is difficult if you don't know how many columns you'll have. One thing I've done is create a dynamic SQL statement with cursors that pivots them all together. When the statement is built, it looks like it was basically hardcoded, but it is correct for the data as it is when it is run. The cursor(s) you need depend on what the varying numbers of rows or columns are that you have. Build your SQL statement by looping through the cursors as needed. See if you can hardcode a statement that will work and then look to see what the repeating elements are and how would you get those repeating elements with a SQL statement. You may need to query schema tables to get field names or use DISTINCT to get what values you have. I know thi sisn't very clear, but if you hardcode it and then see how that statement repeats, you might see what I'm talking about.
|||Along the lines of what PDraigh has advised, you might find this post helpful:http://forums.asp.net/1041295/showpost.aspx
|||Hi,
Thanks for the info above, it actually led me to this page:
http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
This provides an alternative solution which pivots the data using .net rather than trying to force sql into doing it for you.

Need VB.NET code to generate snapshot reports automatically

I need to generate hundreds of snapshot reports, which would be
refreshed every night. Each employee would view a snapshot report
pertaining to his employee number (which is the parameter in the
report). The employee is not allowed to look at anyone else's report,
and the company doesn't want employees to be refreshing reports all
day long.
So, here's what I need:
1. VB.NET code that calls the Reporting Services web service to
generate a linked snapshot report for each employee report and every
employee number (for the employee parameter) in my SQL database
2. Code to automatically schedule these snapshots for a nightly run
using a shared scheduled execution time
3. A way to name each linked snapshot report using some kind of naming
convention (e.g. "Employee Report - Employee 100", "Employee Report -
Employee 205", etc.)
Can anyone help? Does anyone have any sample VB.NET code to share?Just another way to do this. Depending on the size of the reports would
determine if this would work for you. Create a filter that uses the global
user!userid. Then instead of having to have a report snapshot for every
employee, the report would be shared but the employee would only see their
data, nobody elses. Then you would not even have to have the app you are
looking for. Then you could just handle the report normally, i.e. schedule
it to run nightly.
Bruce L-C
"Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
news:437b6286.0409231824.5aee8e85@.posting.google.com...
> I need to generate hundreds of snapshot reports, which would be
> refreshed every night. Each employee would view a snapshot report
> pertaining to his employee number (which is the parameter in the
> report). The employee is not allowed to look at anyone else's report,
> and the company doesn't want employees to be refreshing reports all
> day long.
> So, here's what I need:
> 1. VB.NET code that calls the Reporting Services web service to
> generate a linked snapshot report for each employee report and every
> employee number (for the employee parameter) in my SQL database
> 2. Code to automatically schedule these snapshots for a nightly run
> using a shared scheduled execution time
> 3. A way to name each linked snapshot report using some kind of naming
> convention (e.g. "Employee Report - Employee 100", "Employee Report -
> Employee 205", etc.)
> Can anyone help? Does anyone have any sample VB.NET code to share?|||Bruce, I wish that I could use the global user!userid value, but I
need to produce snapshot reports for a whole slew of parameter
combinations. For instance, we have some reports that use a Goal ID
and Organization ID parameter that might produce a combination such as
"Goal X Results for Region 1" or "Goal Y Results for Department 200".
Our Department Manager for Department 200 won't be allowed to see the
regional reports, but he will be allowed to see the dozens of Goal
reports for his department. Even though his userid is useful in
regards to sorting out what he can see, it doesn't solve the dilemma
with having to produce snapshots for all the goal report combinations.
You may be wondering why on earth we need thousands of snapshot
reports. Basically, users are not allowed to refresh reports during
the day because of processing concerns from upper management. So, a
snapshot report for each parameter combination must be produced at
night.
I just need the VB.NET code to automatically create and eliminate
snapshot reports based on new employees coming on board, employees
transferring to new departments, and employees leaving the company.
Any help would be appreciated.
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<ulzfQwjoEHA.1800@.TK2MSFTNGP15.phx.gbl>...
> Just another way to do this. Depending on the size of the reports would
> determine if this would work for you. Create a filter that uses the global
> user!userid. Then instead of having to have a report snapshot for every
> employee, the report would be shared but the employee would only see their
> data, nobody elses. Then you would not even have to have the app you are
> looking for. Then you could just handle the report normally, i.e. schedule
> it to run nightly.
> Bruce L-C
> "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> news:437b6286.0409231824.5aee8e85@.posting.google.com...
> > I need to generate hundreds of snapshot reports, which would be
> > refreshed every night. Each employee would view a snapshot report
> > pertaining to his employee number (which is the parameter in the
> > report). The employee is not allowed to look at anyone else's report,
> > and the company doesn't want employees to be refreshing reports all
> > day long.
> >
> > So, here's what I need:
> >
> > 1. VB.NET code that calls the Reporting Services web service to
> > generate a linked snapshot report for each employee report and every
> > employee number (for the employee parameter) in my SQL database
> > 2. Code to automatically schedule these snapshots for a nightly run
> > using a shared scheduled execution time
> > 3. A way to name each linked snapshot report using some kind of naming
> > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > Employee 205", etc.)
> >
> > Can anyone help? Does anyone have any sample VB.NET code to share?

Need VB.NET code to generate snapshot reports automatically

I need to generate hundreds of snapshot reports, which would be
refreshed every night. Each employee would view a snapshot report
pertaining to his employee number (which is the parameter in the
report). The employee is not allowed to look at anyone else's report,
and the company doesn't want employees to be refreshing reports all
day long.
So, here's what I need:
1. VB.NET code that calls the Reporting Services web service to
generate a linked snapshot report for each employee report and every
employee number (for the employee parameter) in my SQL database
2. Code to automatically schedule these snapshots for a nightly run
using a shared scheduled execution time
3. A way to name each linked snapshot report using some kind of naming
convention (e.g. "Employee Report - Employee 100", "Employee Report -
Employee 205", etc.)
Can anyone help? Does anyone have any sample VB.NET code to share?question. How will you set up security for filter out employee's to read
only the
report snaped from their ID?
dlr
"Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> I need to generate hundreds of snapshot reports, which would be
> refreshed every night. Each employee would view a snapshot report
> pertaining to his employee number (which is the parameter in the
> report). The employee is not allowed to look at anyone else's report,
> and the company doesn't want employees to be refreshing reports all
> day long.
> So, here's what I need:
> 1. VB.NET code that calls the Reporting Services web service to
> generate a linked snapshot report for each employee report and every
> employee number (for the employee parameter) in my SQL database
> 2. Code to automatically schedule these snapshots for a nightly run
> using a shared scheduled execution time
> 3. A way to name each linked snapshot report using some kind of naming
> convention (e.g. "Employee Report - Employee 100", "Employee Report -
> Employee 205", etc.)
> Can anyone help? Does anyone have any sample VB.NET code to share?|||Dennis, when the user logs in to the web application, a stored
procedure fires to retrieve the ID for the employee, where the
employee works, where the employee is in the management food chain,
and what reports the user is authorized to see.
So, when the user enters the reports page in the web application, the
user would see all the reports he/she is permitted to see that the
stored procedure brought back from that report table I mentioned.
Because the web application has the employee and workplace ID in
memory, it would call the respective snapshot by taking the report
name and concatenating the employee ID and workplace ID, which then
references the snapshot report name. Here's an example...
Let's assume that the user's employee ID is 205 and workplace ID is
5000. If the user clicks on a report called "Sales by Employee", the
web application would then construct the snapshot report name (e.g.
"Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
snapshots available in the Reporting Services database (i.e. one
snapshot combination for every employee ID and work place ID) and
display the correct snapshot.
Unfortunately, we don't know how to do the VB.NET code to
automatically build all the snapshots from our database table of
employee and workplace IDs. We need a means for automatically
generating and eliminating snapshots as employees come on board,
switch departments, or leave the organization.
"Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> question. How will you set up security for filter out employee's to read
> only the
> report snaped from their ID?
> dlr
> "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > I need to generate hundreds of snapshot reports, which would be
> > refreshed every night. Each employee would view a snapshot report
> > pertaining to his employee number (which is the parameter in the
> > report). The employee is not allowed to look at anyone else's report,
> > and the company doesn't want employees to be refreshing reports all
> > day long.
> >
> > So, here's what I need:
> >
> > 1. VB.NET code that calls the Reporting Services web service to
> > generate a linked snapshot report for each employee report and every
> > employee number (for the employee parameter) in my SQL database
> > 2. Code to automatically schedule these snapshots for a nightly run
> > using a shared scheduled execution time
> > 3. A way to name each linked snapshot report using some kind of naming
> > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > Employee 205", etc.)
> >
> > Can anyone help? Does anyone have any sample VB.NET code to share?|||ok Steve. I am a little more pluged in to your design.
The Web Service "UpdateReportExecutionSnapshot" method is not going to allow
you to name your output snapshots anything different from the base name of
the report (see BOL on this function and the section of snapshots with
parameterized reports).
I think, based on what you are telling me is that you will want to
(0) identify the user and her report parameters
(1) use the Web Service "Render" method (which returns a stream of bytes) to
create the report stream
(2) write the bytes to a file share (and name it using your paramater
values) and then
(3) redirect the user to that file.
[you will want to skip (1) and (2) if a valid file on share exists when the
user jumps in]
does this sound correct?
dlr
"Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
news:437b6286.0409241745.6fa9b007@.posting.google.com...
> Dennis, when the user logs in to the web application, a stored
> procedure fires to retrieve the ID for the employee, where the
> employee works, where the employee is in the management food chain,
> and what reports the user is authorized to see.
> So, when the user enters the reports page in the web application, the
> user would see all the reports he/she is permitted to see that the
> stored procedure brought back from that report table I mentioned.
> Because the web application has the employee and workplace ID in
> memory, it would call the respective snapshot by taking the report
> name and concatenating the employee ID and workplace ID, which then
> references the snapshot report name. Here's an example...
> Let's assume that the user's employee ID is 205 and workplace ID is
> 5000. If the user clicks on a report called "Sales by Employee", the
> web application would then construct the snapshot report name (e.g.
> "Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
> snapshots available in the Reporting Services database (i.e. one
> snapshot combination for every employee ID and work place ID) and
> display the correct snapshot.
> Unfortunately, we don't know how to do the VB.NET code to
> automatically build all the snapshots from our database table of
> employee and workplace IDs. We need a means for automatically
> generating and eliminating snapshots as employees come on board,
> switch departments, or leave the organization.
> "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message
news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> > question. How will you set up security for filter out employee's to
read
> > only the
> > report snaped from their ID?
> >
> > dlr
> > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > > I need to generate hundreds of snapshot reports, which would be
> > > refreshed every night. Each employee would view a snapshot report
> > > pertaining to his employee number (which is the parameter in the
> > > report). The employee is not allowed to look at anyone else's report,
> > > and the company doesn't want employees to be refreshing reports all
> > > day long.
> > >
> > > So, here's what I need:
> > >
> > > 1. VB.NET code that calls the Reporting Services web service to
> > > generate a linked snapshot report for each employee report and every
> > > employee number (for the employee parameter) in my SQL database
> > > 2. Code to automatically schedule these snapshots for a nightly run
> > > using a shared scheduled execution time
> > > 3. A way to name each linked snapshot report using some kind of naming
> > > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > > Employee 205", etc.)
> > >
> > > Can anyone help? Does anyone have any sample VB.NET code to share?|||Dennis, I'll need to research more on the Web Service method you
referred to. It seems like the web service has everything I would
need to do generate snapshot reports, but I'd like to see some sample
VB.NET code to help me along.
As for your numbered items below, I would have to say that we already
have the logic to identify the user and get the right snapshot (e.g.
"Sales by Employee - 36", where "Sales by Employee" is the base report
name, "36" is the parameter value for the employee number, and "Sales
by Employee - 36" is the saved snapshot name).
I've successfully created some snapshots manually and retrieved the
right snapshot based on the employee ID of the user logged in...so,
rendering the snapshot report is no problem.
The problem is generating all the snapshots I need via an automated
process. I'm sure with the web service, there are available methods
to do this. I've already created a console application that
automatically hides parameters for all 50 of my reports.
So, the VB.NET code will need the following:
1. Retrieve a collection of reports
2. Set a default parameter for the Employee ID to each report
3. Create a linked report for each base report and respective Employee
ID value and concatenate the parameter value to the report name (e.g.
"Sales by Employee - 36")
4. Create a snapshot from the linked report
5. Set the snapshot to use the shared schedule for my nightly refresh
6. Remove existing snapshots for those employees who have left the
company
7. Remove the default value for the Employee ID from the base reports
so they can be refreshed separately from the snapshot reports
"Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message news:<OcjTCRLpEHA.3552@.TK2MSFTNGP15.phx.gbl>...
> ok Steve. I am a little more pluged in to your design.
> The Web Service "UpdateReportExecutionSnapshot" method is not going to allow
> you to name your output snapshots anything different from the base name of
> the report (see BOL on this function and the section of snapshots with
> parameterized reports).
> I think, based on what you are telling me is that you will want to
> (0) identify the user and her report parameters
> (1) use the Web Service "Render" method (which returns a stream of bytes) to
> create the report stream
> (2) write the bytes to a file share (and name it using your paramater
> values) and then
> (3) redirect the user to that file.
> [you will want to skip (1) and (2) if a valid file on share exists when the
> user jumps in]
> does this sound correct?
>
> dlr
> "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> news:437b6286.0409241745.6fa9b007@.posting.google.com...
> > Dennis, when the user logs in to the web application, a stored
> > procedure fires to retrieve the ID for the employee, where the
> > employee works, where the employee is in the management food chain,
> > and what reports the user is authorized to see.
> >
> > So, when the user enters the reports page in the web application, the
> > user would see all the reports he/she is permitted to see that the
> > stored procedure brought back from that report table I mentioned.
> > Because the web application has the employee and workplace ID in
> > memory, it would call the respective snapshot by taking the report
> > name and concatenating the employee ID and workplace ID, which then
> > references the snapshot report name. Here's an example...
> >
> > Let's assume that the user's employee ID is 205 and workplace ID is
> > 5000. If the user clicks on a report called "Sales by Employee", the
> > web application would then construct the snapshot report name (e.g.
> > "Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
> > snapshots available in the Reporting Services database (i.e. one
> > snapshot combination for every employee ID and work place ID) and
> > display the correct snapshot.
> >
> > Unfortunately, we don't know how to do the VB.NET code to
> > automatically build all the snapshots from our database table of
> > employee and workplace IDs. We need a means for automatically
> > generating and eliminating snapshots as employees come on board,
> > switch departments, or leave the organization.
> >
> > "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message
> news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> > > question. How will you set up security for filter out employee's to
> read
> > > only the
> > > report snaped from their ID?
> > >
> > > dlr
> > > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > > news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > > > I need to generate hundreds of snapshot reports, which would be
> > > > refreshed every night. Each employee would view a snapshot report
> > > > pertaining to his employee number (which is the parameter in the
> > > > report). The employee is not allowed to look at anyone else's report,
> > > > and the company doesn't want employees to be refreshing reports all
> > > > day long.
> > > >
> > > > So, here's what I need:
> > > >
> > > > 1. VB.NET code that calls the Reporting Services web service to
> > > > generate a linked snapshot report for each employee report and every
> > > > employee number (for the employee parameter) in my SQL database
> > > > 2. Code to automatically schedule these snapshots for a nightly run
> > > > using a shared scheduled execution time
> > > > 3. A way to name each linked snapshot report using some kind of naming
> > > > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > > > Employee 205", etc.)
> > > >
> > > > Can anyone help? Does anyone have any sample VB.NET code to share?|||Are you using integrated security?
"Steve Pantazis" wrote:
> Dennis, I'll need to research more on the Web Service method you
> referred to. It seems like the web service has everything I would
> need to do generate snapshot reports, but I'd like to see some sample
> VB.NET code to help me along.
> As for your numbered items below, I would have to say that we already
> have the logic to identify the user and get the right snapshot (e.g.
> "Sales by Employee - 36", where "Sales by Employee" is the base report
> name, "36" is the parameter value for the employee number, and "Sales
> by Employee - 36" is the saved snapshot name).
> I've successfully created some snapshots manually and retrieved the
> right snapshot based on the employee ID of the user logged in...so,
> rendering the snapshot report is no problem.
> The problem is generating all the snapshots I need via an automated
> process. I'm sure with the web service, there are available methods
> to do this. I've already created a console application that
> automatically hides parameters for all 50 of my reports.
> So, the VB.NET code will need the following:
> 1. Retrieve a collection of reports
> 2. Set a default parameter for the Employee ID to each report
> 3. Create a linked report for each base report and respective Employee
> ID value and concatenate the parameter value to the report name (e.g.
> "Sales by Employee - 36")
> 4. Create a snapshot from the linked report
> 5. Set the snapshot to use the shared schedule for my nightly refresh
> 6. Remove existing snapshots for those employees who have left the
> company
> 7. Remove the default value for the Employee ID from the base reports
> so they can be refreshed separately from the snapshot reports
>
> "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message news:<OcjTCRLpEHA.3552@.TK2MSFTNGP15.phx.gbl>...
> > ok Steve. I am a little more pluged in to your design.
> >
> > The Web Service "UpdateReportExecutionSnapshot" method is not going to allow
> > you to name your output snapshots anything different from the base name of
> > the report (see BOL on this function and the section of snapshots with
> > parameterized reports).
> >
> > I think, based on what you are telling me is that you will want to
> > (0) identify the user and her report parameters
> > (1) use the Web Service "Render" method (which returns a stream of bytes) to
> > create the report stream
> > (2) write the bytes to a file share (and name it using your paramater
> > values) and then
> > (3) redirect the user to that file.
> >
> > [you will want to skip (1) and (2) if a valid file on share exists when the
> > user jumps in]
> >
> > does this sound correct?
> >
> >
> > dlr
> > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > news:437b6286.0409241745.6fa9b007@.posting.google.com...
> > > Dennis, when the user logs in to the web application, a stored
> > > procedure fires to retrieve the ID for the employee, where the
> > > employee works, where the employee is in the management food chain,
> > > and what reports the user is authorized to see.
> > >
> > > So, when the user enters the reports page in the web application, the
> > > user would see all the reports he/she is permitted to see that the
> > > stored procedure brought back from that report table I mentioned.
> > > Because the web application has the employee and workplace ID in
> > > memory, it would call the respective snapshot by taking the report
> > > name and concatenating the employee ID and workplace ID, which then
> > > references the snapshot report name. Here's an example...
> > >
> > > Let's assume that the user's employee ID is 205 and workplace ID is
> > > 5000. If the user clicks on a report called "Sales by Employee", the
> > > web application would then construct the snapshot report name (e.g.
> > > "Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
> > > snapshots available in the Reporting Services database (i.e. one
> > > snapshot combination for every employee ID and work place ID) and
> > > display the correct snapshot.
> > >
> > > Unfortunately, we don't know how to do the VB.NET code to
> > > automatically build all the snapshots from our database table of
> > > employee and workplace IDs. We need a means for automatically
> > > generating and eliminating snapshots as employees come on board,
> > > switch departments, or leave the organization.
> > >
> > > "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message
> > news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> > > > question. How will you set up security for filter out employee's to
> > read
> > > > only the
> > > > report snaped from their ID?
> > > >
> > > > dlr
> > > > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > > > news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > > > > I need to generate hundreds of snapshot reports, which would be
> > > > > refreshed every night. Each employee would view a snapshot report
> > > > > pertaining to his employee number (which is the parameter in the
> > > > > report). The employee is not allowed to look at anyone else's report,
> > > > > and the company doesn't want employees to be refreshing reports all
> > > > > day long.
> > > > >
> > > > > So, here's what I need:
> > > > >
> > > > > 1. VB.NET code that calls the Reporting Services web service to
> > > > > generate a linked snapshot report for each employee report and every
> > > > > employee number (for the employee parameter) in my SQL database
> > > > > 2. Code to automatically schedule these snapshots for a nightly run
> > > > > using a shared scheduled execution time
> > > > > 3. A way to name each linked snapshot report using some kind of naming
> > > > > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > > > > Employee 205", etc.)
> > > > >
> > > > > Can anyone help? Does anyone have any sample VB.NET code to share?
>

Need to transform table data to excel 2003 files

Hi,
Can I use SQL Server Integration Services to generate Excel 2003 spread
sheets based on or database data?
We already have Excel 2003 templates and we need to generate excel 2003 xls
files based on our database table data. What would b the best way to do
that?
Any help would be appreciated,
Max
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:O1R9oeXGHHA.3616@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Can I use SQL Server Integration Services to generate Excel 2003 spread
> sheets based on or database data?
> We already have Excel 2003 templates and we need to generate excel 2003
> xls files based on our database table data. What would b the best way to
> do that?
I did something like this once for an Intranet reporting application. The
best solution (easy and performant) that I came up with was to grab the data
using SELECT ... FOR XML and perform an XSL transformation on it. Excel
2003 has a rich set of features in the XML format (though I don't think you
can embed tables and charts in the Excel XML format files).
|||Hello Mike,
To understand the issue better, I'd like to know how you want to use the
2003 template when transform the data from database to spreedsheet. An
example might be more clear.
You shall be able to export data directly to Excel file in SQL Server. In
Management Studio (2005), right click the database->Tasks->Export data,
follow the wizard to select source and select Microsoft Excle as
destination, and select the destination path of excel file.
Also, in SSIS, you could be more flexisble to get the source of the data.
1. You could use the OLEDB or datareader data source, and use select SQL
query to get the data you want.
2. You could use Excel Destination as the data destination and select Excel
file or create new Excel as you want.
3. YOu also be able to select the proper columns for mapping in able object
and use other data folow transformation if necessary.
As Mike mentioned, you could use "SELECT ... FOR XML" to get XML format of
the data and do transformation by using XSL.
As for as I know, Excel destination does not include configuration to
create new file from xlt template. This might be finished before
transformation by using an Activex scipt task or a Script task. For example
by using Activex script task you may use code like:
Dim sTemplatePath, sTargetPath, sSourceFileName, sTargetFileName
sSourceFileName = "temp.xlt"
sTargetFileName = "test.xls"
sTemplatePath = "C:\"
sTargetPath = "C:\"
sTargetFileName = sTargetPath & year(now) & "-" & month(now) & "-" &
day(now) - 1 & " " & sTargetFileName
DTSGlobalVariables("sFileName").Value = sTargetFileName
sSourceFileName = sTemplatePath & sSourceFileName
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile sSourceFileName, sTargetFileName
Set oFSO = Nothing
If you have any update or comments, please feel free to let's know. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:32UR4gaGHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Mike,
> To understand the issue better, I'd like to know how you want to use the
> 2003 template when transform the data from database to spreedsheet. An
> example might be more clear.
I don't "want to", I already "did". It's been done. Now if you really want
to help me instead of Max, tell me how to embed charts and graphics in Excel
XML format documents.

> You shall be able to export data directly to Excel file in SQL Server. In
> Management Studio (2005), right click the database->Tasks->Export data,
> follow the wizard to select source and select Microsoft Excle as
> destination, and select the destination path of excel file.
> Also, in SSIS, you could be more flexisble to get the source of the data.
> 1. You could use the OLEDB or datareader data source, and use select SQL
> query to get the data you want.
> 2. You could use Excel Destination as the data destination and select
> Excel
> file or create new Excel as you want.
> 3. YOu also be able to select the proper columns for mapping in able
> object
> and use other data folow transformation if necessary.
> As Mike mentioned, you could use "SELECT ... FOR XML" to get XML format of
> the data and do transformation by using XSL.
> As for as I know, Excel destination does not include configuration to
> create new file from xlt template. This might be finished before
> transformation by using an Activex scipt task or a Script task. For
> example
> by using Activex script task you may use code like:
>
> Dim sTemplatePath, sTargetPath, sSourceFileName, sTargetFileName
> sSourceFileName = "temp.xlt"
> sTargetFileName = "test.xls"
> sTemplatePath = "C:\"
> sTargetPath = "C:\"
> sTargetFileName = sTargetPath & year(now) & "-" & month(now) & "-" &
> day(now) - 1 & " " & sTargetFileName
> DTSGlobalVariables("sFileName").Value = sTargetFileName
> sSourceFileName = sTemplatePath & sSourceFileName
> Dim oFSO
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> oFSO.CopyFile sSourceFileName, sTargetFileName
> Set oFSO = Nothing
> If you have any update or comments, please feel free to let's know. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hello Mike,
Sorry for my fault. My reply is for Max insead of you.
The XML Spreadsheet format describes the contents of the workbook by
persisting cell data and formulas, cell formats, worksheet settings, and
workbook settings. Some Excel features cannot be persisted in XML. The
following Excel features cannot be persisted in XML: Charts
OLE Objects
Drawing shapes or AutoShapes
VBA Projects
Group and Outline
Please see the following article for details:
287739XL2002: Error Message: The Following Features from Your Workbook
Will Not Be Saved in the XML Spreadsheet
http://support.microsoft.com/default.aspx?scid=kb;EN-US;287739
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Need to transform table data to excel 2003 files

Hi,
Can I use SQL Server Integration Services to generate Excel 2003 spread
sheets based on or database data?
We already have Excel 2003 templates and we need to generate excel 2003 xls
files based on our database table data. What would b the best way to do
that?
Any help would be appreciated,
Max"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:O1R9oeXGHHA.3616@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Can I use SQL Server Integration Services to generate Excel 2003 spread
> sheets based on or database data?
> We already have Excel 2003 templates and we need to generate excel 2003
> xls files based on our database table data. What would b the best way to
> do that?
I did something like this once for an Intranet reporting application. The
best solution (easy and performant) that I came up with was to grab the data
using SELECT ... FOR XML and perform an XSL transformation on it. Excel
2003 has a rich set of features in the XML format (though I don't think you
can embed tables and charts in the Excel XML format files).|||Hello Mike,
To understand the issue better, I'd like to know how you want to use the
2003 template when transform the data from database to spreedsheet. An
example might be more clear.
You shall be able to export data directly to Excel file in SQL Server. In
Management Studio (2005), right click the database->Tasks->Export data,
follow the wizard to select source and select Microsoft Excle as
destination, and select the destination path of excel file.
Also, in SSIS, you could be more flexisble to get the source of the data.
1. You could use the OLEDB or datareader data source, and use select SQL
query to get the data you want.
2. You could use Excel Destination as the data destination and select Excel
file or create new Excel as you want.
3. YOu also be able to select the proper columns for mapping in able object
and use other data folow transformation if necessary.
As Mike mentioned, you could use "SELECT ... FOR XML" to get XML format of
the data and do transformation by using XSL.
As for as I know, Excel destination does not include configuration to
create new file from xlt template. This might be finished before
transformation by using an Activex scipt task or a Script task. For example
by using Activex script task you may use code like:
Dim sTemplatePath, sTargetPath, sSourceFileName, sTargetFileName
sSourceFileName = "temp.xlt"
sTargetFileName = "test.xls"
sTemplatePath = "C:\"
sTargetPath = "C:\"
sTargetFileName = sTargetPath & year(now) & "-" & month(now) & "-" &
day(now) - 1 & " " & sTargetFileName
DTSGlobalVariables("sFileName").Value = sTargetFileName
sSourceFileName = sTemplatePath & sSourceFileName
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile sSourceFileName, sTargetFileName
Set oFSO = Nothing
If you have any update or comments, please feel free to let's know. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:32UR4gaGHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Mike,
> To understand the issue better, I'd like to know how you want to use the
> 2003 template when transform the data from database to spreedsheet. An
> example might be more clear.
I don't "want to", I already "did". It's been done. Now if you really want
to help me instead of Max, tell me how to embed charts and graphics in Excel
XML format documents.

> You shall be able to export data directly to Excel file in SQL Server. In
> Management Studio (2005), right click the database->Tasks->Export data,
> follow the wizard to select source and select Microsoft Excle as
> destination, and select the destination path of excel file.
> Also, in SSIS, you could be more flexisble to get the source of the data.
> 1. You could use the OLEDB or datareader data source, and use select SQL
> query to get the data you want.
> 2. You could use Excel Destination as the data destination and select
> Excel
> file or create new Excel as you want.
> 3. YOu also be able to select the proper columns for mapping in able
> object
> and use other data folow transformation if necessary.
> As Mike mentioned, you could use "SELECT ... FOR XML" to get XML format of
> the data and do transformation by using XSL.
> As for as I know, Excel destination does not include configuration to
> create new file from xlt template. This might be finished before
> transformation by using an Activex scipt task or a Script task. For
> example
> by using Activex script task you may use code like:
>
> Dim sTemplatePath, sTargetPath, sSourceFileName, sTargetFileName
> sSourceFileName = "temp.xlt"
> sTargetFileName = "test.xls"
> sTemplatePath = "C:\"
> sTargetPath = "C:\"
> sTargetFileName = sTargetPath & year(now) & "-" & month(now) & "-" &
> day(now) - 1 & " " & sTargetFileName
> DTSGlobalVariables("sFileName").Value = sTargetFileName
> sSourceFileName = sTemplatePath & sSourceFileName
> Dim oFSO
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> oFSO.CopyFile sSourceFileName, sTargetFileName
> Set oFSO = Nothing
> If you have any update or comments, please feel free to let's know. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Mike,
Sorry for my fault. My reply is for Max insead of you.
The XML Spreadsheet format describes the contents of the workbook by
persisting cell data and formulas, cell formats, worksheet settings, and
workbook settings. Some Excel features cannot be persisted in XML. The
following Excel features cannot be persisted in XML: Charts
OLE Objects
Drawing shapes or AutoShapes
VBA Projects
Group and Outline
Please see the following article for details:
287739 XL2002: Error Message: The Following Features from Your Workbook
Will Not Be Saved in the XML Spreadsheet
http://support.microsoft.com/defaul...kb;EN-US;287739
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Need to transform table data to excel 2003 files

Hi,
Can I use SQL Server Integration Services to generate Excel 2003 spread
sheets based on or database data?
We already have Excel 2003 templates and we need to generate excel 2003 xls
files based on our database table data. What would b the best way to do
that?
Any help would be appreciated,
Max"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:O1R9oeXGHHA.3616@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Can I use SQL Server Integration Services to generate Excel 2003 spread
> sheets based on or database data?
> We already have Excel 2003 templates and we need to generate excel 2003
> xls files based on our database table data. What would b the best way to
> do that?
I did something like this once for an Intranet reporting application. The
best solution (easy and performant) that I came up with was to grab the data
using SELECT ... FOR XML and perform an XSL transformation on it. Excel
2003 has a rich set of features in the XML format (though I don't think you
can embed tables and charts in the Excel XML format files).|||Hello Mike,
To understand the issue better, I'd like to know how you want to use the
2003 template when transform the data from database to spreedsheet. An
example might be more clear.
You shall be able to export data directly to Excel file in SQL Server. In
Management Studio (2005), right click the database->Tasks->Export data,
follow the wizard to select source and select Microsoft Excle as
destination, and select the destination path of excel file.
Also, in SSIS, you could be more flexisble to get the source of the data.
1. You could use the OLEDB or datareader data source, and use select SQL
query to get the data you want.
2. You could use Excel Destination as the data destination and select Excel
file or create new Excel as you want.
3. YOu also be able to select the proper columns for mapping in able object
and use other data folow transformation if necessary.
As Mike mentioned, you could use "SELECT ... FOR XML" to get XML format of
the data and do transformation by using XSL.
As for as I know, Excel destination does not include configuration to
create new file from xlt template. This might be finished before
transformation by using an Activex scipt task or a Script task. For example
by using Activex script task you may use code like:
Dim sTemplatePath, sTargetPath, sSourceFileName, sTargetFileName
sSourceFileName = "temp.xlt"
sTargetFileName = "test.xls"
sTemplatePath = "C:\"
sTargetPath = "C:\"
sTargetFileName = sTargetPath & year(now) & "-" & month(now) & "-" &
day(now) - 1 & " " & sTargetFileName
DTSGlobalVariables("sFileName").Value = sTargetFileName
sSourceFileName = sTemplatePath & sSourceFileName
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile sSourceFileName, sTargetFileName
Set oFSO = Nothing
If you have any update or comments, please feel free to let's know. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:32UR4gaGHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Mike,
> To understand the issue better, I'd like to know how you want to use the
> 2003 template when transform the data from database to spreedsheet. An
> example might be more clear.
I don't "want to", I already "did". It's been done. Now if you really want
to help me instead of Max, tell me how to embed charts and graphics in Excel
XML format documents.
> You shall be able to export data directly to Excel file in SQL Server. In
> Management Studio (2005), right click the database->Tasks->Export data,
> follow the wizard to select source and select Microsoft Excle as
> destination, and select the destination path of excel file.
> Also, in SSIS, you could be more flexisble to get the source of the data.
> 1. You could use the OLEDB or datareader data source, and use select SQL
> query to get the data you want.
> 2. You could use Excel Destination as the data destination and select
> Excel
> file or create new Excel as you want.
> 3. YOu also be able to select the proper columns for mapping in able
> object
> and use other data folow transformation if necessary.
> As Mike mentioned, you could use "SELECT ... FOR XML" to get XML format of
> the data and do transformation by using XSL.
> As for as I know, Excel destination does not include configuration to
> create new file from xlt template. This might be finished before
> transformation by using an Activex scipt task or a Script task. For
> example
> by using Activex script task you may use code like:
>
> Dim sTemplatePath, sTargetPath, sSourceFileName, sTargetFileName
> sSourceFileName = "temp.xlt"
> sTargetFileName = "test.xls"
> sTemplatePath = "C:\"
> sTargetPath = "C:\"
> sTargetFileName = sTargetPath & year(now) & "-" & month(now) & "-" &
> day(now) - 1 & " " & sTargetFileName
> DTSGlobalVariables("sFileName").Value = sTargetFileName
> sSourceFileName = sTemplatePath & sSourceFileName
> Dim oFSO
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> oFSO.CopyFile sSourceFileName, sTargetFileName
> Set oFSO = Nothing
> If you have any update or comments, please feel free to let's know. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Mike,
Sorry for my fault. My reply is for Max insead of you.
The XML Spreadsheet format describes the contents of the workbook by
persisting cell data and formulas, cell formats, worksheet settings, and
workbook settings. Some Excel features cannot be persisted in XML. The
following Excel features cannot be persisted in XML: ? Charts
? OLE Objects
? Drawing shapes or AutoShapes
? VBA Projects
? Group and Outline
Please see the following article for details:
287739 XL2002: Error Message: The Following Features from Your Workbook
Will Not Be Saved in the XML Spreadsheet
http://support.microsoft.com/default.aspx?scid=kb;EN-US;287739
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================