Paging Header and Detail

I would like to create a report with header and detail, like an invoice with invoice # and line items. I'd like the invoice # to come from a summary query that breaks 1 invoice # per page. Then I'd like to display the line items below the invoice # on each page, from a second query passing the invoice number as a parameter. How is this done? Do I need a subreport?

ie:

header from summary query:

invoice 101

detail from detail query passed invoice # = 101:

line 1 item A

line 2 item B

line 3 item C

page

header from summary query:

invoice 102

detail from detail query passed invoice # = 102:

line 1 item A

line 2 item B

line 3 item C

page

...

Thanks.

If you want to use two queries, yes, you need a subreport to display the line items.

If you can return all the data in one query, then you just need a table with group (by invoice #). The invoice information would be in the group header, and the line items would be in the detail rows.

Pagination Control

Hi Ppl
I have alot of headache with pagination control on reporting services. Is
there a way to control how many groups/items/rows in one page?
Is there a way to show all items on 1 big page, instead of paging them? I've
try to set the report hieght, but that doesn't help at all.
If pagination must happen, then is there a way to search through all pages
for a perticular string?
Lastly is there a way to stop a group being seperated into 2 pages, (I know
you can have 1 group per page, but thats not what i want). The height of the
group are dynamic, so i can adjust the height group with the report size to
make them not break.See Inline
--
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
"gMaster" <gMaster@.discussions.microsoft.com> wrote in message
news:8EBFCE2B-F794-4550-AB86-FB3F6058A0AA@.microsoft.com...
> Hi Ppl
> I have alot of headache with pagination control on reporting services. Is
> there a way to control how many groups/items/rows in one page?
I have placed 2 examples in the downloads section for reporting services->
RDL on
http:://www.msbicentral.com
They are called PagedTableParameters (This uses a Parameter to allow dynamic
selection of the number of rows which should be placed on a page... nice...
uses Northwind.) and PageBreakSample.RDL (For Beginners, take a look at the
page breaks on each grouping. Uses the Northwind Data Source.)
> Is there a way to show all items on 1 big page, instead of paging them?
I've
> try to set the report hieght, but that doesn't help at all.
Simply do not turn on page breaks for the groups, etc... IN html you will
get one long page. when you export to PDF however you will get pages (
because you will print on to actual paper.)
> If pagination must happen, then is there a way to search through all pages
> for a perticular string?
IN html there is a Find textbox at the top with the parameters after you run
a report, simply type in the text you wish to find...
> Lastly is there a way to stop a group being seperated into 2 pages, (I
know
> you can have 1 group per page, but thats not what i want). The height of
the
> group are dynamic, so i can adjust the height group with the report size
to
> make them not break.
There is a group property called Keeptogether( I tink that is the name) turn
this property on...|||Hi,
I kinda have the same prob. I also want everything on one page. There's no
grouping / page breaking or whatever in my rdl. I Have 1 table with +/- 500
rows, and i want them all on one page. I do use the reportviewer control, so
on top, you can see "page x of x". Perhaps it's a setting there ?
Thanks
"Wayne Snyder" wrote:
> See Inline
> --
> 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
> "gMaster" <gMaster@.discussions.microsoft.com> wrote in message
> news:8EBFCE2B-F794-4550-AB86-FB3F6058A0AA@.microsoft.com...
> > Hi Ppl
> > I have alot of headache with pagination control on reporting services. Is
> > there a way to control how many groups/items/rows in one page?
> I have placed 2 examples in the downloads section for reporting services->
> RDL on
> http:://www.msbicentral.com
> They are called PagedTableParameters (This uses a Parameter to allow dynamic
> selection of the number of rows which should be placed on a page... nice...
> uses Northwind.) and PageBreakSample.RDL (For Beginners, take a look at the
> page breaks on each grouping. Uses the Northwind Data Source.)
>
> > Is there a way to show all items on 1 big page, instead of paging them?
> I've
> > try to set the report hieght, but that doesn't help at all.
> Simply do not turn on page breaks for the groups, etc... IN html you will
> get one long page. when you export to PDF however you will get pages (
> because you will print on to actual paper.)
> >
> > If pagination must happen, then is there a way to search through all pages
> > for a perticular string?
> IN html there is a Find textbox at the top with the parameters after you run
> a report, simply type in the text you wish to find...
> >
> > Lastly is there a way to stop a group being seperated into 2 pages, (I
> know
> > you can have 1 group per page, but thats not what i want). The height of
> the
> > group are dynamic, so i can adjust the height group with the report size
> to
> > make them not break.
> There is a group property called Keeptogether( I tink that is the name) turn
> this property on...
>
>

Paged Result Sets

What is the recommended mechanism for selecting paged results from SQL.

Presently I pass various params including the request Max Items Per Page and the requested page.

The I execute the query as a count with the search params.

Then comes the paging logic, which validates the page number against the request page and number of hits etc.

Then a temp table and record variables are created for the results.

Then I run the query again with a cursor and select the appropriate Items into the temp table based on the paging values (First Item and Last Item).

Then I return the temp table & some additional return params with the Total Hits etc.

The Stored procedure is accessed via an ADO.Net client and the system.data.IDBReader populates a .Net strongly typed collection and is for read only display.

Thanks for any input,

Martin.

hi martin,

i think this is a front end issue not SQL's

vb.net objects like grids, detailsview and formview

supports paging builtin to them without the need of relying

to Sql server.

you can read the data into the dataset and present it with vb using

objects that support paging.

regards,

joey

|||

Hi Joey,

I am more than capable with ADO.Net. This is not a front end issue.

I have say 200,000 records and I want 15 items, page 30 in a result set displaying 15 items per page. I do not wish to return 450 items to a dataset or to a datareader that has to do multiple round trips to the server to get top the records I require.

I want the SPROC to return the 15 items that I am requesting. Do you understand what I am saying taking this in context with what I have said above?

I can presently do this. Though, I am looking for some one of you SQL Pro's to tell how I should be doing it.

|||

edited

hi martin,

sorry just clarifying. Anyway try this

use northwind

create proc pagemynorthwindorders(@.page int)
as
declare @.pagesize int
select @.pagesize=15

select IDENTITY(int, 1,1) AS ID_Num ,str(orderId)as orderid
into #dummyorders from orders
select * from orders where orderid in
(
select orderid from #dummyorders orders
where id_num between (@.page-1)*@.pagesize and (@.page)*@.pagesize
)

exec pagemynorthwindorders 1

regards,

joey

|||

Hello again!

Looking at this this means that I am selecting all - say using Top 1000 - one thousand records into a temp table?

into #dummyorders I am unfamiliar with this syntax.

Inner queries and stuff! Is this more effeective than the Cursor approach? Is it possible you could break out the SPROC above a little with some comments.

It is much appreciated.

|||

no problem

use northwind

create proc pagemynorthwindorders(@.page int)
as
declare @.pagesize int
select @.pagesize=15 -- in case you want to change your paging size

-- create a temp table with its own identity column starting from 1

-- since order id has its own identity i need translate it to a string using str

-- there can only be one identity column in the table

-- i need only the pk (orderid) since it can identify the records i need

-- the #dummyorders record returns my own id_num and the orderid

-- id_num shall be used for paging , orderid is to identify what records

belong to the page

select IDENTITY(int, 1,1) AS ID_Num ,str(orderId)as orderid
into #dummyorders from orders

-- this get the records from orderid that exist in the required page

select * from orders where orderid in
(
select orderid from #dummyorders orders
where id_num between (@.page-1)*@.pagesize and (@.page)*@.pagesize
)

--(@.page-1)*@.pagesize+1 and (@.page)*@.pagesize

let say you want page 2 it gets the record between

-- (2-1)*15+1 and (2*15)

-- between 16 and 30 which is actually page 2

--at the end of the procedure #dummyorders destroys itself

exec pagemynorthwindorders 1

|||

final query, just improve it to suite you needs

use northwind

alter proc pagemynorthwindorders(@.page int)
as
declare @.pagesize int
select @.pagesize=15
select IDENTITY(int, 1,1) AS ID_Num ,str(orderId)as orderid
into #dummyorders from orders
select * from orders where orderid in
(
select orderid from #dummyorders orders
where id_num between (@.page-1)*@.pagesize+1 and (@.page)*@.pagesize
)

go

exec pagemynorthwindorders 2

|||

This is a very common question and there are actually many good answers.

If you are using SQL2005 there are two really cool features you could use:

- the tops now accept a variable: select top @.variable

- the ROW_NUMBER function, that creates an additional column with the ID of the row

Try this:

declare @.ipagesize as int,

@.ipage as int

set @.ipagesize = 5

set @.ipage = 0

select top ( @.ipagesize ) *

from

(

select ROW_NUMBER () over ( order by orderID ) as row_order , *

from orders

)

ordered_set

where ordered_set.row_order > @.ipagesize * @.ipage

|||

Thanks people,

Both are cleaner than my current solution.

Though, is there a way to get an estimate of the total hits from the same statement or must I use an additional count?

Also, I know there is a knew Table or .Table statement for SQL 2005 which I thought may have popped up in the answers. The Top Param non variable limitation was an anoying so that is also valuable info.

|||new!

PageBreak after x items for group, different on first page

I am trying to add a page break for table details after x items with x being different for the first page of report.

I am able to add a page break by adding a group to table with group expression =Int((RowNumber(Nothing)-1)/25). But I can't figure out how to do this where groupexpression on page 1 of report = Int((RowNumber(Nothing)-1)/15) and on all other pages =Int((RowNumber(Nothing)-1)/25).

I tried

=IIF(Globals!PageNumber=1,Int((RowNumber(Nothing)-1)/15),Int((RowNumber(Nothing)-1)/25))

but Globals cannot be used in group expression. I also tried putting group expression in a textbox on the report but it will not let me refer to the textbox.

Any assistance is appreciated!
Chris

references: http://msdn2.microsoft.com/en-us/library/ms157328.aspx , http://msdn2.microsoft.com/en-us/library/ms157328(en-US,SQL.90).aspx

Try a group expression like this:

=iif(RowNumber(Nothing) <= 15, Int((RowNumber(Nothing)-1)/15), Int((RowNumber(Nothing)-1-15)/25))

This should result in 15 rows for the first page, and 25 rows on subsequent pages.

-- Robert