in the report am getting error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

in the report am getting error

Author

Topic

shm
Yak Posting Veteran

86 Posts

Posted - 2008-12-15 : 08:03:36

hi

i 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 colB
0 0
1 10
0 0
1 30
1 40
0 0
0 0

in the sp for colB some calculation for that value..whereever there is a value 1 then insert another Col C value otherwise 0
now here i want max(colB),min(colB),avg(colB) where colA = 1

for 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 this

but i want max(colB)= 40 ,min(colB)=10 and avg(colB)= 80/3=26.6 like this

please 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 error

MAX(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 error

MAX(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 work

MAX(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)))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources