Group on Summary field

[mrmookster]

CR10 / SQL server (SP)

I am trying to group on a summary field as follows

G1 Financial Year Quarters
G2 Bill Value bands
G3 Bill Number

where G3 sums all the bills for that bill number, and G2 groups in bands based on the formula

select <BILL VALUE>
case 0 to 249.99 : "1. under £250"
case 250 to 999.99 : "2. £250 - £1k"
case 1000 to 4999.99 : "3. £1k - £5k"
case 5000 to 9999.99 : "4. £5k - £10k"
case 10000 to 49999.99 : "5. £10k - £50k"
case 50000 to 9999999999 : "6. over £50k"
Default: "Other"

the <BILL VALUE> needs to be the sum of G3 ie
Sum ({_SP_CR0009;1.TOTAL_BIL}, {_SP_CR0009;1.BILL_NUM})

However this throws up the error "Group specified on non recurring field)

How do i evaluate the bill sum before grouping level 2 ... is this possible?

 

[Madawc]

Crystal will only group data on some property of the individual record. That's a limit of the reporting tool, which is not a complete computer language.

You might try TopN to get the desired order.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[synapsevampire]

The best way to do this is to let the SP handle it by writing to a temp table.

select date, billnumber, sum(value) value
into #values
from table
group by datepart("q", date), billnumber

select '1. under £250' band, date, billnumber, sum(value) value
from #values
where value < 250
UNION ALL
select '2. £250 - £1k' band, date, billnumber, sum(value) value
from #values
where value < 1000
UNION ALL
etc.

It's ALWAYS best to let the database do the work.

I'd suggest posting in the SQL Server forum as my SQL Server is a tad rusty and there may be a more efficient means.

-k

 

[mrmookster]

I agree I nearly always push the processing onto the database server. And this is what I have done. However I need the ability to drill into the bill detail. So the only way I can see to do this is to create subreports for each of the bills (very inefficient) to allow the user to see bill detail if required.

 

[lbass]

You could create a command similar to what SV is suggesting(database expert->your datasource->add command):

Select sum(table.`amt`) as grpsum, table.`billno`,table.`date`
From `table`table
group by datepart("q", table.`date`), table.`billno`

Link the command to the main table on bill number and date. You can then group on a formula like:

select {command.grpsum}
case 0 to 249.99 : "1. under £250"
case 250 to 999.99 : "2. £250 - £1k"
case 1000 to 4999.99 : "3. £1k - £5k"
case 5000 to 9999.99 : "4. £5k - £10k"
case 10000 to 49999.99 : "5. £10k - £50k"
case 50000 to 9999999999 : "6. over £50k"
Default: "Other"

-LB

 

[synapsevampire]

Please post the actual requirements in your posts as opposed to a moving target.

If you had already done this, then you couldn't have been asking for what you did because you wouldn't have had the level of detail referenced.

You can add in a join from the original table to the union above (of course the union wouldn't require the sum then) to return the quarter, band level information and the details.

Again, for advanced SQL try a SQL Server forum, we're generally average SQL coders hereabouts.

-k