September 23, 2009 at 12:44 pm
Hi Members,
I have a column named TotSamples in the report.In one of the groups am dispalying Sum(Fields!TotSamples.Value) for this column.
I need to dispaly a 0 when there's no data and show the sum(Fields!TotSamples.Value) when there's data.
=iif(Fields!TotSamples.Value=" ",0,Sum(Fields!TotSamples.Value))
The above expression dispalys 0 when there's no data but error when there's data.
Please suggest.
Thanks,
Nemo.
September 24, 2009 at 6:35 am
Hi Nemo,
Given the Fact that your expression displays 0 when there is no data shows, that the IIF-Clause works. Therefore the error must be in the Sum-Clause.
My first suggestion would be, that the data-type of Fields!TotSamples.Value is not "summable".
If you provide us with some sample data, then we could look into it deeper.
September 25, 2009 at 12:10 pm
Nemo - 4828 (9/23/2009)
Hi Members,=iif(Fields!TotSamples.Value=" ",0,Sum(Fields!TotSamples.Value))
The above expression dispalys 0 when there's no data but error when there's data.
.
Grashopper,
There's no fault in your sum clause , nor is there one with the iif statement,
Its just that you are trying to operate on a string value and at the same time on a numeric value field
when =" " <-- string portion
sum() <-- number function function
try this
=iif(Fields!TotSamples.Value=" ",0,Sum(cint(Fields!TotSamples.Value)))
also, depending on where this field is located, you have to be aware of the use of a dataset.
best is to go to the field and drag the action you want performed first,
cause the most sum() i already have seen in SSRS look something like this
sum(fieldname.value, datasetname)
Hope this helps,
Wkr,
Eddy
September 28, 2009 at 12:49 am
Hi Eddy
Good point, i missed the space in the Check clause. 🙂
best is to go to the field and drag the action you want performed first,
cause the most sum() i already have seen in SSRS look something like this
sum(fieldname.value, datasetname)
This happens if you drag a Dataset field to a textbox for example.
If you assign a Dataset to a Grid, then you can access it like Nemo described. You can change the DS-Assignment in the Properties of the Table.
Now back on topic...
@Nemo: this would confirm that your TotSamples.Value has a String Datatype and therefore you cannot perform arithmetic operations on that. Where does the blank space come from?
September 28, 2009 at 7:48 am
Its actually a null value..
September 28, 2009 at 7:58 am
are you setting that null value in your query or is it given from the database?
seems like you've got a space " " somewhere in that column, otherwise the IIF-Clause would never be true
September 28, 2009 at 8:19 am
its coming from the database..
September 28, 2009 at 8:46 am
if you can talk directly to the database, try doing a select on that field and filter out all nulls and then sort by that field. either at the beginning or at the end there should be some weird records.
MSSQL and especially Reporting services try to help a little too much by automatically ajusting the data type of a field to accommodate all values it contains. I've ran into problems like that myself because there was an old "test-Record" that survived the clean-up.
From what I know, there is no possibility to check what datatypes the fields in a dataset have, therefore its quite difficult to find out what it is.
Another way would be to use isnothing() in your field expression in design view. This only jumps at NULL-Values
September 28, 2009 at 11:36 am
Seems to me that your best bet would be to use the IsNumeric function in SSRS
this combined with the iif clause would work altimes even when there would be a NULL value returned
Wkr,
Eddy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply