Listing rows "missing" information?

Here is how part of our database works. We have a table called "Events." Inside the "Events" table, we list various events which takes place for each of our clients. The first event for any client should be "Opened Case." However, some of my employees have not been listing this event so we do not know when the case was actually opened. In theory, all cases should have an "Opened Case" event in them. Is there a way to query that table to find which cases do not contain that event? Or is that more a programming issue? Thanks for any help?I'd suggest something like:SELECT DISTINCT caseID
FROM events AS a
WHERE NOT EXISTS (SELECT *
FROM events AS b
WHERE b.caseID = a.caseID
AND 'Case Opened' = b.eventType)
ORDER BY caseID-PatP|||or something like --select caseID
from events
group
by caseID
having 0
= sum(
case when eventType = 'Case Opened'
then 1 else 0
end ):)|||My, aren't we feeling deviant today! Heck, Rudy's solution will even work in MySQL!

-PatP|||deviant? heh

i was just thinking that yours involves a join (expensive), a NOT EXISTS (expensive), and a sort to remove dupes (expensive)

mine's just one sort, to do grouping

hey pat, did you give that Sock Hop channel a try? i was hoping you'd come back with some comment like "oh wow, i love that stuff!" (which was my reaction)sql

listing all rows in a database

Hi all,
I have a test databsae, I would like on occasion to list all rows in the
whole database. Is there a quick and easy way of doing so.
Thanks
Robert
If you mean all rows from all tables in a database, you can
use the undocumented sp_MSforeachtable
exec sp_MSforeachtable 'select * from ?'

ListBox and Table Values

Hi

got small problems with the table values. 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 but somehow the ListBox don't show them...

Anyone can help me please ?

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));

rs.GetFieldValue("product",value.m_pstring);
m_Buy_List.SetItemText(i,2,LPCTSTR(value));

rs.GetFieldValue("price",value);
m_Buy_List.SetItemText(i,3,LPCTSTR(value.m_fltVal) );

rs.MoveNext( );
}
}This is a VC++ code problem, I presume. Probably you should try some VC++ forums, for ex: www.codeguru.com

Best regards!sql

List problem

I have to print some labels in a report and I am using a list. the problem is
that there are more than 45 rows in the datasource, so I get a report with 2
pages.
How can I list the labels in 2 columns to have a 1 page report.http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/search?q=labels+columns
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:EB8EE841-F6AE-4A34-88BF-2E86680D708E@.microsoft.com...
>I have to print some labels in a report and I am using a list. the problem
>is
> that there are more than 45 rows in the datasource, so I get a report with
> 2
> pages.
> How can I list the labels in 2 columns to have a 1 page report.|||Go to Report>Layout
there you can indicate the number of columns that you need
I using 3 for Labels
:-)
"Dan" wrote:
> I have to print some labels in a report and I am using a list. the problem is
> that there are more than 45 rows in the datasource, so I get a report with 2
> pages.
> How can I list the labels in 2 columns to have a 1 page report.

List of issues in Management Studio

I have been using Management Studio for a few days and it's full of
annoyannces.
1- I just tried to copy 200 rows from a table from Management Studio to
another similar table with a column that doesn't allow nulls. So I get the
same error popup message for each row and I can't cancel from this batch.
2- Why can't I multiselect tables and Stored procs to delete like I used to
in 2000's Enterprise Manager.
3- When I script a stored procedure in 2000's Enterprise Manager, I woule
the create, drop and permission statements ALL in one script. This way I
was able to recreate the SP in one shot even if it exited. Not in
Management Studio. The drop and create are two seperate functions. If you
wanted to script the grant permission, you have to do it from the database
level and go through a bunch of clicks just to do it for one SP. TIME
CONSUMIUNG!
4- So I decided to script the grant permissions from the database level
using the drop procedure and tell it to include the object-level
permissions. The generated script included the drop statements but no
grant statement. I guess it figured that if you're dropping the SP's, why
include include the grant statements? My idea was that the drop statements
are in one line so then I can delete them pretty easy. It didn't work.
5- As mentioned in my previous message, the import export wizard stops too
often and gives cryptic messages without indicating why it stopped in clear
English. That's why I resorted to copy and paste ( see 1)
6- I don't think the import/export wizard is smart enough to transfer the
child tables first to avoid the foreign key issues. This was a problem in
2000
7- When the transferring many tables which have identity columns, I had to
check the "Enable identity insert' checkbox and select 'delete rows in
destination table'. Is there a way to select this option for all tables
instead of doing it one table at a time?
8- Choosing 'modify' for a table and opening a table give the same exact
icon and text in the list of opened windows. There's no visual clue when
trying to select either.
9- No 'back' button in the last step of the import export wizard if the
wizard ran successfuly. This means if I want to select other tables, I have
to restart the wizard. See 10 below for why it's annoying.
10- The wizard doesn't remember my settings. I constantly transfer data
between two sql servers, the first uses windows authentication (local
server) and the other uses sql server authentication and the wizard always
defaults to windows authenitcation for both. I wish there's an option to
remember type of authentication, my username and password or at least
remember the type of authentication and username.
I hope these issues will be addressed in the next version.
John Dalberg
If you want to make MS aware of issues with their products, use the Product Feedback web-site at
http://lab.msdn.microsoft.com/productfeedback/.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Dalberg" <nospam@.nospam.sss> wrote in message news:20060329013741.245$vZ@.newsreader.com...
>I have been using Management Studio for a few days and it's full of
> annoyannces.
> 1- I just tried to copy 200 rows from a table from Management Studio to
> another similar table with a column that doesn't allow nulls. So I get the
> same error popup message for each row and I can't cancel from this batch.
> 2- Why can't I multiselect tables and Stored procs to delete like I used to
> in 2000's Enterprise Manager.
> 3- When I script a stored procedure in 2000's Enterprise Manager, I woule
> the create, drop and permission statements ALL in one script. This way I
> was able to recreate the SP in one shot even if it exited. Not in
> Management Studio. The drop and create are two seperate functions. If you
> wanted to script the grant permission, you have to do it from the database
> level and go through a bunch of clicks just to do it for one SP. TIME
> CONSUMIUNG!
> 4- So I decided to script the grant permissions from the database level
> using the drop procedure and tell it to include the object-level
> permissions. The generated script included the drop statements but no
> grant statement. I guess it figured that if you're dropping the SP's, why
> include include the grant statements? My idea was that the drop statements
> are in one line so then I can delete them pretty easy. It didn't work.
> 5- As mentioned in my previous message, the import export wizard stops too
> often and gives cryptic messages without indicating why it stopped in clear
> English. That's why I resorted to copy and paste ( see 1)
> 6- I don't think the import/export wizard is smart enough to transfer the
> child tables first to avoid the foreign key issues. This was a problem in
> 2000
> 7- When the transferring many tables which have identity columns, I had to
> check the "Enable identity insert' checkbox and select 'delete rows in
> destination table'. Is there a way to select this option for all tables
> instead of doing it one table at a time?
> 8- Choosing 'modify' for a table and opening a table give the same exact
> icon and text in the list of opened windows. There's no visual clue when
> trying to select either.
> 9- No 'back' button in the last step of the import export wizard if the
> wizard ran successfuly. This means if I want to select other tables, I have
> to restart the wizard. See 10 below for why it's annoying.
> 10- The wizard doesn't remember my settings. I constantly transfer data
> between two sql servers, the first uses windows authentication (local
> server) and the other uses sql server authentication and the wizard always
> defaults to windows authenitcation for both. I wish there's an option to
> remember type of authentication, my username and password or at least
> remember the type of authentication and username.
> I hope these issues will be addressed in the next version.
> John Dalberg

List of issues in Management Studio

I have been using Management Studio for a few days and it's full of
annoyannces.
1- I just tried to copy 200 rows from a table from Management Studio to
another similar table with a column that doesn't allow nulls. So I get the
same error popup message for each row and I can't cancel from this batch.
2- Why can't I multiselect tables and Stored procs to delete like I used to
in 2000's Enterprise Manager.
3- When I script a stored procedure in 2000's Enterprise Manager, I woule
the create, drop and permission statements ALL in one script. This way I
was able to recreate the SP in one shot even if it exited. Not in
Management Studio. The drop and create are two seperate functions. If you
wanted to script the grant permission, you have to do it from the database
level and go through a bunch of clicks just to do it for one SP. TIME
CONSUMIUNG!
4- So I decided to script the grant permissions from the database level
using the drop procedure and tell it to include the object-level
permissions. The generated script included the drop statements but no
grant statement. I guess it figured that if you're dropping the SP's, why
include include the grant statements' My idea was that the drop statements
are in one line so then I can delete them pretty easy. It didn't work.
5- As mentioned in my previous message, the import export wizard stops too
often and gives cryptic messages without indicating why it stopped in clear
English. That's why I resorted to copy and paste ( see 1)
6- I don't think the import/export wizard is smart enough to transfer the
child tables first to avoid the foreign key issues. This was a problem in
2000
7- When the transferring many tables which have identity columns, I had to
check the "Enable identity insert' checkbox and select 'delete rows in
destination table'. Is there a way to select this option for all tables
instead of doing it one table at a time?
8- Choosing 'modify' for a table and opening a table give the same exact
icon and text in the list of opened windows. There's no visual clue when
trying to select either.
9- No 'back' button in the last step of the import export wizard if the
wizard ran successfuly. This means if I want to select other tables, I have
to restart the wizard. See 10 below for why it's annoying.
10- The wizard doesn't remember my settings. I constantly transfer data
between two sql servers, the first uses windows authentication (local
server) and the other uses sql server authentication and the wizard always
defaults to windows authenitcation for both. I wish there's an option to
remember type of authentication, my username and password or at least
remember the type of authentication and username.
I hope these issues will be addressed in the next version.
John DalbergIf you want to make MS aware of issues with their products, use the Product
Feedback web-site at
http://lab.msdn.microsoft.com/productfeedback/.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Dalberg" <nospam@.nospam.sss> wrote in message news:20060329013741.245$vZ@.newsreader.co
m...
>I have been using Management Studio for a few days and it's full of
> annoyannces.
> 1- I just tried to copy 200 rows from a table from Management Studio to
> another similar table with a column that doesn't allow nulls. So I get the
> same error popup message for each row and I can't cancel from this batch.
> 2- Why can't I multiselect tables and Stored procs to delete like I used t
o
> in 2000's Enterprise Manager.
> 3- When I script a stored procedure in 2000's Enterprise Manager, I woule
> the create, drop and permission statements ALL in one script. This way I
> was able to recreate the SP in one shot even if it exited. Not in
> Management Studio. The drop and create are two seperate functions. If you
> wanted to script the grant permission, you have to do it from the database
> level and go through a bunch of clicks just to do it for one SP. TIME
> CONSUMIUNG!
> 4- So I decided to script the grant permissions from the database level
> using the drop procedure and tell it to include the object-level
> permissions. The generated script included the drop statements but no
> grant statement. I guess it figured that if you're dropping the SP's, why
> include include the grant statements' My idea was that the drop statement
s
> are in one line so then I can delete them pretty easy. It didn't work.
> 5- As mentioned in my previous message, the import export wizard stops too
> often and gives cryptic messages without indicating why it stopped in clea
r
> English. That's why I resorted to copy and paste ( see 1)
> 6- I don't think the import/export wizard is smart enough to transfer the
> child tables first to avoid the foreign key issues. This was a problem in
> 2000
> 7- When the transferring many tables which have identity columns, I had to
> check the "Enable identity insert' checkbox and select 'delete rows in
> destination table'. Is there a way to select this option for all tables
> instead of doing it one table at a time?
> 8- Choosing 'modify' for a table and opening a table give the same exact
> icon and text in the list of opened windows. There's no visual clue when
> trying to select either.
> 9- No 'back' button in the last step of the import export wizard if the
> wizard ran successfuly. This means if I want to select other tables, I hav
e
> to restart the wizard. See 10 below for why it's annoying.
> 10- The wizard doesn't remember my settings. I constantly transfer data
> between two sql servers, the first uses windows authentication (local
> server) and the other uses sql server authentication and the wizard always
> defaults to windows authenitcation for both. I wish there's an option to
> remember type of authentication, my username and password or at least
> remember the type of authentication and username.
> I hope these issues will be addressed in the next version.
> John Dalberg

List of issues in Management Studio

I have been using Management Studio for a few days and it's full of
annoyannces.
1- I just tried to copy 200 rows from a table from Management Studio to
another similar table with a column that doesn't allow nulls. So I get the
same error popup message for each row and I can't cancel from this batch.
2- Why can't I multiselect tables and Stored procs to delete like I used to
in 2000's Enterprise Manager.
3- When I script a stored procedure in 2000's Enterprise Manager, I woule
the create, drop and permission statements ALL in one script. This way I
was able to recreate the SP in one shot even if it exited. Not in
Management Studio. The drop and create are two seperate functions. If you
wanted to script the grant permission, you have to do it from the database
level and go through a bunch of clicks just to do it for one SP. TIME
CONSUMIUNG!
4- So I decided to script the grant permissions from the database level
using the drop procedure and tell it to include the object-level
permissions. The generated script included the drop statements but no
grant statement. I guess it figured that if you're dropping the SP's, why
include include the grant statements' My idea was that the drop statements
are in one line so then I can delete them pretty easy. It didn't work.
5- As mentioned in my previous message, the import export wizard stops too
often and gives cryptic messages without indicating why it stopped in clear
English. That's why I resorted to copy and paste ( see 1)
6- I don't think the import/export wizard is smart enough to transfer the
child tables first to avoid the foreign key issues. This was a problem in
2000
7- When the transferring many tables which have identity columns, I had to
check the "Enable identity insert' checkbox and select 'delete rows in
destination table'. Is there a way to select this option for all tables
instead of doing it one table at a time?
8- Choosing 'modify' for a table and opening a table give the same exact
icon and text in the list of opened windows. There's no visual clue when
trying to select either.
9- No 'back' button in the last step of the import export wizard if the
wizard ran successfuly. This means if I want to select other tables, I have
to restart the wizard. See 10 below for why it's annoying.
10- The wizard doesn't remember my settings. I constantly transfer data
between two sql servers, the first uses windows authentication (local
server) and the other uses sql server authentication and the wizard always
defaults to windows authenitcation for both. I wish there's an option to
remember type of authentication, my username and password or at least
remember the type of authentication and username.
I hope these issues will be addressed in the next version.
John DalbergIf you want to make MS aware of issues with their products, use the Product Feedback web-site at
http://lab.msdn.microsoft.com/productfeedback/.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Dalberg" <nospam@.nospam.sss> wrote in message news:20060329013741.245$vZ@.newsreader.com...
>I have been using Management Studio for a few days and it's full of
> annoyannces.
> 1- I just tried to copy 200 rows from a table from Management Studio to
> another similar table with a column that doesn't allow nulls. So I get the
> same error popup message for each row and I can't cancel from this batch.
> 2- Why can't I multiselect tables and Stored procs to delete like I used to
> in 2000's Enterprise Manager.
> 3- When I script a stored procedure in 2000's Enterprise Manager, I woule
> the create, drop and permission statements ALL in one script. This way I
> was able to recreate the SP in one shot even if it exited. Not in
> Management Studio. The drop and create are two seperate functions. If you
> wanted to script the grant permission, you have to do it from the database
> level and go through a bunch of clicks just to do it for one SP. TIME
> CONSUMIUNG!
> 4- So I decided to script the grant permissions from the database level
> using the drop procedure and tell it to include the object-level
> permissions. The generated script included the drop statements but no
> grant statement. I guess it figured that if you're dropping the SP's, why
> include include the grant statements' My idea was that the drop statements
> are in one line so then I can delete them pretty easy. It didn't work.
> 5- As mentioned in my previous message, the import export wizard stops too
> often and gives cryptic messages without indicating why it stopped in clear
> English. That's why I resorted to copy and paste ( see 1)
> 6- I don't think the import/export wizard is smart enough to transfer the
> child tables first to avoid the foreign key issues. This was a problem in
> 2000
> 7- When the transferring many tables which have identity columns, I had to
> check the "Enable identity insert' checkbox and select 'delete rows in
> destination table'. Is there a way to select this option for all tables
> instead of doing it one table at a time?
> 8- Choosing 'modify' for a table and opening a table give the same exact
> icon and text in the list of opened windows. There's no visual clue when
> trying to select either.
> 9- No 'back' button in the last step of the import export wizard if the
> wizard ran successfuly. This means if I want to select other tables, I have
> to restart the wizard. See 10 below for why it's annoying.
> 10- The wizard doesn't remember my settings. I constantly transfer data
> between two sql servers, the first uses windows authentication (local
> server) and the other uses sql server authentication and the wizard always
> defaults to windows authenitcation for both. I wish there's an option to
> remember type of authentication, my username and password or at least
> remember the type of authentication and username.
> I hope these issues will be addressed in the next version.
> John Dalberg

