Looking for a way to show a row number on a report. The trick here is the rows are aggregated to show sums. The RowNumber() function works great, but it totals every row used in the sum, so while there may be 34 rows, I am only displaying one total row and thus would like the row number on the report to say 1. I had the thought to try this with a variable and custom code function, but couldn’t figure out the syntax.
Any help appreciated.
Sometimes I’ll make another dataset Ross, like you just do a query for the dataset and make the query do what you’re asking. Count(x) group by Y
If it’s comign from a report data set and not a direct sql statement.
Did you try adding in the scope?
Yes. RowNumber(Nothing) and RowNumber(“GroupName”) give the same output. I assume this means that the rows are created to make the sum and then hidden before the final result. Here is an idea of what I am looking for and what I get now.
Do you have a detail group in your report?
I had to remove the detail group to get the aggregates to work correctly. Maybe I have something set up incorrectly to cause that I don’t know.
Yeah, SSRS is funny, and I always forget the exact tricks until I do it again.
But as long as the groupings don’t cause duplicate entries, you should be able to just drop a field in the correct Group row and have it aggregate correctly. If you add in a Detail group and show it, what do you get? Is it the individual records or just a repeat of the group above?
I had an idea on the way to the office this morning that seems to have worked. I merged the RunningValue function with a CountDistinct like this:
=RunningValue(CountDistinct(Fields!Calc_ponum.Value), Sum, Nothing)
and it seems to work great:
