How do I add an "ungrouped" formula to a "grouped" header? 1

[ewarr]

I'm using Crystal v10 and need to add a formula to a grouped header that will use a different grouping. For example, the report currently groups by Distributor, then Product. On the Product group line, I need to add a formula that will only group by Product and not Distributor/Product.

 

[hilfy]

The only way I know of to do this is to use a subreport that only calculates the total, size it and place that on the product group line where you need it.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

A subreport would work or you might be able to use a SQL expression, but we would have to know more about what you were trying to calculate, what tables and fields you were using, etc.

-LB

 

[ewarr]

Here's the SQL that runs the Distributor/Product grouping:

SELECT "AgdataView"."Distributor Name", "AgdataView"."Agdata Product Name", "AgdataView"."Sales_Quantity", "AgdataView"."Sales_Dollars", "AgdataView"."Invoice_Date"
FROM "Agdata"."dbo"."AgdataView" "AgdataView"
WHERE "AgdataView"."Distributor Name"='FERTIZONA'
ORDER BY "AgdataView"."Distributor Name", "AgdataView"."Agdata Product Name"

Here's an example of one of the custom formulas to print sales for a specific year using invoice date:

if {AgdataView.Invoice_Date} >= CDateTime (2008, 01, 01, 00, 00, 00)
and {AgdataView.Invoice_Date} <= CDateTime (2008, 12, 31, 00, 00, 00)
then {AgdataView.Sales_Dollars}

I would like to use an SQL Expression (not a subreport) that would group by Product only, then summarize sales so that I can compare Distributor/Product sales to Total Company Product sales on the same line for indexing purposes. I need to compare Distributor performance by Product to Company performance by Product. Thanks for the help!

 

[lbass]

If you wanted the product total for 2008, you could try:

(
select sum("Sales_Dollars")
from "AgdataView" A
where A."Agdata Product Name" = "AgdataView"."Agdata Product Name" and
{fn year(A."Invoice_Date")} = 2008
)

I am not clear on whether by company you mean distributor. I'm also not sure whether you have to specify the full table reference in the from clause. Try the above and if that isn't accepted, try:

from "Agdata"."dbo"."AgdataView" A

You might also just be able to use year() instead of the {fn year()} function.

-LB

 

[ewarr]

Thank you lbass! Your SQL worked great! Excellent!