Dynamic page break, can we ever do this?

Our reports need to have page breaks based on User's preferences. I
couldn't find a way to achieve this. Can anyone share some ideas?Thanks for your quick response, Teros.
I am sorry I didn't clearly state my problem. What I need is dynamic page
break on groups within a table. Suppose I have 2 groups named 'Location' and
'Category' within an Inventory table. I need enable/disable page break at
the end of each Location or Category based on user settings. I don't see how
this can be done through conditional expression. Is there any workaround?
Thanks.
"Teros" <Teros@.discussions.microsoft.com> wrote in message
news:D4705B9E-EA3E-4B76-AF8D-546118BA9F17@.microsoft.com...
> First off, where are the page breaks? After X rows in a report, or after
individual tables, or what? I'd think you could modify the PageBreakAtEnd
or PageBreakAtStart property of various items with a conditional such as
IIf(Parameters!Break.Value = 1, True, False) or something similar, depending
on where you want the page breaks.
> That spark any ideas?
> - T
> "Audrey See" wrote:
> > Our reports need to have page breaks based on User's preferences. I
> > couldn't find a way to achieve this. Can anyone share some ideas?
> >
> >
> >

Dynamic crosstab query in MS SQL Server 2000

Hello all!
I have a problem with creating crosstab query in MS SQL Server 2000. I
spent 8 hours on searching internet to achieve my succes but without
result. I would like to transform such data:
MRPController WK Value
C01 200505 1
C01 200505 1
C02 200505 2
C03 200506 4
C03 200506 7
C04 200505 1
C04 200507 5
into:
MRPController 200505 200506 200507
C01 2
C02 2
C03 4
C04 1 5
The data are updated once a w, that`s why I need a dynamic crosstab
query which let me receive such query in MS SQL Server 2000. I found out
that it is no so easy to create such cross tab query in MS SQL Server
2000, but I am wondering why it is so easy even in MS Access 1997 and
Excel 1997, and it is so tough case in MS SQL Server released in 2000. I
have search newsgroups, but I didn`t find anything whcih could help me.
I found some SQL procedures but they didn`t work. I heard that in MS SQL
Server 2005 there is a special function who let do it, but I have MS SQL
Server 2000 and I need to do this in this version on SQL Server. Is it
possible to do it? Is it some correct method to do it. Please be so kind
and help, but I already don`t know what to do and it is very wanted
query in my company. I didn`t think that I stuck on such query.
Thank you in advance for your help
I really apprieciate it
Marcin from Poland
*** Sent via Developersdex http://www.examnotes.net ***Take a look at this link
[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21205811.html[/ur
l]
I use the transform proc just as you described with great results. You
will have to make a few small modifications so the date is labeled to
your likeing. I have mine labled (Month Year i.e. March 2005).
GL|||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
"Marcin Zmyslowski" wrote:

>
> Hello all!
> I have a problem with creating crosstab query in MS SQL Server 2000. I
> spent 8 hours on searching internet to achieve my succes but without
> result. I would like to transform such data:
> MRPController WK Value
> C01 200505 1
> C01 200505 1
> C02 200505 2
> C03 200506 4
> C03 200506 7
> C04 200505 1
> C04 200507 5
> into:
> MRPController 200505 200506 200507
> C01 2
> C02 2
> C03 4
> C04 1 5
> The data are updated once a w, that`s why I need a dynamic crosstab
> query which let me receive such query in MS SQL Server 2000. I found out
> that it is no so easy to create such cross tab query in MS SQL Server
> 2000, but I am wondering why it is so easy even in MS Access 1997 and
> Excel 1997, and it is so tough case in MS SQL Server released in 2000. I
> have search newsgroups, but I didn`t find anything whcih could help me.
> I found some SQL procedures but they didn`t work. I heard that in MS SQL
> Server 2005 there is a special function who let do it, but I have MS SQL
> Server 2000 and I need to do this in this version on SQL Server. Is it
> possible to do it? Is it some correct method to do it. Please be so kind
> and help, but I already don`t know what to do and it is very wanted
> query in my company. I didn`t think that I stuck on such query.
> Thank you in advance for your help
> I really apprieciate it
> Marcin from Poland
> *** Sent via Developersdex http://www.examnotes.net ***
>

Dynamic Connection Strings in SSIS

Possible or not? -->
I maybe lazy - but I want to achieve just specifiying 1 variable in SSIS package ("environment") - and all the connectionStrings should "poof" magically be adjusted to correct locations

In DTS I created a SetDTSenvironmentVariables function for all my packages - so how wouldIi achieve this in SSIS?

Function SetDTSenvironmentVariables( environment )
Folder = "MyDtsPackageFolder"
Select Case environment
case "DEV"
DTSGlobalVariables("WorkingDirectory").value = "C:\Packages" & Folder
case "STAGING"
DTSGlobalVariables("WorkingDirectory").value = "D:\Sql_working_directory\My_production\STAGING" & Folder
case "LIVE"
DTSGlobalVariables("WorkingDirectory").value = "D:\Sql_working_directory\My_production\" & Folder
End Select

'
' Set Connection Properties
'
dim oPackage, oConn
set oPackage = DTSGlobalVariables.parent
oPackage.LogFileName = DTSGlobalVariables("WorkingDirectory").value & "\Logs\Errors.txt"
For Each oConn In oPackage.connections
Select Case oConn.Name
case "My_DB"
Select Case environment
case "DEV"
oConn.datasource = "SERVER01"
oConn.Catalog = "My_Production"
case "STAGING"
oConn.datasource = "SERVER06"
oConn.Catalog = "My_Staging"
case "LIVE"
oConn.datasource = "SERVER06"
oConn.Catalog = "My_Production"
End Select
case "Schools.xls"
oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "\" & "School_Codes.xls"
case else
oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "\" & oConn.Name
End Select
Next

set oPackage = nothing
set oConn = nothing
End Function

The way I do this is to have a variable called RootFolder and all other directories are relative to that and hence can be set dynamically using an expression (on ConnectionString property of the appropriate connection manager).

RootFolder variable is set via a configuration. Its your choice as to what type of configuration you use.

I kinda talk about this a bit here:

Common folder structure
(http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2559.aspx)

-Jamie

|||Awesome, dude

Thanks mate - only thing I wonder how do you get time to write all those blogs...|||

TheViewMaster wrote:

Awesome, dude

Thanks mate - only thing I wonder how do you get time to write all those blogs...

I wonder myself sometime.

I've been doing it for two years tho so there's quite a library of "stuff" up there now. I hardly ever write anything new these days.

-Jamie

Dynamic Column Names

Hi guys 'n gals,

I am trying to achieve something a bit complex (or at least appears to be for me).

I have the following table structure:

UID, GroupID, ColumnName, ColumnValue

here is some example data:

UID, GroupId, ColumnName, ColumnValue
1, 1, MAC Address, 8a7sd87sad
2, 1, IP Address, 192.168.1.100
3, 1, Name, John
4, 2, MAC Address, 09a8sd098as
5, 2, Name, Steven

and here is what I would like the example to come out like:

GroupID, MAC Address, IP Address, Name
1, 8a7sd87sad, 192.168.1.100, John
2, 09a8sd098as, NULL, Steven

It needs to be completely dynamic though, as a new column name could be entered to the source table at any time...

I have tried Dynamic SQL and JOINs, but can only seem to get it to work correctly. I was starting to consider doing a loop similar to this:

Code Snippet

DECLARE @.ColumnName varchar(128)
DECLARE @.Sql varchar(255)
SELECT @.ColumnName = [ColumnName] FROM tblSourceData
SELECT @.Sql = 'SELECT [GroupId], [ColumnValue] AS ['+@.ColumnName+'] FROM tblSourceData'
EXEC (@.Sql)

Could somebody please point me in the right direction? I've heard a bit about Pivot tables in my search for this solution, is that perhaps the route I need to go?

Regards,
Justin

You need not to have dynamic SQL, you are trying to achieve the table pivoting..

Code Snippet

Create Table #data (

[UID] Varchar(100) ,

[GroupId] Varchar(100) ,

[ColumnName] Varchar(100) ,

[ColumnValue] Varchar(100)

);

Insert Into #data Values('1','1','MAC Address','8a7sd87sad');

Insert Into #data Values('2','1','IP Address','192.168.1.100');

Insert Into #data Values('3','1','Name','John');

Insert Into #data Values('4','2','MAC Address','09a8sd098as');

Insert Into #data Values('5','2','Name','Steven');

On SQL server 2000/2005,

Code Snippet

Select

GroupId,

Max(Case When[ColumnName] = 'MAC Address' Then ColumnValue End) as [MAC Address],

Max(Case When[ColumnName] = 'IP Address' Then ColumnValue End) as [IP Address],

Max(Case When[ColumnName] = 'Name' Then ColumnValue End) as [Name]

from

#data

Group By

GroupId

Only on SQL server 2005,

Code Snippet

Select * From

(Select GroupId,ColumnName,ColumnValue from #data) as Data

Pivot

(

Max(ColumnValue) For ColumnName in([MAC Address], [IP Address], [Name])

)

as PVT

|||

The one problem though is that you are still manually specifying column names, when I don't want to have to manually change my SQL each time I add a new column in the table... Is there no way of making this more dynamic?

Regards,

Justin

|||

Using the previous temp table data,

On SQL Server 2000/2005

Code Snippet

Declare @.PreparedQuery as Varchar(8000);

Declare @.SqlQuery as Varchar(8000);

Set @.PreparedQuery = 'Max(Case When[ColumnName] = ''?'' Then ColumnValue End) as [?]'

Set @.SqlQuery = 'Select GroupId'

Select @.SqlQuery = @.SqlQuery + ',' +Replace(@.PreparedQuery,'?',ColumnName)

From

(select Distinct ColumnName from #data) as Data

Exec (@.SqlQuery + 'from #data Group By GroupId')

On SQL Server 2005

Code Snippet

Declare @.PreparedQuery as Varchar(8000);

Declare @.SqlQuery as Varchar(8000);

Set @.PreparedQuery = '[?]'

Set @.SqlQuery =

'Select * From (Select GroupId,ColumnName,ColumnValue from #data) as Data

Pivot (Max(ColumnValue) For ColumnName in('

Select @.SqlQuery = @.SqlQuery + Replace(@.PreparedQuery,'?',ColumnName) + ','

From

(select Distinct ColumnName from #data) as Data

Set@.SqlQuery = Substring(@.SqlQuery,1,Len(@.SqlQuery)-1) + ')) as Pvt'

Exec (@.SqlQuery)

|||Works a charm, and is a lot less code than I originally thought it would be, thank you!

Dyanmic Replica creation

Hi,
Can I create a replica dynamically?
No? Why?
Yes? then How?
More explanation:
I am trying to achieve this model. The client connects to the server and
request a replica be tailored to contain specific tables/stored procedure etc
and then the data tables get populated with specific data (filtering the
data).
The "blueprint" of the replica is pure client specified. My model assume the
server has no replicas to start with. The client will ask the server to
generate the replicas
*all I am looking for is the name of the technology that would allow me to
do that if there is such thing.
Thanks
You could create the publication on the fly using SQLDMO, but there is
nothing in replication which'll do this out of the box.
The easiest method would be to could create a generic publication and have
all clients use it, and just expose the clients to their custom view of the
replica.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)