SSRS Expressions

Question

question

tombiernacki asked Sep 14, '12

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

ssrs report-builder

10 |1200

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

Answer 1 · 2012-09-21T15:58:36Z

tombiernacki answered Sep 21, '12

=Fields!Sum_UNITS.Value is the expression and its taking values from ResidentDays Dataset

1

10 |1200

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

tombiernacki commented · Sep 24, 2012 at 04:02 PM

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 ·

Answer 2 · 2012-09-19T17:52:38Z

jjaroska answered Sep 19, '12

Try this. ..... /Sum(First(Fields!Sum_UNITS.Value,"ResidentDays"))

2

10 |1200

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

tombiernacki commented · Sep 20, 2012 at 09:42 PM

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 ·

jjaroska commented · Sep 20, 2012 at 10:31 PM

could you provide the expression for SumUNITS?

0 ·

Answer 3 · 2012-09-21T16:20:27Z

jjaroska answered Sep 21, '12

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

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

Answer 4 · 2012-09-24T16:01:18Z

tombiernacki answered Sep 24, '12

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

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

question