SQL Reporting: Display All Recurring Appointments

41 posts, 2 answers
  1. Josh
    Josh avatar
    21 posts
    Member since:
    Mar 2007

    Posted 02 Jul 2008 Link to this post

    Does anyone know how to display a list of appointments (recurring specifically) in an SQL Reporting Services report?

    Thanks!
  2. T. Tsonev
    Admin
    T. Tsonev avatar
    2794 posts

    Posted 03 Jul 2008 Link to this post

    Hi Josh,

    The recurrence rule is stored as a string and it has to be expanded into a series by using the RecurrenceRule class. One option is to use a managed stored procedure/function to expand the recurrence series on the SQL Server itself. We can assist you with the code related to the expanding of the recurrence series.

    A second option might be to implement a Data Processing Extension for SSRS. I am afraid that we cannot assist you much with the implementation of the extension itself, but this solution seems to be more flexible.

    Best wishes,
    Tsvetomir Tsonev
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
  3. Josh
    Josh avatar
    21 posts
    Member since:
    Mar 2007

    Posted 03 Jul 2008 Link to this post

    Thank you for the response.  I'd be interested in creating a function / procedure on the SQL Server to expand the stored string.  Could you pass along the code to implement this?

    Thanks for you help!
  4. T. Tsonev
    Admin
    T. Tsonev avatar
    2794 posts

    Posted 08 Jul 2008 Link to this post

    Hi Josh,

    I will try to post an example soon. Thank you for your patience.

    Sincerely yours,
    Tsvetomir Tsonev
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
  5. Jothi Ramashandran
    Jothi Ramashandran avatar
    3 posts
    Member since:
    May 2006

    Posted 09 Jul 2008 Link to this post

    I am looking to accomplish the same thing Josh is. Would be interested in your solution as well.
  6. Answer
    T. Tsonev
    Admin
    T. Tsonev avatar
    2794 posts

    Posted 14 Jul 2008 Link to this post

    Hi,

    We've just finished a draft implementation of a managed User Defined Function that can be used to expand recurring events on the SQL server itself.

    The UDF uses the RecurrenceRule class to expand the recurring series. But as it turns out, we can't just put the whole Telerik.Web.UI assembly in the SQL server, as it has lots of external dependencies. That is why we've extracted the recurrence engine into a separate assembly - Telerik.Web.UI.RecurrenceEngine.dll (included). This effectively means that you don't have a dependacy on Telerik.Web.UI in the UDF.

    In order to deploy the UDF, you need to open the Setup.sql script and fill-in the database name and the path to both UDF and recurrence engine assembly. Note that this will enable CLR integration on the server (global setting).

    Sample usage (full code in Sample.sql):

    SELECT      a.ID, a.Subject, o.StartDate, o.EndDate 
    FROM        Appointments a 
    CROSS APPLY dbo.ExpandRecurrence(a.RecurrenceRule, @@RangeStart, @@RangeEnd) AS o 
    WHERE       a.ID = 15 

    The full source code of the function is included in the attached file.

    Additional resources:

    Feedback is welcome. We will also publish the code as a KB soon.

    All the best,
    Tsvetomir Tsonev
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
  7. Josh
    Josh avatar
    21 posts
    Member since:
    Mar 2007

    Posted 14 Jul 2008 Link to this post

    Tsvetomir,

    Thank you very much for your assistance.  The feature is exactly what I was looking for!

    Thanks,
    Josh
  8. Josh
    Josh avatar
    21 posts
    Member since:
    Mar 2007

    Posted 14 Jul 2008 Link to this post

    Tsvetomir,

    I've been testing the UDF a little more and I think there may be an issue where the script doesn't pull an event if it falls within the specified date range and it isn't a recurring event.

    Was that intended?

    My thought was that it would pull all events (recurring or not) within a given date range.

    Thanks,
    Josh

  9. Answer
    T. Tsonev
    Admin
    T. Tsonev avatar
    2794 posts

    Posted 15 Jul 2008 Link to this post

    Hi Josh,

    The sample query only lists occurrences in a given range. If you want to select the non-recurring appointments too, then you need to use OUTER APPLY. Quoted from the MSDN documentation:

    There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

    We also have to add filtering for the range in the SELECT query itself to ensure we are only getting appointments in range:

    SELECT  a.ID, 
            a.Subject, 
            ISNULL(o.StartDate, a.Start) as Start, 
            ISNULL(o.EndDate, a.[End]) as [End
    FROM    Appointments a 
            OUTER APPLY dbo.ExpandRecurrence(a.RecurrenceRule, @@RangeStart, @@RangeEnd) AS o 
    WHERE   -- Include non-recurring appointments in the range 
            (a.RecurrenceRule IS NULL AND a.Start < @@RangeEnd AND a.[End] > @@RangeStart) OR 
            -- And recurring appointments in range. 
            (a.RecurrenceRule IS NOT NULL AND o.StartDate < @@RangeEnd AND o.EndDate > @@RangeStart) 

    I agree that this is a lot more useful as a sample query and I will use it instead when publishing the KB. Thank you for bringing this question.

    Greetings,
    Tsvetomir Tsonev
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
  10. ATS
    ATS avatar
    49 posts
    Member since:
    Mar 2007

    Posted 24 Jul 2008 Link to this post

    Can the setup script be modified for SQL 2000 databases? If so, how do I go about doing that?
  11. T. Tsonev
    Admin
    T. Tsonev avatar
    2794 posts

    Posted 25 Jul 2008 Link to this post

    Hello Justin,

    Unfortunately, SQL Server 2000 does not support managed SPs and UDFs. It supports extended stored procedures that can be used in conjunction with COM interop to route the data to the recurrence engine assembly.

    Furthermore, SQL 2005 does not support CROSS or OUTER APPLY, so this will result in more complicated queries.

    All the best,
    Tsvetomir Tsonev
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  12. GrZeCh
    GrZeCh avatar
    98 posts
    Member since:
    Dec 2007

    Posted 08 Oct 2008 Link to this post

    please delete this post.
  13. Keith
    Keith avatar
    1 posts
    Member since:
    Feb 2011

    Posted 03 Feb 2011 Link to this post

    Has there been any issue with running this CLR under SQL 2008 R2, 64 bit?

    I'm having some app domain unloading issues that did not exist under SQL 2005, and some things are pointing to this assembly.
  14. Fred
    Fred avatar
    7 posts
    Member since:
    Jan 2011

    Posted 18 May 2011 Link to this post

    Is this user defined function officially supported? Does it get patched if and when bugs get found in the RecurrenceEngine?

    Thanks,

    Fred
  15. Peter
    Admin
    Peter avatar
    6637 posts

    Posted 20 May 2011 Link to this post

    Hi Fred,

    What errors do you get? Can you send us the stack trace?

    Best wishes,
    Peter
    the Telerik team

    Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.

  16. Todd
    Todd avatar
    3 posts
    Member since:
    Oct 2010

    Posted 01 Jun 2011 Link to this post

    I've had great luck with this so far but have run into one issue. Is there a way to specify the maximum recurrences? I think it defaults to 10,000. 
  17. Peter
    Admin
    Peter avatar
    6637 posts

    Posted 02 Jun 2011 Link to this post

    Hi Todd,

    Yes, you can specify the MaximumCandidates for the recurrence rule.

    Greetings,
    Peter
    the Telerik team

    Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.

  18. Todd
    Todd avatar
    3 posts
    Member since:
    Oct 2010

    Posted 02 Jun 2011 Link to this post

    From within a Stored Procedure? For example:

    SELECT  a.ID,         a.Subject,         ISNULL(o.StartDate, a.Start) as Start,         ISNULL(o.EndDate, a.[End]) as [EndFROM    Appointments a         OUTER APPLY dbo.ExpandRecurrence(a.RecurrenceRule, @@RangeStart, @@RangeEnd) AS o WHERE   -- Include non-recurring appointments in the range         (a.RecurrenceRule IS NULL AND a.Start < @@RangeEnd AND a.[End] > @@RangeStart) OR         -- And recurring appointments in range.         (a.RecurrenceRule IS NOT NULL AND o.StartDate < @@RangeEnd AND o.EndDate > @@RangeStart) 

    How would you specify to return more than 10000 recurrences using the ExpandRecurrences_udf?  
  19. Neil
    Neil avatar
    6 posts
    Member since:
    Jul 2012

    Posted 02 Sep 2011 Link to this post

    There seems to be a problem with the recurrence engine in the supplied DLL, it will not return occurrences for some of the more unusual recurrence rules. Our users often have rules that may relate to an event that happens every 18 or 24 months, so the rule may look something like this:
    DTSTART:20090901T000000Z
    DTEND:20000102T000000Z
    RRULE:FREQ=MONTHLY;INTERVAL=24;BYMONTHDAY=1

    I created an additional function to return just the next occurrence date after the date specified as follows:
    public DateTime GetNextDue(string recurrenceRule, DateTime sDate)
            {
                DateTime returnVal = new DateTime(2000, 1, 1);
                RecurrenceRule rrule;
                if (RecurrenceRule.TryParse(recurrenceRule, out rrule))
                {
                    foreach (DateTime occStart in rrule.Occurrences)
                    {
                        if (occStart > sDate)
                        {
                            returnVal = occStart;
                            break;
                        }
     
                    }
                }
                return returnVal;
            }

    When I run the above rule in my web application with an sDate of 9/1/2011...the returned next occurrence date is 9/1/2013 which is the expected result, however when I run the function in SQL as follows:
    Select dbo.GetNextDue('DTSTART:20090901T000000Z
    DTEND:20000102T000000Z
    RRULE:FREQ=MONTHLY;INTERVAL=24;BYMONTHDAY=1
    ', '9/1/2011') as NextDue
    The result returned is 1/1/2000 (the default return value), which is obviously not correct.

    As far as I can see, the only difference here is the version of the recurrence engine. The one created for the CLR function was created in 2008 and the one in my web app is the latest Telerik.Web.UI.dll.

    Unfortunately one cannot import the whole Telerik.Web.UI.dll without it importing a whole bunch of .NET dll's also, so is it possible to get and updated version of the Telerik.Web.UI.RecurrenceEngine.dll that was built for this project?
  20. Neil
    Neil avatar
    6 posts
    Member since:
    Jul 2012

    Posted 06 Sep 2011 Link to this post

    For anybody else that needed an updated recurrence engine dll. The most awesome people at Telerik hav updated the dll in the KB article:

    http://blogs.telerik.com/aspnet-ajax/posts/08-08-04/working-with-radscheduler-recurring-appointments-on-sql-server.aspx

  21. Denis Buchwald
    Denis Buchwald avatar
    29 posts
    Member since:
    Apr 2010

    Posted 05 Oct 2015 Link to this post

    I've installed this new recurrenceEngine dll on my server and no matter what I do, it still returns 0 rows. Here is my query below. I have one record that has RecurrenceRule:

    MeetingID: 41983, Start: 2015-07-09 09:30:00.000, End: 2015-07-09 10:30:00.000, Title: Weekly Team Meeting, Description: NULL , RoomID: NULL IsAllDay:0, RecurrenceRule:FREQ=WEEKLY;BYDAY=TH, RecurrenceId: NULL, RecurrenceException: NULL, StartTimeZone: NULL, EndTimeZone: NULL

     

    I'm running this query and it returns me 0 rows:

    DECLARE @@RangeStart as DATETIME
    SET @@RangeStart = CAST('2010-07-01' AS DATETIME)

    DECLARE @@RangeEnd as DATETIME
    SET @@RangeEnd = CAST('2022-12-12' AS DATETIME)

    SELECT a.MeetingID,
    a.Title,
    ISNULL(o.StartDate, a.Start) as Start,
    ISNULL(o.EndDate, a.[End]) as [End]
    FROM Meetings a
    OUTER APPLY dbo.ExpandRecurrence(a.RecurrenceRule, @@RangeStart, @@RangeEnd) AS o
    WHERE
    (a.RecurrenceRule IS NOT NULL AND o.StartDate < @@RangeEnd AND o.EndDate > @@RangeStart)
    ORDER BY MeetingID​

     

    Can someone help me get this running. I have urgent report to finish and this RecurrenceRule parser is really holding me up.

     

    Thanks

  22. Plamen
    Admin
    Plamen avatar
    2816 posts

    Posted 07 Oct 2015 Link to this post

    Hello,

    In such scenario the recurrence rule is not correct- you have to have interval property set so that the correct output is shown. RadScheduler is also using the DTSTART and DTEND fields.

    Here is the sample rule that worked correctly at my side:
    DTSTART:20150709T123000Z\nDTEND:20150709T130000Z\nRRULE:FREQ=WEEKLY;INTERVAL=1;BYDAY=TH

    Hope this will help you solve the issue.

    Regards,
    Plamen
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  23. Denis Buchwald
    Denis Buchwald avatar
    29 posts
    Member since:
    Apr 2010

    Posted 07 Oct 2015 in reply to Plamen Link to this post

    Plamen:

     

    The record is set by Telerik MVC Scheduler. I can't change it the way it works. Scheduler itself is not setting the Interval.

    Can someone at Telerik update RecurrenceEngine.dll to parse out recurrenceRule entries set by Telerik?

  24. Plamen
    Admin
    Plamen avatar
    2816 posts

    Posted 08 Oct 2015 Link to this post

    Hello,

    This blog post  and the thread are about the Ajax RadScheduler. it is expected that it is not working for MVC Scheduler.


    Regards,
    Plamen
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  25. Brian Mott
    Brian Mott avatar
    6 posts
    Member since:
    Mar 2010

    Posted 06 Nov 2015 in reply to T. Tsonev Link to this post

    Is this still valid in the current RadScheduler? All I need is a way to pull all the recurring apts from SQL using the Telerik logic to guarantee that my recurring apts match those in the scheduler calendar view.

    If need be, I can do it from the control but I would prefer to offload this on SQL. 

  26. Josh
    Josh avatar
    21 posts
    Member since:
    Mar 2007

    Posted 09 Nov 2015 in reply to Brian Mott Link to this post

    Brian Mott said:

    Is this still valid in the current RadScheduler? All I need is a way to pull all the recurring apts from SQL using the Telerik logic to guarantee that my recurring apts match those in the scheduler calendar view.

    If need be, I can do it from the control but I would prefer to offload this on SQL. 

     

    Brian,

     

    I just setup it up this past week and it worked for me.  I believe they posted an updated sample to the blog within this thread but technically the code they are using for parsing the recurrence should work, unless the iCalendar standards change.

     
    Thanks,

    Josh

  27. Plamen
    Admin
    Plamen avatar
    2816 posts

    Posted 10 Nov 2015 Link to this post

    Hello,

    The blog post is still valid and if  you observe some issues with it please share the case with us so we could inspect and fix them.

    Regards,
    Plamen
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  28. RTA DEV
    RTA DEV avatar
    4 posts
    Member since:
    Sep 2007

    Posted 21 Dec 2015 Link to this post

    Hi,

    I find that there is a discrepancy in the .NET assembly of parsing the recurrence rule string compared to the ExpandRecurrence UDF function in SQL Server.

     For example, running the following query return a single row:

    SELECT *
    FROM ExpandRecurrence('DTSTART:20151217T191500Z
    DTEND:20151217T194500Z
    RRULE:FREQ=HOURLY;UNTIL=20151217T191500Z;INTERVAL=12
    ', '2015-12-01', '2015-12-20')

     

    However, if I use the same rrule string in .NET code, it actually return a zero length list:

    Dim rruleString = "DTSTART:20151217T191500Z
    DTEND:20151217T194500Z
    RRULE:FREQ=HOURLY;UNTIL=20151217T191500Z;INTERVAL=12"
    Dim rrule = RecurrenceRule.TryParse(rruleString)
    Dim ruleList = rrule.Occurrences.ToList

     

    Is this a bug? If it is, could this be fixed?

     

    Thanks!

  29. Plamen
    Admin
    Plamen avatar
    2816 posts

    Posted 24 Dec 2015 Link to this post

    Hello,

    Thank you for your feedback. We are going to validate the project and update it as soon as we could. Meanwhile you could also check the zip in the Code Library  and contains extracted source code and check it against the latest source code in the controls. 

    Regards,
    Plamen
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  30. Michael
    Michael avatar
    21 posts
    Member since:
    Aug 2008

    Posted 17 Jan 2016 in reply to Plamen Link to this post

    Any idea if this will work with Sql Server v12 on Azure?
Back to Top