Controlling the resultset size.

I'm using a report basically as a form. In this form I want the same number of lineitems no matter if I have 2 rows of data or 5 rows of data in my resultset. I basically want extra blank lines to roundout the resultset. The reason, is that with extra rows people printing the form can write additional data. Also, it will preseve the formatting. So I either need a way to tell a table in SSRS that it needs to have a minimum number of rows, OR, I need a way to add extra blank/null rows to a result set. Any ideas?

Hello,

I just developed a working solution to the same problem a few minutes ago. My problem was that the footer in the table was appearing in random places depending on the number of rows returned. I can fit 7 rows, so I needed to pad out additional rows to equal 7. So, I had to add additional data to the SQL resultset to do this. After my stored procedure produced a resultset, I immediately invoked this function to add more rows before returning to the report. Hope this helps. If anyone has an elegant solution, please post, I would like to implement it!

ALTER FUNCTION [dbo].[fCreateBlankTblRows]
(
@.nbrRows //my stored procedure figured out how many rows were returned and how many more were needed.
)

RETURNS
@.my_temp_tbl TABLE
(
id int,
id2 int,
xyx int
)
AS
BEGIN
Declare @.loopCount int;
set @.loopCount = 0;
WHILE (@.loopCount < @.nbrRows)
Begin
INSERT INTO @.my_temp_tbl
(id, id2, xyz)
VALUES (null,null,null);
set @.loopCount = @.loopCount + 1;
End;
RETURN
END

Controlling number of rows to display in a table and matrix on one page

Is there a way to control how many Detail Rows are displayed on one page in Table and Matrix controls?

hii

do you mean to say if there are 100 rows and you want only 10 rows to be shown in one page right without any change in the formatting?

i think the rough way is that you can increase the hight of the detail section but it will change the formatting of the page.still am trying with the issue .

Thanks

Mahasweta

Controlling number of Matrix rows displayed

Here's hopefully a simple one and I'm just not looking in the right places for the documentation:

I have a matrix and the result set it displays can range from 20 rows to 200+ rows. The columns range from 25 to possibly 100+. I need a way to set the maximum number of rows displayed per page so that if there are 200 rows returned (or some other arbitrary number) and the user wants to see 25 rows per page, then 8 pages will be returned.

Is there a way to do this?

Wayne E. Pfeffer

im also facing the same issue.. have u found the answer. if so do help me.|||

Not much help, but it may point you in the right direction:

http://www.microsoft.com/technet/prodtechnol/sql/2005/rsdesign.mspx

|||

Here is another link which might help:

http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

Continuous data on report

Hi,
I have a report that has loads of rows but only few narrow columns.
The preferred way to do the layout would be:
Landscape
and having two identical tables next to each other. The one on the left
would display the first 50 rows or so, the one to the right the next
50.
Is that possible in RS and if yes, how'?
Thanks for any help,
DomI don't really have a solution ... but if you really really can't find a way
to do it, you could simply use two dataset query. One using TOP 50 and the
other BOTTOM 50.
But whatever you do, I have had a problem using two tables side by side. In
the preview tab everything displays fine, but when published and viewed
through an asp reportviewer control there is always a breakline after the
first table and they cannot be displayed side by side.
"DominicB" wrote:
> Hi,
> I have a report that has loads of rows but only few narrow columns.
> The preferred way to do the layout would be:
> Landscape
> and having two identical tables next to each other. The one on the left
> would display the first 50 rows or so, the one to the right the next
> 50.
> Is that possible in RS and if yes, how'?
> Thanks for any help,
> Dom
>|||Yes you can set up a multi column report... I have an example on
www.msbicentral.com
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"DominicB" <dbuschi@.gmail.com> wrote in message
news:1124375773.648448.12870@.g44g2000cwa.googlegroups.com...
> Hi,
> I have a report that has loads of rows but only few narrow columns.
> The preferred way to do the layout would be:
> Landscape
> and having two identical tables next to each other. The one on the left
> would display the first 50 rows or so, the one to the right the next
> 50.
> Is that possible in RS and if yes, how'?
> Thanks for any help,
> Dom
>|||Thanks both for your help!
Wayne, where on your site can I find that example (I registered, but
couldn't find it)?
Thx,
Dom

Continuos Form/Letter

I need to create data report that has repeating rows and it is composed of
field names. This report looks like a payment book. I don't know how to do
this, because the report show me the first record but not the next 5 records.
Also, it occurs with the letters. If I have 4 customers that would receive
letters, only the first one received it(show the print preview).
I would appreciate any help.Please specify your question, i can´t get through to your problem.
Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"mlugoc" <mlugoc@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E8094B21-0192-498C-8EE0-F2F243678C46@.microsoft.com...
>I need to create data report that has repeating rows and it is composed of
> field names. This report looks like a payment book. I don't know how to do
> this, because the report show me the first record but not the next 5
> records.
> Also, it occurs with the letters. If I have 4 customers that would receive
> letters, only the first one received it(show the print preview).
> I would appreciate any help.|||I want to create a payment book. Each sheet of the payment book is a fifth
part of one 8.5" x 14" (legal paper size). Each sheet has 5 coupons from
payment book. The payment book might have up to 15 coupons. My problem is
that only the first coupon of the first sheet is printed. How can I do to
print the rest of the payment book?
I examined the query and it return the correct rows.
"Jens Sü�meyer" wrote:
> Please specify your question, i can´t get through to your problem.
> Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "mlugoc" <mlugoc@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:E8094B21-0192-498C-8EE0-F2F243678C46@.microsoft.com...
> >I need to create data report that has repeating rows and it is composed of
> > field names. This report looks like a payment book. I don't know how to do
> > this, because the report show me the first record but not the next 5
> > records.
> >
> > Also, it occurs with the letters. If I have 4 customers that would receive
> > letters, only the first one received it(show the print preview).
> >
> > I would appreciate any help.
>
>

ContainsTable not returning rows

Hello folks,
I have a table with several FT-enabled varchar columns, in SQL Server
2000.
If I ask for SELECT * FROM tbl WHERE CONTAINS(*, SearchStringA) I get
several rows. (as expected)
If I ask SELECT * FROM tbl WHERE CONTAINS(*, SearchStringB) I also get
several rows and they are an overlapping set of rows. (also as expected)
However, if I ask for SELECT * FROM tbl WHERE CONTAINS(*, SearchStringA AND
SearchStringB) I get no rows. The two seperate searches find matches in
different columns. Is this the problem, and if so, what is the best way to
get a correct result?
Thanks in advance,
Martin
If you want to look across columns you would have to do a FreeText query,
however with contains you would have to do this
select * from tablename where contains(col1, 'SearchPhraseA') or
contains(col2,'SearchPhraseB')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Martin Hungerford" <Martin Hungerford@.discussions.microsoft.com> wrote in
message news:4E584970-A671-460B-A6AE-B65ECC68DBF9@.microsoft.com...
> Hello folks,
> I have a table with several FT-enabled varchar columns, in SQL Server
> 2000.
> If I ask for SELECT * FROM tbl WHERE CONTAINS(*, SearchStringA) I get
> several rows. (as expected)
> If I ask SELECT * FROM tbl WHERE CONTAINS(*, SearchStringB) I also get
> several rows and they are an overlapping set of rows. (also as expected)
> However, if I ask for SELECT * FROM tbl WHERE CONTAINS(*, SearchStringA
AND
> SearchStringB) I get no rows. The two seperate searches find matches in
> different columns. Is this the problem, and if so, what is the best way to
> get a correct result?
> Thanks in advance,
> Martin

CONTAINSTABLE doesn't return expected number of rows

Hello,
I've discovered a strange behaviour of the CONTAINSTABLE function.
When I use the TOP statement to return a number of rows, less rows are
returned then when I don't use this statement (while total affected
rows are > 250)
for example:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
tblKeys
returns 49 rows.
When I execute the same query, but without the TOP statement:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
the query returns 317 rows.
In my opinion the first query has to return 250 rows, because the
total number of affected rows is 317.
Does anyone know this problem, or is there something I do wrong?
best regards,
Jens
Its been a while since I played with it, but that is for ranking. Since Fuzzy
Logic is used in executing the Contains against a FTS, it gives rank to each
result set. And when you specifiy the value Top n (Top_n_by_rank), it's just
doing a filter on that field.
Try using the Top n in the select statement as in Select Top 10 * From ...
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Jens" wrote:

