Combining XML Files

I have to combine two seperate fixed-length flat files into 1 xml file, however, the catch is the 1st flat file (header) can/will be converted to an xml file but if there is another flat file *detail) found, then we must transform that to xml and combine into the header into one xml file.

So, the standalone header xml file would look like this:
<document>
<tables>
<header>
<rows>
<row>
<field1>header file 1 info....</field1>
<field2>header file 1 info....</field2>
</row>
</rows>
</header>
</tables>
<document>

and if a detail file was found, then the xml would look like this:
<document>
<tables>
<header>
<rows>
<row>
<field1>header file 1 info....</field1>
<field2>header file 2 info....</field2>
</row>
</rows>
</header>
<detail>
<rows>
<row>
<detfield1>details file 1 info....</detfield1>
<detfield2>details file 2 info....</detfield2>
</row>
</rows>
</detail>
</tables>
<document>

Seeing how you can't easily perform any if logic in a data-flow, what would be the best way to achieve this?

JAson

Are you saying there will be more than one details file? In your example, is detfield2 really supposed to contain a value from the second detail file?

|||

sorry, I meant that there would be more than 1 filed in the details file. So,

<detfield1>details file field_1 info....</detfield1>
<detfield2>details file field_2 info....</detfield2>

The tricky part is just to figure out how to manipulate an xml file (like removing just a few end tags, then inserting another xml portion, and replace the end tags that were removed) after it has been created and written to to a destination.

|||Given that there is no "XML Destination" adapter provided with SSIS, I think you're going to have to write one yourself if your goal is to end up with an XML document. If I were in your shoes, I think I'd write a custom, managed (not script) component. I'd give it two inputs: One for the header row, and the other for the detail rows. There's a good sample in BOL that you can use to get started.

Combining 3 SQL statements

Hey all. Ive got a big problem with an sql statement Im working on.

There are 2 tables with a master/detail relationship. The Header Table
is the master, the Line Table is the detail. So for each Header, there
are many Lines, but a Line can only reference one Header.
There is a Line Total and Line Cost in each Line Record. Each Line
Record has a type.
What I want to be able to do is, for each Header, I want to Sum each
corresponding Line's Total and Cost where the type is either one value
or another. If the type is, for example, 10, only sum the Total, if its
type 2, only sum the Cost.

Therefore, after the query is executed, you should have a result set
something like this

Job : Job1 (header id)
Desc : Job0001 (header desc)
Cost : (sum of Line Costs where Line Type is 2 and header id is Job1)
Total : (sum of Line Totals where Line Type is 10 and header id is
Job1)
--------------------------------
Job : Job2 (header id)
Desc : Job0002 (header desc)
Cost : (sum of Line Costs where Line Type is 2 and header id is Job2)
Total : (sum of Line Totals where Line Type is 10 and header id is
Job2)
--------------------------------

etc.

Hope this makes sense. ThanksTry this one here:

Select
header_id,
header_desc,
SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
FROM headers
INNER JOIN
line
ON line.header_id = header.header_id

HTH, jens Suessmeyer.|||Jens (Jens@.sqlserver2005.de) writes:

> Try this one here:
> Select
> header_id,
> header_desc,
> SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
> SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
> FROM headers
> INNER JOIN
> line
> ON line.header_id = header.header_id

Better:

SELECT h.header_id, h.header_desc,
SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
FROM headers h
JOIN line ON l.header_id = h.header_id
GROUP BY h.header_id, h.header_desc

Particularly that GROUP BY clause is quite important...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You are right, thats not even better that was missing in my example.|||Is there a way to say

SUM(CASE Line_type WHEN 10 THEN costs else 0 END),
SUM(CASE Line_type WHEN NOT 10 THEN Totals else 0 END) ?|||Its OK, I figured it out.
I ended up using

SUM(CASE WHEN Line_type = 10 THEN costs else 0 END),

SUM(CASE WHEN Line_type <> 10 THEN Totals else 0 END)

THanks for the help guys :)

ColumnNamesInFirstDataRow Expression

I have a SSIS package I am trying to create that will accept two types of files. They are the same exact file except for one contains a header and one does not. So I setup a conneciton manager to the csv file. I then set a variable of bool type, and then assigned that to the ColumNamesInFirstDataRow expressions property of the conneciton manager.

