Get Row Count in SSRS Report Builder

Question

I have built a report using Report Builder 3.0 (that uses SQL Server 2008 R2). Now i wish to now how many records are being fetched from database to the report?

This is possible either by count function in SSRS or by using RANK/ROW_NUMBER function in SQL Query and assigning that as field to the report (RANK/ROW_NUMBER would give us rank to each row and navigating to last page in report would help me getting the total row count).

I tried count function but that counts on some field in the report. For instance = Count(Field!FieldName.value, "DataSetName") Problem in this approach: "FieldName" is not unique in the report and hence the counts get repetitive

Second option: Added Rank/Row_Number but they too use the same kind of fieldName and hence here too the counts get duplicated.

Main Problem: There is no field in my query that is unique (and hence i tried ROW_NUMBER())

How can i find the total row count or rank (for each row) in SSRS 2008?

Answer 1

Use the CountRows function. For example

=CountRows("MyDataset")

will give you the number of rows in MyDataSet.

Answer 2

As someone else mentioned above, I couldn't get CountRows("DatasetName") to work in the header until I wrapped it thusly:CSTR(CountRows("DatasetName")).

Answer 3

In the Tablix control's properties, there's a property name called NoRowsMessage put your message here when no row is returned.

Answer 4

I found a workaround for this. First create a data column with the value always set to 1. This will provide a value of one for each row of data.

Query Column

, 1 AS Unit

Use the "RunningValue" function into your report as shown below.

=RunningValue(Fields!Unit.Value,Sum,"DataSet")

This will also work as a 'running sum' if that's something you're looking for.

Answer 5

you can't put aggregation values into the detail wihtout grouping. Solution is below: =Count(Fields!rn.Value)

I use it inside the column/header row.