> Hello,
> I've discovered a strange behaviour of the CONTAINSTABLE function.
> When I use the TOP statement to return a number of rows, less rows are
> returned then when I don't use this statement (while total affected
> rows are > 250)
> for example:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
> tblKeys
> returns 49 rows.
> When I execute the same query, but without the TOP statement:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
> the query returns 317 rows.
> In my opinion the first query has to return 250 rows, because the
> total number of affected rows is 317.
> Does anyone know this problem, or is there something I do wrong?
> best regards,
> Jens
>

CONTAINSTABLE doesn't return expected number of rows

Hello,
I've discovered a strange behaviour of the CONTAINSTABLE function.
When I use the TOP statement to return a number of rows, less rows are
returned then when I don't use this statement (while total affected
rows are > 250)
for example:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
tblKeys
returns 49 rows.
When I execute the same query, but without the TOP statement:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
the query returns 317 rows.
In my opinion the first query has to return 250 rows, because the
total number of affected rows is 317.
Does anyone know this problem, or is there something I do wrong?
best regards,
JensIts been a while since I played with it, but that is for ranking. Since Fuzz
y
Logic is used in executing the Contains against a FTS, it gives rank to each
result set. And when you specifiy the value Top n (Top_n_by_rank), it's jus
t
doing a filter on that field.
Try using the Top n in the select statement as in Select Top 10 * From ...
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Jens" wrote:

> Hello,
> I've discovered a strange behaviour of the CONTAINSTABLE function.
> When I use the TOP statement to return a number of rows, less rows are
> returned then when I don't use this statement (while total affected
> rows are > 250)
> for example:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
> tblKeys
> returns 49 rows.
> When I execute the same query, but without the TOP statement:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
> the query returns 317 rows.
> In my opinion the first query has to return 250 rows, because the
> total number of affected rows is 317.
> Does anyone know this problem, or is there something I do wrong?
> best regards,
> Jens
>

CONTAINSTABLE doesn't return expected number of rows

Hello,
I've discovered a strange behaviour of the CONTAINSTABLE function.
When I use the TOP statement to return a number of rows, less rows are
returned then when I don't use this statement (while total affected
rows are > 250)
for example:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
tblKeys
returns 49 rows.
When I execute the same query, but without the TOP statement:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
the query returns 317 rows.
In my opinion the first query has to return 250 rows, because the
total number of affected rows is 317.
Does anyone know this problem, or is there something I do wrong?
best regards,
JensIts been a while since I played with it, but that is for ranking. Since Fuzzy
Logic is used in executing the Contains against a FTS, it gives rank to each
result set. And when you specifiy the value Top n (Top_n_by_rank), it's just
doing a filter on that field.
Try using the Top n in the select statement as in Select Top 10 * From ...
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Jens" wrote:
> Hello,
> I've discovered a strange behaviour of the CONTAINSTABLE function.
> When I use the TOP statement to return a number of rows, less rows are
> returned then when I don't use this statement (while total affected
> rows are > 250)
> for example:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
> tblKeys
> returns 49 rows.
> When I execute the same query, but without the TOP statement:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
> the query returns 317 rows.
> In my opinion the first query has to return 250 rows, because the
> total number of affected rows is 317.
> Does anyone know this problem, or is there something I do wrong?
> best regards,
> Jens
>

Contains(*) question

When I do a full text index on 2 columns, then do a query like below, it
appears to only match rows where 1 column of the index satisfies the
criteria. I want the query to return all rows where a combination of the 2
columns satisfy the query. Do I have something set up wrong?
SELECT * FROM <table>
WHERE CONTAINS(*,'"lord","rings","dvd"')
For the following data, no row is returned, but I want it to be
column 1 contains 'lord' and 'rings'
column 2 contains 'dvd'
For the following data, a row is returned.
column 1 contains 'lord' and 'rings' and 'dvd'How about :
SELECT * FORM <table>
WHERE CONTAINS(*, '"lord" OR "rings" OR "dvd"')
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Brian Kitt wrote:

>When I do a full text index on 2 columns, then do a query like below, it
>appears to only match rows where 1 column of the index satisfies the
>criteria. I want the query to return all rows where a combination of the 2
>columns satisfy the query. Do I have something set up wrong?
>SELECT * FROM <table>
>WHERE CONTAINS(*,'"lord","rings","dvd"')
>For the following data, no row is returned, but I want it to be
>column 1 contains 'lord' and 'rings'
>column 2 contains 'dvd'
>For the following data, a row is returned.
>column 1 contains 'lord' and 'rings' and 'dvd'
>
>|||But I need the results to contain all 3 terms. An 'or' would return results
that contain 1 of the 3.
"Mike Hodgson" wrote:

> How about :
> SELECT * FORM <table>
> WHERE CONTAINS(*, '"lord" OR "rings" OR "dvd"')
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Brian Kitt wrote:
>
>|||On Sun, 9 Oct 2005 19:17:01 -0700, Brian Kitt wrote:

>When I do a full text index on 2 columns, then do a query like below, it
>appears to only match rows where 1 column of the index satisfies the
>criteria. I want the query to return all rows where a combination of the 2
>columns satisfy the query. Do I have something set up wrong?
>SELECT * FROM <table>
>WHERE CONTAINS(*,'"lord","rings","dvd"')
>For the following data, no row is returned, but I want it to be
>column 1 contains 'lord' and 'rings'
>column 2 contains 'dvd'
>For the following data, a row is returned.
>column 1 contains 'lord' and 'rings' and 'dvd'
Hi Brian,
I don't know much about full text indexing, so this one is a shot in the
dark - but would this work?
SELECT col01, col02, ...
FROM YourTable
WHERE CONTAINS (*, '"lord" AND "rings"')
AND CONTAINS (*, '"dvd"')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Sorry, I misunderstood what you were trying to achieve. It seems like
the media type ought to be in its own column and referenced with normal
string operators rather than the CONTAINS() predicate. Something like:
select MediaTitle, MediaType, ... from Media
where CONTAINS (MediaTitle, '"lord" AND "rings"')
and MediaType = "dvd"
With a nonclustered index on the MediaType column, that would work much
more efficiently than a couple full-text searches. If you cannot change
the design then Hugo's suggestion looks like it should work, but that's
a really poor design (just having all the metadata jumbled together like
that) - you may as well just have a bunch of text files containing the
search terms in a directory structure and use the Windows explorer
search function to trawl through the text files. Why store data in a
relational database if it's not relational data?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Brian Kitt wrote:
>But I need the results to contain all 3 terms. An 'or' would return result
s
>that contain 1 of the 3.
>"Mike Hodgson" wrote:
>
>|||Brian,
This is a FAQ in the fulltext newsgroup, so I've blogged about how to do FTS
across columns - "SQL Server FTS across multiple tables or columns" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.e
ntry
Enjoy,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:OYUovffzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Sorry, I misunderstood what you were trying to achieve. It seems like
> the media type ought to be in its own column and referenced with normal
> string operators rather than the CONTAINS() predicate. Something like:
> select MediaTitle, MediaType, ... from Media
> where CONTAINS (MediaTitle, '"lord" AND "rings"')
> and MediaType = "dvd"
> With a nonclustered index on the MediaType column, that would work much
> more efficiently than a couple full-text searches. If you cannot change
> the design then Hugo's suggestion looks like it should work, but that's
> a really poor design (just having all the metadata jumbled together like
> that) - you may as well just have a bunch of text files containing the
> search terms in a directory structure and use the Windows explorer
> search function to trawl through the text files. Why store data in a
> relational database if it's not relational data?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Brian Kitt wrote:
>
>

Contains finding rows that don't have search word

I am using contains to search a varchar(max) field.
select Page_Id from Q_Page
where contains (Text, 'webapp_user_adduser')
My problem is that the Page_Ids that are being returned don't have the text
I am searching for in them. I get back almost every row. I should only get
back 3 or 4.
I checked the contents of the Text column. Every row has different data.
Almost every search gives me back nearly all the rows in the table.
On XP and Win2003 webapp_user_adduser will not be broken at underscores.
What is the version of your SQL Server, what OS is it running on, and what
language are you searching in?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:OvJCsoedHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Andy wrote on Mon, 2 Apr 2007 08:32:03 -0700:
>
> Try
> select Page_Id from Q_Page
> where contains (Text, '"webapp_user_adduser"')
> (notice the double quotes around the phrase to force a phrase match).
> I think the _ is treated as a word break, so when you look for
> webapp_user_adduser it's actually searching for webapp, user, and adduser
> individually.
> Dan
>

