question

tombiernacki avatar image
tombiernacki asked

SSRS Expressions

HI Everyone, I have a bit of an issue coming up with an expression that if a =Fields!Sum_UNITS.Value, = 0 then it should return 0.00 otherwise it should do a Sum(Fields!AMOUNT.Value)/Sum(Fields!Sum_UNITS.Value, "ResidentDays") Here is what I tried and got an error...

=IIf(Fields!Sum_UNITS.Value."ResidentDays"= 0,"0.00", 
Sum(Fields!AMOUNT.Value)/Sum(Fields!Sum_UNITS.Value,"ResidentDays")")
The Error is... The Value expression for the text box ‘Textbox330’ refers to the field ‘Sum_UNITS’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case. I should also note that =Fields!Sum_UNITS.Value is part of one dataset and =Sum(Fields!AMOUNT.Value)/Sum(Fields!Sum_UNITS.Value,"ResidentDays") is part of another. All the help would be greatly appreciated
ssrsreport-builder
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tombiernacki avatar image
tombiernacki answered
=Fields!Sum_UNITS.Value is the expression and its taking values from ResidentDays Dataset
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for all the help and Ideas. I went a different route and found the answer. What I did was write custom code to devide the expressions from different datasets. .... Public Function DivideBy(ByVal Exp1, ByVal Exp2) If Exp2 = 0 Or IsNothing(Exp2) Or IsDBNull(Exp2) Then DivideBy = 0 Else: DivideBy = Exp1/Exp2 End If End Function Then on the textbox with the expression I put in.... =Code.DivideBy(Sum(Fields!AMOUNT.Value,"OperRev"),Sum(Fields!Sum_UNITS.Value, "ResidentDays")) That fixed my issue.
0 Likes 0 ·
jjaroska avatar image
jjaroska answered
Try this. ..... /Sum(First(Fields!Sum_UNITS.Value,"ResidentDays"))
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That unfortunately did not fix the error. I put =IIf(Fields!Sum_UNITS.Value."ResidentDays"= 0,"0.00", Sum(Fields!AMOUNT.Value)/Sum(First(Fields!Sum_UNITS.Value, "ResidentDays"))) and I receive the following error message. The Value expression for the textrun ‘Textbox330.Paragraphs[0].TextRuns[0]’ uses a First, Last or Previous aggregate in an outer aggregate. These aggregate functions cannot be specified as nested aggregates.
0 Likes 0 ·
could you provide the expression for SumUNITS?
0 Likes 0 ·
jjaroska avatar image
jjaroska answered
Ok. I missed that on the original post. As of right now, SSRS can't do an expression with fields from 2 different datasets. What I would do is to move those calcs to the stored procedure or view and the send that new field through to the report.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tombiernacki avatar image
tombiernacki answered
Thanks for all the help and Ideas. I went a different route and found the answer. What I did was write custom code to devide the expressions from different datasets. .... Public Function DivideBy(ByVal Exp1, ByVal Exp2) If Exp2 = 0 Or IsNothing(Exp2) Or IsDBNull(Exp2) Then DivideBy = 0 Else: DivideBy = Exp1/Exp2 End If End Function Then on the textbox with the expression I put in.... =Code.DivideBy(Sum(Fields!AMOUNT.Value,"OperRev"),Sum(Fields!Sum_UNITS.Value, "ResidentDays")) That fixed my issue.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.