list of columns in a table

Hi All,

I have a simple question, I want to get a list of all columns in a given table. result shouldn't give me rows. so even if table is huge the query runs quickly. what's the code for this

thanks

sonny

One way is something like:

Code Snippet

select c.colid,

c.name

from sysobjects o,

syscolumns c

where o.id = c.id

and o.name = 'nameOfYourTable'

order by c.name

|||

Another way is:

select Column_Name from INFORMATION_SCHEMA.Columns where Table_Name='Tablename'

|||

Hi kent,

that worked. Thanks.

Sonny

|||

Hi Mobin,

This is improvement sinc the columns are sorted as well.

thanks

sonny

List Control Properties

I saw this posting below, and I just want to be clear. I have a data
set with one Column, but many rows. I would like the data to display
horizontaly, then wrap to the next line. My data is actually displayed
in through Image controls. Im pulling the images from an external
path, on the web (http://hostname/images/image.jpg)
Am I understanding correctly, I cannot do that? Is there another
control that I can use instead? If there is can someone point me in
the direction on the properties I need to use?
Thanks,
rwiethorn
*************************************************************
From: Brian Welcker [MSFT] (bwelcker@.online.microsoft.com)
Subject: Re: List Control Shortcomings?
View: Complete Thread (2 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.reportingsvcs
Date: 2004-05-31 12:29:15 PST
No. You can make the entire report multiple columns but you can't make
a
list repeat horizontally.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no
rights.
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:374C89FA-582F-4B82-8A8A-C61A4D58D215@.microsoft.com...
> I have a data set bound to a list control. Is there any way to configure the list control to output horizontally aswell as vertially?
>
> e.g. my dataset has 11 rows, can the first 6 be in 1 column, the next 5 in
another column.
----http://blogs.msdn.com/chrishays/archive/2004/07/23/193292.aspx
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"rwiethorn" <rwiethorn2002@.yahoo.com> wrote in message
news:553a0349.0407301045.6cd21b18@.posting.google.com...
> I saw this posting below, and I just want to be clear. I have a data
> set with one Column, but many rows. I would like the data to display
> horizontaly, then wrap to the next line. My data is actually displayed
> in through Image controls. Im pulling the images from an external
> path, on the web (http://hostname/images/image.jpg)
> Am I understanding correctly, I cannot do that? Is there another
> control that I can use instead? If there is can someone point me in
> the direction on the properties I need to use?
> Thanks,
> rwiethorn
> *************************************************************
> From: Brian Welcker [MSFT] (bwelcker@.online.microsoft.com)
> Subject: Re: List Control Shortcomings?
> View: Complete Thread (2 articles)
> Original Format
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Date: 2004-05-31 12:29:15 PST
>
> No. You can make the entire report multiple columns but you can't make
> a
> list repeat horizontally.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Mark" <anonymous@.discussions.microsoft.com> wrote in message
> news:374C89FA-582F-4B82-8A8A-C61A4D58D215@.microsoft.com...
> > I have a data set bound to a list control. Is there any way to configure
the list control to output horizontally aswell as vertially?
> >
> > e.g. my dataset has 11 rows, can the first 6 be in 1 column, the next 5
in
> another column.
>
> ----
--

List Box - Multiple Columns

Hi,
Any ideas on how to split data from a row set over two columns in a list box or table?
For example I have a data set with 10 rows and I want to display 5 rows in one column and the other 5 in the second column.
I tried creating two tables and filtering the first table to only show the even rows and the second to show odd rows. However the RowNumber(Nothing) function is not allowed in filters - 'RowNumber cannot be used in filters'
Thanks
KevinCould you use a multi-column report and limt the number of rows in each
column?
The major restriction is that you must use a "paged" rendered (PDF, Print
Preview) to see the multiple columns. Following your original posting is a
sample RDL that shows how this is done.
Note the table group expression.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:8512662A-BEDB-4194-8072-03CFFC7E8736@.microsoft.com...
> Hi,
> Any ideas on how to split data from a row set over two columns in a list
box or table?
> For example I have a data set with 10 rows and I want to display 5 rows in
one column and the other 5 in the second column.
> I tried creating two tables and filtering the first table to only show the
even rows and the second to show odd rows. However the RowNumber(Nothing)
function is not allowed in filters - 'RowNumber cannot be used in filters'
> Thanks
> Kevin
--
Sample Multi-Column Report - 5 records per column
<?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">
<PageHeader>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Franklin Gothic Medium</FontFamily>
<FontSize>12pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<Top>0.11458in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.25in</Height>
<Width>3in</Width>
<CanGrow>true</CanGrow>
<Value>Sample Multiple Column Report</Value>
<Left>1.5in</Left>
</Textbox>
</ReportItems>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.5in</Height>
</PageHeader>
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>11pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Company Info</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<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>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<rd:DefaultName>Country</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableGroups>
<TableGroup>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=System.Math.Ceiling(RowNumber(Nothing)/5)</GroupExpression
>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>2.75in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.5in</Height>
<Columns>2</Columns>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>7bec244d-832e-4036-9143-257dd0f8dcaa</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>2.75in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT CompanyName, City, Country
FROM Customers</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>7535e659-af87-485d-b94d-cb398b82610b</rd:ReportID>
<PageFooter>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<Top>0.125in</Top>
<rd:DefaultName>textbox2</rd:DefaultName>
<Width>2in</Width>
<CanGrow>true</CanGrow>
<Value>="Page " & Globals!PageNumber</Value>
</Textbox>
</ReportItems>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.375in</Height>
</PageFooter>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||Kevin,
Did you get your last idea implemented in code? If so, would you share it
with me?
Thanks,
Ming
"Kevin Wilson" wrote:
> Thanks for your time on this Bruce. The multi coloumn report works fine but it isn't really appropriate in this case as I need to view the data rendered in HTML.
> My last idea is to change the the stored proc so each row is identified with a unique number. Create two tables each with a filter - table1 returns odd rows Fields!ID_NUM MOD 2 = 1 and table 2 returns even rows.
> Cheers
> Kevin
> "Bruce Johnson [MSFT]" wrote:
> > Could you use a multi-column report and limt the number of rows in each
> > column?
> > The major restriction is that you must use a "paged" rendered (PDF, Print
> > Preview) to see the multiple columns. Following your original posting is a
> > sample RDL that shows how this is done.
> > Note the table group expression.
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> > news:8512662A-BEDB-4194-8072-03CFFC7E8736@.microsoft.com...
> > > Hi,
> > > Any ideas on how to split data from a row set over two columns in a list
> > box or table?
> > > For example I have a data set with 10 rows and I want to display 5 rows in
> > one column and the other 5 in the second column.
> > > I tried creating two tables and filtering the first table to only show the
> > even rows and the second to show odd rows. However the RowNumber(Nothing)
> > function is not allowed in filters - 'RowNumber cannot be used in filters'
> > > Thanks
> > > Kevin
> >
> > --
> >
> > Sample Multi-Column Report - 5 records per column
> >
> > <?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">
> > <PageHeader>
> > <ReportItems>
> > <Textbox Name="textbox1">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontFamily>Franklin Gothic Medium</FontFamily>
> > <FontSize>12pt</FontSize>
> > <TextAlign>Center</TextAlign>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <Top>0.11458in</Top>
> > <rd:DefaultName>textbox1</rd:DefaultName>
> > <Height>0.25in</Height>
> > <Width>3in</Width>
> > <CanGrow>true</CanGrow>
> > <Value>Sample Multiple Column Report</Value>
> > <Left>1.5in</Left>
> > </Textbox>
> > </ReportItems>
> > <PrintOnLastPage>true</PrintOnLastPage>
> > <PrintOnFirstPage>true</PrintOnFirstPage>
> > <Style />
> > <Height>0.5in</Height>
> > </PageHeader>
> > <RightMargin>1in</RightMargin>
> > <Body>
> > <ReportItems>
> > <Table Name="table1">
> > <Height>1in</Height>
> > <Style />
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>11pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>3</ZIndex>
> > <rd:DefaultName>textbox3</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>Company Info</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > <RepeatOnNewPage>true</RepeatOnNewPage>
> > </Header>
> > <Details>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="CompanyName">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>CompanyName</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!CompanyName.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <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>
> > </TableCells>
> > </TableRow>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="Country">
> > <rd:DefaultName>Country</rd:DefaultName>
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!Country.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Details>
> > <DataSetName>Northwind</DataSetName>
> > <TableGroups>
> > <TableGroup>
> > <Grouping Name="table1_Group1">
> > <GroupExpressions>
> >
> > <GroupExpression>=System.Math.Ceiling(RowNumber(Nothing)/5)</GroupExpression
> > >
> > </GroupExpressions>
> > <PageBreakAtEnd>true</PageBreakAtEnd>
> > </Grouping>
> > </TableGroup>
> > </TableGroups>
> > <TableColumns>
> > <TableColumn>
> > <Width>2.75in</Width>
> > </TableColumn>
> > </TableColumns>
> > </Table>
> > </ReportItems>
> > <Style />
> > <Height>1.5in</Height>
> > <Columns>2</Columns>
> > </Body>
> > <TopMargin>1in</TopMargin>
> > <DataSources>
> > <DataSource Name="Northwind">
> >
> > <rd:DataSourceID>7bec244d-832e-4036-9143-257dd0f8dcaa</rd:DataSourceID>
> > <ConnectionProperties>
> > <DataProvider>SQL</DataProvider>
> > <ConnectString>data source=localhost;initial
> > catalog=Northwind</ConnectString>
> > <IntegratedSecurity>true</IntegratedSecurity>
> > </ConnectionProperties>
> > </DataSource>
> > </DataSources>
> > <Width>2.75in</Width>
> > <DataSets>
> > <DataSet Name="Northwind">
> > <Fields>
> > <Field Name="CompanyName">
> > <DataField>CompanyName</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="City">
> > <DataField>City</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="Country">
> > <DataField>Country</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>Northwind</DataSourceName>
> > <CommandText>SELECT CompanyName, City, Country
> > FROM Customers</CommandText>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <LeftMargin>1in</LeftMargin>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <rd:ReportID>7535e659-af87-485d-b94d-cb398b82610b</rd:ReportID>
> > <PageFooter>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <Top>0.125in</Top>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <Width>2in</Width>
> > <CanGrow>true</CanGrow>
> > <Value>="Page " & Globals!PageNumber</Value>
> > </Textbox>
> > </ReportItems>
> > <PrintOnLastPage>true</PrintOnLastPage>
> > <PrintOnFirstPage>true</PrintOnFirstPage>
> > <Style />
> > <Height>0.375in</Height>
> > </PageFooter>
> > <BottomMargin>1in</BottomMargin>
> > <Language>en-US</Language>
> > </Report>
> >
> >
> >

list all of a set of rows when only one row is true

This is stupid, I used to be able to do this all the time by mistake now I can't do it on purpose

I want to be able to return a full list of matching records when only one is true

Like
Row 1, ID_1, false
Row 2, ID_1, false
Row 3, ID_1, true
Row 4, ID_2, false
Row 5, ID_2, true
Row 6, ID_2, false

I currently get
Row 3, ID_1, true
Row 5, ID_2, true

In order for us to help you, you need to give us better information. We dont know what you are trying to do, what query you are using/what version of SQL you are using etc. Please post all the relevant information.|||

I think I have got it, using a derived table

Using the results from the derived table to join to the main table and substituting the ClosedDate in the main table with the ClosedDate from the Derived table
Of course I have no idea if this is the best solution

SELECT

derivedEvents.ClosedDate, derivedEvents.IRef
FROM(SELECTClosedDate, IRef
FROMdbo.tblOCompEvents
WHERE(ClientID = ClientID)AND(IRef = IRef)AND(closed = 1))ASderivedEventsINNER JOIN
dbo.tblOCompEventsAStblOCompEvents_1ONderivedEvents.IRef = tblOCompEvents_1.IRef
ORDER BYderivedEvents.IRef|||

I have postedmy solution but any thing better... thanks for your interest

In my table I have events for client activity eventualy each series of events will be closed and a date inserted
the user wants to review all events for each client where the series has been closed, previously I could only return the closed row not the whole series

This is for a report which lists all events for all clients where the series of events has been closed