Hi All,
Crystal version 10
Microsoft SQL Server 2000
I am trying to create SQL expressions to enable me to sort groups on a formula in the group footer.
Thread767-874782 and Thread767-899216 both deal with this particular problem but I cannot get the required SQL expressions to save.
Sample Data - tblStock
SKU UnitsUploaded Units Sold
1234 10 0
1234 0 1
1234 0 3
I have a group on SKU and in the group footer I have the sellthru% which is sum(UnitsSold,SKU)/sum(UnitsUploaded,SKU). I want the report to sort the SKU groups, based on the Sellthru%.
I have followed the intstructions in the above threads (create 2 SQL expressions, one that sums upload, one that sums sold, and then create a formula that does the final calculation) but the SQL expressions keep generating syntax errors.
As an example, for the first expression to sum Units Uploaded I have
(
SELECT SUM(AKA.'UnitsUploaded')
FROM tblStock AKA
WHERE AKA.'SKU' = tblStock.'SKU'
)
Using single quotation marks generates a syntax error. If I change to double quotes (which is what Crystal itself uses if you select a field from the field tree) then the message I get is "The column prefix 'tblStock' does not match with a table name or alias name used in the query"
Please could somebody help me out with the correct syntax - I just cannot get this to work and after a couple of days I'm absolutely pulling my hair out. As ever this is urgent, and I would greatly appreciate any help to resolve this - I can't get any further until this is solved!
Many thanks in advance!
NixyJ x
Crystal version 10
Microsoft SQL Server 2000
I am trying to create SQL expressions to enable me to sort groups on a formula in the group footer.
Thread767-874782 and Thread767-899216 both deal with this particular problem but I cannot get the required SQL expressions to save.
Sample Data - tblStock
SKU UnitsUploaded Units Sold
1234 10 0
1234 0 1
1234 0 3
I have a group on SKU and in the group footer I have the sellthru% which is sum(UnitsSold,SKU)/sum(UnitsUploaded,SKU). I want the report to sort the SKU groups, based on the Sellthru%.
I have followed the intstructions in the above threads (create 2 SQL expressions, one that sums upload, one that sums sold, and then create a formula that does the final calculation) but the SQL expressions keep generating syntax errors.
As an example, for the first expression to sum Units Uploaded I have
(
SELECT SUM(AKA.'UnitsUploaded')
FROM tblStock AKA
WHERE AKA.'SKU' = tblStock.'SKU'
)
Using single quotation marks generates a syntax error. If I change to double quotes (which is what Crystal itself uses if you select a field from the field tree) then the message I get is "The column prefix 'tblStock' does not match with a table name or alias name used in the query"
Please could somebody help me out with the correct syntax - I just cannot get this to work and after a couple of days I'm absolutely pulling my hair out. As ever this is urgent, and I would greatly appreciate any help to resolve this - I can't get any further until this is solved!
Many thanks in advance!
NixyJ x