October 18, 2005 at 9:32 am
Hello!
I am having a bit of a hard time trying to figure out how I can limit the number of rows displayed for a particular report. The goal is to only display the top 25 records.
Currently I have the report setup as follows: Created a table to hold the header lines & detail records. Was trying to limit the numbers of records shown by using the Hidden property for the TableRow via this code
=iif(RowNumber(Nothing) > 25, True, False)
It works to certain extent because it does not display the records after 25, but still print all the header lines. To alleviate that concern I made the 'repeat on new page' property false. Problem is now I get 10 additional blank pages. I am getting my data from a stored procedure that returns all records and is properly sorted ahead of time.
How should I structure this report to display only 25 records?
Also have another report with a similiar concern, but it is broken by top 25 per dept.
Please advise...
Mike
October 18, 2005 at 5:47 pm
Possibly a silly question but why not just set the stored procedure to return the top 25 records?
Steve.
October 19, 2005 at 8:03 am
That is what I did for one of the reports. The one that is still giving me trouble is the one that needs the top 25 per comp/dept. I believe this coding will need to take place in the report but unsure how to tackle it.
Thought is to try and use the rownumber. Is there a counter in a group that can be used?
Mike
October 19, 2005 at 8:07 am
SELECT O.XType
, O.name
FROM dbo.SysObjects O
WHERE ID IN (SELECT TOP 25 ID FROM dbo.SysObjects O2 WHERE O2.XType = O.XType order by O2.Name)
ORDER BY O.XType, O.Name
October 19, 2005 at 8:19 am
So you are suggesting handling it in the Stored proc...
Here is my current procedure:
declare @currentdate datetime
set @currentdate = getdate()
SELECT dbo.CreateReportString('NBUR1200-1', A.NBUCMPNY, A.NBUDEPT) as Co_Dept,
dateadd(d, -1, convert(datetime, cast(month(@currentdate) as varchar(2)) + '/1/' + cast(year(@currentdate) as varchar(4)), 101)) as EndDate,
A.NBUCMPNY, A.NBUDEPT, A.NCTBACCT, A.AYTDREVN,
B.TCSTNAM1, B.TCSTNAM2,
C.TNBUNAME
FROM (SELECT NBUCMPNY, NBUDEPT, NCTBACCT, MIN(NCTBSACT) NCTBSACT, SUM(AYTDREVN) AYTDREVN
FROM dbo.NBUORDER
WHERE DMATCHDT > '12/31/1997' AND AYTDREVN <> 0
GROUP BY NBUCMPNY, NBUDEPT, NCTBACCT) A,
CUSTOMER B, NBUOFFIC C
WHERE A.NCTBACCT = B.NCSTACCT AND A.NCTBSACT = B.NCSTSACT AND A.NBUCMPNY = C.NBUCMPNY AND A.NBUDEPT = C.NBUDEPT
ORDER BY A.NBUCMPNY, A.NBUDEPT, A.AYTDREVN DESC
Not sure how I can add your logic in?
October 19, 2005 at 8:29 am
It would look something like this (untested)
SELECT NBUCMPNY, NBUDEPT, NCTBACCT, MIN(NCTBSACT) NCTBSACT, SUM(AYTDREVN) AYTDREVN
FROM dbo.NBUORDER
WHERE DMATCHDT > '12/31/1997' AND AYTDREVN 0
and ID IN (Select ID from dbo.NBUORDER N2 WHERE DMATCHDT > '12/31/1997' AND AYTDREVN 0 AND N2.NBUCMPNY = dbo.NBUORDER.NBUCMPNY AND N2.NCTBACCT = dbo.NBUORDER.NCTBACCT AND N2.NBUDEPT = dbo.NBUORDER.NBUDEPT ORDER BY ??)
GROUP BY NBUCMPNY, NBUDEPT, NCTBACCT
October 19, 2005 at 10:14 am
I dont see / understand how this will give me the Top 25 records for each comp/dept.
October 19, 2005 at 10:17 am
Try the exemple I showed you, it's pretty straight forward... this is something you must learn before using.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply