SUM Function Expression

Question

SUM Function Expression

paula_sql

SSCrazy

Points: 2665
More actions
August 2, 2009 at 6:10 pm

#385099

Please help: Below is how my output currently looks. In the Cost Element Total group in July it shows a sum total of 18,950 when it should show 3,950 as the total of the 3 detail rows. Dec should show 5,000 as the group total and not zero. June should show10,000 as the group total and not zero. Instead, July's group total is 18,950, which is the total of all months. Below is the expression I have used for the Cost Element Total Group:
=iif(Fields!PERIODID.Value=1 and Fields!Year1.Value = Parameters!Year.Value,sum(Fields!Cost_Code_Actual_Cost.Value),"0")
Jul Dec June
Cost Element Total Group 18,950 0 0
Detail Row 1 3,950 0 0
Detail Row 2 0 5,000 0
Detail Row 3 0 0 10,000
PERIODID.Value = 1 refers to July. Only 3 months have been included in this example as the other months are all zero months.
Thanks in adavance for any help.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

bendy1999 SSC Eights! Points: 942 More actions · Answer 1

Hi Paula,

Do you use some kind of grouping to keep the Months together (e.g. Matrix) ? or are the values for the months in different columns in the Dataset?

What is the value in @YearValue? If it doesn't Identify the month as well I would guess that the statement you provided does as it should 🙂

This is just a shot in the blue. Please give some more Information.

manolomalaga SSCommitted Points: 1697 More actions · Answer 2

Hi, you could try CASE WHEN statement instead IFF and compare the execution time.

http://technet.microsoft.com/en-us/library/ms181765.aspx

paula_sql SSCrazy Points: 2665 More actions · Answer 3

Hi Thanks for the feedback. Reporting services doesn't support the CASE statement from my understanding. Is that your understanding?

bendy1999 SSC Eights! Points: 942 More actions · Answer 4

Within Reporting Services you can use Switch instead of Case. Functionality is pretty much the same.

paula_sql SSCrazy Points: 2665 More actions · Answer 5

OK. Thanks!! Could you show me how to convert my code to use the switch function?

The code expression is below:

=iif((Fields!PERIODID.Value=1) and (Fields!Year1.Value = Parameters!Year.Value),(sum(Fields!Cost_Code_Actual_Cost.Value,"Cost_Element_Group")),"0")

manolomalaga SSCommitted Points: 1697 More actions · Answer 6

Hi, here you have doc:

http://msdn.microsoft.com/en-us/library/ms157328.aspx

freezea SSC Enthusiast Points: 195 More actions · Answer 7

Hi, you can try RAQ Report. I'm using RAQ Report now. I find it very useful. It's a free java reporting tool. Besides, RAQ Report provides free reliable Doc, Demo, Forum, Email support. If you have report problem, you can turn to RAQ Report'Get Technical Support for help. RAQ Report has many functions as shown below:

You can download this free reporting tool at http://www.raqsoft.com.

Welcome to my blog[/url].:P

paula_sql SSCrazy Points: 2665 More actions · Answer 8

paula_sql

SSCrazy

Points: 2665

August 4, 2009 at 6:53 pm

#1034569

Thanks for all the feedback

bendy1999 SSC Eights! Points: 942 More actions · Answer 9

paula (8/4/2009)
=iif((Fields!PERIODID.Value=1) and (Fields!Year1.Value = Parameters!Year.Value),(sum(Fields!Cost_Code_Actual_Cost.Value,"Cost_Element_Group")),"0")

If I read your expression correctly, you have just one decision to make with two criteria. For that, the IIF-Function is just fine. But like I said before, you can't address a single field in an Aggregate-row.

Either you would need to make a calculated filed that you can sum afterwards. Or you move that calculation to the SQL-Statement.

Try this:

add this statement as Calculated Field to your Dataset:

=iif((Fields!PERIODID.Value=1) and (Fields!Year1.Value = Parameters!Year.Value),(Fields!Cost_Code_Actual_Cost.Value,"Cost_Element_Group")),cint("0"))

then add this to your Group-Footer

=sum(yourCalculatedField)

Just a shot in the blue, I couldn't test this.

paula_sql SSCrazy Points: 2665 More actions · Answer 10

That works just fine!! Thanks very much for your help!!

Robert.Smith-1001156 Mr or Mrs. 500 Points: 502 More actions · Answer 11

If the iif test is true, you're returning the sum over ALL rows. What I think you meant to do was to sum only those records which pass the iif test. In other words, instead of

iif(,sum(field),0)

...you might try...

sum(iif(,field,0))

Robb

paula_sql SSCrazy Points: 2665 More actions · Answer 12

paula_sql

SSCrazy

Points: 2665

August 6, 2009 at 5:17 pm

#1035730

OK Thanks