Crystal Reports Need to Group by Derived Date Range

Crystal reports group data by floating hours

It worked like this:

1. Created new operator "minutes" (number).

2. Created new formula "floating_hour":

   if minute({TableName.RASP_DATE}) < {?minutes} then
   time(hour({TableName.RASP_DATE})-1,{?minutes},0) else
   if minute({TableName.RASP_DATE}) >= {?minutes} then
   time(hour({TableName.RASP_DATE}),{?minutes},0) 
   

3. Created a group by "RASP_DATE" (Order by weeks)

4. Created a group by "floating_hour" formula.

5. Summarized RACE_HOURS in the "floating_hours" group.

Group Summary Involving dividing in Crystal Reports

I've done something similar but not using Percentages. The way I thought it out is almost painful to do, but you will have drilldown capabilities. I'll post what I've come up with but if someone can modify this to make it easier, by all means.

First, create the 8 groups you want use and hide them. You will need 14 formulas to create columns for your periods. For your first period, the formula (and following periods) should look like this:

IF {table.period} = "01-2012" THEN
    IF DrillDownGroupLevel = 0 THEN
        SUM({table.sales}, {table.group1}) / SUM({table.goal}, {table.group1})
    ELSE IF DrillDownGroupLevel = 1 THEN
        SUM({table.sales}, {table.group2}) / SUM({table.goal}, {table.group2})
    ELSE IF DrillDownGroupLevel = 2 THEN
        SUM({table.sales}, {table.group3}) / SUM({table.goal}, {table.group3})
    ELSE IF DrillDownGroupLevel = 3 THEN
        SUM({table.sales}, {table.group4}) / SUM({table.goal}, {table.group4})
    ...
    ELSE
        SUM({table.sales}, {table.group8}) / SUM({table.goal}, {table.group8})
ELSE
    0

What this formula does is identify what period it is, then determine what group level it is on. It then Sums up the Sales and Goal considering what the group is suppose to be on that level.

• For line IF {table.period} = "01-2012" THEN, I'd set up as a parameter so it will work for multiple years.
• I would rather use a function to determine what group level I was on, but all I know is DrillDownGroupLevel. It is useful but flawed if you try to do something fancy with the groups (conditional surpression) don't do a simple drilldown.
• Change {table.group1} to what ever field you used for that group level. i.e. {table.company}

A drawback on this formula is every time you drill down, the DrillDownGroupLevel will change. The new level you reveal will be correct, but previous levels that still show will change.

Hope this helps.

Related Topics