Change display of subtotal row in Matrix report

I'd like to display the subtotal and Grand total rows of my Matrix report in
grey, but I can't find a proprties box to change. I've changed the area
that displays the text ie. "Grand Total", but there are not separate boxes
for the subtotal values like you'd see with a table. I also seem to be
stuck with my subtotal text in the second column when I'd like to print it in
the first column. I'd like to see
SubTotal xxxxx
Grand Total
I can't define in the same box.
I'm really "winging it" with this Matrix report. All my other report
experiences have been with tables.
Thank you in advance for any help you can provide.... PBOn Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I'd like to display the subtotal and Grand total rows of my Matrix report in
> grey, but I can't find a proprties box to change. I've changed the area
> that displays the text ie. "Grand Total", but there are not separate boxes
> for the subtotal values like you'd see with a table. I also seem to be
> stuck with my subtotal text in the second column when I'd like to print it in
> the first column. I'd like to see
> SubTotal xxxxx
> Grand Total
> I can't define in the same box.
> I'm really "winging it" with this Matrix report. All my other report
> experiences have been with tables.
> Thank you in advance for any help you can provide.... PB
In the Layout view/tab, click on the matrix cell that has the
subtotals/grandtotal and select F4 (to get the Properties window).
Below Appearance, to the right of BackgroundColor select
'<Expressions...>' from the drop-down list and enter something like
the following:
=iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value ="Grand Total", "LightGrey", "White")
Where LightGrey is the value if true and White is the color otherwise.
If you create the subtotal in your stored procedure or query that
sources the report, you can set the column using an expression
(similar to above); however, I believe. if you use the one created in
the report, it will force you to a specific column. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thank you. You have been a great help! I have 60 Finance reports to be
created with Visual Studio and no formal training. I am so grateful for
your answers to my posts! Can you recommend any other resources/discussion
groups that would be helpful? I did purchase the book "Hitchhikers Guide to
SQL Server 2000 Reporting Services". It has been helpful with the basics,
but not with the more kind of detailed "coding" that you have been helping me
with. I'd love to have a reference for creating "code" (I've created some
.code to check for division by zero, but it's been copied from other
references) and for the ".expressions" that can be used with a field names
(ie. .substring, .value, etc.)
Regards,
PB
"EMartinez" wrote:
> On Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I'd like to display the subtotal and Grand total rows of my Matrix report in
> > grey, but I can't find a proprties box to change. I've changed the area
> > that displays the text ie. "Grand Total", but there are not separate boxes
> > for the subtotal values like you'd see with a table. I also seem to be
> > stuck with my subtotal text in the second column when I'd like to print it in
> > the first column. I'd like to see
> >
> > SubTotal xxxxx
> >
> > Grand Total
> >
> > I can't define in the same box.
> >
> > I'm really "winging it" with this Matrix report. All my other report
> > experiences have been with tables.
> >
> > Thank you in advance for any help you can provide.... PB
> In the Layout view/tab, click on the matrix cell that has the
> subtotals/grandtotal and select F4 (to get the Properties window).
> Below Appearance, to the right of BackgroundColor select
> '<Expressions...>' from the drop-down list and enter something like
> the following:
> =iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value => "Grand Total", "LightGrey", "White")
> Where LightGrey is the value if true and White is the color otherwise.
> If you create the subtotal in your stored procedure or query that
> sources the report, you can set the column using an expression
> (similar to above); however, I believe. if you use the one created in
> the report, it will force you to a specific column. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Mr Martinez will probably give you some more tips, but I just want to add a
link to William Pearson's articles on Reporting Serivces
http://www.databasejournal.com/article.php/1459531
You need to scroll down almost to the bottom, but there you find 38
tutorials. A lot of them are focused on OLAP based reports, but most of the
things you can do with OLAP based reports you can do with database based
reports as well.
Direct Reports (Brian Welcker's Weblog) at
http://blogs.msdn.com/bwelcker/default.aspx is good too. I also read Bob's
SQL Reporting Services Blog at http://blogs.msdn.com/bobmeyers/default.aspx
As for discussion groups, I usually end up with this news group, either
following it through a news reader, or searching with Google groups.
Can't give you a lot of tips on the code bit, though, haven't done too much
of that myself.
Kaisa M. Lindahl Lervik
http://blogs.spipp.net/blogs/kaisa
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:7F2266E8-5FC4-4000-B2C3-3C18641C47C3@.microsoft.com...
> Thank you. You have been a great help! I have 60 Finance reports to be
> created with Visual Studio and no formal training. I am so grateful for
> your answers to my posts! Can you recommend any other
> resources/discussion
> groups that would be helpful? I did purchase the book "Hitchhikers Guide
> to
> SQL Server 2000 Reporting Services". It has been helpful with the basics,
> but not with the more kind of detailed "coding" that you have been helping
> me
> with. I'd love to have a reference for creating "code" (I've created
> some
> .code to check for division by zero, but it's been copied from other
> references) and for the ".expressions" that can be used with a field names
> (ie. .substring, .value, etc.)
> Regards,
> PB
>
> "EMartinez" wrote:
>> On Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
>> > I'd like to display the subtotal and Grand total rows of my Matrix
>> > report in
>> > grey, but I can't find a proprties box to change. I've changed the
>> > area
>> > that displays the text ie. "Grand Total", but there are not separate
>> > boxes
>> > for the subtotal values like you'd see with a table. I also seem to
>> > be
>> > stuck with my subtotal text in the second column when I'd like to print
>> > it in
>> > the first column. I'd like to see
>> >
>> > SubTotal xxxxx
>> >
>> > Grand Total
>> >
>> > I can't define in the same box.
>> >
>> > I'm really "winging it" with this Matrix report. All my other report
>> > experiences have been with tables.
>> >
>> > Thank you in advance for any help you can provide.... PB
>> In the Layout view/tab, click on the matrix cell that has the
>> subtotals/grandtotal and select F4 (to get the Properties window).
>> Below Appearance, to the right of BackgroundColor select
>> '<Expressions...>' from the drop-down list and enter something like
>> the following:
>> =iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value =>> "Grand Total", "LightGrey", "White")
>> Where LightGrey is the value if true and White is the color otherwise.
>> If you create the subtotal in your stored procedure or query that
>> sources the report, you can set the column using an expression
>> (similar to above); however, I believe. if you use the one created in
>> the report, it will force you to a specific column. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>|||On Mar 14, 8:04 am, "Kaisa M. Lindahl Lervik" <kais...@.hotmail.com>
wrote:
> Mr Martinez will probably give you some more tips, but I just want to add a
> link to William Pearson's articles on Reporting Serivces
> http://www.databasejournal.com/article.php/1459531
> You need to scroll down almost to the bottom, but there you find 38
> tutorials. A lot of them are focused on OLAP based reports, but most of the
> things you can do with OLAP based reports you can do with database based
> reports as well.
> Direct Reports (Brian Welcker's Weblog) athttp://blogs.msdn.com/bwelcker/default.aspxis good too. I also read Bob's
> SQL Reporting Services Blog athttp://blogs.msdn.com/bobmeyers/default.aspx
> As for discussion groups, I usually end up with this news group, either
> following it through a news reader, or searching with Google groups.
> Can't give you a lot of tips on the code bit, though, haven't done too much
> of that myself.
> Kaisa M. Lindahl Lervikhttp://blogs.spipp.net/blogs/kaisa
> "ppbedz" <ppb...@.discussions.microsoft.com> wrote in message
> news:7F2266E8-5FC4-4000-B2C3-3C18641C47C3@.microsoft.com...
> > Thank you. You have been a great help! I have 60 Finance reports to be
> > created with Visual Studio and no formal training. I am so grateful for
> > your answers to my posts! Can you recommend any other
> > resources/discussion
> > groups that would be helpful? I did purchase the book "Hitchhikers Guide
> > to
> > SQL Server 2000 Reporting Services". It has been helpful with the basics,
> > but not with the more kind of detailed "coding" that you have been helping
> > me
> > with. I'd love to have a reference for creating "code" (I've created
> > some
> > .code to check for division by zero, but it's been copied from other
> > references) and for the ".expressions" that can be used with a field names
> > (ie. .substring, .value, etc.)
> > Regards,
> > PB
> > "EMartinez" wrote:
> >> On Mar 13, 1:44 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> >> > I'd like to display the subtotal and Grand total rows of my Matrix
> >> > report in
> >> > grey, but I can't find a proprties box to change. I've changed the
> >> > area
> >> > that displays the text ie. "Grand Total", but there are not separate
> >> > boxes
> >> > for the subtotal values like you'd see with a table. I also seem to
> >> > be
> >> > stuck with my subtotal text in the second column when I'd like to print
> >> > it in
> >> > the first column. I'd like to see
> >> > SubTotal xxxxx
> >> > Grand Total
> >> > I can't define in the same box.
> >> > I'm really "winging it" with this Matrix report. All my other report
> >> > experiences have been with tables.
> >> > Thank you in advance for any help you can provide.... PB
> >> In the Layout view/tab, click on the matrix cell that has the
> >> subtotals/grandtotal and select F4 (to get the Properties window).
> >> Below Appearance, to the right of BackgroundColor select
> >> '<Expressions...>' from the drop-down list and enter something like
> >> the following:
> >> =iif(Fields!FieldName.Value = "Subtotal" or Fields!FieldName.Value => >> "Grand Total", "LightGrey", "White")
> >> Where LightGrey is the value if true and White is the color otherwise.
> >> If you create the subtotal in your stored procedure or query that
> >> sources the report, you can set the column using an expression
> >> (similar to above); however, I believe. if you use the one created in
> >> the report, it will force you to a specific column. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. SQL Server Developer
You are very welcome. Glad to be of assistance. I can email you a kind
of 'how tos' list (that I have created along the way), if you would
like. This news group is the most active one (for Reporting Services),
so it usually gets the most responses and assistance. A couple of
lower activity ones are: http://groups.google.com/group/ReportingServices
and http://groups.google.com/group/RS2005. I tend to help out on those
as well. These Microsoft links are pretty handy (but not too easy to
navigate): http://msdn2.microsoft.com/en-us/library/ms159106.aspx and
http://msdn2.microsoft.com/en-us/library/ms170246.aspx. Also, if you
know of anyone that would be interested, I will be offering a SQL
Server 2005 Reporting Services class in the Nashville, TN area
starting the end of April.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Hi all!
I'm using the function SubToal built in RS.
How can I change the row's background for the row's handling the subtotals
infos?
Is there a way to find the value of a textboxX instead of the value of a
field?
Thanks, Julien
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:66E08C5E-4E81-49D4-9FA8-81616926508F@.microsoft.com...
> I'd like to display the subtotal and Grand total rows of my Matrix report
> in
> grey, but I can't find a proprties box to change. I've changed the area
> that displays the text ie. "Grand Total", but there are not separate
> boxes
> for the subtotal values like you'd see with a table. I also seem to be
> stuck with my subtotal text in the second column when I'd like to print it
> in
> the first column. I'd like to see
> SubTotal xxxxx
> Grand Total
> I can't define in the same box.
> I'm really "winging it" with this Matrix report. All my other report
> experiences have been with tables.
> Thank you in advance for any help you can provide.... PB|||Hi Julien,
I had the same problem and I solved it using the InScope() function.
You will find an example on this blog :
http://devauthority.com/blogs/chrisslatt/archive/2005/07/06/Reporting_Service_Using_InScope_to_do_custom_subtotals.aspx
Best regards,
Jean-Baptiste
"Julien Bonnier" wrote:
> Hi all!
> I'm using the function SubToal built in RS.
> How can I change the row's background for the row's handling the subtotals
> infos?
> Is there a way to find the value of a textboxX instead of the value of a
> field?
> Thanks, Julien
>
> "ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
> news:66E08C5E-4E81-49D4-9FA8-81616926508F@.microsoft.com...
> > I'd like to display the subtotal and Grand total rows of my Matrix report
> > in
> > grey, but I can't find a proprties box to change. I've changed the area
> > that displays the text ie. "Grand Total", but there are not separate
> > boxes
> > for the subtotal values like you'd see with a table. I also seem to be
> > stuck with my subtotal text in the second column when I'd like to print it
> > in
> > the first column. I'd like to see
> >
> > SubTotal xxxxx
> >
> > Grand Total
> >
> > I can't define in the same box.
> >
> > I'm really "winging it" with this Matrix report. All my other report
> > experiences have been with tables.
> >
> > Thank you in advance for any help you can provide.... PB
>
>|||FABOULOUS!!!
Thanks alot! I'm gonna add this to all my reports!
Thanks again!
Julien
"jbb92" <jbb92@.discussions.microsoft.com> wrote in message
news:905BD939-9C3E-4EBE-B002-7FFBC064752B@.microsoft.com...
> Hi Julien,
> I had the same problem and I solved it using the InScope() function.
> You will find an example on this blog :
> http://devauthority.com/blogs/chrisslatt/archive/2005/07/06/Reporting_Service_Using_InScope_to_do_custom_subtotals.aspx
> Best regards,
> Jean-Baptiste
> "Julien Bonnier" wrote:
>> Hi all!
>> I'm using the function SubToal built in RS.
>> How can I change the row's background for the row's handling the
>> subtotals
>> infos?
>> Is there a way to find the value of a textboxX instead of the value of a
>> field?
>> Thanks, Julien
>>
>> "ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
>> news:66E08C5E-4E81-49D4-9FA8-81616926508F@.microsoft.com...
>> > I'd like to display the subtotal and Grand total rows of my Matrix
>> > report
>> > in
>> > grey, but I can't find a proprties box to change. I've changed the
>> > area
>> > that displays the text ie. "Grand Total", but there are not separate
>> > boxes
>> > for the subtotal values like you'd see with a table. I also seem to
>> > be
>> > stuck with my subtotal text in the second column when I'd like to print
>> > it
>> > in
>> > the first column. I'd like to see
>> >
>> > SubTotal xxxxx
>> >
>> > Grand Total
>> >
>> > I can't define in the same box.
>> >
>> > I'm really "winging it" with this Matrix report. All my other report
>> > experiences have been with tables.
>> >
>> > Thank you in advance for any help you can provide.... PB
>>

