Counting no. of records

Hi

I need the ability to calculate the no. of records based on the no. of times a value in the sql report is given. For example based on a table shown below:

Ref No. First Name Surname 18 test test 18 test test 18 test test 19 test test 19 test test

I need to calulate the records returned on the ref no. I have managed to set page breaks based on a new ref no. with grouping and therefore the count will be displayed at the end of each of the records returned. As you can see there are three records returned for ref no. 18 and 2 for 19. How can I achieve this.

Many thanks in advance

If an aggregate of that column doesn't already exist then create one using something like

SUM(Ref No) AS 'Number of Records'

You could do this in the SQL or you can create it as a function which would look something like

=SUM(Ref No)

This field should be added to the footer column of the group.

Hope this helps

|||Hi,
I have created the table with the same information you have given.
I opened my Sql Server Reporting Services 2005, creating the new report.
First I placed the table control, placing the ref no, firstname, surname then i have created new group in the table called - "refno" ....in the group footer i have used the function ===> =CountRows("refno")

I think you know, how to set page break after group ends....

Do this....You will get the answer?
If you have any queries..let me know...

M Sivakumar|||Thanks for your reply Harley.

I have tried what you have suggested but =SUM is calculating the total for the Ref No. So if the ref no. 18 occurs twice, I get the value 36.. and so forth. Is this the right function? I thought this function would be to calculate the numbers mathematically. I know there is a count function but do not know the syntax of the expression. I need to understand how to use this function to calculate the no. of records returned for the ref no. e.g. if ref.no 18 occurs 10 times in the report, then below it should say total records = 10 and so forth for other ref no.s. I hope this makes more sense.

Cheers|||Hi Siva

First of all thanks for your help in trying to help, greatly appreciated.

Right, I do know how to create groups and set page breaks which I have implemented in my report and have the group footer below my rows. I tried using the countrows function but I think I am doing this wrong, the way I have added this is by using:

=CountRows(Fields!RefNo.Value)

the function with the fieldname in the brackets (is this the correct way?)
Do I need the speech marks also, I have tried all possibilites but it gives me an error:

The value expression for the textbox ‘textbox70’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

Thank you again for your help

|||Sorry,

I meant use COUNT(RefNo) that should work, I always make that mistake then wonder why I get ridiculous answers :)

thanks|||

Hi,
No It is not the correct way. After creating the table, you just the create the new group and by default the group name will be "table1_Group1", you just modify the name into "refno" if you want to make it meaningful or you just leave it..
Now in the group footer...any of your cell you just add
"table1_Group1"
=CountRows("table1_Group1")

if u renamed into "refno"
=CountRows("refno")

I think, it will help your need.

|||I think I have found the solution to this. Siva first of all thank you for pointing me to the right direction.

The countrows function is indeed the right function for counting the rows returned. The way I have got this to work is by saying the following:

=CountRows("groupname") where groupname is the name of your group you have defined. It is imperative to include the speech marks.

I had a group set up as my criteria and have used this and seems to be working .

By the way, a good reference for this function and others supported by Microsoft use this website, its really good:

http://msdn2.microsoft.com/en-us/library/ms226986(en-US,SQL.90).aspx|||The above may be a better way of doing it but the way explained does work!|||Hi Harley

I'm sure it does work, not tried using it but I have the countrows function working so will stick with this one. In fact the reason why I feel the countrows function is better is due to the records returning actually fulfill my criteria which I specified in the group.

Cheers for your help too.