Post reply

How to default start / end date parameters to first / last day of current month?

  • February 12, 2015 at 2:28 pm

    #301250

    I have a SSRS report using 2008 R2. It prompts the user for the start and end dates. This all works. But now I want the start date parm to default to the first day of the current month and the end date parm to default to the last day of the current month. Can anyone help me?

    In the new query window in SQL Server Management Studio, I can run this chunk of code to get the first day of current month:

    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

    And this code to get the last day of current month:

    SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),

    DATEADD(MONTH, 1, GETDATE()))

    But I don't know how to do this in SSRS 2008. How can I make my start / end parms to get these values.

  • February 12, 2015 at 2:35 pm

    #1777364

    I'm not sure if it's the correct way to do it, but I used a new dataset for parameter values.

    See next post.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 12, 2015 at 2:52 pm

    #1777367

    Disregard my previous post.

    On the Default Values tab, set Specify Values and use the function button (fx).

    For start of Month:

    =DateAdd("M", DateDiff("M", CDate("1900-01-01"), Now()), CDate("1900-01-01"))

    For end of Month:

    =DateAdd("M", DateDiff("M", CDate("1900-01-01"), Now()), CDate("1900-01-31"))

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 13, 2015 at 6:08 am

    #1777490

    Thanks man! That worked.

    Another question though. Since I am now defaulting the start / end parms to the beginning and end of month, I'd like to make both parameters as read-only. I want the user to see the values but do not want them changing them. Any way of doing this?

  • February 13, 2015 at 7:17 am

    #1777503

    Use the Available Values tab to set the same values as the default.

    It won't be disabled but the value can't be changed.

    Another option is to define them as hidden and use labels/Textboxes.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 13, 2015 at 1:31 pm

    #1777602

    Couple things to note...

    First, 2008 R2 has an EOMONTH function.

    Second, if you want to assign those values using SQL and not an SSRS expression you could create a dataset for each using your SQL code and then refer the sart/end params to the corresponding dataset for their default value.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 6 posts - 1 through 5 (of 5 total)

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