Getting max value in ssrs

Question

I am trying to sum a total where the year is less than the max year.

=Sum(iif(Fields!YEAR.value < max(Fields!YEAR.value), Fields!Year_Sold.Value,0))

it runs but i am getting #ERROR is my syntax correct?

Hannover Fist · Accepted Answer · 2015-10-20 16:54:50Z

1

Try adding the dataset for the MAX - aggregate functions require the dataset so they get the max over the whole dataset and not the one record.

=Sum(IIF(Fields!YEAR.value < MAX(Fields!YEAR.value, "Dataset1"), Fields!Year_Sold.Value, 0))

answered Oct 20, 2015 at 16:54

Hannover Fist

10.9k1 gold badge21 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

D4TAM4X Over a year ago

I tried your suggestion and I'm still getting the #ERROR.

Martin Soles Over a year ago

Are you using BIDS (Visual Studio shell)? If so, are you getting any warnings in the Error List or the Output window?

D4TAM4X Over a year ago

I'm using report builder 3.o.

Hannover Fist Over a year ago

What is the data type of Year Sold? I was assuming INTEGER. If it's a decimal , try using CDEC(0) instead of 0. On another note, is that the right column - you're adding up your Year_Sold field?

D4TAM4X Over a year ago

Year_sold represent the number of items sold. and it is an integer

|

D4TAM4X · Accepted Answer · 2015-10-21 14:53:27Z

I got it to work by replacing the 0 with Nothing so here is what worked for me =Sum(iif(Fields!YEAR.Value < Parameters!EndYear.Value, Fields!Year_Sold.Value, Nothing))

Collectives™ on Stack Overflow

Getting max value in ssrs

2 Answers 2

9 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Related