The power in reporting services comes from the ability to use vb.net / vb functions to drive the programming aspect of your report.  If you’re coming from an Access background you may be clicking on your RS report hoping that you get a vb source code screen so you can make some coding enhancements.  The problem is you’ll never get that vb source code screen.  A report in reporting services is simply an XML file, if you right click on the report you will notice a “View Code” option.  Here you can view your report file with nested tags – the xml.  Since there is no event coding in reporting services you can use various functions in the .net framework to accomplish the coding you would of done in your access environment.  Lets look at a particular example using dates in our parameters. 

Say you have been asked to create a labor report displaying labor data for all the employees in your company.  You want to be able to have a couple of drop downs and a date used to select the week ending (most cases on Sunday).  The drop downs could be something along the lines of Department as well as Employees.  Forget about these drop downs for now, they are simple enough (2 sprocs, the employees one taking a DepartmentID as a parameter).  Let’s talk about the date parameter.  You want to allow the person requesting the report to enter the week ending date (the Sunday) of the previous week.  This report is usually run on say a Monday to snatch the labor hours recorded from the previous week.  You could just give them an empty date field and have the end user type in the Sunday date, or you could be slick and allow reporting services to set that date by default. To do this we need to set a non-queried default value.  We start by understanding that we will need to use some functions to obtain our date.  We will make use of the following functions:

 ·         Now()

·         WeekDay()

·         DateAdd() 

Now simply returns today’s date.  WeekDay returns an integer value containing a number representing the day of the week:  

Public Function Weekday( _   ByVal DateValue As DateTime, _   Optional ByVal DayOfWeek As FirstDayOfWeek = FirstDayOfWeek.Sunday _) As Integer

ParametersDateValue Required. Date value for which you want to determine the day of the week. DayOfWeek Optional. A value chosen from the FirstDayOfWeek enumeration that specifies the first day of the week. If not specified, FirstDayOfWeek.Sunday is used.  DateAdd returns a date value containing a date and time value to which a specified interval has been added: 

Public Overloads Function DateAdd( _   ByVal Interval As DateInterval, _   ByVal Number As Double, _   ByVal DateValue As DateTime _) As DateTime

-or-

Public Overloads Function DateAdd( _   ByVal Interval As String, _   ByVal Number As Double, _   ByVal DateValue As Object _) As DateTime

ParametersInterval Required. DateInterval enumeration value or String expression representing the time interval you want to add. Number Required. Double. Floating-point expression representing the number of intervals you want to add. Number can be positive (to get date/time values in the future) or negative (to get date/time values in the past). It can contain a fractional part when Interval specifies hours, minutes, or seconds. For other values of Interval, any fractional part of Number is ignored. DateValue Required. Date. An expression representing the date and time to which the interval is to be added. DateValue itself is not changed in the calling program.  

To get the Sunday of the previous week you simply need to subtract the number of days that you are currently on from the date you ran the report.  In this case if you ran the report on say a Tuesday, you would want to subtract 2 days from that date (Now() – 2).  But since you cannot just hard code a digit (since your end user could run the report any day of the week including on sat. / sun) you need to make use of the WeekDay function.   So we simply use DateAdd to add the number of days (which I negate) to get the resulting Sunday.  In the non-queried default value enter the following: 

=DateAdd(”d”, -(WeekDay(Now()))+1, Now()) 

So what happens here when the end user runs this report on January 24^th, 2006 (a Tuesday).  The above function call translates to: 

=DateAdd(“d”, -(WeekDay(January 24, 2006)) + 1, January 24, 2006) 

This further evaluates to: 

=DateAdd(“d”, -(2), January 24,2006) 

And the result is simply adding -2 days to January 24, 2006 (in addition, -2 + Now()) this will result in the default value getting the result of January 22, 2006. Now when your end user runs this report he or she does not have to worry about entering the week ending date, you have RS doing the dirty work for you.  Although this seems minor it certainly helps a user avoid entering tedious data.  Your end users could still modify the default value if they need to go back further then the previous week.