Summarizing Summary Data

I am trying to create a program which will provide some basic Sales Analysis
data to my users, without having to build cubes on my server. My data exists
in four related tables. Customer Groups, Sales Channel, Invoices and Cost
Detail.

Customer Groups and Sales Channel are linked to Invoices on Customer ID
Invoices are linked to Cost Detail on Invoice ID and Invoice Line ID.

A Customer can belong to 0 to X Customer groups
A Customer belongs to 1 Sales Channel
There are 1 to X Invoices Per Customer
There are 1 to X Invoice Lines Per Invoice
There are 0 to X Costs Per Invoice Line

A sample SQL Statement would be similar to the following:

Select [Sales Channel].[Sales Channel],
Invoices.Invoice,
Invoices.Customer,
Invoices.Product,
Invoices.[Total Sale],
Sum([Cost Detail].Cost As [Total Cost]
From [Sales Channel]
Inner Join Invoices on
[Sales Channel].[Customer ID] = Invoices.[Customer ID]
Left Outer Join [Cost Detail] on
Invoices.[Invoice ID] = [Cost Detail].[Invoice ID] And
Invoices.[Invoice Line ID] = [Cost Detail].[Invoice Line ID]
Where clauses as needed
Group By [Sales Channel].[Sales Channel],
Invoices.Invoice,
Invoices.Customer,
Invoices.Product

My problem comes in when I try to get a total of [Total Sale], and [Total
Cost] by Customer or Product after I summarize the Costs. Grouping By a
higher level causes multiple rows to be returned for each Invoice Line.

Is there an easy way to summerize data after the initial dataset is returned?

--
Richard A. Welch
IT Manager
House of Raeford Farms, Inc. - Raeford Division

Elton
Fri Feb 18 21:31:50 CST 2005

Hi Richard,

Although in DataTable there is method Compute can be used
for aggregate function such as Count, Sum, it can't
perform 'group by' function. So when summarizing, it
returns only single result either grand total or total for
a single Customer (or Product).
I think you might create a view based on your sql query
given. Then you can query sum Total Cost from the view. It
is similar to the following:

Select Customer, Product, Sum([Total Cost]) From viewName
Group by Customer, Product

Or a better solution is to use Report Tools, such as
Crystal reports, to show whole query data and subtotals
for individual customers, products.

HTH

Elton Wang
elton_wang@hotmail.com

>-----Original Message-----
>I am trying to create a program which will provide some
basic Sales Analysis
>data to my users, without having to build cubes on my
server. My data exists
>in four related tables. Customer Groups, Sales Channel,
Invoices and Cost
>Detail.
>
>Customer Groups and Sales Channel are linked to Invoices
on Customer ID
>Invoices are linked to Cost Detail on Invoice ID and
Invoice Line ID.
>
>A Customer can belong to 0 to X Customer groups
>A Customer belongs to 1 Sales Channel
>There are 1 to X Invoices Per Customer
>There are 1 to X Invoice Lines Per Invoice
>There are 0 to X Costs Per Invoice Line
>
>A sample SQL Statement would be similar to the following:
>
>Select [Sales Channel].[Sales Channel],
>Invoices.Invoice,
>Invoices.Customer,
>Invoices.Product,
>Invoices.[Total Sale],
>Sum([Cost Detail].Cost As [Total Cost]
>From [Sales Channel]
>Inner Join Invoices on
>[Sales Channel].[Customer ID] = Invoices.[Customer ID]
>Left Outer Join [Cost Detail] on
>Invoices.[Invoice ID] = [Cost Detail].[Invoice ID] And
>Invoices.[Invoice Line ID] = [Cost Detail].[Invoice Line
ID]
>Where clauses as needed
>Group By [Sales Channel].[Sales Channel],
>Invoices.Invoice,
>Invoices.Customer,
>Invoices.Product
>
>My problem comes in when I try to get a total of [Total
Sale], and [Total
>Cost] by Customer or Product after I summarize the Costs.
Grouping By a
>higher level causes multiple rows to be returned for each
Invoice Line.
>
>Is there an easy way to summerize data after the initial
dataset is returned?
>
>--
>Richard A. Welch
>IT Manager
>House of Raeford Farms, Inc. - Raeford Division
>
>.
>

Richard
Fri Feb 18 22:29:02 CST 2005

Thank you. I had about decided to return the result set then read through it
and put the results into a data table doing the totalling as I read each
record.


"Elton Wang" wrote:

> Hi Richard,
>
> Although in DataTable there is method Compute can be used
> for aggregate function such as Count, Sum, it can't
> perform 'group by' function. So when summarizing, it
> returns only single result either grand total or total for
> a single Customer (or Product).
> I think you might create a view based on your sql query
> given. Then you can query sum Total Cost from the view. It
> is similar to the following:
>
> Select Customer, Product, Sum([Total Cost]) From viewName
> Group by Customer, Product
>
> Or a better solution is to use Report Tools, such as
> Crystal reports, to show whole query data and subtotals
> for individual customers, products.
>
> HTH
>
> Elton Wang
> elton_wang@hotmail.com
>
> >-----Original Message-----
> >I am trying to create a program which will provide some
> basic Sales Analysis
> >data to my users, without having to build cubes on my
> server. My data exists
> >in four related tables. Customer Groups, Sales Channel,
> Invoices and Cost
> >Detail.
> >
> >Customer Groups and Sales Channel are linked to Invoices
> on Customer ID
> >Invoices are linked to Cost Detail on Invoice ID and
> Invoice Line ID.
> >
> >A Customer can belong to 0 to X Customer groups
> >A Customer belongs to 1 Sales Channel
> >There are 1 to X Invoices Per Customer
> >There are 1 to X Invoice Lines Per Invoice
> >There are 0 to X Costs Per Invoice Line
> >
> >A sample SQL Statement would be similar to the following:
> >
> >Select [Sales Channel].[Sales Channel],
> >Invoices.Invoice,
> >Invoices.Customer,
> >Invoices.Product,
> >Invoices.[Total Sale],
> >Sum([Cost Detail].Cost As [Total Cost]
> >From [Sales Channel]
> >Inner Join Invoices on
> >[Sales Channel].[Customer ID] = Invoices.[Customer ID]
> >Left Outer Join [Cost Detail] on
> >Invoices.[Invoice ID] = [Cost Detail].[Invoice ID] And
> >Invoices.[Invoice Line ID] = [Cost Detail].[Invoice Line
> ID]
> >Where clauses as needed
> >Group By [Sales Channel].[Sales Channel],
> >Invoices.Invoice,
> >Invoices.Customer,
> >Invoices.Product
> >
> >My problem comes in when I try to get a total of [Total
> Sale], and [Total
> >Cost] by Customer or Product after I summarize the Costs.
> Grouping By a
> >higher level causes multiple rows to be returned for each
> Invoice Line.
> >
> >Is there an easy way to summerize data after the initial
> dataset is returned?
> >
> >--
> >Richard A. Welch
> >IT Manager
> >House of Raeford Farms, Inc. - Raeford Division
> >
> >.
> >
>