How to use the 'Area' Parameter in a subsequent Parameter on a Report
Hint Ref: 020908250002
Hint Date: 25/08/2009
Hint Details:
It does not appear possible to use one Report Parameter in a subsequent Report Parameter's SQL statement via parameter substitution. In other words, if you want the user to select from a list of choices where the choices depend of the site or region the user has selected to run the report against, you cannot simply use the 'Where <Area>' statement.
However, I have written a little SQL that you can use, and it appears to work reliably. Simply substitute the '<Area>' statement for the whole SQL statement below:
((select sitename from site where site.siteid=a.siteid)=(:Areaname)
or
a.siteid in (select siteid from regionsite join region where canseesite(regionsite.siteid,(select staffid from staff where userid=myuserid))=1 and region.name=(:Areaname))
or
(:Areaname)='All')
By way of an example, I wanted to get the user to select from a list of dates that Stock items were added for a site or region, for which I used the following:
select distinct dateformat(movementdate,'dd/mm/yyyy') as x,dateformat(movementdate,'yyyy-mm-dd') as y from stockmovement join ledgeritem a on stockmovement.ledgeritemid=a.ledgeritemid
where movementtype='A' and
((select sitename from site where site.siteid=a.siteid)=(:Areaname)
or
a.siteid in (select siteid from regionsite join region where canseesite(regionsite.siteid,(select staffid from staff where userid=myuserid))=1 and region.name=(:Areaname))
or
(:Areaname)='All')
order by y desc
(Please Note: This Procedure can be destructive and should only be used by Advanced Users. RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure or SQL command. Responsibility resides solely with the user.
IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)