SSRS expression Year and Month only from Date field

Question

How do I build an expression in SSRS that only captures the Month and Year and not the day from a datestamp field?

I am trying to create a Month To Date column in SSRS. I have a date timestamp field, I also created a Month field and a Year field in hopes of solving my own problem. I didn't.

I have an expression built that allows me to capture the month and it works, but in my data set I have July data for both 2013 and 2014. This expression I only want to capture 2014.

=Count(IIF(Fields!Date_Month.Value = Month(Today()), Fields!AcctNo.Value, Nothing), 
"Eligibility")

and I got it to work for the Year:

=Count(IIF(Fields!Year.Value = Year(Today()), Fields!AcctNo.Value, Nothing), 
"Eligibility")

Can I somehow combine the 2 expressions? Should I?

Orrrrrrrrrr

I have tried to use my datestamp field to no avial: I get #Error with this abomination

=Count(IIF(Fields!Datestamp.Value = Month(Today()), Fields!AcctNo.Value, 
Nothing),   "Eligibility")

I'd think the preferred way is to use my above datestamp field and parse out the month and year. It's the way I'd do it....if I knew how to actually do it.

Ian Preston Ian Preston · Accepted Answer · 2014-07-29T13:33:28

As you've suggested, you can combine the two expressions you have to get your required result with a few small changes:

=Count(IIf(Year(Fields!Datestamp.Value) = Year(Today)
      and Month(Fields!Datestamp.Value) = Month(Today)
    , Fields!AcctNo.Value
    , Nothing)
  , "Eligibility")

This will count all rows in the Dataset that have the same year and month as the current date.

SSRS expression Year and Month only from Date field

1 Answers