So the pacakge runs. Loops a directory, runs a script that sets the Header variable to true/false based on the file name. But when it gets to the data flow it always ignores the property and never bypasses the header row. The variable is being set. I tried datarowstoskip and set it to 1 instead of the above mentioned property, and that does not work either.

How can I accomplish this?

There is a known bug in evaluation of flat file connection properties that might be causing this. IT is scheduled to be fixed for the next release. Unfortunately, I do not see an easy workaround...

-Bob

|||Is there any know work around to this with .net code or anything else?|||

You could set up two connection managers (one with headers, one without) and two dataflows to match, and use a script task to check the file for a header row. Then use precedence constraints to pick the data flow to execute.

Or you could parse the whole file in a script source, but that could be a lot of work, depending on the complexity of your file.

ColumnNamesInFirstDataRow Expression

I have a SSIS package I am trying to create that will accept two types of files. They are the same exact file except for one contains a header and one does not. So I setup a conneciton manager to the csv file. I then set a variable of bool type, and then assigned that to the ColumNamesInFirstDataRow expressions property of the conneciton manager.

So the pacakge runs. Loops a directory, runs a script that sets the Header variable to true/false based on the file name. But when it gets to the data flow it always ignores the property and never bypasses the header row. The variable is being set. I tried datarowstoskip and set it to 1 instead of the above mentioned property, and that does not work either.

How can I accomplish this?

There is a known bug in evaluation of flat file connection properties that might be causing this. IT is scheduled to be fixed for the next release. Unfortunately, I do not see an easy workaround...

-Bob

|||Is there any know work around to this with .net code or anything else?|||

You could set up two connection managers (one with headers, one without) and two dataflows to match, and use a script task to check the file for a header row. Then use precedence constraints to pick the data flow to execute.

Or you could parse the whole file in a script source, but that could be a lot of work, depending on the complexity of your file.

Column order issue

Hi
When I want to use all (or at least many) columns in a table, I'm used to
just click the "Column" header in the Object browser window and then drag it
over to the "code pane". In Query Analyzer I was use to get all the columns
in the same order as they where showed in the object browser to the left.
When using MicroSoft SQL Server Mamagement Studio, I get the columns in
alphabetically order when I drag them over, eventhough they are shown in
another order in the Object Browser window.
Does anybody know if this is a setting that can be changed somewhere?
Regards
Steen
Are you on May CTP? When I drag the columns folder to the query window, I get the columns listed in
the same order as in my CREATE TABLE statement...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.mi crosoft.com...
> Hi
> When I want to use all (or at least many) columns in a table, I'm used to just click the "Column"
> header in the Object browser window and then drag it over to the "code pane". In Query Analyzer I
> was use to get all the columns in the same order as they where showed in the object browser to the
> left. When using MicroSoft SQL Server Mamagement Studio, I get the columns in alphabetically order
> when I drag them over, eventhough they are shown in another order in the Object Browser window.
> Does anybody know if this is a setting that can be changed somewhere?
> Regards
> Steen
>
|||Hi Tibor
I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
same table into a Qury Analyser I get the columns in the same order as they
are shown.
I've just tried to get the "CREATE TABLE" from the table, and here the
columns are listed in the "correct" order which is the order they have been
typed in and not alphabetically.
Regards
Steen
Tibor Karaszi wrote:[vbcol=seagreen]
> Are you on May CTP? When I drag the columns folder to the query
> window, I get the columns listed in the same order as in my CREATE
> TABLE statement...
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.mi crosoft.com...
|||Strange. I don't have a 2000 to test against. Perhaps there is a difference. I would report this to
the beta forums if I were you...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:uMLgZp5hFHA.2680@.TK2MSFTNGSA02.privatenews.mi crosoft.com...
> Hi Tibor
> I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the same table into a Qury
> Analyser I get the columns in the same order as they are shown.
> I've just tried to get the "CREATE TABLE" from the table, and here the columns are listed in the
> "correct" order which is the order they have been typed in and not alphabetically.
> Regards
> Steen
> Tibor Karaszi wrote:
>
|||I've got the June CTP and I see the same behaviour with SSMS & a
SQL2000(SP4) database (i.e. alphabetical order). However,
interestingly, when I use SSMS to do the same with a Yukon DB, the
columns are listed in their ordinal positions.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Steen Persson (DK) wrote:

