Dynamic dataset column names and number of columns

I have a dataset that is based on a dynamic pivot stored procedure. There is
at least one column name that I know will be in the dataset, the key column,
but the rest of the columns are dependent on the data in the underlying table.
Once I have the dataset, how can I put it in the layout given that the
column names are dynamic and the number of columns are dynamic?
StephanieOn Aug 20, 4:36 pm, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> I have a dataset that is based on a dynamic pivot stored procedure. There is
> at least one column name that I know will be in the dataset, the key column,
> but the rest of the columns are dependent on the data in the underlying table.
> Once I have the dataset, how can I put it in the layout given that the
> column names are dynamic and the number of columns are dynamic?
> Stephanie
You will want to look into Matrix Reports, where the pivoted column's
unique values are actually the number of columns in the report. This
provides the dynamic functionality you need.
Regards,
Enrique Martinez
Sr. Software Consultant

Dynamic Cross Tab Using Cursors.

I have two tables
1)Rollout_detail
start_date Datetime,
contract_date Datetime,
budget_amt Money
store_id int(foriegn key referring store.store_id)
pan_number varchar(20)
roll_id int

2)store
store_id int(primary key)
skey varchar(10)

these two tables are tied with store_id
and in rollout_detail there can be many pan_numbers for a given
store.(pan_number + store ) are unique.

Now here is the problem.

I need to generate a cross tab report with
store,pan1_contract_date,pan1_budget_amt,pan1_star t_date,
pan2_Contract_date,pan2_budget_amt,pan2_start_date and so on.

I tried this with in a procedure.
------------------
CREATE PROCEDURE crosstab
@.roll_id INT
WITH ENCRYPTION
AS
DECLARE @.sql VARCHAR(8000),@.panNumber VARCHAR(20)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE al_cursor CURSOR FOR SELECT DISTINCT pan_number AS p_pannumber
FROM rollout_detail WHERE roll_id=@.roll_id
OPEN al_cursor
SELECT @.sql='SELECT skey, '
FETCH NEXT FROM al_cursor INTO @.panNumber
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.sql=@.sql+'(CASE pan_number WHEN
'''+rtrim(@.panNumber)+''' THEN start_date END) as
P'+rtrim(@.panNumber)+'_sd,(CASE pan_number WHEN
'''+rtrim(@.panNumber)+''' THEN budget_amt END) as
P'+rtrim(@.panNumber)+'_ba,(CASE pan_number WHEN
'''+rtrim(@.panNumber)+''' THEN contract_date END) as
P'+rtrim(@.panNumber)+'_cd,'

FETCH NEXT FROM al_cursor INTO @.panNumber
END
SELECT @.sql=left(@.sql, len(@.sql)-1)+' '
SELECT @.sql=@.sql+'FROM rollout_detail rd,store s
WHERE rd.store_id=s.store_id AND roll_id='+cast(@.roll_id as varchar)

EXEC (@.sql)
CLOSE al_cursor
DEALLOCATE al_cursor
SET ANSI_WARNINGS ON

EXECUTE crosstab 1
------------------
I am getting multiple records for the same store
the result set is.

skey pan1_cd pan1_ba pan1_sd pan2_cd pan2_ba pan2_sd
1 12/2/04 400.0 3/4/05 NULL NULL NULL
1 NULL NULL NULL 5/6/04 566.00 3/4/04

I want the result set merged for each store.

Please help,

Thanks a million

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Jaidev Paruchuri" <jaip26@.hotmail.com> wrote in message
news:408fbf3d$0$200$75868355@.news.frii.net...
> I have two tables
> 1)Rollout_detail
> start_date Datetime,
> contract_date Datetime,
> budget_amt Money
> store_id int(foriegn key referring store.store_id)
> pan_number varchar(20)
> roll_id int
> 2)store
> store_id int(primary key)
> skey varchar(10)
> these two tables are tied with store_id
> and in rollout_detail there can be many pan_numbers for a given
> store.(pan_number + store ) are unique.
> Now here is the problem.
> I need to generate a cross tab report with
> store,pan1_contract_date,pan1_budget_amt,pan1_star t_date,
> pan2_Contract_date,pan2_budget_amt,pan2_start_date and so on.
> I tried this with in a procedure.
> ------------------
> CREATE PROCEDURE crosstab
> @.roll_id INT
> WITH ENCRYPTION
> AS
> DECLARE @.sql VARCHAR(8000),@.panNumber VARCHAR(20)
> SET NOCOUNT ON
> SET ANSI_WARNINGS OFF
> DECLARE al_cursor CURSOR FOR SELECT DISTINCT pan_number AS p_pannumber
> FROM rollout_detail WHERE roll_id=@.roll_id
> OPEN al_cursor
> SELECT @.sql='SELECT skey, '
> FETCH NEXT FROM al_cursor INTO @.panNumber
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.sql=@.sql+'(CASE pan_number WHEN
> '''+rtrim(@.panNumber)+''' THEN start_date END) as
> P'+rtrim(@.panNumber)+'_sd,(CASE pan_number WHEN
> '''+rtrim(@.panNumber)+''' THEN budget_amt END) as
> P'+rtrim(@.panNumber)+'_ba,(CASE pan_number WHEN
> '''+rtrim(@.panNumber)+''' THEN contract_date END) as
> P'+rtrim(@.panNumber)+'_cd,'
> FETCH NEXT FROM al_cursor INTO @.panNumber
> END
> SELECT @.sql=left(@.sql, len(@.sql)-1)+' '
> SELECT @.sql=@.sql+'FROM rollout_detail rd,store s
> WHERE rd.store_id=s.store_id AND roll_id='+cast(@.roll_id as varchar)
> EXEC (@.sql)
> CLOSE al_cursor
> DEALLOCATE al_cursor
> SET ANSI_WARNINGS ON
> EXECUTE crosstab 1
> ------------------
> I am getting multiple records for the same store
> the result set is.
> skey pan1_cd pan1_ba pan1_sd pan2_cd pan2_ba pan2_sd
> 1 12/2/04 400.0 3/4/05 NULL NULL NULL
> 1 NULL NULL NULL 5/6/04 566.00 3/4/04
>
> I want the result set merged for each store.
> Please help,
> Thanks a million
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

You're probably looking for something like this - using MAX() and GROUP BY:

select skey,
max(case when ...) as A,
max(case when ...) as B,
...
from
...
where
...
group by skey

Simon|||Simon,

That worked.
Thankyou very much for the solution.
Though it was simple,I was thinking about alternate solutions.I really
appreaciate you guys.

--Jay

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Dynamic Connection Strings in a Scipt

I am trying to split a .csv file on a week+location key. As there are 500+ locations and 52 weeks in a year this is not a manual task.

I have a Script component written but need to dynamically open/close the connection manager changing the connection string in between, to the Week+location key as the filename.

It has to be do-able because the ForEachLoop does it. But how do I?

You'll most likely want to use a ForEach Loop, variables and property expressions.

This blog post should help you out - Looping over files with the Foreach Loop

HTH,

~Matt

|||That would be fine if I was trying to concatenate many to one, I use the ForEachLoop a lot, but I am trying to split one into many. I can't see how to use an FEL for that, or am I just being dumb?|||

I see two approaches you could take:

1. Use a script task (in the control flow, not the data flow) to split up your CSV file into multiple files, then process them all using a dataflow inside of a for each loop.

2. Continue using your script component, but use a foreach loop to pass in the week + location key you want to process, and process them one by one.

~Matt

|||Maybe this post might give you some ideas: http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx|||

Ah, thanks John! I hadn't see that entry before. I'll keep that link handy for future reference.

~Matt

|||Thanks for the help.

The technique I ended up using has a script task inside a for each loop which sets the connect string before a dataflow connects using the new connect string and processing each file. Simple when you have a night's sleep!!
|||Can you please tell me how to set the connectionstring in script task dynamically ? I need to set the initialcatalog property of connectionstring and loop over multiple databases to transfer data in foreach loop in SSIS package. So I have taken the database names in a collection and looping each of them. However when I create another script in it, the visual studio breaks down and says you have encountered unhandled exception. Can you throw some light over it ?|||Can you post the script your are using? Sounds like you might have a bug in it.

Dynamic Connection Strings in a Scipt

I am trying to split a .csv file on a week+location key. As there are 500+ locations and 52 weeks in a year this is not a manual task.

I have a Script component written but need to dynamically open/close the connection manager changing the connection string in between, to the Week+location key as the filename.

It has to be do-able because the ForEachLoop does it. But how do I?

You'll most likely want to use a ForEach Loop, variables and property expressions.

This blog post should help you out - Looping over files with the Foreach Loop

HTH,

~Matt

|||That would be fine if I was trying to concatenate many to one, I use the ForEachLoop a lot, but I am trying to split one into many. I can't see how to use an FEL for that, or am I just being dumb?|||

I see two approaches you could take:

1. Use a script task (in the control flow, not the data flow) to split up your CSV file into multiple files, then process them all using a dataflow inside of a for each loop.

2. Continue using your script component, but use a foreach loop to pass in the week + location key you want to process, and process them one by one.

~Matt

|||Maybe this post might give you some ideas: http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx|||

Ah, thanks John! I hadn't see that entry before. I'll keep that link handy for future reference.

~Matt

|||Thanks for the help.

The technique I ended up using has a script task inside a for each loop which sets the connect string before a dataflow connects using the new connect string and processing each file. Simple when you have a night's sleep!!
|||Can you please tell me how to set the connectionstring in script task dynamically ? I need to set the initialcatalog property of connectionstring and loop over multiple databases to transfer data in foreach loop in SSIS package. So I have taken the database names in a collection and looping each of them. However when I create another script in it, the visual studio breaks down and says you have encountered unhandled exception. Can you throw some light over it ?|||Can you post the script your are using? Sounds like you might have a bug in it.

Dyanamic stored procedures

Hi all,
I'm building a search function and I need do it using stored procedures. I'm searching a table on 'Name' 'email address' and 'key words'. (basically 3 columns). The user must be able to use it as
SELECT ......... WHERE (NAME LIKE @.name) AND/OR (Email LIKE @.email) AND/OR ..........
Can some one tell me how to handle this in a stored procedure. By the way is this approch correct. Please tell me if there is a better method to tackle this situation.
Thanks,
-VJ

I always generate a dynamic SQL statement based on what the user has entered and then execute it or use sp_executesql to run it. Some will say that dynamic SQL is bad because the execution plan doesn't get cached like normal procs, but you can get better performance with dynamic SQL sometimes because since the query criteria can change dramtically, the cached execution plan may be a bad one. Always bets to time and view the execution plan in QA, but my experience is that dynamic SQL within the stored proc works best when there are varying search parameters.

|||If it's just 3 columns you're validating, you should just use case statements instead of utilizing dynamic sql.
selelct * from table where -- normal stuff
Name Like (
CASE
WHEN @.name IS NOT NULL THEN @.name
ELSE Name
END
)

-----
A lot of times, I'll validate a string for nulls and empty strings, so I'll usually have this function in most of my databases.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[IsEmptyString]
(
@.String VarChar(2000)
)
RETURNS BIT AS
BEGIN
DECLARE @.ReturnVar BIT
IF (@.String IS NOT NULL AND @.String <> '') -- you can trim if you are avoiding whitespaces.
SET @.ReturnVar = 0
ELSE
SET @.ReturnVar = 1

RETURN @.ReturnVar
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||Test it both ways. I've done it with case statements and gotten terrible performance compared with dynamic SQL. It all depends on the situation. Having 3 or 6 or 20 columns really isn't the issue; it's how the procedure performs when varying numbers or combinations of the criteria are entered. I've seen demos where the dynamic SQL blew away using case or isnull.|||You're right that it's completely situational in terms of performance,and many will do their best to keep the actual logic in code as much aspossible. From personal experience, iterations through decentsized databases (2 million records in each of the major tables), a casestatement will perform fairly well as compared to utilizing dynamicsql. It helps further when you require calculations to be madeper record to assist in it's selection criteria (Distance for example),and the one of the factors is based on the parameters. In caseslike this, a case statement tends to outweigh a dump of string to beexecuted in a stored procedure.
Create Procedure KraGiE.ExecuteString
@.SQL varchar (2000)
AS
EXECUTE( @.SQL )
GO
For some reason, that makes me cringe, and I'm sure many database administrators will plainly say "no."