I want to count the number of rows in my result DataSet, I put in my DataSet a calculated field with this expression :

=CountRows("DataSet1") But I have the following error message:

"The expression used for the calculated field '=CountRows' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions." What should I do ? thanks

  • Why don't you just include a count column in the query? – Aaron Bertrand Nov 28 '14 at 13:02
  • Because I have Lead function and over partition, it will count by group by – BKChedlia Nov 28 '14 at 13:13
  • Add COUNT(*) OVER() as a column in the query. This does not require or interfere with grouping. – Aaron Bertrand Nov 28 '14 at 14:33
  • it will add multiple rows instead of 1 row which contains the rownumber of table at the end – BKChedlia Nov 28 '14 at 14:43
  • No, it will add an extra column with the same value populated in every row (but if you look at the plan, it should only pay the calculation cost once). SSRS can then look at that column from the first or last row to show the count. If this is a problem for some reason, then add a separate result set (SELECT <dataset query>; SELECT rc = @@ROWCOUNT;). Or read the docs on SSRS to see how to derive the row count properly. – Aaron Bertrand Nov 28 '14 at 16:23

You cannot use an aggregate like CountRows() in a calculated field in your dataset. But you can use CountRows() as an expression in a textbox (alone or within a table) scoped to your dataset. Now that you have your dataset created, you can put a textbox on the report and populate it with the expression =CountRows("DataSet1"), and it will provide the correct answer.

Otherwise, you will need to modify your dataset to include the rowcount. It would seem that using the expression in the report would be the desired option as modiying the dataset would give you a column populated with either a running total or the total rows repeated on each column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.