1

I have a dataset that has 3 years worth of data, and I only want the latest year in the dataset for a tablix, in this case, 2019.

In this case, I want to total(count) how many IncidentID's occur by grade for 2019 only.

Data set:

enter image description here

Desired Result:

enter image description here

  • Is counting IncidentID's (they are unique) the best way to total this?
  • What are some ways I can achieve the desired result in SSRS?

I'm not sure if I'm to filter on Group Properties and try to do a Max date, or to use some other function. Every year the dataset will have a new year added to it, the years are not hard coded.

Thank you.

JM1
  • 1,595
  • 5
  • 19
  • 41
  • I suppose you could set up an additional data set with the max year and use that as the source of a parameter which you could use to filter your report? – JonTout Oct 01 '18 at 15:33
  • Thanks @JonTout. There are elements of the report that require multiple years, and other elements that require only one year. The particular dataset I'm using has three years of data, but I only want it to show the most current year. Is that possible with SSRS? I thought Max{schoolyear] might work, but it's leaving me with no data when I try that expression. – JM1 Oct 01 '18 at 16:05
  • Does this report have multiple tables and just the one dataset? If you have multiple tables, you could filter with a static value, I don't think that you can use an aggregate as table filter. – JonTout Oct 01 '18 at 16:26
  • This report has multiple tables and multiple datasets. I think it needs to be filtered on the table, but I haven't been able to get it to work yet. Thanks for your help. – JM1 Oct 01 '18 at 17:27

3 Answers3

1

You can use an expression like this to get a conditional count:

=Count(IIf(Fields!SchoolYear.Value = Max(Fields!SchoolYear.Value, "GradeRowGroup"), 1, Nothing))

This will check the maximum year within each group. You can use the row group or the entire dataset for the scope in the Max function. The IIf statement returns a 1 for the rows where the school year matches the maximum value. Then the Count simply aggregates those.

StevenWhite
  • 5,907
  • 3
  • 21
  • 46
  • Thanks! A coworker worked out something very similar on the data bar chart data (listed below). Thank you for your explanation and the time you took to help! – JM1 Oct 02 '18 at 11:44
0

There are two practical ways I think:

You can make the School Year a parameter. If you want (by default) to open the actual year you can go to the School Year parameter Right Click > Parameter Properties > Default Values > Specify Values. There should be something like this:

[School Year].[Year].&[2018]

Change the above expression to the following:

="[School Year].[Year].&[" & CStr(Year(Now())) & "]"

This way your report opens by default with the actual School Year, and you can also chose other School Years for information. In the tablix just add your Count() and Sum() and everything else you need.

Or you can add a filter in your tablix (this is more static), something like:

'Expression
=Fields!SchoolYear.Value = Year(Now()) 'Integer
=Fields!SchoolYear.Value = CStr(Year(Now())) 'String
'Type
Boolean
'Value
True
Strawberryshrub
  • 3,301
  • 2
  • 11
  • 20
  • Thanks! This report has various tables and they use multiple data sets which contain various years data between them, so I'm not sure the parameter idea would work in this specific instance, thanks for taking the time to help me! – JM1 Oct 02 '18 at 11:46
0

A coworker helped me work it out. I am using a databar to so show the aggregate and he said to put an expression on the Chart Data and it worked.

=Sum(IIF(MAX(Fields!SchoolYear.Value)=Fields!SchoolYear.Value,Fields!NumDays.Value,0))
JM1
  • 1,595
  • 5
  • 19
  • 41