Report limited to Top 8 but not in the SQL satement?

I got a report that seems to be limiting its displayed data to the first 8 records but not in the SQL statement itself. Although I have been working with SQL for many years I'm very new to Reporting Services so it may be something very simple, like perhaps a property that can be changed on the report for me to increase the amount of records the report is pulling from the database?

I don't seem to find any property set to 8 though.

Also the report is not pulling the records in the order the query pulls them, but in an apparent random order, I don't need to change this but it may help describe the report a bit better.

In Visual Studio, are all the records returned when you execute your SQL statement while you're in the Data tab?

Check to see if you have any filters on your table. Right click on your table and go to Properties, then check the Filters tab. Filters limit your recordset after it is returned to the report, so your query would return it, but it wouldn't show on your report.

This may sound silly, but are you sure the rest of the records are not on another page?

Jarret

|||Yes, the records are all returned without limits in the Data tab, and no there are no extra pages. I was told by the report user that it is intentionally set to, according to him, "8 random records", but for the records appear to be the same 8 every time, just not sorted in any logical order.

I checked the filters in the dataset as that didn't occur to me at first but there were no filters for this dataset.
|||

Do you have any grouping in your table? If so, are you hiding the detail records and only showing the group header/footer?

Can you try running Profiler when the report runs to make sure the query is being run correctly?

Jarret

|||There is no grouping, The query is a very simple select, although it calls a Stored Procedure it is working perfectly. I tested it inside the Visual Studio Business Intelligence environment and inside SQL Management Studio, plus ran the simple SQL that is inside the Stored Procedure on it's own, all with the same results (all records being returned.)

I was told this report was intentionally designed to show 8 records, I simply was requested to increase it to 10.
|||

Well, I think I will have to defer to someone else to help you. I've never seen RS 'eat' records before without having a filter of some sort. Like a filter on the table, a 'top' in the query, or a 'set rowcount X'.

Can you get in contact with the report creator and see how they got it to only show 8?

You could open the RDL file in notepad and see if you can see an '8' anywhere, and what setting it is on.

Jarret

|||Thank you, i forgot that rpl files where xml. I looked it up and found a filter being applied to a "Table1", oddly it never shows in the interface.

Code Snippet

<Table Name="table1">
<Filters>
<Filter>
<Operator>TopN</Operator>
<FilterValues>
<FilterValue>=8</FilterValue>
</FilterValues>
<FilterExpression>=Fields!Account_Number.Value</FilterExpression>
</Filter>
</Filters>

I changed the 8 to 10 but I'm still lost on why it does not show up in the dataset filters... although also there is no dataset called "table1".
|||

I understand now. Here, do this:

In your properties window (F4), use the drop-down to select 'table1'. This is the name of the table object in your report. Then, in the properties window, find and click on 'Filters'. It should say (Collection) next to it, click on the '...' button in the filters property. This will open a dialog box with an expression, operator, and value. In your case, you will see the following settings:

Expression: =Fields!Account_Number.Value

Operator: =

Value: =8

Here is where you can change the number of records you want returned.

Jarret

|||Thank you, this information is very useful!