Null values causing multiple rows

[jonnythesaint]

Hello

I'm new to the board but not new to Business Objects.

I can't, however, seem to fix this seemingly simple problem.

A date field within my universe must have multiple rows. One for each value and one to return a null value.

Therefore, when I attempt to build a variable to ask if a specific customer has this date or not, that customer returns both a yes and a no, therefore being incorrectly double counted when I apply a sum.

The customer should only be returned as a "yes" if the date exists.

How do I get this on one row please?

Thanks

Jonny

 

[blom0344]

You should not need to add Installation type to the group by as the column is not returned. If it does not work, then I suspect there is some setting to be changed. .prm files used to contain those settings, but I honestly do not know how this is regulated now. Perhaps Steve will kick in on this one..

Ties Blom

 

[jonnythesaint]

Yeah I had the same thoughts, but the error message specifically pointed to the fact that the Installation Type did not appear in "either an aggregate function or the GROUP BY clause".

I would argue that it DOES appear in an aggregate, but since this would be futile I resorted to hard coding it into the GROUP BY.

This seemed to work but still gave me incorrect reporting.

 

[blom0344]

True, if you add it, the aggregate will never roll up to one record, giving you multiple records for more than one type per customer.
Hard coding is a definite no-no, I'd say.
If you are on sql2005/sql2008 then OLAP functions are available. They really offer lots of benefits!

Ties Blom

 

[jonnythesaint]

Thanks again, I shall investigate.

Any pointers?

 

[blom0344]

http://www.sqlservercentral.com/articles/Development/2738/

for example.

you may need to register with SQLSERVERCENTRAL to see the article.
(If you post the entire SQL you have now, I will write the alternative..)

Ties Blom