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
<%Employee%> <%Effective_Date%> <%Customer%> <%Station%>
Collection Options
<%EmployeeSET%> <%CustomerSET%> <%StationSET%> <%OrderSET%>
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.
-- 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%>;
- When the above is actually query is actually executed SQL will see the query as
SELECT 1,2,3
- 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.
SELECT * FROM dbo.CrystalListToIntTable( '<%ProductSET%>' ); -- This would give you 1 value per line in a table format when executed.
- 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.
Range Options
<%Report_DateSTART%> <%Report_DateEND%>
Setting the Option Type in Control
Examples
Exporting An Employees Calendar to Outlook
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)) )
Tags
SQL Report Parameters Variables
You could leave a comment if you were logged in.