Very often you will need to design a certain amount of user-specified flexibility into your SQL Reports, similar to that which is done through options in with Crystal Reports. Control's SQL Reports allow you to build options that dynamically build a dialog for the user when running the report. This functionality is similar to, though not identical to, the options in Crystal Reports.

===== Option Syntax =====

==== Discrete Options ====

<code>
<%Employee%>
<%Effective_Date%>
<%Customer%>
<%Station%>
</code>

==== Collection Options ====

<code>
<%EmployeeSET%>
<%CustomerSET%>
<%StationSET%>
<%OrderSET%>
</code>

Notes:

  * Collections are created by including SET in the name at the end as shown in the examples above. You can name the parameters anything you like such as <%ProductSET%>. When SET is included Control will pass a comma delimited list of ID to the SQL query prior to it being executed on the database. You can verify this by running a simple query like. 
<code:sql>-- Run just this one line below in your SQL Report and set the Parameter Type on the Options tab of the SQL Report to Product. Select 2 or 3 products from the list and run the report. 
SELECT <%ProductSET%>;
</code>
  * When the above is actually query is actually executed SQL will see the query as 
 <code:sql> SELECT 1,2,3</code>
  * At times, you may to have your list of values passed by your Collection (SET) to be displayed in a table fashion. This can be accomplished by utilizing the function below. 
  <code:sql>SELECT * FROM dbo.CrystalListToIntTable( '<%ProductSET%>' );
  -- This would give you 1 value per line in a table format when executed. 
  </code> 
  * You can also select from a set of standard variables data types such as Date, Date & Time, Order Number, Employees Lists and Products Lists that will be selectable at runtime.

{{:screen_shot_2018-04-23_at_3.50.27_pm.png?nolink&}}

==== Range Options ====
<code>
<%Report_DateSTART%>
<%Report_DateEND%>
</code>

===== Setting the Option Type in Control =====

===== Examples =====

==== Exporting An Employees Calendar to Outlook ====

<code:sql>
DECLARE @CalendarID INT;
DECLARE @MinDate datetime;  -- Set the absolute earliest date you want to query
DECLARE @MaxDate datetime;  -- Set the absolute latest date you want to query
 
SET @CalendarID = <%Employee%>;
SET @MinDate   = ISNULL(CONVERT(DateTime, <%Export_DateSTART%>),'1/1/1900');
SET @MaxDate   = ISNULL(CONVERT(DateTime, <%Export_DateEND%>),'1/1/2100');
 
DECLARE @ZeroDate DATE;
DECLARE @Delimiter VARCHAR(7);
DECLARE @AddressDelimiter VARCHAR(7);
 
SET @ZeroDate  = '12/30/1900';
SET @Delimiter = ' //'+CHAR(13)+CHAR(10);
SET @AddressDelimiter = ', ';
 
SELECT
  J.ID AS [KeyID],
  CONVERT( VARCHAR(10), COALESCE(J.StartDateTime, J.QueryStartDateTime), 101) AS [START DATE],
  CONVERT( VARCHAR(12), CONVERT(TIME, CASE WHEN (CA.AllDayEvent=1 OR CA.IsTimeless=1) THEN '00:00' ELSE COALESCE(J.StartDateTime, J.QueryStartDateTime) END), 0 ) AS [START TIME],
  J.Description AS [Subject],
 
  RTrim(Address.StreetAddress1 + COALESCE(' ' + Address.StreetAddress2, ''))
  + @AddressDelimiter + COALESCE(Address.City + ', ', '') + COALESCE(Address.State + '  ', '') + COALESCE(Address.PostalCode, '')
  + ' (' + Account.CompanyName + ')'
  AS [Location],
 
  Account.CompanyName
  + ' (' + AC.FirstName + ' ' + AC.LastName
  + COALESCE(' ' + Phone.FormattedText, '') + ') '
  AS [Meeting Organizer],
 
  'Company:  '+ Account.CompanyName
  + @Delimiter + 'Contact:  ' + (AC.FirstName + ' ' + AC.LastName)
  + @Delimiter + 'Phone:    ' + Phone.FormattedText
  + @Delimiter + 'Employee: ' + Employee.LastName +', '+Employee.FirstName
  + @Delimiter + 'Notes:    ' + CAST(J.Notes AS VARCHAR(1024))
    AS [Description],
 
  CONVERT( VARCHAR(10), COALESCE(J.EndDateTime, J.QueryEndDateTime), 101) AS [END DATE],
  CONVERT( VARCHAR(12), CONVERT(TIME, CASE WHEN (CA.AllDayEvent=1 OR CA.IsTimeless=1) THEN '23:59:59.9' ELSE COALESCE(J.EndDateTime, J.QueryEndDateTime) END), 0 ) AS [END TIME],
  CASE WHEN (CA.AllDayEvent=1 OR CA.IsTimeless=1) THEN 'TRUE' ELSE 'FALSE' END AS [ALL DAY Event],
  CASE WHEN J.ReminderPrompt = 1 THEN 'TRUE' ELSE 'FALSE' END AS [Reminder ON/Off],
  CASE WHEN J.ReminderPrompt=0 THEN NULL ELSE CAST(J.ReminderDateTime AS DATE) END AS [Reminder DATE],
  CASE WHEN J.ReminderPrompt=0 THEN NULL ELSE CAST(J.ReminderDateTime AS TIME) END AS [Reminder TIME],
  CASE WHEN CA.PrivateEvent = 1 THEN 'TRUE' ELSE 'FALSE' END AS [Private],
  CA.PriorityText AS [Priority],
 
  Employee.LastName +', '+Employee.FirstName AS [Employee Name],
  Account.CompanyName AS [Company Name],
  (AC.FirstName + ' ' + AC.LastName) AS [Contact Name],
  Phone.FormattedText AS [Phone NUMBER],
  J.Notes AS [Notes ONLY],
  @CalendarID AS CalendarID,
  J.ModifiedDate AS [LAST Modified],
  J.AccountID AS AccountID,
  J.TransactionID AS TransHeaderID,
  J.ID AS JournalID
 
FROM
  Journal J
  JOIN ContactActivity CA     ON J.ID = CA.ID
  JOIN CalendarLink CL        ON J.ID = CL.JournalID
  LEFT JOIN TransHeader TH    ON J.TransactionID = TH.ID
  LEFT JOIN Account           ON J.AccountID = Account.ID
  LEFT JOIN AccountContact AC ON J.ContactID = AC.ID
  LEFT JOIN Employee          ON CL.CalendarID = Employee.ID
  LEFT JOIN Address           ON COALESCE(AC.ShippingAddressID, Account.BillingAddressID) = Address.ID
  LEFT JOIN PhoneNumber Phone ON COALESCE(AC.MainPhoneNumberID, Account.MainPhoneNumberID) = Phone.ID
WHERE
  EmployeeID = @CalendarID
  AND J.QueryStartDateTime BETWEEN @MinDate AND @MaxDate
  AND J.CompletedDateTime IS NULL
 
  -- There is no field to save whether this record was exported or not.
  -- In order to put this some place, we are going to use two depricated integer fields in the CalendarLink table.
  -- The CalendarStoreID will store the days from the "Zero Date" since the activity was last modified.
  -- The JournalStoreID will store the minutes from midnight since the activity was last modified.
  -- After the export we will need to run a query to set these values!
  AND ( (J.ModifiedDate - COALESCE(CL.CalendarStoreID, 0) > @ZeroDate)
        OR (DatePart(HOUR, J.ModifiedDate) * 24 + DatePart(MINUTE, J.ModifiedDate) > COALESCE(CL.JournalStoreID, 0)) )
</code>


===Tags===
SQL Report Parameters Variables