Try MySite.GetContent() Catch(Useless Info) Finally Site.Close() End Try

The guide to programming and analyzing SQL, .NET, and SAP
posts - 18, comments - 280, trackbacks - 0

My Links

News

Hello World. I'm Jon Hermiz and am a software engineer here in Michigan. Feel free to look around and leave feedback!

Archives

Post Categories

How to set a default date in Microsoft Reporting Services

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 24th, 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.

Print | posted on Wednesday, August 15, 2007 11:28 AM | Filed Under [ Reporting Services ]

Feedback

Gravatar

# re: How to set a default date in Microsoft Reporting Services

Excelent
4/25/2008 8:36 AM | Rafa
Gravatar

# re: How to set a default date in Microsoft Reporting Services


What value does Weekday() returns if the date is not valid 2/31/2008 for example?
8/26/2008 12:00 PM | sam
Gravatar

# re: How to set a default date in Microsoft Reporting Services

Sam try it!!! Thats the best way. My demonstration would work for all cases because I dont hard code the date I rely on functions such as Today() and Now().
9/3/2008 3:51 PM | Jon
Gravatar

# re: How to set a default date in Microsoft Reporting Services

Can we assign the date value to the date pick parameter in reporting service? I am getting the dates from the database and want to highlight those dates in the date pick parameter.
11/21/2008 7:01 AM | Prasant
Gravatar

# re: How to set a default date in Microsoft Reporting Services

i need to set a default value for the parameters we have used in store procedure
1/19/2009 7:55 AM | jana
Gravatar

# re: How to set a default date in Microsoft Reporting Services

Is it poosible to set change the default time of default date.
generally the default time is 12:00:00.0
i want to change it 23:59:59.992
1/29/2009 7:12 AM | suhag
Gravatar

# re: How to set a default date in Microsoft Reporting Services

Ugggh!! I'm trying to set the default date as the first day of the month. There is not function for that so I have to build the string for that and convert it to a date:
=CDate(str(month(Now())) + "/01/" + Str(Year(Now())))
3/17/2009 1:00 PM | tbot
Gravatar

# re: How to set a default date in Microsoft Reporting Services

Yes there is tbot.

See my other post here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99696

It is called GetMonthStart

Returns first day of the month for any year.
3/17/2009 1:02 PM | Jon H.
Gravatar

# re: How to set a default date in Microsoft Reporting Services

I'm still pretty confused as to how this is supposed to work - when I try the functions above, I get a Microsoft Report Designer Error stating:

"An error occurred while reading data from the query result set. Conversion failed when converting datetime from character string".

I do not understand this error - my date fields in the SQL code are of type DATETIME. My variables are also of type DATE - why am I getting this error?

<soapbox>
Personally, I find the VB expressions to be confusing to work with, especially since SSRS is essentially a data tool. Most people I know who use SSRS are T-SQL data folks and know nothing of VB (or C# or any other language), so it doesn't make sense to me that we can't use T-SQL syntax in SSRS expressions. </soapbox>

Thanks!!

6/15/2009 3:31 PM | Chad
Gravatar

# re: How to set a default date in Microsoft Reporting Services

This was very helpful! Thank you.
8/7/2009 11:47 AM | Carl Sheffield
Gravatar

# re: How to set a default date in Microsoft Reporting Services

first day of month

=DateSerial(YEAR(TODAY), MONTH(TODAY), 1)




5/18/2010 5:29 PM | bluebunny72
Gravatar

# re: How to set a default date in Microsoft Reporting Services

Hi,
I have one would list with problem incidents where I gladly to now filter would like after day or week.

1. I need a filter where show me the provided elements from the previous day (filter field! actual_start)
2. I need a filter with the provided elements, from the past week (Monday until Sunday) (filter field! actual_start).
I have this already times had and used for an Oracle query

/***************************
Incidents Yesterday
***************************/
selectto_char(inc.ACTUALSTART,'dd.mm.yy')as"Day", inc.SERVICE_NAME, inc.INCIDENTCODE1, inc.ACTUALSTART, inc.ACTUALFINISH
, inc.DESCRIPTION, inc.SOLUTION, inc.CATEGORY, inc.ID
from ao_ovsd.v_incident inc
where inc.INCIDENTWORKGROUP1_NAME like'Service Control Center'
andtrunc(inc.ACTUALSTART)=trunc(sysdate-1)
and inc.INCIDENTCODE1 notlike'Service Available'
OrderBy ACTUALSTART asc

/***************************
Incidents Last Week
***************************/
SELECT inc.SERVICE_NAME, inc.INCIDENTCODE1, inc.ACTUALSTART, inc.ACTUALFINISH
, inc.DESCRIPTION, inc.SOLUTION, inc.CATEGORY, inc.ID
FROM ao_ovsd.v_incident inc
WHERE(inc.INCIDENTWORKGROUP1_NAME Like'Service Control Center')
AND(to_char(inc.ACTUALSTART,'YYYY')=to_char(sysdate,'YYYY'))
AND(to_number(to_char(inc.ACTUALSTART,'IW'))=to_number(to_char(sysdate,'IW'))-1)
AND(inc.INCIDENTCODE1 NotLike'Service Available')
OrderBy ACTUALSTART asc

I need now exactly the same however for ms sql (Reporting Service)
Can me someone help or gives a Taps how can i this to be made.

best regards,
6/8/2010 11:36 AM | reflex82
Gravatar

# re: How to set a default date in Microsoft Reporting Services

Bluebunny72 solved my problem!
10/15/2010 3:11 AM | Dallas
Gravatar

# re: How to set a default date in Microsoft Reporting Services

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:

snow boots for women | columbia sportswear outlet | cheap mac makeup | the north face jackets

womens snow boots | columbia sportswear | cheap makeup | cheap north face jackets
10/21/2010 5:40 PM | columbia jackets
Gravatar

# re: How to set a default date in Microsoft Reporting Services

HI If you would like to have running month sales on first day of next month you could use:

=DateSerial(YEAR(DateAdd("d",-1,Today())), MONTH(DateAdd("d",-1,Today())), 1)
9/1/2011 2:07 PM | Rando
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET