Crystal Reports - Using Report Data as Sql Command Parameter

Question

I'm hoping someone here might be able to help me with a Crystal Reports problem that's been bugging me on and off for days.

I have a report that is used as a picking slip for our warehouse staff to tell them which products to pack for each order that comes through our sales system.

The report contains all the products for the order and the quantities that need to be packed in the details section of the report which come from a number of joined tables.

My problem is that I have to then query the database again in order to get the total amount of items for each product on the order that have been packed across all orders throughout the warehouse. The report datasource only returns records relating to the order on the picking slip so I need a sub query.

I've tried to use a SQL command with the following query:

SELECT ISNULL(SUM(QtyPacked), 0)
  FROM tblPackingSlipLines
  WHERE Status = 'Packed'
    AND ProductId = '[ProductId]'

I'm stuck on passing the product id into the command as it is held in a text field on the report itself which is populated from tblProducts.ProductId (one of the joined tables).

Is it even possible to access this value from the command?

I've tried things like ProductId = '{tblProducts.ProductId}', @ProductId, ?ProductId and numerous other weird and wonderful permutations.

I can't use a parameter as that would require either user interaction; not possible as the report is auto generated by a Windows service or passing a static value in from the service.

I've also tried using a formula but that only pulls back the sum of QtyPacked for that distinct order line and not all packed orders in the DB:

SUM('{tblPickingSlipLines.QtyPacked}', '{tblProducts.ProductId}')

Any help would be greatly appreciated.

Use the same SQL query in SQL expression field, not in SQL command. For productid use SQL field name (ProductId = tblProducts.ProductId) - and enclose entire statement into parens (...). — Arvo

Siva Siva · Accepted Answer · 2015-10-03T09:15:22

try using the sub report for this purpose.

Write your second query in sub report and pass the tblProducts.ProductId to the record selection parameter of the sub report through sub report links now your query comes in sub report as:

SELECT ISNULL(SUM(QtyPacked), 0)
  FROM tblPackingSlipLines
  WHERE Status = 'Packed'
    AND ProductId = {pm-product ID}

Crystal Reports - Using Report Data as Sql Command Parameter

1 Answers