Using Parameters in the Query

After building the query (or pasting in the query from another query builder tool), you can add parameters which can restrict the data to be viewed. The parameters can either be visible/amendable by the users when "refresh" is clicked, or hidden.

To add a new parameter, click on the Properties icon

Click "Add Parameter" to add a new parameter, or click the existing parameter to amend an existing parameter :

Per parameter you can define:

The parameter Name
The parameter Label
The parameter tool tip description
an optional default value
set if it is a hidden parameter

After defining the parameters, these must be edited into the query as is required - between % symbols. For example "FROMFISCALYEAR" and :TOFISCALYEAR" as shown in red below :

SELECT rtrim(A.ACCTSEGVAL)+' - '+rtrim(A.ACCTDESC) AS Account, rtrim(A.ACSEGVAL02)+' - '+rtrim(S1.SEGVALDESC) AS SEGMENT1, rtrim(A.ACSEGVAL03)+' - '+rtrim(S2.SEGVALDESC) AS SEGMENT2, rtrim(A.ACSEGVAL04)+' - '+rtrim(S3.SEGVALDESC) AS SEGMENT3, rtrim(A.ACCTGRPSCD)+' - '+rtrim(GRP.ACCTGRPDES) AS AccountGroup, CASE rtrim(A.ACCTTYPE) WHEN 'B' THEN 'Balance Sheet' ELSE 'Income Statement' END AS AccountType, F.FSCSYR, '01' AS Period, (case when F.FSCSDSG = 'A' then (F.OPENBAL + F.NETPERD1) else 0 end) AS Actual,

(case when F.FSCSDSG = '1' then (F.OPENBAL + F.NETPERD1) else 0 end) AS Budget1

FROM GLAMF AS A LEFT OUTER JOIN GLASV AS S1 ON A.ACSEGVAL02 = S1.SEGVAL AND S1.IDSEG = '000002'

LEFT OUTER JOIN GLASV AS S2 ON A.ACSEGVAL03 = S2.SEGVAL AND S2.IDSEG = '000003'

LEFT OUTER JOIN GLASV AS S3 ON A.ACSEGVAL04 = S3.SEGVAL AND S3.IDSEG = '000004'

LEFT OUTER JOIN GLACGRP AS GRP ON A.ACCTGRPSCD = GRP.ACCTGRPCOD

LEFT OUTER JOIN GLAFS AS F ON A.ACCTID = F.ACCTID AND F.CURNTYPE = 'F' and FSCSYR >= %FROMFISCALYEAR% and FSCSYR <= %TOFISCALYEAR%