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?

score 64 · Accepted Answer · answered Feb 03 '12 at 16:55

64

Use the CountRows function. For example

=CountRows("MyDataset")

will give you the number of rows in MyDataSet.

answered Feb 03 '12 at 16:55

Jamie F

23,189
5
61
77

It is simple to get row count. Just use of ROW_NUMBER in SQL Query would fulfill the aim – xorpower Feb 09 '12 at 06:57
Also, if you're using FetchXML (i.e. for Dynamics) you won't be able to execute SQL functions. – Mark Micallef Mar 05 '18 at 03:58

score 5 · Answer 2 · edited Jul 31 '19 at 14:51

5

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

edited Jul 31 '19 at 14:51

Brian Tompsett - 汤莱恩

5,753
72
57
129

answered Jul 31 '19 at 14:39

chainsofthought

51
1
3

@Brian - thanks for marking the code for me, much more legible. I'll have to remember that! – chainsofthought Jul 31 '19 at 17:06

score 1 · Answer 3 · answered Sep 27 '18 at 01:46

1

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

answered Sep 27 '18 at 01:46

Tholitz_Reloaded

102
2
7

score 1 · Answer 4 · answered Aug 10 '21 at 12:09

1

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.

answered Aug 10 '21 at 12:09

Mich28

519
3
14

score 0 · Answer 5 · answered Sep 27 '18 at 13:58

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.

score 0 · Answer 6 · answered Jun 29 '23 at 14:12

0

I used rowcount to get the index count per row in the grid

answered Jun 29 '23 at 14:12

Golden Lion

3,840
2
26
35

Get Row Count in SSRS Report Builder

6 Answers6