How to count number of occurrences of data subject to a condition

Posted on 2008-11-05
Last Modified: 2012-05-05
I have a table containing sales leads. Each lead has a type, salesperson and a date created. I have a report showing total number of leads of type A against each salesperson - this is done by using the select expert to filter the leads to include only type A.

My problem is I want a second column to show the number of leads of type A which have a date created value that falls within a date range that I am entering as a parameter field. This is effectively a subset of the first column data. Is there an easy way to do this?
Question by:rjm2803
    LVL 4

    Expert Comment

    The attached code snippet should help.
    select count(*) as sales_count, salesperson
    from sales
    group by salesperson;

    Open in new window

    LVL 77

    Expert Comment

    It sounds like you could use a running totals field for this.

    Count any field , reset on change of group and for the evaluate when use a formula that says something like:

    {table.datefield} in {?dateparam}
    LVL 100

    Accepted Solution

    Agree with the running total idea.

    You could also use a simple formula that tests for meeting the conditions

    If ({DateFIeld} in {?DateParam}) then

    You can then use the summary functions on the formula to get the count.


    Author Closing Comment

    Thanks for that  - simple and works well

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now