January 29, 2009 at 3:18 pm
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
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.
January 29, 2009 at 6:18 pm
bitbucket (1/29/2009)
You may want to look at the CASE Function in BOLms-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
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
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
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