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, 2012 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, 2012 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, 2012 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, 2012 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, 2012 at 09:42 PM tombiernacki
could you provide the expression for SumUNITS?
Sep 20, 2012 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, 2012 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, 2012 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:

x547
x15

asked: Sep 14, 2012 at 09:25 PM

Seen: 1798 times

Last Updated: Sep 24, 2012 at 04:02 PM