Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
ssrs multi parameter wrapped with Month() function

Answered ssrs multi parameter wrapped with Month() function

  • Thursday, March 07, 2013 9:24 PM
     
     
    Sign In to Vote
    0

    I have a prameter in my ssrs report called @Period, the user can select multiple dates ,i.e  1/4/2012, 1/5/2012, 1/1/2013 in the prompt

    In my Dataset my query goes like so :

    select columns,...
    from table
    where month_int  IN  Month(@Period)   -- I have the month() function around the @Period
    and  Year_int   IN  Year(@Period)

    I get an error saying that Month() expects single value, How can I do something to @Period to give me for month ('4','5','1') 
    and for year ('2012', '2013')

    thanks,

     

All Replies

  • Thursday, March 07, 2013 11:18 PM
     
     
    Sign In to Vote
    0

    I tried the following but getting some error in string

    declare @sqltext as varchar(200)

    set @sqltext ='
    select b.Quarter_INT,a.Month_INT,a.Year_INT,a.GrossArrears from dbo.ArrearsKPI a
    inner join
    dbo.Calendar_T b
    on cast(cast(a.Year_INT as varchar) + ''/'' + cast(a.Month_INT as varchar) + ''/01'' as date)
    = b.Calendar_DT
    where
    a.Month_INT in (' + Join(parameters!Period.value, ",") + ')'          --- error here

    select @sqltext

     
  • Thursday, March 14, 2013 11:08 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hello,

    I can reproduce the issue in my test environment. The argument of Month() function should be a single value.

    How did you specify the available values for the parameter “Period"? If the values are retrieved from a dataset, you can try to create two parameter "Month" and "Year" which retrieved values from the data source.

    Another solution is to create two single-value parameter "Start-Period-date" and "End-Period-date" and specify the WHERE cluse of the main dataset query as follows:
    WHERE ( month_int >month(@Start-Period-date) and month_int < month(@Start-Period-date) )

    If you have any question, please feel free to ask.

    Regards,
    Fanny Liu

    Fanny Liu
    TechNet Community Support

     
  • Thursday, March 14, 2013 3:24 PM
     
     Answered
    Sign In to Vote
    0

    I vote for Fanny's suggestion as your answer. Dont show multiple dates from with the same month/year.

    If you insist on your current method, you can  also try this. Modify your query to be an expression:

    ="select columns from tables where month_int = MONTH(CDATE('" & JOIN(@Period,"')) AND month_int=MONTH(CDATE('") & "')) and Year_int =YEAR(CDATE('" & JOIN(@Period,"')) AND Year_int=YEAR(CDATE('") & "'))"

    Using the same multi dates examples from your original post, this will translate to:

    select columns from tables where month_int=4 AND month_int =5 AND month_int=1 and Year_int=2012 AND Year_int=2012 and Year_int=2013

    Notice the duplicate 2012. Does not really hurt, but it's not good logic. I would go for the two parameters Month and Year showing unique values from your table for the periods that can be selected.


    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z