locked
Report Parameters not working RRS feed

  • Question

  • Hi,
    Hope someone can help me here.
    I am populating a date time parameter from another parameter using the following query

    Select( 
    Case @Schedule
    When 'Daily' then DATEADD(d,-1,GETDATE())
    When 'Current Week' then DATEADD(DD, 1 - DATEPART(DW, GETDATE()),GETDATE())
    When 'Previous Week' then DATEADD(dd, -(datepart(dw, GetDate())+6), GetDate())
    when 'Current Month' then DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
    when 'Previous Month' then DATEADD(MONTH, -1, DATEADD(DAY, - DAY(GETDATE()) + 1, GETDATE()))
    when 'Current Quarter' then DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)
    when 'Previous Quarter' then DATEADD(qq, DATEDIFF(qq, 0, (DateAdd(day, -1,Dateadd(qq, DateDiff(qq, 0, GETDATE()), 0)))), 0)
    END) AS DateFrom

    This gives me the start date and

    Select (
    Case @Schedule
    When 'Daily' then GETDATE()
    When 'Current Week' then GETDATE()
    When 'Previous Week' then DATEADD(dd, -(datepart(dw, GetDate())), GetDate())
    when 'Current Month' then GETDATE()
    when 'Previous Month' then DATEADD(DAY, - DAY(GETDATE()), GETDATE())
    when 'Current Quarter' then GETDATE()
    when 'Previous Quarter' then DateAdd(day, -1, dateadd(qq, DateDiff(qq, 0, GETDATE()), 0))

    END ) AS DATETO

    This gives me the end date to be used. Now the problem is that when the start and end dates are populated, and the View Report button is pressed, the repport shows nothing. Also the parameters are also lost in the process. What i did see is that the date parameters stick if the time in the date is 12:00:00 AM. Anyother time and the report does not work and also the param is lost. For the previous month drop down both the dates have a 12:00:00 AM time stamp and that works.
    Can anyone tell me what can be happening?
    Thanks


    Zafar
    Tuesday, December 16, 2008 7:34 PM

Answers

All replies

  • Hi Zafar,

    Instead of populating the DateFrom, DateTo parameters, try to set their default. Set the “Available Values” to Non-queried and leave it blank, then attach the default value to the query for both parameters.

    Regards,
    Krisztian
    Thursday, December 18, 2008 1:35 AM
    Answerer
  • Thanks Bocso,
    That did solve my problem. Only one question. I have the parameters that are populated above as invisible. It works with them not visible, but when i make them visible, the dates are stuck to the default at the begining and cant be changed. Can you tell me whats the problem there??
    Thanks

    Zafar
    Thursday, December 18, 2008 4:07 PM
  • Because the default value in your parameter is assigned by you. Just select available values is non queried and default value null. dont make it hidden.
    Thursday, December 18, 2008 4:18 PM
  • This might be a stupid question, is NON QUERIED, SPECIFY VALUES in 2008 RS? Cause i dont see the non queried in 2008?


    Zafar
    Thursday, December 18, 2008 4:41 PM
  • NON QUERIED is called as NONE in the RS 2008 Report Builder.
    Friday, December 19, 2008 1:11 AM
    Answerer