0

I have 2 parameters in my query @selectedMonth and @selectedYear. I have 5 different date columns and I need to count all rows for each column where the month and year in the date match the parameters selected. I have set up each parameter with a set of available values (month names Jan to Dec with values 1 to 12 respectively) and (Years 2018 to 2022 inclusive and the values are the same as the label). enter image description here In my SQL query I have used the following in the WHERE clause: (updated 4/2/19 to show whole where clause)

   (DATEPART(mm, createdon) = @selectedMonth) AND (DATEPART(YY, createdon) = @selectedYear) OR (DATEPART(mm, sb_provisionalstatuschangedate) = @selectedMonth) AND (DATEPART(YY, sb_provisionalstatuschangedate) = @selectedYear) OR (DATEPART(MM, sb_confirmedstatuschangedate) = @selectedMonth) AND (DATEPART(YY, sb_confirmedstatuschangedate) = @selectedYear) OR

(sb_status = 3) AND (statecode = 1) AND (DATEPART(MM, sb_eventdate) = @selectedMonth) AND (DATEPART(MM, sb_eventdate) = @selectedYear) OR (statecode = 2) AND (DATEPART(MM, actualclosedate) = @selectedMonth) AND (DATEPART(YY, actualclosedate) = @selectedYear)

and I have used the following Expression in SSRS table (in the first column - I will need similar in the other 4 date columns):

=SUM(iiF(Fields!createdon.Value=Parameters!selectedMonth.Value AND Fields!createdon.Value=Parameters!selectedYear.Value),1,0)

I think I need the expression because the WHERE is using OR's across the 5 date columns and I only want it to count the valid dates in each column.

I am not quite sure where the problem lies. I thought that I might need to specify the 'Month' part of Fields!createdon.value is equal to the parameter value? (and same for year), however I cannot work out how to do that in an expression. Or maybe the syntax is completely wrong.

Sorry but I have looked at this so long I cant see the woods for the trees. If someone could point me in the right direction I am happy to carry on researching how to do it but need a nudge please.

Caroline Allen
  • 183
  • 14

2 Answers2

1

your expression within the SSRS table needs to be (and amended for each date column):

=Sum(iiF(DatePart("m", Fields!createdon.Value) = Parameters!selectedMonth.Value And DatePart("yyyy", Fields!createdon.Value) = Parameters!selectedYear.Value, 1, 0))

SuperSimmer 44
  • 964
  • 2
  • 7
  • 12
  • This was really easy to convert and did the job. Thank you for your help. I think that I had just looked at it for too long and needed a fresh pair of eyes. – Caroline Allen Feb 05 '19 at 08:37
0

You are restricting the dataset by using your where clause in the sql query and therefore you do not the Expression in the SSRS table, insert an aggregate function or add Totals to your details rows.

SuperSimmer 44
  • 964
  • 2
  • 7
  • 12
  • Thank you for the reply however because there are 5 date columns in the WHERE clause there are dates in the columns that do not fit the criteria that I do not want to count e.g. where the createdon date fits the parameter, EventDate may not fit the parameter but will still come out in the results because it is part of the row. So therefore I do need to restrict the results that are counted in each column. Any help on what I am doing wrong would be gratefully received – Caroline Allen Feb 04 '19 at 08:28