Creating Parameters in SQL Command
I've created a report via the Add Command option rather than pulling directly from the tables and that works great. However, I need to create a dynamic parameter for user to select records from the many thousands of records on the report.
I created a parameter in the Parameter area of the Add Command screen & integrated that in the WHERE clause of my SQL commands and the parameter seems to work okay.
However, I just discover that the parameter was "STATIC" instead of dynamic as I intended it to be. Is it possible to make it dynamic? Any help would be greatly appreciated.
Abhilash Kumar replied
The second command object, which is the source for the list of value, should be:
SELECT Distinct POLINE."PO_NUMBER" PO#
FROM POLINE POLINE
LEFT OUTER JOIN MMDIST MMDIST
ON ((POLINE."PO_NUMBER" = MMDIST."DOC_NUMBER") AND
(POLINE."PO_CODE" = MMDIST."PO_CODE")) AND (POLINE."LINE_NBR" = MMDIST."LINE_NBR")
WHERE POLINE."PO_CODE" = 'CAP' AND
MMDIST."DOC_TYPE" = 'PT' AND
The dynamic prompt's 'Value' should then point to this PO Number field and Not the PO Number field in the First Command Object.
I aliased the PO_NUMBER field as PO# to easliy identify it.