SSRS Conditional Summing

reporting-services conditional sum
MikeTWebb picture MikeTWebb · Jan 18, 2011 · Viewed 37.3k times · Source

I have an SSRS report that displays several pages of rows. In each row is a "TYPE" field. In that TYPE field there is either an "M" for the value or a "P" for the value. At the end of the report I want to summ up all the price values for the "P" TYPES. I tried this but it prioduced an #Error:

=Sum(iif(Fields!TYPE.Value = "P",Fields!EXT_QTY.Value * Fields!PRICE.Value ,0))

this summed all rows

=iif(Fields!PART_TYPE.Value = "P" ,  Sum(Fields!EXT_QTY.Value * Fields!PRICE.Value ),  0 )

I'm sure this is do-able. Any ideas? Thanks

Answer

MikeTWebb picture MikeTWebb · Jan 18, 2011

Found the answer....

=SUM(IIF(Fields!PART_TYPE.Value ="P",CDbl(Fields!EXT_QTY.Value * Fields!PRICE.Value), CDbl(0.0)))

Related questions

The permissions granted to user ' are insufficient for performing this operation. (rsAccessDenied)"}

I created a report model using SSRS (2005) and published to the local server. But when I tried to run the report for the model I published using report builder I get the following error. Report execution error:The permissions granted …

Read More
How do I format date and time on ssrs report?

on SSRS report I need to show todays date and current time i tried this =FormatDateTime(Now,"MM/dd/yyyy hh:mm tt") but this is not working for me giving an error. Anyone please help me for expression ? I …

Read More
SQL variable to hold list of integers

I'm trying to debug someone else's SQL reports and have placed the underlying reports query into a query windows of SQL 2012. One of the parameters the report asks for is a list of integers. This is achieved on the report …

Read More