count of records greater than?

Hello All,

Trying to set up a column in a grouped matrix that displays a count of all record over a specificed number.

The field I am counting are response time of transaction and I want to count how many were over 500 milliseconds. I though it would be something like this...

Code Snippet

=Count(Fields!ResponseTime.Value > "500")

However, this appears to just return the count of all rows and ignores the "500" part.

Am I missing something? If someone could post a alternate code snippet, that would be great.

Thanks in advance,

Clint

Hi Clint,

Try this expression

Code Snippet

=Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

Best Regards,

Rajiv

|||

Thanks that works well..as an after thought, I need to add something in that would separate on of the field that has a different threshhold. Its grouped up and all but one has the 500 threshold count but one has 1000. Any ideas on how to separate it out?

I was thinking

Code Snippet

=Count(iif (Fields!ResponseTime.Value="QMEN" > 1000,1,nothing)) or Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

But that errored out.|||

Need a second iff. Try just wrapping it around the timeout value

(I can't see your original post, so I'm just going to alter the innermost part of it. I don't think you meant the responsetime = QMEN..)

. . . > iif(XXXX.Value="QMEN", 1000, 500) . . .

|||

How would I incorporate that into?...

=Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

Thanks.

|||

=Count(iif (Fields!ResponseTime.Value > iif(XXXX.Value="QMEN", 1000, 500) , 1, nothing))

Where XXXX is whatever field has the value QMEN

|||Awsome. Thanks so much. the worked perfectlysql

Count Group Members

Hi,

I'm trying to make a matrix report for current and previous month. I've gotten the matrix to show current month dat on the top data region and previous month on the side data region. Now i'm trying to compare and count group members for current and previous month.

example: count for students with the same exam score. If a student got 20 on thier exam Previous month and 20 on their current month exam, they will be counted as 1.

20

40

60

80

100

20

2

4

0

7

1

60

0

1

0

4

0

80

2

0

3

0

0

so the data shows 2 students who got 20 last month also got 20 this month. It also shows 4 students who got 20 last month managed to get 40 this month, and so on.

How do i count the students as i'm using student ID.

Thanks in advance.

Hi,

count is overloaded. YOu can also use that with a group expressions COUNT(SomeValue, SomeGroup)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Thanks for the reply. I've already tried that method. the count only produced 0, but there is data to be counted.

the data grouping is like this: Year -> Month -> Class -> ID -> Score becuase the score is the measure or detail data. However i group the data in the report by Year -> Month -> Class -> Score and then count the IDs that are associated with each distinct score value.

This is to compare how many student got a certain score amount and what are thier scores for the current month as in the sample I have given previous post.

Hope this is much clearer explanation than before.

|||Here's an idea for getting this to work. When querying for the data, join the table with itself, based on the student's id and the month. This will give you one dataset with the student's data for each month compared to each of the following months that you can use to build the matrix.
Using this data

TestDate ID Score
1/1/2000 1 20
1/1/2000 2 20
1/1/2000 3 20

1/1/2000 4 20
1/1/2000 5 20

1/1/2000 6 20
2/1/2000 1 20

2/1/2000 2 20

2/1/2000 3 40

2/1/2000 4 40

2/1/2000 5 40

2/1/2000 6 40

The query would look something like this

Select t1.TestDate AS TestDate, t2.TestDate AS TestDate2, t1.Score AS Score, t2.Score AS Score2, t1.ID FROM TestScores As t1 INNER JOIN TestScores As t2 ON t1.ID = t2.ID AND t1.TestDate < t2.TestDatesql

Could not connect to database

Hello I am new to the ASP.NET project and using Microsoft ASP.Net Web Matrix. When I try to connect to an Access Database it is no problem, but do I try to set up an connection with a SQL or MSDE database there is a problem.

It can not connect with my SQL server and gives the error:
Could not create new database.

What can be the problem. My MSSQL Server is running perfect.

Greets
Ben
HollandNeed more information. Are you using a connection string or creating a connection object in Visual Studio. Can you find your SQL Server instance in the VS Server Explorer?