SQL Server Reporting Reset Page count or use distinctcount() for page number
-
I have a report that will come back as 20 pages. What the user wants is on the beginning of another unique ID, which causes a new page to be started, to have the page count restart to 1. So, if I have 3 UniqueIDs for all 20 pages, and the first, IDAA, is 10 pages long, the second, IDBB, is 4 pages long, and the third, IDCC, is 6 pages long, then the page numbering would be as: 1-10 for IDAA 1-4 for IDBB 1-6 for IDCC I've been told to set a cursor in a SP that determines what page I'm on and then passses this to the result set as a column. This is then set in a group to control page breaks. How could I do this? I'm not very familiar with SPROC cursors. I know how to do this scenario in Crystal Reports, but am trying to figure it out here with either a page count formula, or some type of distinct count(). Thanks!
-
I have a report that will come back as 20 pages. What the user wants is on the beginning of another unique ID, which causes a new page to be started, to have the page count restart to 1. So, if I have 3 UniqueIDs for all 20 pages, and the first, IDAA, is 10 pages long, the second, IDBB, is 4 pages long, and the third, IDCC, is 6 pages long, then the page numbering would be as: 1-10 for IDAA 1-4 for IDBB 1-6 for IDCC I've been told to set a cursor in a SP that determines what page I'm on and then passses this to the result set as a column. This is then set in a group to control page breaks. How could I do this? I'm not very familiar with SPROC cursors. I know how to do this scenario in Crystal Reports, but am trying to figure it out here with either a page count formula, or some type of distinct count(). Thanks!
This is what I've tried so far, but it resets on every page. So, each page is 1 even if the Unique ID spans 2-3 pages. I only need it to reset on a new field grouping of the list object. Here's the VB code for the report: Shared offset As Integer Public Function GetPN(reset As Boolean, pagenumber As Integer) As Integer If reset offset = pagenumber - 1 End If Return pagenumber - offset End Function *************************** In the header, I have a textbox named "tag" In the footer, I've got the following expression: ="Unique ID Page: " & Code.GetPN(Not(ReportItems!tag.Value Is Nothing),Globals!PageNumber)
-
This is what I've tried so far, but it resets on every page. So, each page is 1 even if the Unique ID spans 2-3 pages. I only need it to reset on a new field grouping of the list object. Here's the VB code for the report: Shared offset As Integer Public Function GetPN(reset As Boolean, pagenumber As Integer) As Integer If reset offset = pagenumber - 1 End If Return pagenumber - offset End Function *************************** In the header, I have a textbox named "tag" In the footer, I've got the following expression: ="Unique ID Page: " & Code.GetPN(Not(ReportItems!tag.Value Is Nothing),Globals!PageNumber)