Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

reports expression for Date filter Expand / Collapse
Posted Thursday, April 25, 2013 1:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 8:11 AM
Points: 78, Visits: 233
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)

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:
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.


Post #1446322
Posted Monday, April 29, 2013 9:07 PM


Group: General Forum Members
Last Login: Wednesday, January 27, 2016 1:20 PM
Points: 175, Visits: 532
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.
Post #1447852
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse