Author |
Topic |
shm
Yak Posting Veteran
86 Posts |
Posted - 2008-12-15 : 08:03:36
|
hii have written a sp am calling that..wt is there is a col A which is value 0 and 1..relating to this there is one col B which stores the value 0,20.2,33.4,0..and so on wt is i have to the value of col A which has 0 and 1 value..and with the col B..colA colB0 01 100 01 301 400 00 0in the sp for colB some calculation for that value..whereever there is a value 1 then insert another Col C value otherwise 0now here i want max(colB),min(colB),avg(colB) where colA = 1for that in the layout i have given max(colB),min(colB),avg(colB)it il give correct data for max(colB) but for avg and min wt is giving for min(colB) = 0 and avg(colB)=80/7=11.1 something like thisbut i want max(colB)= 40 ,min(colB)=10 and avg(colB)= 80/3=26.6 like thisplease help me................am trying to get this but it is giving error |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-15 : 08:35:34
|
max(nullif(colB, 0)), min(nullif(colB, 0)), avg(1.0E * nullif(colB, 0)) E 12°55'05.63"N 56°04'39.26" |
|
|
shm
Yak Posting Veteran
86 Posts |
Posted - 2008-12-15 : 08:44:46
|
thank u but in the reporting service nullif is unrecognised identifier know..in the dataset i il get the colB from this in the layout i il take the max,min and avg...in the layout hw i shld get this |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-15 : 09:03:41
|
Use IIF instead of NULLIF. E 12°55'05.63"N 56°04'39.26" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-15 : 09:10:26
|
you should be using below:-for max of b=MAX(IIF(Field!colA.value=1,Field!colB.value,Nothing))for Min of b=MIN(IIF(Field!colA.value=1,Field!colB.value,Nothing))Avg of B=AVG(IIF(Field!colA.value=1,Field!colB.value,Nothing)) |
|
|
shm
Yak Posting Veteran
86 Posts |
Posted - 2008-12-15 : 23:47:42
|
hi,thank u visakh and peso thank u so much visakh.....it is working:-)but i tried in this way MAX(IIF(Field!colA.value=1,Field!colB.value,0)) it was giving errorMAX(IIF(Field!colA.value=1,Field!colB.value,Nothing)) hw if nothing is placed then it is coming correct..wt i understood is nothing will not take 0 only value of colB will take..wt i understood is correct?if am wrong please correct once again thank u..... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-16 : 00:07:05
|
quote: Originally posted by shm hi,thank u visakh and peso thank u so much visakh.....it is working:-)but i tried in this way MAX(IIF(Field!colA.value=1,Field!colB.value,0)) it was giving errorMAX(IIF(Field!colA.value=1,Field!colB.value,Nothing)) hw if nothing is placed then it is coming correct..wt i understood is nothing will not take 0 only value of colB will take..wt i understood is correct?if am wrong please correct once again thank u.....
ideally this should workMAX(IIF(Field!colA.value=1,Field!colB.value,0))what was error you got? |
|
|
shm
Yak Posting Veteran
86 Posts |
Posted - 2008-12-16 : 02:02:08
|
when i run the report, in the preview it is coming like this #error,,,for this col MAX(IIF(Field!colA.value=1,Field!colB.value,0)) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-16 : 02:09:37
|
what about this?=MAX(IIF(Val(Field!colA.value)=1,Val(Field!colB.value),0)) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-16 : 03:28:42
|
why invoke colA?=MAX(IIF(Field!colB.value > 0, Field!colB.value, NULL)) E 12°55'05.63"N 56°04'39.26" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-16 : 03:46:58
|
quote: Originally posted by Peso why invoke colA?=MAX(IIF(Field!colB.value > 0, Field!colB.value, NULL)) E 12°55'05.63"N 56°04'39.26"
that depends on requirement. what if he needs to include only cases where ColA=1. though as per sample op its equivalent to above, but we can never be sure that only non zero values will have colA value as 1 |
|
|
shm
Yak Posting Veteran
86 Posts |
Posted - 2008-12-16 : 03:55:59
|
i tried this also it is giving error=MAX(IIF(Val(Field!colA.value)=1,Val(Field!colB.value),0)) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-16 : 04:41:03
|
quote: Originally posted by shm i tried this also it is giving error=MAX(IIF(Val(Field!colA.value)=1,Val(Field!colB.value),0))
what are datatypes of colA & colB in table? |
|
|
shm
Yak Posting Veteran
86 Posts |
Posted - 2008-12-17 : 05:04:32
|
both are int datatype |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-17 : 05:33:08
|
and you're sure that you're giving this under grouping or in heading/footer? try one more variant also=MAX(IIF(Cint(Field!colA.value)=1,Cint(Field!colB.value),Cint(0))) |
|
|
|