grouping active accounts in Crystal Reports

Posted on 2012-09-17
Last Modified: 2013-01-17
I have a membership-based program where people sign up and their subscription lasts for 365 days.
I am trying to develop a crystal report that shows how many people are signed up at a given time e.g. each day (or instead of each day, I might choose the first day of each month as my date to reference to keep the number of data points fewer.).  
There is a date field in the database that signifies the start date of the membership.  
I can easily generate a report showing how many people are signed up at a particular date, and a report showing how many people have signed up during a time period, but I'm looking for a way to graph the # of currently enrolled over four years.  
I thought it would be easy, but I'm having trouble with the logic or something here.  I thought I'd be inserting groups for each day and then counting the subscriptions that fall between that day and 365 days before that day, but I'm having problems.

9/17/12:  1,209 enrolled (includes those enrolling between 9/17/11 and 9/17/12)
9/14/12:  1,235 enrolled (includes those enrolling between 9/14/12 and 9/14/11)
9/13/12:  1,2330 enrolled "" "" 9/13/12 and 9/13/11
4/20/10:  725 enrolled (4/20/10-4/20/09)
9/17/08:  112 enrolled (start date of the program was 8/20/08)
8/21/08:  15 enrolled
8/20/08:  0 enrolled

I need help.

Question by:Nero83
    LVL 18

    Expert Comment

    You should generate a table or a view with the dates for the last for years. I guess it will be enough to have a record per month. Then left join this table with the view presenting the grouped data by month. The result recordset will contain all months for the last 4 years and the number of clients per each month. The same logic could be applied on per day basis , but you will deal with more than 1200 records which might be an overkill.

    Once you have the recordset you can create a chart ( I guess your question is not about the chart preparation)

    The view to group numbers will be something like this:

    SELECT Date, count(*) From <YourTable> GROUP BY Date

    The whole SQL will be

    SELECT d.Date , t.Customers
    FROM Dates d
    LEFT JOIN (SELECT Date, count(*) as Customers From <YourTable> GROUP BY Date)  t
    ON d.Date=t.Date

    where Dates  is the table with all months/dates for the last 4 years

    Author Comment

    How do I generate a table or view - do you mean in another program?

    So, one table has a record for each month and the count of the people that enrolled during that month, and the other table has just the dates?  how do i eliminate those that have expired after a year from the count?  I'm confused...can you give me an example?

    LVL 18

    Assisted Solution

    What is your database type ? In SQLServer you can use a function like this one:

    CREATE FUNCTION [dbo].[fnGetDatesInPeriod]
        @FromDate DATE,
        @ToDate DATE
    RETURNS @DateTable TABLE (DateValue DATETIME)
        WITH DatesTable(CurrentDate) AS
            SELECT @FromDate AS datetime
            UNION ALL
            SELECT DATEADD(DAY, 1, CurrentDate) FROM DatesTable WHERE CurrentDate < @ToDate
        INSERT INTO @DateTable (DateValue)
        SELECT CurrentDate FROM DatesTable OPTION (MAXRECURSION 0)

    Open in new window

    It will return a list of all days between the @FromDate and @ToDate

    You can see an example how this function is used here:
    LVL 18

    Assisted Solution

    You can also generate all  possible dates and save them in a table and filter this table for the records in the last 4 years.

    If you are using a function you will need to base your report on a command.
    LVL 34

    Accepted Solution

    I think vasto has you headed in the right direction, but just to provide some explanation ...

     I see two basic problems with what you were trying to do.

     You basically want to group by the date (each day or month or whatever), but the only date you have in your table is the subscription date.  You could add 365 to that to get the end date, but if you grouped on that you'd just be grouping by the subscription end dates, which isn't what you want either.  For example, the 09/17 group would include only the people with subscriptions ending on 09/17.  You could group by month instead, but then each month would only be the people with subscriptions ending that month, which is still not what you want.

     The second problem is that CR only includes each record in one group.  Even if you were able to get the group to include everyone that was subscribed during that month, it would only be the people that were subscribed during that month, and not included in an earlier group (month) on the report.  Let's say you had the months in descending order, starting with September.  September would include everyone that was subscribed in September.  August would only include the people that were subscribed in August, but not in September (ie. their subscription ended in August), because the people that were still subscribed in September were included already in the September group.  July would only include the people that were subscribed in July, but not in August or September.  And so on.

     I hope that made sense.

     vasto's suggestion to use a table with dates in it handles both of these problems.  You put the dates you want (each day or month or whatever) in that table, and when you link your main table to that, those records are counted separately for each matching date.


    Author Closing Comment

    Doesn't allow me to do what I wanted, but that's a function of the program not lack of a good response.  Thanks.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    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 …
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 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

    9 Experts available now in Live!

    Get 1:1 Help Now