Consolidating Rows and Summing Totals

I would like to know if there is an easy way to consolidate lines of the same item but differing quantities.

For example:

ITEM DESCRIPT MFR QTY
-- ---- -- --
ABC TABLE OSH 1
ABC TABLE OSH 3
ABC TABLE OSH 2
ABD CHAIR KMT 2
ABD CHAIR KMT 1
ABE PILLOW SOF 1

I would like to display this information as:

ITEM DESCRIPT MFR QTY
-- ---- -- --
ABC TABLE OSH 6
ABD CHAIR KMT 3
ABE PILLOW SOF 1

Summary: I want to consolidate those items which appear in the table more than once by combining their quantities and leaving one row that has the sum of all.

Your help is greatly appreciated.
TechRickselect ITEM, DESCRIPT, MFR
, sum(QTY)
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR

delete from yourtable

insert into yourtable
select * from temptable

rudy|||Thanks Rudy,

I just tried it but I lose my column header when I use the SUM.
Can I select by column number?

This is the error I recieved:
Server: Msg 8155, Level 16, State 1, Line 70
No column was specified for column 4 of 'temptable'

Thanks again,
TechRick|||sorry, i should have anticipated that

select ITEM, DESCRIPT, MFR
, sum(QTY) as QTY
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR|||Originally posted by r937
sorry, i should have anticipated that

select ITEM, DESCRIPT, MFR
, sum(QTY) as QTY
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR

Rudy,

I've tried repeatedly and in various ways to implement your suggestion and for some reason the rows are not being consolidated. The same information is in both tables.

Is is possible that I need to do a loop and then consolidate like items?

TechRick|||pick a couple of items, let's call them 'itm1' and 'itm2', and run a detail report

select ITEM, DESCRIPT, MFR, QTY
from yourtable
where ITEM in ('itm1','itm2')
order by ITEM, DESCRIPT, MFR

post the results (assuming only a few lines, eh)

now run a summary report

select ITEM, DESCRIPT, MFR, sum(QTY) as QTY
from yourtable
where ITEM in ('itm1','itm2')
group by ITEM, DESCRIPT, MFR

and post the results|||Originally posted by r937
pick a couple of items, let's call them 'itm1' and 'itm2', and run a detail report

select ITEM, DESCRIPT, MFR, QTY
from yourtable
where ITEM in ('itm1','itm2')
order by ITEM, DESCRIPT, MFR

post the results (assuming only a few lines, eh)

now run a summary report

select ITEM, DESCRIPT, MFR, sum(QTY) as QTY
from yourtable
where ITEM in ('itm1','itm2')
group by ITEM, DESCRIPT, MFR

and post the results

Here's what I did. It seems to work.

CODE:
select mfr_sku, title, MFR_name, Q_stk
from A1_TEMPTABLE
where mfr_sku = 'st19171wc'
order by mfr_sku, title, MFR_name

select mfr_sku, title, MFR_name, sum(Q_stk) as Q_stk
from A1_TEMPTABLE
where mfr_sku = 'item1'
group by mfr_sku, title, MFR_name

RESULTS:

ITEM1 9GB 3.5 80PIN SCSI SEA 486
ITEM1 9GB 3.5 80PIN SCSI SEA 431

ITEM1 9GB 3.5 80PIN SCSI SEA 941

I think I might know what I was doing wrong but I'll have to check it to make sure...

Thanks again,
Rick|||Originally posted by r937
sorry, i should have anticipated that

select ITEM, DESCRIPT, MFR
, sum(QTY) as QTY
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR

Sure enough, it was as I thought. I was adding the QTY in the 'group by' statement and that seems to have been causing the problem. I thought that it was required but it wasn't. It's all worked out now -- working like a charm.

Thanks Rudy!

Best Regards,
TechRick