CODEWITHSUNDEEP
crystal-reports
vice
vice

Reputation: 605

Monthly maximum values of summarized fields

Good afternoon. I am attempting to produce a report that shows monthly summaries of stock holdings.

The table I am using has the following data (datatype): Site (string), Customer (string), Week Ending Date (int - YYMMDD format), Closing Balance (int)

And would look something like this:

Site Cust   Wk End  Bal

UK - Dave - 110821 - 25

UK - Sam - 110821 - 15

UK - John - 110821 - 30

UK - Dave - 110828 - 80

UK - Sam  - 110828 - 30

UK - John - 110828 - 10

UK - Dave - 110904 - 100

UK - Sam  - 110904 - 29

UK - John - 110904 - 51

UK - Dave - 110911 - 97

UK - Sam  - 110911 - 34

UK - John - 110911 - 12

What I am trying to find is the maximum weekly closing balance per site. Such that it may display information like:

UK - 08/11 - 120

UK - 09/11 - 180

It needs to produce for each site, one line per month with the maximum week's total for that month.

The month/year formula is a couple of substrings from Week End to pull the month/year (remembering data is in YYMMDD format).

When I try this in crystal using groups across Site and the formula to produce MM/YY. I always receive "Cannot summarize this field" errors when trying to get the maximum of the month's weekly summary.

EDIT: I have written SQL to return one site's results. This may help explain the problem better. Please see below:

  Firstly a definition for clarity MMYY=(substr(A.WEEKEND,3,2)||'/'||substr(A.WEEKEND,1,2))

    select distinct(MMYY) as Month, MAX(B.CLOSEBAL) as MonthMax
    from balance_file as A
    join(
    select MMYY as dte,
    sum(CLOSING) as CLOSEBAL
    from balance_file
    where SITE='UK'
    group by WEEKEND
    order by WEEKEND)
    as B on MMYY=B.dte
    where WEEKEND>110101 and WEEKEND<110505
    group by WEEKEND
    order by Month

Upvotes: 2

Views: 3954

Answers (2)

paulmelnikow
paulmelnikow

Reputation: 17208

I think you can do this with a running total instead of a variable.

Write a formula TotalClosingBal:

Sum({ClosingBal}, {WeekEnd})

Then create a running total:

  • Evaluate Maximum of TotalClosingBal
  • On change of group {WeekEnd}
  • Reset on change of group {Month}

Upvotes: 0

Ryan
Ryan

Reputation: 7287

First, set up your groups: Create a formula to convert that Week Ending Date integer into a date. Now you can outer group on the site and inner group on the new date formula that you have just created. In the latter group, you can set to print by month (Group Expert -> Options -> "This section will be printed: for each month").

Next, it's only a matter of doing a maximum summary for each inner group on {table.close_balance}. (Insert Summary -> Field to Summarize: {table.close_balance}, Calculate this summary: Maximum, Summary Location: Group 2 {@DateFormula}).

Now each Group Footer 2 will show an entire month and the maximum weekly end balance in that month for each site.

OK, let's try this again.

In addition to the two groups above (Site and Month) you can add a final inner grouping by Week using the same formula. So G1 - Site, G2 - Month, and G3 - Week.

Create a formula, {@reset}, that can be placed in Group Header 2 and will reset the variable 'max'.

whileprintingrecords; numbervar max := -1

Create another formula that can be placed in Group Footer 3 that updates the 'max' variable when it is appropriate.

whileprintingrecords;
numbervar max;
local numbervar weeklySum := sum();
if max < weeklySum then max := weeklySum

Finally, create a third formula that can be placed in Group Footer 2 to display your monthly max summary.

whileprintingrecords;
numbervar max

Upvotes: 2

Related Questions