Summarizing Summary Data

[Guest]

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?

 

[Elton Wang]

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
(e-mail address removed)

 

[Guest]

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.

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
(e-mail address removed)

-----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

.