Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Past & Future Appointments RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I am generating a report using the SQL Reporting Service. The report contains two tables - one for past appointments and one for scheduled future appointments. Besides that both are exactly the same. So instead of creating two identical datasets where only the WHERE clause changes I would like to have one dataset and inject a variable to determine if past or future appointments should be returned for a particular table. How do I go after doing that?

     

    Thanks

    Wednesday, December 3, 2008 6:56 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    there are several options for this, you can do it in your sql query or in the table of your report. 

     

    One way to do this within your sql query is by using a case statement that puts P or F or some other value in a field to designate if the scheduled date is in the past or future.  You can use the getdate() function to make the determination within your Case statment.  Once you have the field in your dataset, you can filter your table on the value.

     

    There are several other ways to do this as well.  The above is just one example.

     

    Wednesday, December 3, 2008 9:00 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Your users should be changing the status of the appointments by closing/completing them. Then you can report based on the status:

     

    Status

    Associated Status Reason

    0 Open

    1 Free *

    2 Tentative

    1 Completed

    3 Completed *

    2 Canceled

    4 Canceled *

    3 Scheduled

    5 Busy *

    6 Out of Office

     

    Michael Cross

    Microsoft Dynamics CRM Consultant

    http://www.navint.com

     

    Wednesday, December 3, 2008 8:04 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    there are several options for this, you can do it in your sql query or in the table of your report. 

     

    One way to do this within your sql query is by using a case statement that puts P or F or some other value in a field to designate if the scheduled date is in the past or future.  You can use the getdate() function to make the determination within your Case statment.  Once you have the field in your dataset, you can filter your table on the value.

     

    There are several other ways to do this as well.  The above is just one example.

     

    Wednesday, December 3, 2008 9:00 PM