Assistance with expressions using brackets

[que1983]

if([Pend Filter]=1) then
count([Presentation Layer].[PIPELINE POST FINAL ACTION].[Loan Number]) else (NULL)

I keep getting stumped with expressions with brakcets.  In this statement I want to say that if the pend_filter is 1 peform a count of the loan_number.  I keep moving the brackets but continue to get errors.  Any ides how I can correct this one and is there a preferred way to format your expressions to ensure your brackets are correct?

[bear412]

I always start by typing:
if ()
then ()
else ()

Then write the expression in the parenthesis.  Hope this helps!

[BigChris]

As Bear412 points out, you're missing some brackets:

if([Pend Filter]=1)
then (count([Presentation Layer].[PIPELINE POST FINAL ACTION].[Loan Number]))
else (NULL)

[Lynn]

I find case statements much more readable and happily devoid of all the pesky parentheses.

Also, I find that it generally works better to put the expression of what should be counted inside the count function, not surrounding it.

If the value of [Pend Filter] is based on a prompt response such that all loan numbers are either counted or none are counted, then I think the original expression as well as my suggestion would work the same.

If, however, [Pend Filter] is an attribute of a loan and can change from one record to the next, effectively dictating which loan numbers to count, then I'd go with putting the expression inside the count. Of course the inner portion could be either an "if" statement or a "case" since that is just a matter of preference.

Code Select Expand


count(
  case [Pend Filter]
      when 1
         then [Presentation Layer].[PIPELINE POST FINAL ACTION].[Loan Number]
      else null
  end
)


[BigChris]

Completely as an aside, how does Cognos handle counting NULLs? If it ignores them then your approach will work, but if it includes them then you'll just get a count of all the records. As a belt and braces approach, I'd be tempted to use

Code Select Expand

total(
  case [Pend Filter]
    when 1
      then 1
    else 0
  end
)

[Lynn]

Nulls are ignored in aggregate functions. I believe this is SQL behavior rather than a Cognos-ism.

The tricky part in your approach is how the auto group and summarize aspect of a query might affect things. It could be that just a single value of 1 comes back (for the granularity defined by the grouping) rather than a series of 1s based on the individual records in the source.

Actually we should peel back the layers all the way to the source database design. It is often useful to have a column on a fact table with a value of 1 on every row, specifically to simplify counts.