>Hi Tibor
>I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
>same table into a Qury Analyser I get the columns in the same order as they
>are shown.
>I've just tried to get the "CREATE TABLE" from the table, and here the
>columns are listed in the "correct" order which is the order they have been
>typed in and not alphabetically.
>Regards
>Steen
>Tibor Karaszi wrote:
>
>
>
|||Steen Persson (DK) (spe@.REMOVEdatea.dk) writes:
> When I want to use all (or at least many) columns in a table, I'm used
> to just click the "Column" header in the Object browser window and then
> drag it over to the "code pane". In Query Analyzer I was use to get all
> the columns in the same order as they where showed in the object browser
> to the left. When using MicroSoft SQL Server Mamagement Studio, I get
> the columns in alphabetically order when I drag them over, eventhough
> they are shown in another order in the Object Browser window.
> Does anybody know if this is a setting that can be changed somewhere?
Like Tibor, I was not able to repeat this. And I also tried against
SQL 2000.
I can't recall having seen any setting for this.
If you do this on Northwind..Orders, what do you see? I see:
OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate,
ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||OK, so I was able to repeat this on SQL 2000. And investigating the issue
further by using Profiler, I found that the bug applies to both SQL 2000 and
SQL 2005. To wit the cause is there the underlying SELECT statement does not
have any ORDER BY clause. The queries are different, but both are missing
ORDER BY.
I've filed bug FDBK32428 about this. In the bug report I left open for
both alphabetic order and column-number order, but indicated that the
latter is probably what users expects.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Column order issue

Hi
When I want to use all (or at least many) columns in a table, I'm used to
just click the "Column" header in the Object browser window and then drag it
over to the "code pane". In Query Analyzer I was use to get all the columns
in the same order as they where showed in the object browser to the left.
When using MicroSoft SQL Server Mamagement Studio, I get the columns in
alphabetically order when I drag them over, eventhough they are shown in
another order in the Object Browser window.
Does anybody know if this is a setting that can be changed somewhere?
Regards
SteenAre you on May CTP? When I drag the columns folder to the query window, I ge
t the columns listed in
the same order as in my CREATE TABLE statement...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
> Hi
> When I want to use all (or at least many) columns in a table, I'm used to
just click the "Column"
> header in the Object browser window and then drag it over to the "code pan
e". In Query Analyzer I
> was use to get all the columns in the same order as they where showed in t
he object browser to the
> left. When using MicroSoft SQL Server Mamagement Studio, I get the columns
in alphabetically order
> when I drag them over, eventhough they are shown in another order in the O
bject Browser window.
> Does anybody know if this is a setting that can be changed somewhere?
> Regards
> Steen
>|||Hi Tibor
I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
same table into a Qury Analyser I get the columns in the same order as they
are shown.
I've just tried to get the "CREATE TABLE" from the table, and here the
columns are listed in the "correct" order which is the order they have been
typed in and not alphabetically.
Regards
Steen
Tibor Karaszi wrote:[vbcol=seagreen]
> Are you on May CTP? When I drag the columns folder to the query
> window, I get the columns listed in the same order as in my CREATE
> TABLE statement...
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...|||Strange. I don't have a 2000 to test against. Perhaps there is a difference.
I would report this to
the beta forums if I were you...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:uMLgZp5hFHA.2680@.TK2MSFTNGSA02.privatenews.microsoft.com...
> Hi Tibor
> I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
same table into a Qury
> Analyser I get the columns in the same order as they are shown.
> I've just tried to get the "CREATE TABLE" from the table, and here the col
umns are listed in the
> "correct" order which is the order they have been typed in and not alphab
etically.
> Regards
> Steen
> Tibor Karaszi wrote:
>|||I've got the June CTP and I see the same behaviour with SSMS & a
SQL2000(SP4) database (i.e. alphabetical order). However,
interestingly, when I use SSMS to do the same with a Yukon DB, the
columns are listed in their ordinal positions.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Steen Persson (DK) wrote:

>Hi Tibor
>I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
>same table into a Qury Analyser I get the columns in the same order as they
>are shown.
>I've just tried to get the "CREATE TABLE" from the table, and here the
>columns are listed in the "correct" order which is the order they have bee
n
>typed in and not alphabetically.
>Regards
>Steen
>Tibor Karaszi wrote:
>
>
>|||Steen Persson (DK) (spe@.REMOVEdatea.dk) writes:
> When I want to use all (or at least many) columns in a table, I'm used
> to just click the "Column" header in the Object browser window and then
> drag it over to the "code pane". In Query Analyzer I was use to get all
> the columns in the same order as they where showed in the object browser
> to the left. When using MicroSoft SQL Server Mamagement Studio, I get
> the columns in alphabetically order when I drag them over, eventhough
> they are shown in another order in the Object Browser window.
> Does anybody know if this is a setting that can be changed somewhere?
Like Tibor, I was not able to repeat this. And I also tried against
SQL 2000.
I can't recall having seen any setting for this.
If you do this on Northwind..Orders, what do you see? I see:
OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate,
ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, so I was able to repeat this on SQL 2000. And investigating the issue
further by using Profiler, I found that the bug applies to both SQL 2000 and
SQL 2005. To wit the cause is there the underlying SELECT statement does not
have any ORDER BY clause. The queries are different, but both are missing
ORDER BY.
I've filed bug FDBK32428 about this. In the bug report I left open for
both alphabetic order and column-number order, but indicated that the
latter is probably what users expects.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Column order issue

