SQL Server Performance

SQL counting rows for multiple criteria

Discussion in 'SQL Server Reporting Services' started by erickwidya, Jan 23, 2006.

  1. erickwidya New Member

    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
  2. mkmathi New Member

    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

  3. erickwidya New Member

    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
  4. mkmathi New Member

    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

  5. erickwidya New Member

    argh..i see..thx

  6. mkmathi New Member

    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=':p' />]<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 />
  7. erickwidya New Member

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

Share This Page