Post reply

Summing a calculated column

  • January 29, 2009 at 3:18 pm

    #204741

    I have a column, that depending on =iif returns a 0 or a 1.

    I want to be able to get a sum of these and put them in a Total box.

    I tried with reportitems, but it says I can't aggregate reportitems.

    Any help would be greatly appreciated.

    ps. I tried creating a field, but, I keep getting "report server error"

    Thank you in advance.

  • January 29, 2009 at 6:10 pm

    #935202

    You may want to look at the CASE Function in BOL

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/658039ec-8dc2-4251-bc82-30ea23708cee.htm

    In the lower portion of the page it shows how to use the CASE function to replace the IIF function available in Access.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • January 29, 2009 at 6:18 pm

    #935209

    bitbucket (1/29/2009)

    You may want to look at the CASE Function in BOL

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/658039ec-8dc2-4251-bc82-30ea23708cee.htm

    In the lower portion of the page it shows how to use the CASE function to replace the IIF function available in Access.

    My problem is not the iif. It works fine. My problem is how to sum a non field (calculated) column.

    thnx.

  • January 30, 2009 at 6:45 am

    #935488

    How about this.

    Instead of doing the =IIF logic in the text box on the Layout tab, manually add a calculated field to the dataset on the Data tab, using your =IIF logic there, and then you would just have another field from the dataset on which you can do the SUM.

  • January 30, 2009 at 12:29 pm

    #935786

    TKD-BB (1/30/2009)

    How about this.

    Instead of doing the =IIF logic in the text box on the Layout tab, manually add a calculated field to the dataset on the Data tab, using your =IIF logic there, and then you would just have another field from the dataset on which you can do the SUM.

    I tried that and I kept getting an internal report server error. But thanks for mentioning it.

    So the question remains. Can you sum a column that has values that are calculated (results) instead of being database fields???????

  • February 17, 2009 at 7:28 am

    #945184

    Not sure of your actual syntax, but summing a calculated value is very easy:

    EG:

    Calculated value = IIF(Field1.Value="Yes", 1, 0)

    The Total of this would be =SUM(IIF(Field1.Value="Yes",1,0))

    It's really as simple as that, wherever you place your sum dictates if you get a sub-total or a grand total.

    Good luck,

    Nigel.

    Nigel West
    UK

Viewing 6 posts - 1 through 5 (of 5 total)

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