Change date with trigger

I create this trigger, but it change all rows. I change only the rows that I insert or update. How can I this?
CREATE TRIGGER [datep] ON [prueba]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE prueba SET datepm = getdate()
FROM inserted i
END

Quote:

Originally posted by strellita
I create this trigger, but it change all rows. I change only the rows that I insert or update. How can I this?
CREATE TRIGGER [datep] ON [prueba]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE prueba SET datepm = getdate()
FROM inserted i
END

Needs:
INNER JOIN i.[id column] = preuba.[id column]
after your FROM statement
then it will work correctly.
Full example:
CREATE TRIGGER [datep] ON [prueba]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE p SET datepm = getdate()
FROM inserted i
INNER JOIN preueba p ON i.[ID] = p.[ID]
END
GO
Peace,
tree

Change color of alternating rows

Please help. I need to distinguish blocks of information on some reports and
I need to be able to altenate the colors.
Please advise.
Thank you.Use RowNumber(scope) function to determine the report row is odd or even and
then set its backcolor property accordingly (e.g. set report detail row's
BackColor property to an Expression, like:
=IIF(RowNumber(Nothing) Mod 2=0, "Silver","White")
Note, if your report has different groups, you need to pass a correct value
to the "scope" argument of RowNumber() function.
"assistanceappreciated" <assistanceappreciated@.discussions.microsoft.com>
wrote in message news:E63FD05E-86E1-486A-A0FF-5B90AAB8DFED@.microsoft.com...
> Please help. I need to distinguish blocks of information on some reports
> and
> I need to be able to altenate the colors.
> Please advise.
> Thank you.|||Thanks so very much.
"Norman Yuan" wrote:
> Use RowNumber(scope) function to determine the report row is odd or even and
> then set its backcolor property accordingly (e.g. set report detail row's
> BackColor property to an Expression, like:
> =IIF(RowNumber(Nothing) Mod 2=0, "Silver","White")
> Note, if your report has different groups, you need to pass a correct value
> to the "scope" argument of RowNumber() function.
>
> "assistanceappreciated" <assistanceappreciated@.discussions.microsoft.com>
> wrote in message news:E63FD05E-86E1-486A-A0FF-5B90AAB8DFED@.microsoft.com...
> > Please help. I need to distinguish blocks of information on some reports
> > and
> > I need to be able to altenate the colors.
> > Please advise.
> >
> > Thank you.
>
>

Change Capitalization in Sql Table Column

I have some data that was entered in the DB in ALL CAPS. I would like to reformat all the rows so that it follows the normal "Capitalization". What is the best way to do this? Shoud I create a console application to accomplish this or is there some way to do it natively in sql server 2005.

Here's a script that converts a given string to proper case, but you'd somehow need to incorporate that into another routine that runs through all of your tables.

http://vyaskn.tripod.com/code/propercase.txt

|||

This is a good example but will work very slow ( it process word char by char) and it will not work with special cases like McDonald ,DaVinci, VanDerVals and so on. The better way is to split words by spaces and next process each word separately with specific logic which will take care about some or all possible scenarios.

But it will be very big project.

Challanging task, for fun - answer two questions from given data!

Hi, I'm looking for the answer to these questions.
1. How many rows contained in table PERSON
2. How many rows contained in teble CAR
ONLY by reading information given. No DB's is needed!
Following table contain info about persons. Table do contain a lot of
rows!
CREATE TABLE Person
{
PersonID int NOT NULL IDENTITY(1,1)
PersNumber char(11) NOT NULL,
Name1 varchar(50) NOT NULL,
Name2 varchar(50) NOT NULL,
ShoeSize int NOT NULL,
Address varchar(50) NOT NULL,
Zip varchar(10) NOT NULL,
City varchar(50) NOT NULL
}
Further more, this table containg cars connected to persons in table
above.
CREATE TABLE Car
{
CarID int NOT NULL IDENTITY(1,1),
RegNr varchar(8) NOT NULL,
PersonID int NULL
}
Following SELECT statements are executed:
SELECT *
FROM Person P JOIN Car C ON P.PersonID = C.PersonID
(1037854 rows is affected)
SELECT PersNumber, COUNT(*)
FROM Person P JOIN Car C ON P.PersonID = C.PersonID
GROUP BY PersNumber
HAVING COUNT(*) > 1
(132892 rows are affected)
SELECT PersNumber, COUNT(*)
FROM Person P JOIN Car C ON P.PersonID = C.PersonID
GROUP BY PersNumber
HAVING COUNT(*) > 2
(0 rows are affected)
SELECT COUNT(DISTINCT P. PersonID), COUNT (DISTINCT C.CarID)
FROM Person P FULL OUTER JOIN Car C ON P.PersonID = C.PersonID
WHERE C.CarID IS NULL OR P.PersonID IS NULL
-- --
198898 114388
(1 rows are affected)
Now...the answers.. GOOD LUCK!!! :)
1. How many rows contained in table PERSON
2. How many rows contained in teble CAR
Thanks to all gurus taking time solving this. Please, if you know this
- try to explain your solution!
Thanks again!!
/Markthere are 1037854 cars belonging to a person
SELECT *
FROM Person P JOIN Car C ON P.PersonID = C.PersonID
(1037854 rows is affected)
and 114388 cars not belonging to anyone
total cars 1037854 + 114388
Similarly, there are (1037854 - 132892 - 0) persons having cars, and
198898 persons without cars
HIH

chained rows

Hi ,
How to find changed rows in sql server
Thanks
AjayYou cannot by default, unless you have some kind of auditing in place.
Auditing can be implemented using triggers.
If you have a datetime column in your table, and if you make sure that gets
updated with every change, then you can find the updated rows based on that
column as well.
Another option is to use a tool like Lumigent Log Explorer to look at the
transaction log of SQL Server database.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23vzexOvaEHA.4032@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> How to find changed rows in sql server
>
> Thanks
> Ajay
>|||Hi ,
Thanks for the reply sir ,
I learned many good things from your website .
One more doubt please is forwarded row count is similar to the chained rows
& which is not good for performance .
Please give some more details
Thanks & Regards
Ajay
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uxjXu7vaEHA.212@.TK2MSFTNGP12.phx.gbl...
> You cannot by default, unless you have some kind of auditing in place.
> Auditing can be implemented using triggers.
> If you have a datetime column in your table, and if you make sure that
gets
> updated with every change, then you can find the updated rows based on
that
> column as well.
> Another option is to use a tool like Lumigent Log Explorer to look at the
> transaction log of SQL Server database.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23vzexOvaEHA.4032@.TK2MSFTNGP11.phx.gbl...
> > Hi ,
> > How to find changed rows in sql server
> >
> >
> > Thanks
> > Ajay
> >
> >
>|||I am not sure I understand your question.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23orAyFwaEHA.3332@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Thanks for the reply sir ,
> I learned many good things from your website .
> One more doubt please is forwarded row count is similar to the chained
rows
> & which is not good for performance .
> Please give some more details
> Thanks & Regards
> Ajay
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uxjXu7vaEHA.212@.TK2MSFTNGP12.phx.gbl...
> > You cannot by default, unless you have some kind of auditing in place.
> > Auditing can be implemented using triggers.
> >
> > If you have a datetime column in your table, and if you make sure that
> gets
> > updated with every change, then you can find the updated rows based on
> that
> > column as well.
> >
> > Another option is to use a tool like Lumigent Log Explorer to look at
the
> > transaction log of SQL Server database.
> >
> > --
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> >
> >
> > "Aju" <ajuonline@.yahoo.com> wrote in message
> > news:%23vzexOvaEHA.4032@.TK2MSFTNGP11.phx.gbl...
> > > Hi ,
> > > How to find changed rows in sql server
> > >
> > >
> > > Thanks
> > > Ajay
> > >
> > >
> >
> >
>|||Hi ,
Since my previous experience is into oracle I used the word chained rows
here . My question when a row doesn't get space in a page it may migrate to
some other leaving pointer in older one , that way for small no. of rows
query has to access many pages . So how to avoid this row migration if any
in the database .
Thanks
R. Ajay
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O2MVwTwaEHA.2812@.tk2msftngp13.phx.gbl...
> I am not sure I understand your question.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23orAyFwaEHA.3332@.TK2MSFTNGP09.phx.gbl...
> > Hi ,
> > Thanks for the reply sir ,
> > I learned many good things from your website .
> >
> > One more doubt please is forwarded row count is similar to the chained
> rows
> > & which is not good for performance .
> >
> > Please give some more details
> >
> > Thanks & Regards
> > Ajay
> >
> >
> >
> > "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> > news:uxjXu7vaEHA.212@.TK2MSFTNGP12.phx.gbl...
> > > You cannot by default, unless you have some kind of auditing in place.
> > > Auditing can be implemented using triggers.
> > >
> > > If you have a datetime column in your table, and if you make sure that
> > gets
> > > updated with every change, then you can find the updated rows based on
> > that
> > > column as well.
> > >
> > > Another option is to use a tool like Lumigent Log Explorer to look at
> the
> > > transaction log of SQL Server database.
> > >
> > > --
> > > Vyas, MVP (SQL Server)
> > > http://vyaskn.tripod.com/
> > >
> > >
> > > "Aju" <ajuonline@.yahoo.com> wrote in message
> > > news:%23vzexOvaEHA.4032@.TK2MSFTNGP11.phx.gbl...
> > > > Hi ,
> > > > How to find changed rows in sql server
> > > >
> > > >
> > > > Thanks
> > > > Ajay
> > > >
> > > >
> > >
> > >
> >
> >
>|||SQL Server will allow a maximum row size of 8,060 bytes only.
If you are talking abut fragmented tables, see DBCC DBREINDEX, DBCC
INDEXDEFRAG, and DBCC SHOWCONTIG in SQL Sercer 2000 Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ujOlOXxaEHA.2932@.TK2MSFTNGP10.phx.gbl...
Hi ,
Since my previous experience is into oracle I used the word chained rows
here . My question when a row doesn't get space in a page it may migrate to
some other leaving pointer in older one , that way for small no. of rows
query has to access many pages . So how to avoid this row migration if any
in the database .
Thanks
R. Ajay
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O2MVwTwaEHA.2812@.tk2msftngp13.phx.gbl...
> I am not sure I understand your question.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23orAyFwaEHA.3332@.TK2MSFTNGP09.phx.gbl...
> > Hi ,
> > Thanks for the reply sir ,
> > I learned many good things from your website .
> >
> > One more doubt please is forwarded row count is similar to the chained
> rows
> > & which is not good for performance .
> >
> > Please give some more details
> >
> > Thanks & Regards
> > Ajay
> >
> >
> >
> > "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> > news:uxjXu7vaEHA.212@.TK2MSFTNGP12.phx.gbl...
> > > You cannot by default, unless you have some kind of auditing in place.
> > > Auditing can be implemented using triggers.
> > >
> > > If you have a datetime column in your table, and if you make sure that
> > gets
> > > updated with every change, then you can find the updated rows based on
> > that
> > > column as well.
> > >
> > > Another option is to use a tool like Lumigent Log Explorer to look at
> the
> > > transaction log of SQL Server database.
> > >
> > > --
> > > Vyas, MVP (SQL Server)
> > > http://vyaskn.tripod.com/
> > >
> > >
> > > "Aju" <ajuonline@.yahoo.com> wrote in message
> > > news:%23vzexOvaEHA.4032@.TK2MSFTNGP11.phx.gbl...
> > > > Hi ,
> > > > How to find changed rows in sql server
> > > >
> > > >
> > > > Thanks
> > > > Ajay
> > > >
> > > >
> > >
> > >
> >
> >
>|||SQL server will only leave forwarding pointers on tables which have no
clustered index. These forwarding pointers will only be indirected one
level, even if the row changes pages multiple times.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23vzexOvaEHA.4032@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> How to find changed rows in sql server
>
> Thanks
> Ajay
>

chained rows

Hi ,
How to find changed rows in sql server
Thanks
Ajay
You cannot by default, unless you have some kind of auditing in place.
Auditing can be implemented using triggers.
If you have a datetime column in your table, and if you make sure that gets
updated with every change, then you can find the updated rows based on that
column as well.
Another option is to use a tool like Lumigent Log Explorer to look at the
transaction log of SQL Server database.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23vzexOvaEHA.4032@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> How to find changed rows in sql server
>
> Thanks
> Ajay
>
|||Hi ,
Thanks for the reply sir ,
I learned many good things from your website .
One more doubt please is forwarded row count is similar to the chained rows
& which is not good for performance .
Please give some more details
Thanks & Regards
Ajay
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uxjXu7vaEHA.212@.TK2MSFTNGP12.phx.gbl...
> You cannot by default, unless you have some kind of auditing in place.
> Auditing can be implemented using triggers.
> If you have a datetime column in your table, and if you make sure that
gets
> updated with every change, then you can find the updated rows based on
that
> column as well.
> Another option is to use a tool like Lumigent Log Explorer to look at the
> transaction log of SQL Server database.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23vzexOvaEHA.4032@.TK2MSFTNGP11.phx.gbl...
>
|||I am not sure I understand your question.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23orAyFwaEHA.3332@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Thanks for the reply sir ,
> I learned many good things from your website .
> One more doubt please is forwarded row count is similar to the chained
rows[vbcol=seagreen]
> & which is not good for performance .
> Please give some more details
> Thanks & Regards
> Ajay
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uxjXu7vaEHA.212@.TK2MSFTNGP12.phx.gbl...
> gets
> that
the
>
|||Hi ,
Since my previous experience is into oracle I used the word chained rows
here . My question when a row doesn't get space in a page it may migrate to
some other leaving pointer in older one , that way for small no. of rows
query has to access many pages . So how to avoid this row migration if any
in the database .
Thanks
R. Ajay
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O2MVwTwaEHA.2812@.tk2msftngp13.phx.gbl...
> I am not sure I understand your question.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23orAyFwaEHA.3332@.TK2MSFTNGP09.phx.gbl...
> rows
> the
>
|||SQL Server will allow a maximum row size of 8,060 bytes only.
If you are talking abut fragmented tables, see DBCC DBREINDEX, DBCC
INDEXDEFRAG, and DBCC SHOWCONTIG in SQL Sercer 2000 Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ujOlOXxaEHA.2932@.TK2MSFTNGP10.phx.gbl...
Hi ,
Since my previous experience is into oracle I used the word chained rows
here . My question when a row doesn't get space in a page it may migrate to
some other leaving pointer in older one , that way for small no. of rows
query has to access many pages . So how to avoid this row migration if any
in the database .
Thanks
R. Ajay
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O2MVwTwaEHA.2812@.tk2msftngp13.phx.gbl...
> I am not sure I understand your question.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23orAyFwaEHA.3332@.TK2MSFTNGP09.phx.gbl...
> rows
> the
>
|||SQL server will only leave forwarding pointers on tables which have no
clustered index. These forwarding pointers will only be indirected one
level, even if the row changes pages multiple times.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23vzexOvaEHA.4032@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> How to find changed rows in sql server
>
> Thanks
> Ajay
>

chained rows

Hi ,
How to find changed rows in sql server
Thanks
AjayYou cannot by default, unless you have some kind of auditing in place.
Auditing can be implemented using triggers.
If you have a datetime column in your table, and if you make sure that gets
updated with every change, then you can find the updated rows based on that
column as well.
Another option is to use a tool like Lumigent Log Explorer to look at the
transaction log of SQL Server database.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23vzexOvaEHA.4032@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> How to find changed rows in sql server
>
> Thanks
> Ajay
>|||Hi ,
Thanks for the reply sir ,
I learned many good things from your website .
One more doubt please is forwarded row count is similar to the chained rows
& which is not good for performance .
Please give some more details
Thanks & Regards
Ajay
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uxjXu7vaEHA.212@.TK2MSFTNGP12.phx.gbl...
> You cannot by default, unless you have some kind of auditing in place.
> Auditing can be implemented using triggers.
> If you have a datetime column in your table, and if you make sure that
gets
> updated with every change, then you can find the updated rows based on
that
> column as well.
> Another option is to use a tool like Lumigent Log Explorer to look at the
> transaction log of SQL Server database.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23vzexOvaEHA.4032@.TK2MSFTNGP11.phx.gbl...
>|||I am not sure I understand your question.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23orAyFwaEHA.3332@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Thanks for the reply sir ,
> I learned many good things from your website .
> One more doubt please is forwarded row count is similar to the chained
rows
> & which is not good for performance .
> Please give some more details
> Thanks & Regards
> Ajay
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uxjXu7vaEHA.212@.TK2MSFTNGP12.phx.gbl...
> gets
> that
the[vbcol=seagreen]
>|||Hi ,
Since my previous experience is into oracle I used the word chained rows
here . My question when a row doesn't get space in a page it may migrate to
some other leaving pointer in older one , that way for small no. of rows
query has to access many pages . So how to avoid this row migration if any
in the database .
Thanks
R. Ajay
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O2MVwTwaEHA.2812@.tk2msftngp13.phx.gbl...
> I am not sure I understand your question.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23orAyFwaEHA.3332@.TK2MSFTNGP09.phx.gbl...
> rows
> the
>|||SQL Server will allow a maximum row size of 8,060 bytes only.
If you are talking abut fragmented tables, see DBCC DBREINDEX, DBCC
INDEXDEFRAG, and DBCC SHOWCONTIG in SQL Sercer 2000 Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Aju" <ajuonline@.yahoo.com> wrote in message
news:ujOlOXxaEHA.2932@.TK2MSFTNGP10.phx.gbl...
Hi ,
Since my previous experience is into oracle I used the word chained rows
here . My question when a row doesn't get space in a page it may migrate to
some other leaving pointer in older one , that way for small no. of rows
query has to access many pages . So how to avoid this row migration if any
in the database .
Thanks
R. Ajay
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:O2MVwTwaEHA.2812@.tk2msftngp13.phx.gbl...
> I am not sure I understand your question.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23orAyFwaEHA.3332@.TK2MSFTNGP09.phx.gbl...
> rows
> the
>|||SQL server will only leave forwarding pointers on tables which have no
clustered index. These forwarding pointers will only be indirected one
level, even if the row changes pages multiple times.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23vzexOvaEHA.4032@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> How to find changed rows in sql server
>
> Thanks
> Ajay
>

Certain rows to excel files

Hello,

I have a table T1 with ID and Desc fields. I want to export this table to excel files based on the ID field. So my stored procedure select all the same T1.ID and export it to ID.xls files. How can I do this?

You can easily achieve this in DTS Import/Export Wizard. Suppose you're using SQL2000, open Enterprise Manager->go to the table from which you want to export data->right click choose All Tasks-> Export Data-> in the Specify Table Copy or Query step, choose Use a Query...->enter the SELECT command (e.g. SELECT * FROM Orders WHERE EmployeeID=6), or use Query Builder->complete the wizard.

|||

Thanks for the reply, How should I run the same queries for all the IDs separately in DTS and save the results to excel files?

certain rows to Excel files

Hello,

I have a table T1 with ID and Desc fields. I want to export this table to excel files based on the ID field. So my stored procedure select all the same T1.ID and export it to ID.xls files. How can I do this?

Hi,

While Using DTS Import/Export Wizard,
After Choosing Data Source and DataDestination,
Wizard asks to Specify Table Copy or Query,
At the Time Select the Radio Button [Use a Query to Specify the Data To Transfer].

It will Take you To Type Sql Statement where Query Statement Panel is there ,you can Type Query with your Condition.

|||

Thanks for the reply.

This should be done once every day , so I am trying to automate it. How should I develop my query and dts based on this criteria? Any example will be greatly appreciated.

|||

Select Jobs From SQL Server Agent Under Management of the Selected Server(In EnterpriseManager)

Select New Job and Name it
Select Steps Tab in New Job Properties
Click New step and Name Step and Write Query in Command Panel
Click New Schedule and Name Schedule and Select Schedule Type

Now you Achieve the Required Thing

|||

I still do not see how the query that I am writing will export data to a different excel file based on the ID. All the rows that have the same ID should go to a single excel file, so I am expecting more than one excel file based on the distinct values of ID to be created. How can I do this in DTS?

Certain rows to excel files

Hello,
I have a table T1 with ID and Desc fields. I want to export this table to
excel files based on the ID field. So my stored procedure select all the sam
e
T1.ID and export it to ID.xls files. How can I do this?Jim,
is this a one-off? If so I'd use the import/export wizard. If it is a
regular process, I'd look at using a scheduled DTS package (you can use the
import/export one as a template for this). There are other options namely
BCP and OSQL which run from the command-line which might also be relevant
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul,
This should be done once every day , so I am trying to automate it. How
should I develop my query and dts based on this criteria? Any example will b
e
greatly appreciated.
"Paul Ibison" wrote:

> Jim,
> is this a one-off? If so I'd use the import/export wizard. If it is a
> regular process, I'd look at using a scheduled DTS package (you can use th
e
> import/export one as a template for this). There are other options namely
> BCP and OSQL which run from the command-line which might also be relevant
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Jim,
is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
you'll have a single 'Transform data' task for each ID and the destination
will be also hardcoded in this case.
If this is not the case, then I'd have a lookup table which contains the
ID/path as a pair of columns. You'll need to then iterate through that
table. Each iteration could call the package, sending in the 2 details as
global variables. These global variables will be used to modify the package
to run with the correct ID values. This is done through the Dynamic
Properties task, which would be mapped to the excel file path and the
parameter used for the stored proc.
I realize this sounds complicated, but it's actually quite easy to set up.
The main issue is whether the ID's are known in advance or not. If they are,
then it is quite simple.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This table has IDs and Desc already in it. IDs might be duplicated and I am
trying to export the rows with the same IDs to a single excel file, so
expecting many excel files based on the distinct values of IDs. DTS seems
quite complicated to me since I have not done anything with that yet.
"Paul Ibison" wrote:

> Jim,
> is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
> you'll have a single 'Transform data' task for each ID and the destination
> will be also hardcoded in this case.
> If this is not the case, then I'd have a lookup table which contains the
> ID/path as a pair of columns. You'll need to then iterate through that
> table. Each iteration could call the package, sending in the 2 details as
> global variables. These global variables will be used to modify the packag
e
> to run with the correct ID values. This is done through the Dynamic
> Properties task, which would be mapped to the excel file path and the
> parameter used for the stored proc.
> I realize this sounds complicated, but it's actually quite easy to set up.
> The main issue is whether the ID's are known in advance or not. If they ar
e,
> then it is quite simple.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>|||JIM.H. wrote:
> This table has IDs and Desc already in it. IDs might be duplicated and I a
m
> trying to export the rows with the same IDs to a single excel file, so
> expecting many excel files based on the distinct values of IDs. DTS seems
> quite complicated to me since I have not done anything with that yet.
>
Jim, here is a link to a message thread that describes how to use a
template XLS file, some VBScript, and OPENROWSET to create a new Excel
document and export data to it, without using DTS. Might prove useful
to you:
http://www.sqlteam.com/forums/topic...926&whichpage=6
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Jim,
if you know all the IDs in advance, then you could hardcode these into the
DTS package as data flow tasks and this would be surprisingly easy (use the
export wizard to create the main template to see how it works), or if you
are more familiar with BCP, you could use a separate bcp commandline for
each excel file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Great. Thanks Tracy.
"Tracy McKibben" wrote:

