Report Definition

Is it possible to control the number of table rows that can be displayed by modifying the report definition?Not sure if this is what you mean, but you can control the # of rows displayed by limiting the # of rows fetched from the data source. There's no way AFAIK to fetch N rows but only display N-M rows.|||

>> There's no way AFAIK to fetch N rows but only display N-M rows.

Yes, actually there is <s>.

You do need to give yourself appropriate information from the data source, but this doesn't necessarily mean limiting the rows! (because you might have another data region that shows a different count, in the same report, from the same data).

Here's a reporting query from my favorite ASP.NET add-on table (ELMAH error handler):

Code Snippet

SELECT ErrorId, Application, Host, Type, Source, Message,
[User], StatusCode, TimeUtc,

Sequence, AllXml

FROM ELMAH_Error ORDER BY ErrorId

... now suppose we add a column, as follows:

Code Snippet

SELECT ErrorId, Application, Host, Type, Source, Message,

[User], StatusCode, TimeUtc,

Sequence, AllXml, Row_Number() OVER (ORDER BY ErrorID) AS ItemID
FROM ELMAH_Error ORDER BY ErrorId

(you can add a counter like this different ways in other data sources)

... Now we can add the following filter to a table (or whatever):

Code Snippet

=Fields!ItemID.Value <= 8 ' or whatever maximum you have in mind,

' for example you can say:

=Fields!ItemID.Value <= =Parameters!MyMax.Value

... and readers please note, if you have trouble when you follow these instructions:

When I built this example I had to CInt() (cast) on both sides of the filter expression (including the literal 8!) to get it to work the first couple of times. It may be because I was building the query interactively for the dataset, and the designer doesn't support this clause so, while it processes the SQL, it is not sure about the data type of the result. IAC it does work.

>L<

|||Thanks for your help.

Report creates extra page

I have a report that should only generate 2 pages but generates 3.
Group 1 of the report has 41 rows
Group 2 of the report has 52 rows
When the report is generated, Group 1 creates Page 1, Group 2 creates Page 2
"and" Page 3. Page 2 has 46 rows and the remainder of the rows (6) overflow
to page 3.
Is there a limit? Or is there some setting somewhere to control this?
TIAThere is an option under the properties of the table/matrix to "fit on one
page if possible". Also at the top with file, edit, view....there is Report,
then report properties. Under the layout, the length of the page determines
how many objects can fit.
"czac" wrote:
> I have a report that should only generate 2 pages but generates 3.
> Group 1 of the report has 41 rows
> Group 2 of the report has 52 rows
> When the report is generated, Group 1 creates Page 1, Group 2 creates Page 2
> "and" Page 3. Page 2 has 46 rows and the remainder of the rows (6) overflow
> to page 3.
> Is there a limit? Or is there some setting somewhere to control this?
> TIA|||thanks for the reply, scraejtp.
I checked the "fit on one page if possible", this is checked. Also, with
the Layout properties of the Report, Page width: 11in. Page height:8.5in.
(for Landscape printing) Isnt this just for the printing of the report?
thanks again.
"scraejtp" wrote:
> There is an option under the properties of the table/matrix to "fit on one
> page if possible". Also at the top with file, edit, view....there is Report,
> then report properties. Under the layout, the length of the page determines
> how many objects can fit.
> "czac" wrote:
> > I have a report that should only generate 2 pages but generates 3.
> >
> > Group 1 of the report has 41 rows
> > Group 2 of the report has 52 rows
> >
> > When the report is generated, Group 1 creates Page 1, Group 2 creates Page 2
> > "and" Page 3. Page 2 has 46 rows and the remainder of the rows (6) overflow
> > to page 3.
> >
> > Is there a limit? Or is there some setting somewhere to control this?
> >
> > TIA|||No, if you change that length it will also change how much can fit on a page.
"czac" wrote:
> thanks for the reply, scraejtp.
> I checked the "fit on one page if possible", this is checked. Also, with
> the Layout properties of the Report, Page width: 11in. Page height:8.5in.
> (for Landscape printing) Isnt this just for the printing of the report?
> thanks again.
>
> "scraejtp" wrote:
> > There is an option under the properties of the table/matrix to "fit on one
> > page if possible". Also at the top with file, edit, view....there is Report,
> > then report properties. Under the layout, the length of the page determines
> > how many objects can fit.
> >
> > "czac" wrote:
> >
> > > I have a report that should only generate 2 pages but generates 3.
> > >
> > > Group 1 of the report has 41 rows
> > > Group 2 of the report has 52 rows
> > >
> > > When the report is generated, Group 1 creates Page 1, Group 2 creates Page 2
> > > "and" Page 3. Page 2 has 46 rows and the remainder of the rows (6) overflow
> > > to page 3.
> > >
> > > Is there a limit? Or is there some setting somewhere to control this?
> > >
> > > TIA|||...well...I changed the Page Height to 11in., that fixed the page 3 issue
but created a problem with printing landscape. Any ideas where to go from
here?
thanks.
"scraejtp" wrote:
> No, if you change that length it will also change how much can fit on a page.
> "czac" wrote:
> > thanks for the reply, scraejtp.
> >
> > I checked the "fit on one page if possible", this is checked. Also, with
> > the Layout properties of the Report, Page width: 11in. Page height:8.5in.
> > (for Landscape printing) Isnt this just for the printing of the report?
> >
> > thanks again.
> >
> >
> > "scraejtp" wrote:
> >
> > > There is an option under the properties of the table/matrix to "fit on one
> > > page if possible". Also at the top with file, edit, view....there is Report,
> > > then report properties. Under the layout, the length of the page determines
> > > how many objects can fit.
> > >
> > > "czac" wrote:
> > >
> > > > I have a report that should only generate 2 pages but generates 3.
> > > >
> > > > Group 1 of the report has 41 rows
> > > > Group 2 of the report has 52 rows
> > > >
> > > > When the report is generated, Group 1 creates Page 1, Group 2 creates Page 2
> > > > "and" Page 3. Page 2 has 46 rows and the remainder of the rows (6) overflow
> > > > to page 3.
> > > >
> > > > Is there a limit? Or is there some setting somewhere to control this?
> > > >
> > > > TIA|||Yes, I said it would also change the landscape. I don't see how else you want
it to fit. It will have to be longer to fit on the page, or make the typing
smaller to fit everything on the same size page. You could try keeping the
page size the same and making the margins less to fit a little more
information on the page.
"czac" wrote:
> ...well...I changed the Page Height to 11in., that fixed the page 3 issue
> but created a problem with printing landscape. Any ideas where to go from
> here?
> thanks.
> "scraejtp" wrote:
> > No, if you change that length it will also change how much can fit on a page.
> >
> > "czac" wrote:
> >
> > > thanks for the reply, scraejtp.
> > >
> > > I checked the "fit on one page if possible", this is checked. Also, with
> > > the Layout properties of the Report, Page width: 11in. Page height:8.5in.
> > > (for Landscape printing) Isnt this just for the printing of the report?
> > >
> > > thanks again.
> > >
> > >
> > > "scraejtp" wrote:
> > >
> > > > There is an option under the properties of the table/matrix to "fit on one
> > > > page if possible". Also at the top with file, edit, view....there is Report,
> > > > then report properties. Under the layout, the length of the page determines
> > > > how many objects can fit.
> > > >
> > > > "czac" wrote:
> > > >
> > > > > I have a report that should only generate 2 pages but generates 3.
> > > > >
> > > > > Group 1 of the report has 41 rows
> > > > > Group 2 of the report has 52 rows
> > > > >
> > > > > When the report is generated, Group 1 creates Page 1, Group 2 creates Page 2
> > > > > "and" Page 3. Page 2 has 46 rows and the remainder of the rows (6) overflow
> > > > > to page 3.
> > > > >
> > > > > Is there a limit? Or is there some setting somewhere to control this?
> > > > >
> > > > > TIA|||I have also had problem w/ xtra page. Sometimes it was due to a textbox
object in the header of my report. The textbox would 'hang' on the margin and
would force and extra page. Adjusting the size and or placement of header
items seemed to fix it. (also footer)
--
U. Tokklas
"scraejtp" wrote:
> Yes, I said it would also change the landscape. I don't see how else you want
> it to fit. It will have to be longer to fit on the page, or make the typing
> smaller to fit everything on the same size page. You could try keeping the
> page size the same and making the margins less to fit a little more
> information on the page.
> "czac" wrote:
> > ...well...I changed the Page Height to 11in., that fixed the page 3 issue
> > but created a problem with printing landscape. Any ideas where to go from
> > here?
> >
> > thanks.
> >
> > "scraejtp" wrote:
> >
> > > No, if you change that length it will also change how much can fit on a page.
> > >
> > > "czac" wrote:
> > >
> > > > thanks for the reply, scraejtp.
> > > >
> > > > I checked the "fit on one page if possible", this is checked. Also, with
> > > > the Layout properties of the Report, Page width: 11in. Page height:8.5in.
> > > > (for Landscape printing) Isnt this just for the printing of the report?
> > > >
> > > > thanks again.
> > > >
> > > >
> > > > "scraejtp" wrote:
> > > >
> > > > > There is an option under the properties of the table/matrix to "fit on one
> > > > > page if possible". Also at the top with file, edit, view....there is Report,
> > > > > then report properties. Under the layout, the length of the page determines
> > > > > how many objects can fit.
> > > > >
> > > > > "czac" wrote:
> > > > >
> > > > > > I have a report that should only generate 2 pages but generates 3.
> > > > > >
> > > > > > Group 1 of the report has 41 rows
> > > > > > Group 2 of the report has 52 rows
> > > > > >
> > > > > > When the report is generated, Group 1 creates Page 1, Group 2 creates Page 2
> > > > > > "and" Page 3. Page 2 has 46 rows and the remainder of the rows (6) overflow
> > > > > > to page 3.
> > > > > >
> > > > > > Is there a limit? Or is there some setting somewhere to control this?
> > > > > >
> > > > > > TIA|||I too had this problem (RS2005) - it is hugely frustrating. The solution for me turned out to be related to the width of my sub reports. To fix it, I had to play with the page width and margins in each of the sub reports (I set the margins to zero in parent and sub reports).
It seemed to be that my sub report changes would not show up when previewing the main report. Once I had saved and previewed the sub reports, then clicked the refresh button when previewing the parent report, the changes finally showed up and the extra page went away... hooray!
Good luck with this nasty proble
From http://www.developmentnow.com/g/115_2005_7_0_0_565515/Report-creates-extra-page.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

report builder not displaying some rows

Hi friends
am using report builder to create a report ,the report not displaying some rows.
i've 2 tables . Patients and transactions (1- many)

and assume that i've following records
patient
--
1 , sriram , albert street,nz
2 , david , victoria street ,aus

transactions (flds are patientid,charge,transaction date)

1 , $100, diabetes problem ,10/10/05
1, $100 , diabetes problem , 19/01/06
2, $150,someother problem

if i create a report with following fields

name , address (from patient table)

charge , problem (from transactions table) i get following results

sriram , albert street,nz,$100
david , victoria street ,aus , $150

as

u can see it ignored the 2nd record for patient "sriram" ?i used sql

profiler to get query report builder creating and if i run it in QA i

get all results am expecting but report builder thinks ignores 2rd

record as it thinks its duplicate.

i know how to fix this by

just adding transaction date field but the abv report is very common

one our users create ,is there any way report builder displays all rows ?
Thanks

it is still an issue for me !! no one has faced similar situation ?|||

Sorry: I have not used Report Builder (we're on SQL 2000 and Report Designer), but it sounds like it is just returning DISTINCT recordset.

There may be an attribute on the table? Or the query uses SELECT DISTINCT?

Perry

|||Thanks for the post Perry
The query RB creates is fine as it returns correct results if i run it QA.
but looks like RB doing a distinct after gettting the data!

report builder not displaying some rows

Hi friends
am using report builder to create a report ,the report not displaying some rows.
i've 2 tables . Patients and transactions (1- many)

and assume that i've following records
patient
--
1 , sriram , albert street,nz
2 , david , victoria street ,aus

transactions (flds are patientid,charge,transaction date)

1 , $100, diabetes problem ,10/10/05
1, $100 , diabetes problem , 19/01/06
2, $150,someother problem

if i create a report with following fields
name , address (from patient table)
charge , problem (from transactions table) i get following results

sriram , albert street,nz,$100
david , victoria street ,aus , $150

as u can see it ignored the 2nd record for patient "sriram" ?i used sql profiler to get query report builder creating and if i run it in QA i get all results am expecting but report builder thinks ignores 2rd record as it thinks its duplicate.

i know how to fix this by just adding transaction date field but the abv report is very common one our users create ,is there any way report builder displays all rows ?
Thanks
it is still an issue for me !! no one has faced similar situation ?|||

Sorry: I have not used Report Builder (we're on SQL 2000 and Report Designer), but it sounds like it is just returning DISTINCT recordset.

There may be an attribute on the table? Or the query uses SELECT DISTINCT?

Perry

|||Thanks for the post Perry
The query RB creates is fine as it returns correct results if i run it QA.
but looks like RB doing a distinct after gettting the data!

Report Builder mdx filter returns too many rows

I captured this mdx statement issued by Report Builder in SQL Profiler to
figure out why it was returning more than one year:
(SELECT
{ ( Extract( filter( { [Accounting Date].[MDY Date].Levels(1).Members * {
[Accounting Date].[Date].[Calendar] } },
( ( [Accounting Date].[MDY Date].CurrentMember.membervalue >= "09/01/2005" )
AND ( [Accounting Date].[MDY Date].CurrentMember.membervalue <= "09/30/2005" ) ) ),
[Accounting Date].[MDY Date] ) , * ) } on 0
FROM
[cube]
I just want September 2005 data, but it is returning September data for all
years in the cube.same filter in select statement rendered in Reporting Services Wizard:
FROM ( SELECT ( STRTOMEMBER(@.FromAccountingDateMDYDate, CONSTRAINED) :
STRTOMEMBER(@.ToAccountingDateMDYDate, CONSTRAINED) ) ON COLUMNS FROM [cube])
This works, just can't save report due to known bug using wizard against
Analysis Services 2005.
"bhorwatt" wrote:
> I captured this mdx statement issued by Report Builder in SQL Profiler to
> figure out why it was returning more than one year:
> (SELECT
> { ( Extract( filter( { [Accounting Date].[MDY Date].Levels(1).Members * {
> [Accounting Date].[Date].[Calendar] } },
> ( ( [Accounting Date].[MDY Date].CurrentMember.membervalue >= "09/01/2005" )
> AND ( [Accounting Date].[MDY Date].CurrentMember.membervalue <=> "09/30/2005" ) ) ),
> [Accounting Date].[MDY Date] ) , * ) } on 0
> FROM
> [cube]
> I just want September 2005 data, but it is returning September data for all
> years in the cube.
>

Report Builder - A filter is required

Using Report Builder, I am querying a table that has:

85,000 rows
5 columns

The report will run with only 4 of the columns, but when I add the 5th (data type is nvarchar(1024)), I get an error message that says:

Cannot run this report.
A filter must be specified.

When I add a filter on that 5th column, it will run.

Does anyone know if there is a record limit in Report Builder? Is there some sort of governor that I can modify?

Thanks in advance!I think I figured this out. The entity in my model had a property called "InstanceSelection" with the value of "MandatoryFilter." I cannot completely explain what this property does, but I changed the value to "List" and fixed the problem.

I don't know why the filter was mandatory only when the 5th column was there.

Report - Limit rows in table per page

I have a report that is a master-detail style report. The detail is displayed in a table. I would like to be able to limit the number of rows that can be in that table, and if there are more rows, it goes to the next page.

The report is essentially invoices. The master part has the name address, etc... and the detail is the line item detail. But with the format i am using, i can only fit x amount of line items per page (Depends on the size font i have). So, lets say i can only fit 10 items on a page. If there is an invoice with 12 items, i would like it to print 10 on the first page, and the last 2 on the second page. Everything else can be identical (doesn't really matter though). Is this possible?

Thanks

Hi bortiquai

you didn't mentioned which control you are using?

if you are using gridview than you can simple set the property

pagesize="10" or whatever you want

hope it helps

|||

I'm using the Table object in the Report Designer (RDLC), and the ReportViewer Control to view the report on the page. The pagesize=10 property on the GridView control is pretty much what I'm looking for, but the Table object in the report viewer does not have this property.

|||

This article will help you.

http://msdn2.microsoft.com/en-us/library/ms251668(VS.80).aspx

Look for "Page Breaks" section. This "=Int((RowNumber(Nothing)-1)/25)" expression will limit the number of rows displayed to 25 per page but you have to set it in the report. If you want your users to decide the number of pages, you just need to pass a parameter to the report that will contain the value. If you need help with that, let me know.

good luck...

Replication. Cant delete rows

Hi All.

I have the folling replication issue:

BD1 > BD0
Replicate with filter

BD2 > BD0
Replicate with filter

BD3

Four data bases BD0,BD1,BD2,BD3
In BD1 y BD2 there are two merged publications subscribed continuous in BD0.
The publications are defined as insert only and allow changes in the subscriber. Everything works perfectly. The records replicate immediately. In BD0 you can insert and modify records, however when I attemept to delete a record I get the following message:

error:

You can't insert a row with a duplicate key in the object 'dbo.MSmerge_tombstone' with unique index 'uc1MSmerge_tombstone'

Note: This is a translation of the original error message in Spanish below.

No se puede insertar una fila de clave duplicada en el objeto
'dbo.MSmerge_tombstone' con índice único 'uc1MSmerge_tombstone'

Cheers,

Have you defined multiple publications? i.e. a publication that is sent from BD0 to BD1 and a different publication that is sent from BD0 to BD2?