April 25, 2013 at 1:23 am
Hi All,
I have 3 parameters in my report, 1.@FromMonth 2.@ToMonth 3. @TomonthMinus11
Here @ToMonth value is default(populating from a dataset)
Values are like :
-----------------------------------------------
My dimension members are like this:
1.[Date DIM].[Month].[2013].&[[9]
2.[Date DIM].[Month].[2013].&[[10]
Basing on ToMonth value ,it should subtract 11 months from toMonth value and pass the resulted value to @FromMonth parameter
Expression to get the required values is:( @TomonthMinus11 expression)
“dateadd(dateinterval.Month,-11,
cdate(
left(right(Parameters!ToMonth.Value,10),4) & "-" & left(right(Parameters!ToMonth.Value,3),2) & "-01"
)
)”
Above expression will work for 2nd dim member, but not for the 1st member.
so I have changed the above expression as below:
=Iif(len(Parameters!ToMonth.Value)>=31,
dateadd(dateinterval.Month,-11,cdate(left(right(Parameters!ToMonth.Value,9),4) & "-0" & left(right(Parameters!ToMonth.Value,2),1) & "-01")),
dateadd(dateinterval.Month,-11,cdate(left(right(Parameters!ToMonth.Value,10),4) & "-" & left(right(Parameters!ToMonth.Value,3),2) & "-01")))
Now, it is not working for any of the baove case, cany anybody suggest on this
Or can tell any other way to achieve this.
TR,
Niha
April 29, 2013 at 9:07 pm
To extract the year value, my suggestion is to use a SUBSTRING function instead of nested LEFT and RIGHT functions. This is possible if the four digits of the year are always in the same four positions in the string (as they are for the two examples given). Example:
To extract the month value, my suggestion is again to use a SUBSTRING function, since the starting position appears to be fixed for all rows of data and since you can omit the length parameter and get all the characters from the starting position to the end of the string. To remove the square brackets I would suggest the REPLACE function or method to "replace" the "]" with "" (nothing between double-quotation marks). That would leave just the number of the month.
Here is a sample of code to do what you are seeking to do:
=CDate(CStr(Parameters!ToMonth.Value).Substring(20,4) & "/" & CStr(Parameters!ToMonth.Value).Substring(29).Replace("]", "") & "/1").AddMonths(-11)
*Edit Note: I almost forgot that parameter and field values are not "typed" natively in SSRS, so it is necessary to use a convert function to make their types explicit in the context of the expression. So I added CStr() functions around the parameter value references.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply