Post reply

Input integer parameter values for quarter and year: How to get last day of month and last month of quarter for footer

  • December 4, 2015 at 11:41 am

    #322733

    I have a master report and 25 subreports that use two user input parameters to drive them all. Works great. I want to be able to take the integer input for quarter (1, 2, 3, or 4) and convert it into the last month and last day of the quarter in the footer. So if the user input 1, I want to get 3/31 as the last day of quarter one. I've tried various things that didn't work. Here's my latest:

    ="Data for period 1/1/" & Parameters!Yr.Value & " through " & datepart("m",Parameters!Yr.Value) & "/" & datepart("d",Parameters!Qtr.Value)

    This is the expression in a textbox in the footer. When I run the report the textbox writes #Error on the report.

    I've googled a lot but I haven't found a case of someone wanting to take an integer parameter and converting it last month and day. Almost all examples use the current date with date arithmetic. I would like the flexibility of using the user input quarter for the desired report.

    Thanks in advance.

  • December 4, 2015 at 11:51 am

    #1844132

    Here is one way to calculate the last day of the quarter (untested):

    =DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, DateSerial(Parameters!Yr.Value, Parameters!Qtr.Value * 3, 1)))

    It constructs the first day of the quarter ending month

    then adds one month to get the first of the next month

    and then subtracts 1 day to get the last day of the quarter

  • December 4, 2015 at 1:24 pm

    #1844155

    Thanks! That worked perfectly!

Viewing 3 posts - 1 through 2 (of 2 total)

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