SQL counting rows for multiple criteria

The project I am working on involves Reporting Services.

The requirement is to track sales representative contact activity with clients.

For example:

Total Calls Made to clients.......xxx
Total Appointments.................xxx
Total Calls to Active Clients.....xxx
Total Calls resulting in Sale......xxx
Total Meetings.......................xxx
...
... etc
...

The problem I have is that the data is stored in one table, each row representing a contact.

The columns identify the

record type.........: call / appointment / message / other
Sub record type..: call in/ call out / message in/.....etc
result code.........: Very Interested / Moderate Interest / Sale / No interest
Client Status......: Active / Prospective

I believe I need to do separate counts based on the specific criteria for each report item.
I am new to Reporting services, I don't know if it has the ability to count detail lines at different criteria.
So I thought this would have to be done in the SQL query extracting the data.

Does anyone know of a sql approach to extract different criteria counts into on result?

Thanks

PS: this issue not belong to mine, just curious what the solution would be and sorry if i post at wrong forum

We have functions called Runningvalue & IIF
we can use above functions to get condtional aggregate
syntax :

Runningvalue : RunningValue(Fields!Fieldname.Value, any one of aggregate , Region)
iif : iif(condtions,truepart,falsepart)

try this and let me know your comments

To get condtional count write expression like below

=RunningValue(iif(Fields!RecordType.Value ="CALL",Fields!RecordType.value,System.Dbnull.value),Count,"DataSet1")

Thanks
Mathi





Thanks & Regards
Mathivanan K
Great work may have to pass through these stages - ridicule, opposition, and then acceptance. Each man who thinks ahead of his time will probably be greatly misunderstood.- vivekananda

thx Mathivanan

but the RunningValue u give seem to me like function from client code not in MSSQL/Query, am i right?

can u elaborate more?

thx

This is related to Reporting service functions.place a textbox in sql report and write click u will get expression menu there u have to write these expressions.
this is nothing but Formulas like crystal

Thanks & Regards
Mathivanan K
Great work may have to pass through these stages - ridicule, opposition, and then acceptance. Each man who thinks ahead of his time will probably be greatly misunderstood.- vivekananda

argh..i see..thx

Hi Erickwidya,<br />Have you solved your isses?[<img src='/community/emoticons/emotion-1.gif' alt='' />].Is runningvalue function working for condtional count?[8D]<br />let me know<br />ManyThanks [8D]<br />Mathi[<img src='/community/emoticons/emotion-4.gif' alt='' />]<br /><br />Thanks & Regards<br />Mathivanan K<br /> Great work may have to pass through these stages - ridicule, opposition, and then acceptance. Each man who thinks ahead of his time will probably be greatly misunderstood.- vivekananda<br /><br />

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Have you solved your isses?.Is runningvalue function working for condtional count?<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />i already send a link to this post, hope we can get some news about it from him<br /><br />PS: this is not my issue <img src='/community/emoticons/emotion-1.gif' alt='' /><br />