> JIM.H. wrote:
> Jim, here is a link to a message thread that describes how to use a
> template XLS file, some VBScript, and OPENROWSET to create a new Excel
> document and export data to it, without using DTS. Might prove useful
> to you:
> http://www.sqlteam.com/forums/topic...926&whichpage=6
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Certain rows to excel files

Hello,
I have a table T1 with ID and Desc fields. I want to export this table to
excel files based on the ID field. So my stored procedure select all the same
T1.ID and export it to ID.xls files. How can I do this?Jim,
is this a one-off? If so I'd use the import/export wizard. If it is a
regular process, I'd look at using a scheduled DTS package (you can use the
import/export one as a template for this). There are other options namely
BCP and OSQL which run from the command-line which might also be relevant
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul,
This should be done once every day , so I am trying to automate it. How
should I develop my query and dts based on this criteria? Any example will be
greatly appreciated.
"Paul Ibison" wrote:
> Jim,
> is this a one-off? If so I'd use the import/export wizard. If it is a
> regular process, I'd look at using a scheduled DTS package (you can use the
> import/export one as a template for this). There are other options namely
> BCP and OSQL which run from the command-line which might also be relevant
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Jim,
is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
you'll have a single 'Transform data' task for each ID and the destination
will be also hardcoded in this case.
If this is not the case, then I'd have a lookup table which contains the
ID/path as a pair of columns. You'll need to then iterate through that
table. Each iteration could call the package, sending in the 2 details as
global variables. These global variables will be used to modify the package
to run with the correct ID values. This is done through the Dynamic
Properties task, which would be mapped to the excel file path and the
parameter used for the stored proc.
I realize this sounds complicated, but it's actually quite easy to set up.
The main issue is whether the ID's are known in advance or not. If they are,
then it is quite simple.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This table has IDs and Desc already in it. IDs might be duplicated and I am
trying to export the rows with the same IDs to a single excel file, so
expecting many excel files based on the distinct values of IDs. DTS seems
quite complicated to me since I have not done anything with that yet.
"Paul Ibison" wrote:
> Jim,
> is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
> you'll have a single 'Transform data' task for each ID and the destination
> will be also hardcoded in this case.
> If this is not the case, then I'd have a lookup table which contains the
> ID/path as a pair of columns. You'll need to then iterate through that
> table. Each iteration could call the package, sending in the 2 details as
> global variables. These global variables will be used to modify the package
> to run with the correct ID values. This is done through the Dynamic
> Properties task, which would be mapped to the excel file path and the
> parameter used for the stored proc.
> I realize this sounds complicated, but it's actually quite easy to set up.
> The main issue is whether the ID's are known in advance or not. If they are,
> then it is quite simple.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>|||JIM.H. wrote:
> This table has IDs and Desc already in it. IDs might be duplicated and I am
> trying to export the rows with the same IDs to a single excel file, so
> expecting many excel files based on the distinct values of IDs. DTS seems
> quite complicated to me since I have not done anything with that yet.
>
Jim, here is a link to a message thread that describes how to use a
template XLS file, some VBScript, and OPENROWSET to create a new Excel
document and export data to it, without using DTS. Might prove useful
to you:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=6
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Jim,
if you know all the IDs in advance, then you could hardcode these into the
DTS package as data flow tasks and this would be surprisingly easy (use the
export wizard to create the main template to see how it works), or if you
are more familiar with BCP, you could use a separate bcp commandline for
each excel file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Great. Thanks Tracy.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > This table has IDs and Desc already in it. IDs might be duplicated and I am
> > trying to export the rows with the same IDs to a single excel file, so
> > expecting many excel files based on the distinct values of IDs. DTS seems
> > quite complicated to me since I have not done anything with that yet.
> >
> Jim, here is a link to a message thread that describes how to use a
> template XLS file, some VBScript, and OPENROWSET to create a new Excel
> document and export data to it, without using DTS. Might prove useful
> to you:
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=6
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Cell Spanning Multiple Rows

I have a report where the first column needs to be spanned over multiple rows. The first column is part of a group, just the group name is shown on the detail row with the details. The data looked like this:
Group1 Detail 1
Group1 Detail 2
Group1 Detail 3
Group2 Detail 1
Group2 Detail 2
But I dont want the first column to repeat so it looks like this:
Group1 Detail 1
Detail 2
Detail 3
Group2 Detail 1
Detail 2
I tried to use the "Hide Duplicates" option on the group column textbox, but the borders for the textbox still show up. I could not find a simple way to get the first column to span all the rows in the group, so what I did was take the detail columns and put them in their own table and nest it in the group header row. That worked great but here is the problem:
The first column's data pushes the textbox to display 5 lines of text, but the detail may only have 1 line of data. It worked fine in the designer, but when rendered to HTML the lone detail line was aligned to the bottom of the group row. Even though the nested table's vertical alignment property is set to top, it was ignored.
I have service pack 1 installed, so I'm on the latest version. Any Ideas guys?Tables support cells that span multiple cells. When you select an entire row
an Merge Cells icon becomes active on in the Toolbar area Report Designer.
See the Merge Cells section at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_2esl.asp
for more information.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JRodman" <JRodman@.discussions.microsoft.com> wrote in message
news:BCCC1E74-354B-40F4-8938-B71C50DFF9A1@.microsoft.com...
> I have a report where the first column needs to be spanned over multiple
rows. The first column is part of a group, just the group name is shown on
the detail row with the details. The data looked like this:
> Group1 Detail 1
> Group1 Detail 2
> Group1 Detail 3
> Group2 Detail 1
> Group2 Detail 2
> But I dont want the first column to repeat so it looks like this:
> Group1 Detail 1
> Detail 2
> Detail 3
> Group2 Detail 1
> Detail 2
> I tried to use the "Hide Duplicates" option on the group column textbox,
but the borders for the textbox still show up. I could not find a simple way
to get the first column to span all the rows in the group, so what I did was
take the detail columns and put them in their own table and nest it in the
group header row. That worked great but here is the problem:
> The first column's data pushes the textbox to display 5 lines of text, but
the detail may only have 1 line of data. It worked fine in the designer, but
when rendered to HTML the lone detail line was aligned to the bottom of the
group row. Even though the nested table's vertical alignment property is set
to top, it was ignored.
> I have service pack 1 installed, so I'm on the latest version. Any Ideas
guys?
TableSpanCells.rdl
---
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Country</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Top>0.125in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ColSpan>2</ColSpan>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="CountryGroup">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<Left>0.125in</Left>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.375in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>d5774a08-d8e5-4e74-a663-f500669d7aba</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.62501in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.*
FROM Customers
ORDER BY Country</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4f653deb-b9af-49ca-98e1-fe972d395f88</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||You have a couple of options for row spanning:
Hide the cell that contains the group field using Previous():
<Hidden>=iif(Fields!Country.Value = Previous(Fields!Country.Value),
true, false)</Hidden>
Use a matrix that does not contain row groups or detail cells.
A sample report that demonstrates both approaches is at the end of this
posting.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JRodman" <JRodman@.discussions.microsoft.com> wrote in message
news:818FDD2E-9ED1-41D1-AD0B-3BB95EE85D78@.microsoft.com...
> What I am attempting to do is span ROWS not columns. Any ideas how to do
this without using the "Hide Duplicates" option?
> "Bruce Johnson [MSFT]" wrote:
> > Tables support cells that span multiple cells. When you select an entire
row
> > an Merge Cells icon becomes active on in the Toolbar area Report
Designer.
> > See the Merge Cells section at
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_2esl.asp
> > for more information.
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "JRodman" <JRodman@.discussions.microsoft.com> wrote in message
> > news:BCCC1E74-354B-40F4-8938-B71C50DFF9A1@.microsoft.com...
> > > I have a report where the first column needs to be spanned over
multiple
> > rows. The first column is part of a group, just the group name is shown
on
> > the detail row with the details. The data looked like this:
> > >
> > > Group1 Detail 1
> > > Group1 Detail 2
> > > Group1 Detail 3
> > > Group2 Detail 1
> > > Group2 Detail 2
> > >
> > > But I dont want the first column to repeat so it looks like this:
> > >
> > > Group1 Detail 1
> > > Detail 2
> > > Detail 3
> > > Group2 Detail 1
> > > Detail 2
> > >
> > > I tried to use the "Hide Duplicates" option on the group column
textbox,
> > but the borders for the textbox still show up. I could not find a simple
way
> > to get the first column to span all the rows in the group, so what I did
was
> > take the detail columns and put them in their own table and nest it in
the
> > group header row. That worked great but here is the problem:
> > >
> > > The first column's data pushes the textbox to display 5 lines of text,
but
> > the detail may only have 1 line of data. It worked fine in the designer,
but
> > when rendered to HTML the lone detail line was aligned to the bottom of
the
> > group row. Even though the nested table's vertical alignment property is
set
> > to top, it was ignored.
> > >
> > > I have service pack 1 installed, so I'm on the latest version. Any
Ideas
> > guys?
> >
SpanRows.rdl
---
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<ZIndex>2</ZIndex>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>0.125in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_ColumnGroup1">
<GroupExpressions>
<GroupExpression />
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>2.125in</Width>
<Top>1.25in</Top>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_Country">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Country_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Country_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_City">
<GroupExpressions>
<GroupExpression>=Fields!City.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="City_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>City_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
<Table Name="table1">
<Height>0.75in</Height>
<ZIndex>1</ZIndex>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Country</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>City</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<Visibility>
<Hidden>=iif(Fields!Country.Value =Previous(Fields!Country.Value), true, false)</Hidden>
</Visibility>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<TableGroups>
<TableGroup>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.20834in</Width>
</TableColumn>
<TableColumn>
<Width>2.20834in</Width>
</TableColumn>
<TableColumn>
<Width>2.20834in</Width>
</TableColumn>
</TableColumns>
</Table>
<Line Name="line1">
<Top>1in</Top>
<Height>0in</Height>
<Width>6.375in</Width>
<Style>
<BorderWidth>
<Default>4pt</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Left>0.125in</Left>
</Line>
</ReportItems>
<Style />
<Height>2.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>e1d9b0a0-afac-4bfc-b4d3-342bf39c17d4</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.62502in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select * from customers</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4f653deb-b9af-49ca-98e1-fe972d395f88</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

