How to get total page count for individual groups

Hello,

I have report in which I have created groups base on the customer name. Can anybody please tell me how to get the total page counts for the individual group? I have page break after every new group and I am able to reset page count to 1 when new group start but I am getting the total number of pages for a particular group.

For ex, let say I have 4 groups, 1 group has 3 page, 2 group has 2 pages and 3 group has 6 page and 4th group has 7 pages I need something like,

For 1st group

'Page 1 of 3’ when user click next page it should be 'Page 2 of 3' etc

Similarly for other groups as wll.

Thanks!

Hello,

Brian Welcker has a blog post that covers this:

http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx

but you will want to modify his code slightly to use a hash table as he notes at the end.

Let me know if you need assistance implementing this solution or if this doesn't solve your issue.

Larry

|||

Hi Larry,

Thanks a lot for the reply.

I am able to implement the code but I am not able to get the total page count for the individual group.

Let say there are total 15 Pages in the report and there are 3 groups with page break then I need something like

For Group 1 paging would be -> Page 1 of 3, Page 2 of 3.....etc

For Group 1 paging would be -> Page 1 of 5, Page 2 of 5, Page 3 of 5...etc

For Group 1 paging would be -> Page 1 of 7

Currently I am getting like

Group 1 - Page1 of 15, Page 2 of 15...ect

Group 2 - Page 1 of 15, Page 2 of 15..etc

Group 3 - Page 1 of 15, Page 2 of 15..etc

Thanks

|||

I see. I didn't fully read your original post. Take a look at the following thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1807270&SiteID=1

where Lisa posts a method of doing what you want. It requires some external code as the pages in a report are rendered in a single pass and it would require two passes (one to count and one to update) to accomplish what you are wanting.

Larry

how to get the max value from a table join

Hi all,

I have 2 table with one having customer info like personal details. The other table has sales records of each customer. I need join these 2 tables and retrive only the latest sales record per customer. How could I do this. Any help appriciated.

Many thanks,

Cheers,

Vije

You really need to describe your problem better. Information that ought to be included is (1) which version of SQL server you are using -- whether 2000 or 2005 or what, (2) the names of the tables, and (3) the names of the relevant columns.

One potential is something like:

Code Snippet

select column1,

column2,

...

columnN

from salesTable s

join customerTable c

on s.customerKey = c.customerKey

where s.salesDate

= ( select max(s.salesDate

from salesTable t

where t.customerKey = s.customerKey

)

This probably isn't the best guess, but without a better description of the target it is hard to hit the middle of the target.

|||

Here the sample,

Code Snippet

Create Table #salesdetails (

[CustomerId] int ,

[Date] DateTime ,

[Product] int ,

[qty] int

);

Insert Into #salesdetails Values('1','1/1/2007','1','10');

Insert Into #salesdetails Values('1','2/1/2007','2','10');

Insert Into #salesdetails Values('2','2/1/2007','2','10');

Insert Into #salesdetails Values('3','2/1/2007','3','10');

Insert Into #salesdetails Values('2','4/1/2007','2','10');

Insert Into #salesdetails Values('3','5/1/2007','3','10');

Insert Into #salesdetails Values('4','2/1/2007','2','10');

Create Table #customer (

[CustomerId] int ,

[Name] Varchar(100)

);

Insert Into #customer Values('1','Hari');

Insert Into #customer Values('2','Mani');

Insert Into #customer Values('3','Viji');

Insert Into #customer Values('4','Shiv');

Code Snippet

Select det.*,Cust.[Name] From #salesdetails det

Join (Select [CustomerId],Max([Date]) LastestDatefrom #salesdetails Group By [CustomerId]) as Latest

On Latest.[CustomerId]= Det.[CustomerId] and Latest.LastestDate = Det.[Date]

Join #customer Cust

On Cust.[CustomerId] = det.[CustomerId]