Sum once per group 1

[marckssg]

Hi,

I am trying in effect to perform a distinct sum once per group, I had thought if I could identify the first record in the group then sum this I'd be fine, but its proving harder than expected.

Previously I've used running totals or variables to bypass the problem, however the manager requesting this report is insistent that he drills down from the top down, if that makes sense, so displaying data in the group footer won't cut it with him. Sub reports was another alternative but the performance his on this particular report is pretty bad.

I am using Crystal 2008 on an Oracle 10g database.

The data is for a hire and service company

The data is grouped;
Manufacturer
Product
Machine Serial Number
Call Number

With in a Manufacturer there are several product ranges, within a product range are the serial numbers for the machines and against a machine I have every call number against a machine of which there could be 10 or 20.

I have the monthly income per machine listed on one table, then when I join that to the call table I get it listed once per call. I only want to sum the income once, but need to list every call in full to sum the labour and product costs. I then do a comparison between the income and the expenditure at each group level.

Sorry for the long explanation, was trying to get everything in there!

Running totals are the obvious answer I know, but are no good to me here due to the insistence everything is displayed in the group header. A group number function that reset every group and could be used in a summary would be ideal, but I am having a total mental block on how to achieve it!!!

Cheers

Marc

 

[Madawc]

If you have a running-total count for the group, then this will be one for the first record.

Or if all records in the group have the same value, then you could do a summary total for the maximum or minimum.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[marckssg]

Hi thanks for the reply.

I can't do the running total option to identify the first record in a group as I then can't go on to sum this at a group header level.

Again Maximum and Minimum are no good to me as I may need to perform other summaries on the value eg;
Its a monthly value if they decide they want to run the report for a week (which they will!) then I'll have to calculate the lower income rate for the time period, then sum it up into headers.

My ideal forumula would be;
If group-row-number = 1 then Income else 0. Then I could drop what ever summaries I needed on it at whatever level of the report I wished.


Cheers

Marc

 

[lbass]

Another approach would be to use SQL expressions to return the summaries or to use a command as your datasource where you build in subqueries for the summaries. This might be complicated if you have a lot of tables.

-LB

 

[jmd0252]

ok try this idea, since the income and cost are the same on each line,, the when you do the sum,, define it as an average,, which should be the income or cost once.

 

[marckssg]

lbass - I'll take a look at SQL Expressions, its a part of Crystal I've always tended to under utilize.

jmd0252 - I did try the averages idea, but it doesn't roll up for the higher groups, even if I got it to work I'd have trouble when the request to break down the monthly value into shorter or longer periods comes through as I just know the requester will at some point want to run the report for varying time periods and as we only hold the monthly value in the database I'll have to perform the calculations in the report.

Cheers

Marc

 

[lbass]

Here is another method from Ken Hamady that looks promising:

http://www.kenhamady.com/news0912.shtml

Look for the item on "Calculating a Distinct Sum at the Top of the Report..."

-LB

 

[marckssg]

Thanks lbass, I'd been through Kens site but missed that one, its not going to work for me I get a "Not Supported" "OK" pop up box and a blank screen, but re-reading the text I breach the 1000 unique records.

There are 16500 machines, each tending to have 5 or 6 calls against them.

Not had much luck with SQL Expressions either, so still looking.

Cheers

Marc

 

[lbass]

Can you post the current SQL query from database->show SQL query?

-LB

 

[marckssg]

Hi lbass, thanks for your help, I've sorted it by creating a view that flags an event on the first call the machines have against them, I've then used this flag in an if then else statement that only brings through the days installed once per machine, job done!

Thanks for that link to Kens solution, I've booked marked that one for future use.

Cheers

Marc