dynamic excel destination depend on dataflow data

I created a data flow with complaicated SQL. There is "type" field in the output column.

I would like to created excel files for each "type" value

E.g. If there is 3 "type" values (A, B, C), I would like to create 3 excel files to store type A, type B, and type C data respectively.

Since the number of possibe value of "type" field is various, how can I create the xls destination dynamic and move the correct type to the corresponding excel file?

The conditional split has fixed conditions, it is not suitable for by dynamic number of value

For Loop condition is not a good choice because I need to run the complicated SQL for many time.

Thanks.

Put all data in one place, but with the additional type column. Then drive a loop to extract each type of data into a file. This assumes that each types file has the same format, and would require two data flows, the latter of which would be inside the loop.

I would store the list of unique types in advance, perhaps by multicasting the preparatory flow just prior to writing the full file, and then getting distinct type values, use the Sort component.

Your second data flow would just be source, conditional split acting as a filter then the destination. The split filter would be based on typecolumn == @.typevariable. @.typevariable would be set from the loop enumerator, it is type value for that iteration. I would also use that variable in an expression on the destination connection to give a type specific filename.

Dynamic Crosstab

Say I have the following output from a view on SQL Server 2000;
RegID StudID SeqNo EnrolNo Name Status
Prog AttendDate AttendCode
20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 8/8/2005 X
20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 15/8/2005 X
20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 22/8/2005 O
How do I massage this using a Stored Procedure into the following output?
RegID StudID SeqNo EnrolNo Name Status
Prog 8/8/2005 15/8/2005 22/8/2005
20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A X X O
Bearing in mind that the date columns are dynamic and can be anything from 0
to 18.
I have found some scripts but I couldn't get the mto work with dates :(
Thanks.See if these help.
Dynamic Crosstab Queries
http://www.windowsitpro.com/SQLServ...5608/15608.html
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
AMB
"Mark Parter" wrote:

> Say I have the following output from a view on SQL Server 2000;
> RegID StudID SeqNo EnrolNo Name Status
Prog AttendDate AttendCode
> 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 8/8/2005 X
> 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 15/8/2005 X
> 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A 22/8/2005 O
> How do I massage this using a Stored Procedure into the following output?
> RegID StudID SeqNo EnrolNo Name Status
Prog 8/8/2005 15/8/2005 22/8/200
5
> 20 0123036 01 0123036/1 COLIN ANSELL C ENCOMPOL/D1/A X X O
> Bearing in mind that the date columns are dynamic and can be anything from
0
> to 18.
> I have found some scripts but I couldn't get the mto work with dates :(
> Thanks.

Dynamic Columns

Hi all again,
I need an output like :
|--|--|--|--|--|
|row1|row2|row3|...|row(n)|
|--|--|--|--|--|

is possible to create output like that, without querying manually
cause too many dynamic columns in my application.
sorry if my question a bit weird cause i'm a newbie in MSSQL.

Cheers.aCe wrote:

Quote:

Originally Posted by

I need an output like :
|--|--|--|--|--|
|row1|row2|row3|...|row(n)|
|--|--|--|--|--|

I recommend you return normal data (where the rows are, y'know, rows)
from your database layer, then rearrange them in your reporting
layer. (If you don't have a reporting layer, then I recommend you
add one; I like Crystal Reports, myself.)

Dynamic Column - Total - Using Matrix - Help Neede!

Hi All,
I have a report with Dynamic Column - achieved using Matrix
The Output is something like this
Static-Col1 Dynamic-Col1 Dyn-Col2 Dyn-Col3
AA 100 10 20
30
BB 200 20 15
30
CC 300 10 10
10
I need the total of static and dynmaic column for each column individually
Static-Col1 Dynamic-Col1 Dyn-Col2 Dyn-Col3
AA 100 10 20
30
BB 200 20 15
30
CC 300 10 10
10
Total 500 40
45 70
For static Column I can say (=Sum(Field!Static-Col1.Value), but how can I
display the total for dynamic columns
Thanks
Balaji
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200510/1BALAJI K wrote:
>Hi All,
>I have a report with Dynamic Column - achieved using Matrix
>The Output is something like this
> Static-Col1 Dynamic-Col1 Dyn-Col2 Dyn-Col3
>AA 100 10 20
>30
>BB 200 20 15
>30
>CC 300 10 10
>10
>I need the total of static and dynmaic column for each column individually
> Static-Col1 Dynamic-Col1 Dyn-Col2 Dyn-Col3
>AA 100 10 20
>30
>BB 200 20 15
>30
>CC 300 10 10
>10
>Total 500 40
>45 70
>For static Column I can say (=Sum(Field!Static-Col1.Value), but how can I
>display the total for dynamic columns
>Thanks
>Balaji
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200510/1|||BALAJI K wrote:
>Hi All,
>I have a report with Dynamic Column - achieved using Matrix
>The Output is something like this
> Static-Col1 Dynamic-Col1 Dyn-Col2 Dyn-Col3
>AA 100 10 20
>30
>BB 200 20 15
>30
>CC 300 10 10
>10
>I need the total of static and dynmaic column for each column individually
> Static-Col1 Dynamic-Col1 Dyn-Col2 Dyn-Col3
>AA 100 10 20 30
>BB 200 20 15 30
>CC 300 10 10 10
>Total 500 40 45 70
>For static Column I can say (=Sum(Field!Static-Col1.Value), but how can I
>display the total for dynamic columns
>Thanks
>Balaji
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200510/1

Dyanamic Sql

Hi I was building a dynamic sql with output please tell me where I am wrong.
Or Tell me right way to do it

My table name is variable (Stock name)
I want close price for given Stock

CREATE PROCEDURE SP_GET_CLOSE
@.TableName as varchar(50),
@.close_price as varchar(50) output

AS
Declare @.SQL NVarChar(1000)
Declare @.high varchar(50)
SET @.SQL = 'Select top 1 @.close_price = [close] from '+ @.TableName+ ' order by trade_date desc'

print @.SQL
Exec sp_executesql @.SQL, N'@.close_price varchar(50) output' , @.close_price
GOWaht abotu this idea?

CREATE PROCEDURE SP_GET_CLOSE
@.TableName as varchar(50),
@.close_price as varchar(50) output

AS
Declare @.SQL NVarChar(1000)
Declare @.high varchar(50)
SET @.SQL = 'Select top 1 [close] from '+ @.TableName+ ' order by trade_date desc'

create table #tmp(ret varchar(450))

insert #tmp
exec(@.SQL)

select @.close_price=cast(ret as money) from #tmp|||Yes That seems a good idea but I have some 200 trading Scripts in month and this would create lot of #tmp tables I thought it should be possible to send dynamic SQl a output variable.
Besides I am having lot of similar quiries in mind for my project

Thanks|||Originally posted by shriya
Yes That seems a good idea but I have some 200 trading Scripts in month and this would create lot of #tmp tables I thought it should be possible to send dynamic SQl a output variable.
Besides I am having lot of similar quiries in mind for my project

Thanks

Temporary table will dropped after procedure execution. BTW, sql server is using temporary objects during any operations (even like select from with order by) so do not worry about this at all.