Thursday, March 07, 2013 9:24 PM
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 :
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')
Thursday, March 07, 2013 11:18 PM
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
on cast(cast(a.Year_INT as varchar) + ''/'' + cast(a.Month_INT as varchar) + ''/01'' as date)
a.Month_INT in (' + Join(parameters!Period.value, ",") + ')' --- error here
Thursday, March 14, 2013 11:08 AMModerator
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.
TechNet Community Support
Thursday, March 14, 2013 3:24 PM
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