SQL Server Performance Forum – Threads Archive

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, Have you solved your isses?[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />].Is runningvalue function working for condtional count?[8D] let me know ManyThanks [8D] Mathi[<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />] 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

<blockquote 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"></blockquote id="quote"> i already send a link to this post, hope we can get some news about it from him PS: this is not my issue <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

]]>