CODEWITHSUNDEEP
ssrs-2008-r2
Todd Orloff
Todd Orloff

Reputation: 1

SSRS Expression to count rows based on another rows values when you have 2 datasets

I have a stored procedure that returns values that I use in SSRS 2008R2. The report has 2 datasets, Dataset1 and Dataset2. On the report I count rows using an expression in a text box 

="Number of Events: " & Count(Fields!e_code.Value,"DataSet1")

That works fine.

I need to also count rows in another tetxbox based on when the value of another column that is called days in the stored procedure. I get that column by using

DATEDIFF(DAY,GETDATE(),hevents.e_nextdate) as Days

When I try to create the new textbox expression 

="Events Overdue: " & Sum((Fields!e_code.Value,"DataSet1")IIF(Fields!Days.Value "DataSet1")<0,1,0)

or

="Events Overdue: " & Sum(IIF(DateDiff("d",Fields!e_nextdate.Value,"DataSet1", Now())<0)

Either way I get an error

The value expression for the text box uses an aggregate expression without a scope
A scope is required for all aggregates used used outside of a data region unless the report contains one dataset.

Can someone please help explain where I am going wrong?

Upvotes: 0

Views: 836

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10870

You figured out it isn't going to work the way you want.

What you can try is to add a Calculated field to Dataset 1 with your expression and then SUM that new field.

=IIF(DateDiff("d", Fields!e_nextdate.Value, TODAY() ) < 0, 1, 0)

For the Expression:

="Events Overdue: " & Sum(Fields!CalcField.Value, "DataSet1")

Upvotes: 1

Related Questions