Post reply

Date functions for parameter defaults

  • June 11, 2006 at 4:54 pm

    #114543

    Hi. I'm trying to get default dates set up in parameters using RS2000, and can't believe something so basic is so hard to do. I've messed around with this for hours and feel like pulling our what is left of my hair! (It's already a bit thin on top so I don't want to lose more). Can someone please help me?

    I am trying to set up a report with default start and end dates, using an expression for non-queried default values. Reporting Services does not seem to like the GETDATE() function, so I am using = Now for the end date.

    For the start date I want the default value to be one week ago from the current date, and don't want any time i.e. the time should be 0:00:00. In Query Analyzer I can do this with the following formula (I have a dd/mm/yyyy format):

    =DATEADD("wk",-1,CONVERT(datetime,CONVERT(VARCHAR(11), GetDate(), 106)))

    When I try to preview it an error occurs saying the CONVERT is a type and cannot be used as an expression. How do I convert the above to a function that works in Reporting Services?

    Thanks in advance,

    Mark.

     

  • June 11, 2006 at 10:53 pm

    #643013

    Try this

    = DateAdd(dateinterval.year,-1,Today)

    Regards

    Raj Deep.A

  • June 11, 2006 at 11:24 pm

    #643015

    Thanks for that Raj - I have made some progress.

    I only want to go back one week, so I have changed your formula to the following:

    =DateAdd(dateinterval.DayOfYear,-7,Today)

    This does set the parameter to a week ago with a start time of 12:00am, which is a good start. For some reason though the date format for the parameter is m/d/yyyy. I have checked the regional settings on the report server and the short date format is dd/mm/yyyy. Do you know how I can get the parameters to the same format?

    Thanks again,

    Mark. 

  • June 12, 2006 at 2:01 am

    #643017

    I used this as the Default Non-Quried Value:

    =DateAdd("d", -(WeekDay(Today()))+1, Today())

    Then in the query itself, I just reference the parameter as: to_date('&Parametername','MM/DD/YYYY')

    (I'm querying an Oracle DB, so you will need to change the 'to_date' and '&' to your SQL.)

    HTH's

    David

  • June 12, 2006 at 6:06 pm

    #643259

    I have found (via a search on sqlservercental.com) the following article which shows how to create user defined functions and a stored procedure for commonly requested default dates:

    http://www.developer.com/db/article.php/3513996

    This seems to work well in terms of returning the required dates. The author says it is a breeze to pull these into Reporting Services - "simply add the uspCommonDates stored procedure as a new Dataset". Although he does not state how to do that. I did not know how to do it so I executed the procedure in Query Analyzer and used the code from that, which was:

    DECLARE @rc int

    EXEC @rc = [databaseName].[dbo].[uspCommonDates]

    If you execute the dataset it the produces all the dates such as week_start, week_end, month_start, month_end, last_week_start etc.

    However I still have the problem that the date parameters are coming up in the m/d/yyyy format. Can someone please tell me how I can get them to show as dd/mm/yyyy?

     

     

     

  • June 13, 2006 at 4:05 am

    #643312

    Hi Mark.

    Thanks for that article! Helps a lot, and would probably sort out some of my issues too. One thing though: when creating the FUNCTION on page two of that article, I get the following error:

    Msg 156, Level 15, State 1, Procedure udfCommonDates, Line 75

    Incorrect syntax near the keyword 'BEGIN'.

    Did you get the same thing? If so, what was the workaround? I can't see it here... I've basically created it all in the order it was shown in the article, so am not sure where I'm going wrong here?

    Thanks,

    David

  • June 13, 2006 at 4:12 am

    #643313

    I always use as the default for a parameter

    =datetime.today.addmonths(-1)

    for the date this time last month for instance. There are similar methods for days and years.

  • June 13, 2006 at 3:04 pm

    #643497

    create a new dataset in you report and enter the below to setup your default param value

    SELECT CONVERT (VARCHAR (10), GETDATE(), 103) AS Today

    103 format is DD/MM/YYYY format notice the data is a VARCHAR, set your report params for date as String type and select the default value based on a dataset.

    Then in your query WHERE clause use CONVERT to change the VARCHAR param value back to a DATETIME e.g.

    CONVERT (DATETIME, @Start_Date, 103)

    You can allow for datetime value containing a time by using

    CONVERT (DATETIME, CONVERT (CHAR (11), @End_Date, 103) + '23:59:59.997', 103)

    on your end date param.

    I've found this the best way to deal with dates in RS2000 as it keeps wanting to format dates as US. Works really well unless your user enters an invalid date, but hey most people should be able to handle it no probs.

    Kindest Regards,

    Martin

  • June 13, 2006 at 3:17 pm

    #643500

    Thanks Martin - I'll try that.

    David, I did get one or maybe two error messages but can't tell you exactly what they were. However I did just cut and paste from the article, so just execute them one at at time and check the syntax carefully. You should be able to get them to work without much problem.

    One error I think is that in Create Function udfCommonDates there should not be a comma before the RETURN at the bottom of the routine.

    Mark. 

  • June 13, 2006 at 11:52 pm

    #643556

    Got it. Thanks Mark. Works really well now

    David

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply