x

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

more ▼

asked Sep 14 '12 at 09:25 PM in Default

tombiernacki gravatar image

tombiernacki
338 18 20 23

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first
=Fields!Sum_UNITS.Value is the expression and its taking values from ResidentDays Dataset
more ▼

answered Sep 21 '12 at 03:58 PM

tombiernacki gravatar image

tombiernacki
338 18 20 23

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.

Sep 24 '12 at 04:02 PM tombiernacki
(comments are locked)
10|1200 characters needed characters left
Try this. ..... /Sum(First(Fields!Sum_UNITS.Value,"ResidentDays"))
more ▼

answered Sep 19 '12 at 05:52 PM

jjaroska gravatar image

jjaroska
170 2

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.
Sep 20 '12 at 09:42 PM tombiernacki
could you provide the expression for SumUNITS?
Sep 20 '12 at 10:31 PM jjaroska
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Sep 24 '12 at 04:01 PM

tombiernacki gravatar image

tombiernacki
338 18 20 23

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Sep 21 '12 at 04:20 PM

jjaroska gravatar image

jjaroska
170 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x536
x15

asked: Sep 14 '12 at 09:25 PM

Seen: 1487 times

Last Updated: Sep 24 '12 at 04:02 PM