Crystal Reports XI - Sum of Cases between date range

Posted on 2009-04-23
Last Modified: 2012-05-06
Please look below at the attachment to see what I'm trying to do more clearly in case this isn't clear.

Have a report that I'm trying to build that WILL NOT be a CROSS TAB. I want to build a formula that says:
- SUM{tblcases.numbercases} in {tblcases.casedate} in Date(2008,10,01) to (2008,12,31)
-  This would of course be a sum, for a particular hospital, for Q408.

I would then build a separate formula for Q108, Q208, Q308 and so on and drop them in the appropriate spot in the  layout.

I want my current Quarter Total (in the example image USE Q108 as current) to calcualte absolute percentage increase or decrease over the previous quarter.

ANY HELP would be GREAT!!!! Thanks!
Question by:dsabine
    LVL 100

    Accepted Solution

    Try this idea

    If {tblcases.casedate} in Date(2008,10,01) to (2008,12,31) then

    You can then use a summary function on the formula

    Another way if you are going to group by the quarter is
    Group on the date field
    Select QUARTER as the period
    You can then summarize the fields in the group footer

    LVL 16

    Assisted Solution

    Well for your Quarter columns I would using running totals.

    Set up each like this.
    Field to Summarize: numbercases
    Summary Type: Sum

    Evaluate: Use a formula: This is where you would specify a date range for each quarter

    Reset: On change of group: CustomerName. Assuming you are grouping by customer (which you should be otherwise you run the risk of having a dup customer name.)

    Click ok. Do this for each. Then your variance column is a formula based on the running totals.

    Author Comment


    When I tried your solution, the Quarter Columns give me either a 1 or a zero but not a total for each hospital. I am able to do a running total that appears to be correct total for the quarter but I also need totals for each hospital.


    When I try your solution, it gives me totals for the hospitals but only for the date parameter set for the ENTIRE REPORT based on GROUP SELECTION in SELECTION FORMULAS. When I incorporate this, if the report is for all of 2008, the column shows total numbers for 2008 not just the quarter.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
    This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    745 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

    14 Experts available now in Live!

    Get 1:1 Help Now