January 13, 2015 at 9:03 am
I am trying to count just the rows on group1 level without using CountDistinct in ssrs 2008 without counting the detail rows.
CountRows("group1") or count(group1) all gives me the detail count and countdistinct also gives me the distinct count which misses some records.
Thanks
January 13, 2015 at 10:15 am
gissah (1/13/2015)
I am trying to count just the rows on group1 level without using CountDistinct in ssrs 2008 without counting the detail rows.CountRows("group1") or count(group1) all gives me the detail count and countdistinct also gives me the distinct count which misses some records.
Thanks
If you are trying to get the sequential number for each group, where the output might look something like:
[font="Courier New"]1 Arizona -
01/2014 - $300
02/2014 - $350
03/2014 - $275
2 Nevada -
01/2014 - $150
02/2014 - $125
03/2014 - $200
3 Utah -
01/2014 - $185
02/2014 - $325
03/2014 - $210 [/font]
Each state is a group and the group number increments.
Go to Report Properties -> Code and create a function something like this:
Public iGroupNumber As Integer
Public Function fnGroupNumber() As Integer
iGroupNumber = iGroupNumber + 1
return iGroupNumber
End Function
Go to the group properties, variables and add a variable named something like groupNumber with an expression like this for the value:
=code.fnGroupNumber()
In a group row cell on the tablix, create an expression like:
=Variables!groupNumber.Value
Don Simpson
January 13, 2015 at 10:27 am
Incidentally, I have a similar problem with CountDistinct. For a particular report, where there are 15 groups, this function:
RunningValue(Fields!ClaimID.Value, CountDistinct, Nothing)
skips 2 when the set is ordered alpha ascending, so I get row 1, then 3 - 16
and it skips 13 when the set is ordered descending.
Don Simpson
January 13, 2015 at 10:43 am
Thank you so much for responding I have tried both I have just attached 2 images I want my Customer count to be 4 instead of 3 even though the account ending with 030 are in both sides the detail are different. thanks again for your help
January 13, 2015 at 10:45 am
This one seems to be a good start, is there a away I can restart or reset the count at the end of each account or group count.
January 13, 2015 at 11:16 am
This is the function I am using
Dim private count as integer = 0
Dim private CustName as integer = 0
public function nroFile(Byval rowNum as integer) as integer
if CustName =0 then
CustName =rowNum
end if
if rowNum= CustName then
count =0
end if
count = count + 1
return count
end function
And below is what I have in my cell =Code.nroFile(RowNumber(Nothing))
it works but I wish it could reset whenever a new group starts
January 13, 2015 at 12:22 pm
I did modify ur code a little bit by adding Shared but still is not being counted by group and help will be appreciated.
Public Shared iGroupNumber As Integer
Public Shared Function fnGroupNumber() As Integer
iGroupNumber = iGroupNumber + 1
return iGroupNumber
End Function
January 13, 2015 at 3:56 pm
You might have better luck doing the grouping is SQL.
Don Simpson
January 16, 2015 at 10:15 am
I agree about doing as much code as possible in the SQL directly
Much like we call a histogram
ex.
SELECT COUNT(*)
,[BusRoute]
,[BusRouteDescription]
FROM [ICES].[dbo].[CardAFCTransactionDetails]
where BusRoute is not null and BusRoute > ' ' and RecordType <> 6
group by [BusRoute]
,[BusRouteDescription]
order by [BusRoute]
,[BusRouteDescription]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply