COUNT Expression in Report Author 10.2.1

[gosoccer]

Hi everyone,

I have a count expression within a Case When statement but I need to add a expression to it.

Please see below:

case
when [STATE].[TYPE_CD] = '7' then count([STATE].[STATE_DATA_REC_ID]) else 0
end

Now, I need to add the below to the above expression:
case
when [STATE].[TYPE_CD] = '7' then count([STATE].[STATE_DATA_REC_ID] FOR ONLY STATE='7') else 0
end

This way, I'm making sure the COUNT is for only STATE = 7

Please help if you can.

thx so much!! :) 8)

[Francis aka khayman]

is state = 7 equivalent to [state].[type_cd] = 7?

if so, better create another query filtered by type_cd = 7. then join back to your main query.

[Lynn]

Hi everyone,

I have a count expression within a Case When statement but I need to add a expression to it.

Please see below:

case
when [STATE].[TYPE_CD] = '7' then count([STATE].[STATE_DATA_REC_ID]) else 0
end

Now, I need to add the below to the above expression:
case
when [STATE].[TYPE_CD] = '7' then count([STATE].[STATE_DATA_REC_ID] FOR ONLY STATE='7') else 0
end

This way, I'm making sure the COUNT is for only STATE = 7

Please help if you can.

thx so much!! :) 8)

Another approach is to create an expression that returns the ID for what you want to count and null for what you don't want to count and set the aggregate function for the data item to count.

Code Select Expand



case
   when [STATE].[TYPE_CD] = '7'
   then [STATE].[STATE_DATA_REC_ID]
   else null
end



[gosoccer]

Thanks Khyman and Lynn,

Lynn,

I tried your approach first and it worked perfectly. I want to thank you
so much for your time. It means a lot to me. I identified a way last week
but the counts were coming very wrong.  :D :) ;)

[Lynn]

Thanks Khyman and Lynn,

Lynn,

I tried your approach first and it worked perfectly. I want to thank you
so much for your time. It means a lot to me. I identified a way last week
but the counts were coming very wrong.  :D :) ;)

You are most welcome. I'm glad it worked out.   :)

For future reference, you could also embed the case statement INSIDE the count function. I generally only do this if I need to specify the scope with a "for" clause. In that case the aggregate function of the query item would be automatic or possibly total (can't remember exactly what works best). This is sort of what you were attempting at first so thought you should know you were certainly on the right track in your thought process, but unless you need the "for" clause I think the first approach is easier.

Code Select Expand



count(
          case
             when [STATE].[TYPE_CD] = '7'
             then [STATE].[STATE_DATA_REC_ID]
             else null
          end
for [some other thing in your query]
)



[gosoccer]

Yeah! That looks like a really nice approach as well. I'll make a note of it.
If I ever can be a help, please let me know.
Thx,