locked
How to change the default values of one parameter depending on the values of other parameter RRS feed

  • Question

  • i have 2 parameters one is month(text datatype like june, july..etc) and other is date (date/time datatype).

    if a change the month then the date should be set to first date of that month.

    i tried creating  a dataset which has month as input and giving the output as first dtae and then i used this dataset for the date variable in default values option and get values from a query and i gave this dataset but i am getting error

     

     

    ERROR:

    The report parameter 'dt' has default value or a validvalue that depends on the report parameter 'month'.Forward dependences are not valid.

     

     

    How to overcome this issue 

    please help.


    • Edited by reddy335 Wednesday, November 23, 2011 3:48 PM
    Wednesday, November 23, 2011 3:47 PM

Answers

  • Hi reddy335,

    If you want to refresh @date parameter according to @month and @year, please create a dataset with following query statement and get the Available Value from the dataset for @date parameter:

    Declare  @previous int
    set   @previous=CAST(@month as int)-1
    select  datediff("d",CAST(cast(@previous as varchar(2))+'-01-'+cast(@year as varchar(4)) as DATE),CAST(cast(@month as varchar(2))+'-01-'+cast(@year as varchar(4)) as DATE)) as Date

    Regards,
    Lola


    Please remember to mark the replies as answers if they help.
    • Marked as answer by Lola Wang Wednesday, December 7, 2011 9:09 AM
    Friday, November 25, 2011 8:03 AM

All replies

  • You probably created the month parameter before the dt parameter. Move the dt parameter up before the month parameter using the up/down arrows.
    • Proposed as answer by Kiranmayee Wednesday, November 23, 2011 6:15 PM
    Wednesday, November 23, 2011 3:53 PM
  • Hi,

     

    thanks for the reply and i moved month up and it worked but i still have one problem.

     

    Initial if a give a month it is taking the first date but if i change the month again the date is not changing. I want the date to refresh and show the date in the month that i have selected.

     

    Thanks again


    • Edited by reddy335 Wednesday, November 23, 2011 4:04 PM
    Wednesday, November 23, 2011 4:04 PM
  • Hi Reddy - Please post the query/expressions you've used.
    Wednesday, November 23, 2011 4:07 PM
  • Hi,
    @month and @year are my parameters
    declare @l int,
    @monthly varchar(8)
    SELECT @l=DATEPART(mm,CAST(@month+ ' 1, 1900' AS DATETIME))
    set @monthly=CONVERT(varchar(4),@year)+CONVERT(varchar(2),@l)+'01' 
    if(LEN(@monthly)!=8)
    select @monthly=CONVERT(varchar(4),@year)+'0'+CONVERT(varchar(2),@l)+'01'
    select CONVERT(date,@monthly,121)
    Thanks
    Wednesday, November 23, 2011 4:10 PM
  • Hi

     

    I am using sql server 2008 R2.

    My issue is i have 2 parameters. First is month( obviously month has multiples values to choose from) and second is date.

    i have a dataset that uses the month as input and gives me the first day of the month as output.

    I want the second parameter default value to be the first date of the selected month, so i have used the option default value and get values from a query and used the dataset to get values and i also checked the always update option in advanced properties.

     

    When i preview the report and input the month the date changes for the first date of the month but when i change the month the date does not refresh to the first date of the selected month.

    what should i do to fix this issue.


    • Edited by reddy335 Wednesday, November 23, 2011 5:53 PM
    • Merged by Lola Wang Friday, November 25, 2011 8:31 AM same
    Wednesday, November 23, 2011 5:52 PM
  • This is the behavior of SSRS.  It will only calculate the default when it is first making the parameter available.  Changes to previous parameters will not cause the default to recalculate.

     

    Cheers!


    Ryan - Please all mark answers and useful posts!
    Wednesday, November 23, 2011 5:59 PM
  • Then can you please suggest me any work around

     

    Wednesday, November 23, 2011 6:03 PM
  • The first date of every month is "1", isnt it?

    ARe you trying to get the date on the first monday? or any other sort?

    For anything you can use the inbuilt functions from SSRS, by passing the month selected as parameter

    DateAdd(mm,DateDiff(mm,0,Today),0) gives the first date in a month. Change the DateDiff function to get the value for the month selected

    Wednesday, November 23, 2011 6:08 PM
  • i have other scenario where i have to select city based on the state.

     

    I have seen that it was a bug with RS2008 and they had this cumilative update #5 to fix this .

    I am using R2 and i dont know what Cumilative update should i use.

     

    Any suggestions

    Wednesday, November 23, 2011 7:10 PM
  • In the second parameter, available values, choose the query and set the values properly. For default values, do the same, but specify the record to be used.

    Under advanced tab, check :Always Refresh

    I am not sure about the bug, as I use these cascading params all the time without any issue.


    Edit:

    A simple check on forums revealed this:

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/08042521-c058-4897-9ba4-ba1f1ce1e458

    One reason I dont see the error could be that I am using accessing the reports differently

    • Edited by Kiranmayee Wednesday, November 23, 2011 7:20 PM
    Wednesday, November 23, 2011 7:17 PM
  • I have already tried it and it does not work
    Wednesday, November 23, 2011 7:21 PM
  • Hi reddy335,

    If you want to refresh @date parameter according to @month and @year, please create a dataset with following query statement and get the Available Value from the dataset for @date parameter:

    Declare  @previous int
    set   @previous=CAST(@month as int)-1
    select  datediff("d",CAST(cast(@previous as varchar(2))+'-01-'+cast(@year as varchar(4)) as DATE),CAST(cast(@month as varchar(2))+'-01-'+cast(@year as varchar(4)) as DATE)) as Date

    Regards,
    Lola


    Please remember to mark the replies as answers if they help.
    • Marked as answer by Lola Wang Wednesday, December 7, 2011 9:09 AM
    Friday, November 25, 2011 8:03 AM