[Mary Mallazzo]

Hello
I am trying to add a conditional total to a SSRS report. Regular hours total.  I created a calculated field “ST_Hrs” for when paytype = 1…then for the final total I created another calculated field “Total_ST_Hrs” defined as

=RunningValue(Fields!ST_Hrs.Value, Sum, Nothing)
but am receiving the error:
The expression used for the calculated field ‘Total_ST_Hrs” includes an aggregate, RowNumber,RunningValue,Previous or lookup function.
What is the correct way to accomplish this?

Thank you

——————————
Mary Mallazzo
Fromkin Brothers
Edison NJ
——————————

[Matthew Arp]

You can’t use an aggregate function in a calculated field. Try adding a field in your Table with the expression for Total_ST_Hours.

——————————
Matthew Arp
Business Systems Developer
Hunton Group
Houston TX
——————————
——————————————-

[Mary Mallazzo]

Matthew
I was able to add the totals by inserting a Matrix.

Thank  you

——————————
Mary Mallazzo
Fromkin Brothers
Edison NJ
——————————
——————————————-

[Charles Allen]

You don’t need a running sum unless you just want one.

 

I created a simple report that lists the data in UPR10302 and then has a sum at the bottom of the hours where the PAYTYPE = 1.

 

=SUM(iif(Fields!PAYTYPE.Value = 1,(Fields!TRXHRUNT.Value),0.00))

 

 

 

Charles Allen

Senior Managing Consultant | BKD, LLP

BKD Technologies

Office 713.499.4629

Cell 713.494.2104

 

 

****** BKD, LLP Internet Email Confidentiality Footer ******

Privileged/Confidential Information may be contained
in this message. If you are not the addressee indicated in
this message (or responsible for delivery of the message
to such person), you may not copy or deliver this message to
anyone. In such case, you should destroy this message, and
notify us immediately. If you or your employer do not consent
to Internet email messages of this kind, please advise us
immediately. Opinions, conclusions and other information
expressed in this message are not given or endorsed by my
firm or employer unless otherwise indicated by an authorized
representative independent of this message.

Any tax advice contained in the body of this email was not
intended or written to be used, and cannot be used, by the
recipient for the purpose of avoiding penalties that may be
imposed under the Internal Revenue Code or applicable state
or local tax law provisions.

These discussions and conclusions are based on the facts
as stated and existing authorities as of the date of this
email. Our advice could change as a result of changes in the
applicable laws and regulations. We are under no obligation
to update this information if such changes occur. Our advice
is based on your unique facts and circumstances as you
communicated them to us and should not be used or relied
on by anyone else.

——Original Message——

Hello
I am trying to add a conditional total to a SSRS report. Regular hours total.  I created a calculated field “ST_Hrs” for when paytype = 1…then for the final total I created another calculated field “Total_ST_Hrs” defined as

=RunningValue(Fields!ST_Hrs.Value, Sum, Nothing)
but am receiving the error:
The expression used for the calculated field ‘Total_ST_Hrs” includes an aggregate, RowNumber,RunningValue,Previous or lookup function.
What is the correct way to accomplish this?

Thank you

——————————
Mary Mallazzo
Fromkin Brothers
Edison NJ
——————————

[Blair Christensen]

For conditionals, use the IIF statement.  One thing to be careful of – the IIF will evaluate the entire expression so if (like me) you use it to try to avoid divide-by-zero issues, it can get a little messy.

The second question I would ask is why you are trying to use a running total.  If you use the grouping mechanisms you get subtotals on your groups and regular SUM() phrases get you totals.

(PS – if you really want running totals, you can do them in SSRS or you can simply use windowing functions in SQL which are very efficient.)

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-