Hi
When I want to use all (or at least many) columns in a table, I'm used to
just click the "Column" header in the Object browser window and then drag it
over to the "code pane". In Query Analyzer I was use to get all the columns
in the same order as they where showed in the object browser to the left.
When using MicroSoft SQL Server Mamagement Studio, I get the columns in
alphabetically order when I drag them over, eventhough they are shown in
another order in the Object Browser window.
Does anybody know if this is a setting that can be changed somewhere?
Regards
SteenAre you on May CTP? When I drag the columns folder to the query window, I get the columns listed in
the same order as in my CREATE TABLE statement...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
> Hi
> When I want to use all (or at least many) columns in a table, I'm used to just click the "Column"
> header in the Object browser window and then drag it over to the "code pane". In Query Analyzer I
> was use to get all the columns in the same order as they where showed in the object browser to the
> left. When using MicroSoft SQL Server Mamagement Studio, I get the columns in alphabetically order
> when I drag them over, eventhough they are shown in another order in the Object Browser window.
> Does anybody know if this is a setting that can be changed somewhere?
> Regards
> Steen
>|||Hi Tibor
I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
same table into a Qury Analyser I get the columns in the same order as they
are shown.
I've just tried to get the "CREATE TABLE" from the table, and here the
columns are listed in the "correct" order which is the order they have been
typed in and not alphabetically.
Regards
Steen
Tibor Karaszi wrote:
> Are you on May CTP? When I drag the columns folder to the query
> window, I get the columns listed in the same order as in my CREATE
> TABLE statement...
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
>> Hi
>> When I want to use all (or at least many) columns in a table, I'm
>> used to just click the "Column" header in the Object browser window
>> and then drag it over to the "code pane". In Query Analyzer I was
>> use to get all the columns in the same order as they where showed in
>> the object browser to the left. When using MicroSoft SQL Server
>> Mamagement Studio, I get the columns in alphabetically order when I
>> drag them over, eventhough they are shown in another order in the
>> Object Browser window. Does anybody know if this is a setting that can
>> be changed somewhere?
>> Regards
>> Steen|||Strange. I don't have a 2000 to test against. Perhaps there is a difference. I would report this to
the beta forums if I were you...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:uMLgZp5hFHA.2680@.TK2MSFTNGSA02.privatenews.microsoft.com...
> Hi Tibor
> I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the same table into a Qury
> Analyser I get the columns in the same order as they are shown.
> I've just tried to get the "CREATE TABLE" from the table, and here the columns are listed in the
> "correct" order which is the order they have been typed in and not alphabetically.
> Regards
> Steen
> Tibor Karaszi wrote:
>> Are you on May CTP? When I drag the columns folder to the query
>> window, I get the columns listed in the same order as in my CREATE
>> TABLE statement...
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
>> Hi
>> When I want to use all (or at least many) columns in a table, I'm
>> used to just click the "Column" header in the Object browser window
>> and then drag it over to the "code pane". In Query Analyzer I was
>> use to get all the columns in the same order as they where showed in
>> the object browser to the left. When using MicroSoft SQL Server
>> Mamagement Studio, I get the columns in alphabetically order when I
>> drag them over, eventhough they are shown in another order in the
>> Object Browser window. Does anybody know if this is a setting that can be changed somewhere?
>> Regards
>> Steen
>|||This is a multi-part message in MIME format.
--040307040208010607010408
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I've got the June CTP and I see the same behaviour with SSMS & a
SQL2000(SP4) database (i.e. alphabetical order). However,
interestingly, when I use SSMS to do the same with a Yukon DB, the
columns are listed in their ordinal positions.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Steen Persson (DK) wrote:
>Hi Tibor
>I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
>same table into a Qury Analyser I get the columns in the same order as they
>are shown.
>I've just tried to get the "CREATE TABLE" from the table, and here the
>columns are listed in the "correct" order which is the order they have been
>typed in and not alphabetically.
>Regards
>Steen
>Tibor Karaszi wrote:
>
>>Are you on May CTP? When I drag the columns folder to the query
>>window, I get the columns listed in the same order as in my CREATE
>>TABLE statement...
>>"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>>news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
>>
>>Hi
>>When I want to use all (or at least many) columns in a table, I'm
>>used to just click the "Column" header in the Object browser window
>>and then drag it over to the "code pane". In Query Analyzer I was
>>use to get all the columns in the same order as they where showed in
>>the object browser to the left. When using MicroSoft SQL Server
>>Mamagement Studio, I get the columns in alphabetically order when I
>>drag them over, eventhough they are shown in another order in the
>>Object Browser window. Does anybody know if this is a setting that can
>>be changed somewhere?
>>Regards
>>Steen
>>
>
>
--040307040208010607010408
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I've got the June CTP and I see the same behaviour with SSMS &
a SQL2000(SP4) database (i.e. alphabetical order). However,
interestingly, when I use SSMS to do the same with a Yukon DB, the
columns are listed in their ordinal positions.<br>
</tt>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Steen Persson (DK) wrote:
<blockquote
cite="miduMLgZp5hFHA.2680@.TK2MSFTNGSA02.privatenews.microsoft.com"
type="cite">
<pre wrap="">Hi Tibor
I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
same table into a Qury Analyser I get the columns in the same order as they
are shown.
I've just tried to get the "CREATE TABLE" from the table, and here the
columns are listed in the "correct" order which is the order they have been
typed in and not alphabetically.
Regards
Steen
Tibor Karaszi wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Are you on May CTP? When I drag the columns folder to the query
window, I get the columns listed in the same order as in my CREATE
TABLE statement...
"Steen Persson (DK)" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:spe@.REMOVEdatea.dk"><spe@.REMOVEdatea.dk></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com">news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi
When I want to use all (or at least many) columns in a table, I'm
used to just click the "Column" header in the Object browser window
and then drag it over to the "code pane". In Query Analyzer I was
use to get all the columns in the same order as they where showed in
the object browser to the left. When using MicroSoft SQL Server
Mamagement Studio, I get the columns in alphabetically order when I
drag them over, eventhough they are shown in another order in the
Object Browser window. Does anybody know if this is a setting that can
be changed somewhere?
Regards
Steen
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--040307040208010607010408--|||Steen Persson (DK) (spe@.REMOVEdatea.dk) writes:
> When I want to use all (or at least many) columns in a table, I'm used
> to just click the "Column" header in the Object browser window and then
> drag it over to the "code pane". In Query Analyzer I was use to get all
> the columns in the same order as they where showed in the object browser
> to the left. When using MicroSoft SQL Server Mamagement Studio, I get
> the columns in alphabetically order when I drag them over, eventhough
> they are shown in another order in the Object Browser window.
> Does anybody know if this is a setting that can be changed somewhere?
Like Tibor, I was not able to repeat this. And I also tried against
SQL 2000.
I can't recall having seen any setting for this.
If you do this on Northwind..Orders, what do you see? I see:
OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate,
ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||OK, so I was able to repeat this on SQL 2000. And investigating the issue
further by using Profiler, I found that the bug applies to both SQL 2000 and
SQL 2005. To wit the cause is there the underlying SELECT statement does not
have any ORDER BY clause. The queries are different, but both are missing
ORDER BY.
I've filed bug FDBK32428 about this. In the bug report I left open for
both alphabetic order and column-number order, but indicated that the
latter is probably what users expects.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

column heading is missing on some pdf rendered pages

I am new to RS so this might be a stupid question. Since the page header
can't have table, I have placed the table in the body as table group header.
The table contains column heading for my report. The report produces over a
hundred pages of PDF files. As I scroll through the PDF pages I noticed that
on some pages the column heading is missing. Does anyone know why or is there
a better way to do this?
Thanks,
TimThis might not be the right answer, but my impression is that where pages
break in the web report, and in the PDF exported report, are two different
things. I noticed it when I added a footer with a line to a report. THis
line would show up at the end of each page in the web report, and totally
randomly in the PDF report. And there would be a lot more text on each page
in the web report, than in the PDF. At least this goes for reports with 8pt
font and landscape view.
Try to check if what you see on each page in the PDF corresponds with what
you see in your web report.
Kaisa M. Lindahl
"Tim2Be" <Tim2Be@.discussions.microsoft.com> wrote in message
news:5660FC32-2C75-43F2-BA22-212001F7D535@.microsoft.com...
> I am new to RS so this might be a stupid question. Since the page header
> can't have table, I have placed the table in the body as table group
header.
> The table contains column heading for my report. The report produces over
a
> hundred pages of PDF files. As I scroll through the PDF pages I noticed
that
> on some pages the column heading is missing. Does anyone know why or is
there
> a better way to do this?
> Thanks,
> Tim|||My web report looks perfectly find with all the column heading in tack on all
pages. Only some of the PDF pages that is missing the column heading. I don't
know why it does that.
"Kaisa M. Lindahl" wrote:
> This might not be the right answer, but my impression is that where pages
> break in the web report, and in the PDF exported report, are two different
> things. I noticed it when I added a footer with a line to a report. THis
> line would show up at the end of each page in the web report, and totally
> randomly in the PDF report. And there would be a lot more text on each page
> in the web report, than in the PDF. At least this goes for reports with 8pt
> font and landscape view.
> Try to check if what you see on each page in the PDF corresponds with what
> you see in your web report.
> Kaisa M. Lindahl
> "Tim2Be" <Tim2Be@.discussions.microsoft.com> wrote in message
> news:5660FC32-2C75-43F2-BA22-212001F7D535@.microsoft.com...
> > I am new to RS so this might be a stupid question. Since the page header
> > can't have table, I have placed the table in the body as table group
> header.
> > The table contains column heading for my report. The report produces over
> a
> > hundred pages of PDF files. As I scroll through the PDF pages I noticed
> that
> > on some pages the column heading is missing. Does anyone know why or is
> there
> > a better way to do this?
> >
> > Thanks,
> > Tim
>
>

Column header properties gone after using URL report access

I set a border and interactive sort action properties to columns in a
report. When I render the report using a URL I lose those column header
properties. Is there a URL command to pass those settings with the URL?
(I'm using 'rs:Command=Render&rs:Format=HTML4.0' plus passing some
params)
For more description see my post at: Pagination question - retrieve
only records for each page
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/024cb61912a7630e/4e5c08587fee845a#4e5c08587fee845a
FredMy mistake. I copied and pasted the url from the previous version and
didn't update the file name in the url. I was just calling the old
version when ever I hit my 'next' button. Everything is working now.

Column Header Problems in PDF exports

Hi. I have some matrix style reports that many customers export to PDF.
Most of these are quite large files and span numerous pages. The first 3 - 4
pages of the report look great, then after that the column header is pushed
down into the first data row of the report.
Has anyone run into this or know of a workaround etc.?
Thanks in advance.this is a known microsoft bug is what i found out
"comet61" wrote:
> Hi. I have some matrix style reports that many customers export to PDF.
> Most of these are quite large files and span numerous pages. The first 3 - 4
> pages of the report look great, then after that the column header is pushed
> down into the first data row of the report.
> Has anyone run into this or know of a workaround etc.?
> Thanks in advance.

Column Header for Matrix Report

I have a matrix report, which looks like the following:

Header C Header D

Column A Column B Column C Column D

Column A and B are Row Group columns in the matrix. Column C and D are the details columns. How can I add column header to column A and B. Seems I cannot do it in Reporting Services 2005.

Fan

Yes, you can not able to achieve this with Matrices currently and we are hoping to make it possible in our next release.

Workaround: Now you can get this with placing text boxes in the corner of the Matrix which aligns to these cloumns make it give a similar look.

Column Header Color

Is there a way in SSRS to conditionally set the color of a column?

I want to do something like:

=IFF(some condition, Color(Fields!Date, "Green"), Color(Fields!Date, "Red"))

Yes you go into the Color property of the text box in the Detail row of the table and set the color to an <expression..> then in the dialog that comes up enter somthing like this...

=iif(Fields!Name.Value = "Smith", "Green", "Red")

if the name = smith make the color green other wise make it red

the key point being that you go to the property and set the property equal to an expression.

if you need more value/color options you can put in a little code in the report that you can reference from the expression.

1. right click in the little box at the top left of the report (intersection of the vertical and horizontal rulers) in the designer and choose properties.

2. select the code tab

3. type in some VB code

ex.

Public Function GetColor(ByVal sname as String) as String
IF sname = "Smith" Then
Return "Blue"
End IF
IF sname = "Jones" Then
Return "Black"
End IF
IF sname = "Woods" Then
Return "Red"
End IF
End Function

4. set the color or background property to =code.GetColor(Fields!Name.Value)

================= off topic FYI

here is another example of a little code for the report that can be used in an expression

this is used in a calulated field in a dataset =Code.GetBox(Fields!Percentile.Value,Fields!Importance.Value)

Public Function GetBox(ByVal PercentImp As Integer, ByVal Important As Integer) As Integer

Select Case Important
Case 1
Select Case PercentImp
Case 0 To 39
GetBox = 1
Case 40 To 75
GetBox = 2
Case Is > 75
GetBox = 3
End Select

Case 2
Select Case PercentImp
Case 0 To 39
GetBox = 4
Case 40 To 75
GetBox = 5
Case Is > 75
GetBox = 6
End Select
Case 3
Select Case PercentImp
Case 0 To 39
GetBox = 7
Case 40 To 75
GetBox = 8
Case Is > 75
GetBox = 9
End Select

End Select

End Function

|||You've made my day. Totally sweet. Thank you!!!

Column Header Color

Is there a way in SSRS to conditionally set the color of a column?

I want to do something like:

=IFF(some condition, Color(Fields!Date, "Green"), Color(Fields!Date, "Red"))

Yes you go into the Color property of the text box in the Detail row of the table and set the color to an <expression..> then in the dialog that comes up enter somthing like this...

=iif(Fields!Name.Value = "Smith", "Green", "Red")

if the name = smith make the color green other wise make it red

the key point being that you go to the property and set the property equal to an expression.

if you need more value/color options you can put in a little code in the report that you can reference from the expression.

1. right click in the little box at the top left of the report (intersection of the vertical and horizontal rulers) in the designer and choose properties.

2. select the code tab

3. type in some VB code

ex.

Public Function GetColor(ByVal sname as String) as String
IF sname = "Smith" Then
Return "Blue"
End IF
IF sname = "Jones" Then
Return "Black"
End IF
IF sname = "Woods" Then
Return "Red"
End IF
End Function

4. set the color or background property to =code.GetColor(Fields!Name.Value)

================= off topic FYI

here is another example of a little code for the report that can be used in an expression

this is used in a calulated field in a dataset =Code.GetBox(Fields!Percentile.Value,Fields!Importance.Value)

Public Function GetBox(ByVal PercentImp As Integer, ByVal Important As Integer) As Integer

Select Case Important
Case 1
Select Case PercentImp
Case 0 To 39
GetBox = 1
Case 40 To 75
GetBox = 2
Case Is > 75
GetBox = 3
End Select

Case 2
Select Case PercentImp
Case 0 To 39
GetBox = 4
Case 40 To 75
GetBox = 5
Case Is > 75
GetBox = 6
End Select
Case 3
Select Case PercentImp
Case 0 To 39
GetBox = 7
Case 40 To 75
GetBox = 8
Case Is > 75
GetBox = 9
End Select

End Select

End Function

|||You've made my day. Totally sweet. Thank you!!!

Column Group Row Alignment - Matrix

I just can not understand why when I add text to a group header the report displays more group row space but when I export to excel the extra space disappears....

What the....Help Microsoft please explain...

Thank You...

Any help on this would be great...

Please

Column Descriptions in Matrix

Is there a way to have a column description/header for each column in a matrix?Try setting the ToolTip property on the textbox in the column header. If it's an expression, make sure it returns a string value.