CDBVariant

Hi

got small problem. I got three rows in a table

Index (int)
Product (varchar)
Price (float)

I could successfully connect to the database. I also get the values and store them in a CDBVariant type. I can't put the values into List Control Box... insertItem always return -1. It ask for LPCTSTR type.

Anyone can help me please, hanging around here know for hours?

Please
Thx

CODE

{
CDBVariant value;
char sql_statement [2048] = "";

//CDatabase object "db" created to connect database
CDatabase db;
db.OpenEx(_T("DSN=Beauty"),CDatabase::noOdbcDialog);

//CRecordset object "rs" created to access and manipulate database records.
CRecordset rs(&db);
strcpy(sql_statement,_T("SELECT * FROM TestTabelle"));
rs.Open(CRecordset::forwardOnly,sql_statement);

//Get quantity from Database
int n = rs.GetODBCFieldCount( );

while(!rs.IsEOF())
{
for( int i = 0; i < n; i++ )

{

rs.GetFieldValue("index",value);
m_Buy_List.InsertItem(i,LPCTSTR(value.m_lVal)); Won't work at all

rs.GetFieldValue("product",value.m_pstring);
m_Buy_List.SetItemText(i,2,LPCTSTR(value));Getting weird String for example "Soap" and ListBox shows "Aq3"

rs.GetFieldValue("price",value);
m_Buy_List.SetItemText(i,3,LPCTSTR(value.m_fltVal) ); Won't accept float

rs.MoveNext( );
}
}Perhaps you would get better results on a C++ forum...

If you know that the values must be strings, and you are retrieving each one explicitly, why not just create three variables (strings), and use those. Seems like it would be a lot easier and less trouble prone.

If you are concerned about NULLs (which you should be), change your SQL statement to use the ISNULL function on each of the fields you are dealing with:

SELECT ISNULL(index,0) AS INDEX, ISNULL(product,'') AS PRODUCT, ISNULL(price,0) AS PRICE FROM TestTabelle|||thx

i figured another way out... getFieldValue isn't accepting strings so i converted them in char :)

weird... someday i will find the official way... i will change it then :)

iam new into c++ and ms sql... learning by doing :)